Insert on NOT NULL column

pecram24 · New User Joined: 12 Oct 2009 Posts: 35 Location: Chennai

Hi,

I am having a modified table ( i.e. an existing tables is modified by adding few new fields...)
The new columns are defined as DATE NOT NULL... in the CREATE table statement....

I was trying to insert a record, with values for all the columns except the new columns.. (NOT NULL columns)..
The INSERT was successful. The new columns are having a default date value.

Please tell whether my below assumption is correct or NOT...The new fields should have been defined with the default constraint...so that the insert operation was successful even there where no values for the new columns...

i have one more doubt also...in the case of loading data into the new table, the load operation was failed as there were no values for the new fields in the load file.

What could be the reason for this?

Thanks in advance..
sorry if the above question is already posted in the forum.

guptae · Posted: Mon Apr 05, 2010 12:41 pm

Hello There,

As you specified that new columns are defined as NOT NULL Columns so you need to specify them while insert or load if default values are not specified.

Would you kindly paste the DDL for your table which will enable us to guide you better?

pecram24 · New User Joined: 12 Oct 2009 Posts: 35 Location: Chennai

Hi,

I am new to these things..
Could you please tell where i can find the DDL.
and how can i check whether the new columns are defined with default values or NOT...

guptae · Posted: Mon Apr 05, 2010 1:18 pm

Hello Pecram,

You can get the DDL using Db2 admin tool if you have access . Otherwise
check SYSIBM.SYSCOLUMNS table that have coulmn NULLS to identify whether column can contain NULL or not and Column DEFAULT to specify default values .

pecram24 · New User Joined: 12 Oct 2009 Posts: 35 Location: Chennai

hi,

As i suspect the new columns are having default values in the SYSIBM.SYSCOLUMNS table..

but now i need to check the DDL once..
What is the DB2 admin tool....could you please give some detail info on this...

Thanks

guptae · Posted: Mon Apr 05, 2010 1:31 pm

Hello Prem,

Whats is the value of DEFAULT COLUMN in SYSIBM.SYSCOLUMNS table? Please check DEFAULTVALUE column as well? If you provide this information will be able to guide you.

You can get the ddl using db2 admin tool if you have access to that but I dont need DDL if you provide me above info.

pecram24 · New User Joined: 12 Oct 2009 Posts: 35 Location: Chennai

Hi,

The DEFAULTVALUE is same as that of the values populated for the new columns..

why i am aking for the DDL is to confirm that the new fields were defined using NOT NULL DEFAULT constraints....

guptae · Posted: Mon Apr 05, 2010 2:23 pm

Hello Prem,

SYSIBM.SYSCOLUMNS contain detail according to DDL but if you dont have db2 admin tool just get the defination with help of DBA at ur site