Brian Brian - 1 year ago 206
SQL Question

Setting Timestamp column to NULL on Insert In DB2

I'm using DB2 version 10. I have a table with a timestamp for updates defined for me as follows in the Create Table statement :

Update_Timestamp For Column D2TIMSTU Timestamp DEFAULT NULL

On initial load of the data to this table, I want to set the value of this column to NULL for all rows, but I have not been successful. I have tried specifying NULL as the insert value and I have even tried specifying DEFAULT.

Both attempts gave the same error :

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable DEFAULT not found. Cause . . . . . . : DEFAULT was not found as a column of table *N in *N and was not found as a global variable in *N.

If the table is *N, DEFAULT is not a column of any table or view that can be reference

Same error for NULL - replacing NULL for DEFAULT in the error message.

I know I can get around this by just not specifying the column name and value, but I'd really like to know why this won't work.

Answer Source

First, are you sure that col3 in the destination table is allowed to accept NULL values ?

If so, try something like this (I cant test--I dont have access to a DB2 system anymore)

INSERT INTO [Table Name](col1, col2, col3)SELECT Value1, Value2, 
    cast(NULL as timestamp) FROM [Source Table]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download