How to find table in tbspaceid=2 and tableid=1583?

grofaty · #1 (**permalink**) 04-14-10, 08:51

Hi,
running DB2 v9.5 on Linux I get the following error:

Code:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0723N  An error occurred in a triggered SQL statement in trigger
"DB2INST1.TRIGG_I".  Information returned for the error includes SQLCODE
"-407", SQLSTATE "23502" and message tokens "TBSPACEID=2, TABLEID=1583,
COLNO=3".  SQLSTATE=09000

If I understand correctly, trigger with name DB2INST1.TRIGG_I reports that trigger tries to insert a null value in not null column. But which table is TABLEID=1583 and which columns is COLNO=3? Can you please help me out which table and which column is violating this trigger rule? Is there a system table with above info?
Thanks

Cougar8000 · #2 (**permalink**) 04-14-10, 08:58

Have you looked at SYSCAT schema views? Specifically TABLES and COLUMNS.

You are correct on what trigger is doing.

MarkhamDBA · #3 (**permalink**) 04-14-10, 10:08

you can find all information on the trigger (incl. text of create trigger statement) in table syscat.triggers. then you will know what exactly the trigger is doing (table(s) and col(s) names).

ARWinner · #4 (**permalink**) 04-14-10, 10:27

Quote:

Originally Posted by grofaty

Hi,

If I understand correctly, trigger with name DB2INST1.TRIGG_I reports that trigger tries to insert a null value in not null column. But which table is TABLEID=1583 and which columns is COLNO=3? Can you please help me out which table and which column is violating this trigger rule? Is there a system table with above info?
Thanks

If you look up the full description for the -407 SQLCODE (SQL0407N), it will give you a query that will give you the information you need.

Andy

grofaty · #5 (**permalink**) 04-14-10, 12:21

ARWinner,
it looks like I should read error description more carefully

Code:

db2 ? sql407n

suggest to use the following query:

Code:

SELECT C.TABSCHEMA, C.TABNAME,
C.COLNAME
FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C
WHERE T.TBSPACEID = 2
AND T.TABLEID = 1583
AND C.COLNO = 3
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME

BTW, just can't understand why the error message does not already outputs this info in the first place.
Thanks