DB2 INSERT to nullable column with default results in Assignment
of a NULL value to a NOT NULL column error
Symptom
Upgraded the DB2 database from ZOS to LUW.
Performing an INSERT on a table that has a column that allows
nulls and is defined WITH DEFAULT ‘n’ results in the following
error:
|
1 2 |
(4515440): *** ERROR -407 ***(rc -1) : SQLSTATE = 23502 [IBM][CLI Driver][DB2/NT64] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=60, TABLEID=2177, COLNO=5" is not allowed. SQLSTATE=23502 |
Environment
PowerBuilder
Reproducing the Issue
Do the following to reproduce this issue:
1. Set up pbtrace on the connection to the database. This can be
done either of the following two ways:
A. Add TRACE to the database connection parameters:
SQLCA.DBMS=”TRACE ODBC”
B. Select “Generate Trace” in the Connection tab of the database
profile setup window in PowerBuilder:
2. Set the database parameter DisableBind=1.
3. PBSupportBindUpdate=’YES’ is entered in the appropriate DB2
section of the pbodbxxx.ini file. For example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
; IBM DB2/NT 2.1 DB2CLI [DB2/NT] PBCatalogOwner='PBCATOWN' PBSyntax='DB2NT_SYNTAX' PBDateTime='ISO_DATETIME' PBFunctions='DB2CS_FUNCTIONS' DelimitIdentifier='YES' PBSystemOwner='SYSIBM,SYSSTAT,SYSCAT' IdentifierCase=3 PBSupportBindSelect='NO' PBSupportBindUpdate='YES' PBSupportDBBind='NO' BlobSegmentSize=32767 ForeignKeyDeleteRule='Disallow if Dependent Rows Exist (RESTRICT),Delete any Dependent Rows (CASCADE),Set Dependent Columns to NULL (SET NULL)' TableListType='ALIAS' Block=1 |
4. Test the INSERT and it will fail with the Assignment of a NULL
value to a NOT NULL column error message.
Cause
This problem is caused by the database trying to use bind
variables because PBSupportBindUpdate=’YES’ is in the pbodbxxx.ini
file.
Solution
In most cases setting PBSupportBindUpdate=’NO’ in the
pbodbxxx.ini should solve the problem. However, there may be times when
it doesn’t. For those instances do the following:
Add DescribeParm=0 to the db2cli.ini file.