In error message display actual table's column name that is violating NOT NULL definition instead of outputing "TBSPACEID=2, TABLEID=5, COLNO=0"

See this idea on ideas.ibm.com

I am constantly running into this problem when inserting null values into not null table column. Getting out tablespaceid, tableid and colno is time consuming and annoying to constantly check the column name that is defined not null, but null values are inserted. Very simple sample to demonstrate:

db2 "create table test.mytab (a int not null)"
db2 "insert into test.mytab values (null)"

Error is returned: SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=5, COLNO=0" is not allowed. SQLSTATE=23502

From help there is info to execute command:

SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME FROM SYSCAT.TABLES AS T, SYSCAT.COLUMNS AS C WHERE T.TBSPACEID = n1 AND T.TABLEID = n2 AND C.COLNO = n3 AND C.TABSCHEMA = T.TABSCHEMA AND C.TABNAME = T.TABNAME

I need to type in n1, n2 and n3 values and execute the SQL, to get TEST.MYTAB.A has NOT NULL definition, but NULL is inserted into it.

My idea: Please instead of writing TABLESPACEID=2, TABLEID=5, COLNO=0 write actual column name. In this case please output error message: SQL0407N Assignment of a NULL value to a NOT NULL column TEST.MYTAB.A is not allowed. SQLSTATE=23502

Needed By

Not sure -- Just thought it was cool

Post comment

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

In error message display actual table's column name that is violating NOT NULL definition instead of outputing "TBSPACEID=2, TABLEID=5, COLNO=0"