Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,595

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

    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

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Have you looked at SYSCAT schema views? Specifically TABLES and COLUMNS.

    You are correct on what trigger is doing.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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).
    Last edited by MarkhamDBA; 04-14-10 at 10:12.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jan 2003
    Posts
    4,126
    Quote Originally Posted by grofaty View Post
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    1,595
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,126
    Quote Originally Posted by grofaty View Post
    BTW, just can't understand why the error message does not already outputs this info in the first place.
    Thanks
    It probably is that at the package level, DB2 only works with the IDs and not the names, or something like that.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •