Find the column causing a DB2 import to fail

Ever run DB2 IMPORT and get a super helpful error like…

SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=7,
TABLEID=265, COLNO=2" is not allowed.  SQLSTATE=23502

SQL3185W  The previous error occurred while processing data from row "126" of 
the input file.

After you calm down from wanting to smack a DB2 developer in the face, remember the syscat tables and run this SQL to find out the table and column giving you grief…

select  * 
from    syscat.columns c
            inner join syscat.tables t
                on  c.TABSCHEMA = t.TABSCHEMA
                    and c.TABNAME = t.TABNAME
where   c.COLNO = 2 
        and t.TABLEID = 265
        and t.TBSPACEID = 7

Leave a Reply