If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > How to find table in tbspaceid=2 and tableid=1583?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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).
__________________
DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Last edited by MarkhamDBA; 04-14-10 at 10:12.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On