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