Topic
  • 5 replies
  • Latest Post - ‏2013-07-01T15:07:43Z by DougBreaux
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

‏2013-01-03T15:06:07Z |
We have a couple of instances on a DB2 9.5 server which are reporting SQL407 (NULL value inserted into NOT NULL column), all of a sudden, on multiple tables. This seems to be occurring only on NOT NULL WITH DEFAULT columns, whether or not we specify a value for those columns.

The same table definitions in another instance on the same server are fine, and these tables/instances were fine on 12/31 but not on 1/1. We don't believe anything was deliberately done to the databases, tables, or instances, and upon restarting the instances there is nothing notable logged to db2diag.log.

I think we're going to have to open a PMR with IBM Support, but I thought I'd ask around to see if anyone has seen anything like this before. It's acting like some kind of corruption.

db2 describe table transaction

Data type Column
Column name schema Data type name Length Scale Nulls

---------
----------
------
TRANSACTION_ID SYSIBM CHARACTER 17 0 No
ID SYSIBM CHARACTER 9 0 Yes
CONFIRMATION_NUMBER SYSIBM CHARACTER 17 0 Yes
ADDR_CHANGE SYSIBM CHARACTER 1 0 Yes
OFFICE_ID SYSIBM CHARACTER 8 0 No
TRANSACTION_DATE SYSIBM TIMESTAMP 10 0 No
From db2look:

  • This CLP file was created using DB2LOOK Version "9.5"
  • Timestamp: Thu Jan 3 08:45:05 CST 2013
  • Database Name: xxx
  • Database Manager Version: DB2/AIX64 Version 9.5.5
  • Database Codepage: 819
  • Database Collating Sequence is: UNIQUE
CONNECT TO xxx;


  • DDL Statements for table "yyy "."TRANSACTION"

CREATE TABLE "yyy "."TRANSACTION" (
"TRANSACTION_ID" CHAR(17) NOT NULL ,
"ID" CHAR(9) ,
"CONFIRMATION_NUMBER" CHAR(17) ,
"ADDR_CHANGE" CHAR(1) ,
"OFFICE_ID" CHAR(8) NOT NULL WITH DEFAULT '' ,
"TRANSACTION_DATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP )
IN "USERSPACE1" ;
  • DDL Statements for primary key on Table "yyy "."TRANSACTION"

ALTER TABLE "yyy "."TRANSACTION"
ADD PRIMARY KEY
("TRANSACTION_ID");

All 3 of these commands:

db2 "insert into transaction (transaction_id) values ('dougtest1')"
db2 "insert into transaction (transaction_id,office_id) values ('dougtest1','xx')"
db2 "insert into transaction (transaction_id,office_id) values ('dougtest1',DEFAULT)"

return the same error:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,TABLEID=110, COLNO=5" is not allowed. SQLSTATE=23502
Updated on 2013-01-03T21:03:22Z at 2013-01-03T21:03:22Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

    ‏2013-01-03T15:08:06Z  
    Ok, colleague pointed out that the column numbering starts with zero, so I tested these additional inserts with the same results:

    db2 "insert into transaction (transaction_id,transaction_date) values ('dougtest1',current timestamp)"
    db2 "insert into transaction (transaction_id,transaction_date) values ('dougtest1','2013-01-03-10.06.28.511873')"
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

    ‏2013-01-03T15:35:00Z  
    Further, a new table created from the existing one, in the same tablespace, does not exhibit the problem.

    db2 create table doug.transaction like yyy.transaction in userspace1
  • mwandishi
    mwandishi
    47 Posts

    Re: DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

    ‏2013-01-03T16:54:07Z  
    Further, a new table created from the existing one, in the same tablespace, does not exhibit the problem.

    db2 create table doug.transaction like yyy.transaction in userspace1
    Hi Doug,

    Very odd. Is it always COLNO=5 ( OFFICE_ID ) that's the subject of the SQL0407 ? Can you dump the PD data and send it to me please ( levetts@uk.ibm.com )

    db2cat -d <dbname> -s yyy -n transaction -o /tmp/transaction_yyy.out

    Can you repeat for doug.transaction:

    db2cat -d <dbname> -s doug -n transaction -o /tmp/transaction_doug.out

    I would concur, getting a PMR open is a good idea.

    Bets regards,
    Stephen Levett.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

    ‏2013-01-03T21:03:22Z  
    Alright, to close the loop, it was user error and a confusing DB2 error message.

    Unbeknownst to me (data designer out on vacation), we have a trigger on timestamp insertion that relies on some external data which hadn't been populated for 2013.
  • DougBreaux
    DougBreaux
    1 Post

    Re: DB2 incorrectly reporting SQL407 (NULL value in NOT NULL column)

    ‏2013-07-01T15:07:43Z  

    (Replying so I'll be able to search and locate this thread again in the future by my name/id.)