Tag Archives: debugging

DB2 Duplicate Key Debugging

If you’ve ever gotten a DB2 error like this…

SQL0803N  One or more values in the INSERT statement, UPDATE statement, 
or foreign key update caused by a DELETE statement are not valid because 
the primary key, unique constraint or unique index identified by "1" 
constrains table "XX.TABLE_NAME" from having duplicate values for 
the index key.  SQLSTATE=23505

You may be asking yourself, but exactly which unique index is causing the problem?

Use the info from the error along with this query to find out:

SELECT *
FROM   SYSCAT.INDEXES
WHERE  IID = 1
       AND TABSCHEMA = 'XX'
       AND TABNAME = 'TABLE_NAME';

Of course you will want to replace the WHERE clause conditions with your values.

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

Resolving Ant javac “class file has wrong version” issues

Ever run an ANT build in eclipse and it works just fine, but as soon as you run it outside of eclipse you get something like…

class file has wrong version 51.0, should be 49.0

The issue is likely that you need to “fork” in your javac task…

Before (with error)

javac srcdir="${build.path}" 
   verbose="true" 
   includeantruntime="false" 
   includejavaruntime="false"

After (no error)

javac srcdir="${build.path}" 
   verbose="true" 
   includeantruntime="false" 
   includejavaruntime="false" 
   fork="yes"

Hopefully this can save you about 2 hours some day!