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:

(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.  Selecting Generate Trace in the database profile in the 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:

; 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.

Resolution

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:

1.  Add DescribeParm=0 to the db2cli.ini file.

1
1