Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Insert on NOT NULL column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pecram24

New User


Joined: 12 Oct 2009
Posts: 35
Location: Chennai

PostPosted: Mon Apr 05, 2010 12:17 pm    Post subject: Insert on NOT NULL column
Reply with quote

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.
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Apr 05, 2010 12:41 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
pecram24

New User


Joined: 12 Oct 2009
Posts: 35
Location: Chennai

PostPosted: Mon Apr 05, 2010 1:09 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Apr 05, 2010 1:18 pm    Post subject:
Reply with quote

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 .
Back to top
View user's profile Send private message
pecram24

New User


Joined: 12 Oct 2009
Posts: 35
Location: Chennai

PostPosted: Mon Apr 05, 2010 1:27 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Apr 05, 2010 1:31 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
pecram24

New User


Joined: 12 Oct 2009
Posts: 35
Location: Chennai

PostPosted: Mon Apr 05, 2010 2:20 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Apr 05, 2010 2:23 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us