Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us