Unable to use the APPEND procedure in a DB2 table with a BIGINT column


Messages similar to the following might occur when you attempt to append into a DB2 table with a BIGINT column

WARNING: Variable CONT_ID not appended because of type mismatch.
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0407N  
Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=264, COLNO=0" is not allowed.
SQLSTATE=23502

To work around this issue, use the DBSASTYPE= data set option to allow the loading of a large character field into a BIGINT data type in DB2.

proc append base=db2lib.cwdbigint(dbsastype=(col1='char(20)')) data=foo; run;

This appends a SAS data set into a DB2 table. DBSASTYPE= simply changes the description of a table. Traditionally, DBSASTYPE= is used only in reading data, but in this situation, it can be used for appending to a database table.