Insert missing values into DB2

[Rikard]

In a DB2 database I have a table with the following attributes:

NAME     TYPE    NULLABLE DEFAULT
CHAR_COL CHAR    N
NUM_COL  INTEGER N        1

I.e. the two columns cannot be empty and the NUM_COL should be assigned the default value 1 if a missing value is inserted. If I want to insert data into this table from SAS I expect rows where CHAR_COL is missing to be rejected and rows where NUM_COL is missing should be accepted and assign the default value 1.

If using the INSERT statement I can insert an empty string into a character column even if this is against the DB2 constrains. I assume this is due to missing value for a character column is ‘ ‘ (a blank space) and not a NULL value in SAS. This method can however assign the default value to column NUM_COL.

data to_be_rejected;
	char_col = '';
  	num_col = 123;
	output;
run;

libname db2db db2 datasrc=xxx user=xxx password=xxx schema='xxx';

proc sql noprint; 
	insert into db2db.db2table(char_col,num_col)
	select char_col,num_col from work.to_be_rejected;
quit;

If using the APPEND statement DB2 does not accept empty character values but does not assigned missing values the default value.

data to_be_accepted;
	char_col = 'aaa';
  	num_col = .;
	output;
run;

proc datasets nolist;
	append base=db2db.db2table data=work.to_be_accepted nowarn force;
run;quit;

How can I insert data into this table using a method that both reject missing character values and let DB2 assign the default value?

[LinusH]

But in your APPEND example the char_col has a value...?

I think that NUM and char MISSING conversions is handled differently, since char MISSING is in fact a space, a valid char value. But the NUM MISSING can't be interpreted into a valid numerical value.

I haven't worked with default constraints in DB2 (only in SQL Server), and then the case that the DBMS always set the default value. In your case it sounds like you are trying to do a coalesce() type of logic. So have you tried the same logic within DB2?

[Rikard]

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. 

[LinusH]

If that's the case, it's sounds like the situation I described.

We solved that issue by setting IGNORE_READ_ONLY_COLUMNS=YES, which in out case made the columns under automatic constraints be invisible for the SAS client, and not regarded during insert/append operations.