In the APPEND example I would have expected the data to be inserted successfully (since CHAR_COL has a value and NUM_COL should be assign it's default value). But this append fails since NUM_COL is missing.
NOTE: Appending WORK.TO_BE_ACCEPTED to DB2DB.DBTABLE
NOTE: There were 1 observations read from the data set WORK.TO_BE_ACCEPTED.
NOTE: 0 observations added.
NOTE: The data set DB2DB.DBTABLE has . observations and 2 variables.
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0407N Assignment of a NULL value to a
NOT NULL column "TBSPACEID=-6, TABLEID=-32265, COLNO=1" is not allowed. SQLSTATE=23502
NOTE: Statements not processed because of errors noted above.
Using this method, empty character values can be handled using e.g. the NULLCHAR= data set option.
Even if I don't supply the num_col column DB2 does not assign the default value
data to_be_accepted;
char_col = 'aaa';
output;
run;
proc datasets nolist;
append base=db2db.db2table data=work.to_be_accepted nowarn force;
run;quit;
I simply want to insert one character value into a two-columns table and letting the database set the other column's value to a default value.
... View more