How to find table and column in DB2 with tbspaceid tableid specified in error message

database db2 tablespace
Mathias G. picture Mathias G. · Jan 4, 2017 · Viewed 23.1k times · Source

I get following error message when trying to insert an object in the database:

com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: 
DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2,
    TABLEID=19, COLNO=0, DRIVER=4.15.134

How can I retrieve the table/column name for which the error is thrown?

Answer

Mathias G. picture Mathias G. · Jan 4, 2017

Apparently at the package level, DB2 only works with the IDs and not the names.

You can find them back using the following query:

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

Related questions

Difference between CLOB and BLOB from DB2 and Oracle Perspective?

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows : BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such …

Read More
How to update multiple columns in single update statement in DB2

I want to update multiple columns of a table in DB2 with single Update statement. Any hint or idea will be appreciable. Thanks.

Read More
Calculating how many days are between two dates in DB2?

I need to get the difference in days between two dates in DB2. I tried a couple of different queries but nothing seems to be working. So basically what i need to get is something like this. SELECT DAYS (CURRENT …

Read More