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
 

 

What does NOT NULL WITH DEFAULT both in a same field

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

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Jun 02, 2006 4:39 pm    Post subject: What does NOT NULL WITH DEFAULT both in a same field
Reply with quote

Hi all,

I have a doubt in the following case:
Code:

  CREATE TABLE XX
  ( NAME CHAR(30) NOT NULL WITH DEFAULT);


I have seen a table like this . i cannot understand what does NOT NULL WITH DEFAULT both in a same field. Plz have a analze and let me know.
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Jun 02, 2006 7:48 pm    Post subject: Re: Doubt
Reply with quote

This is from the IBM DB2 manual, Hope it helps


NOT NULL WITH DEFAULT
Is used for a column that does not allow null values, but provides a default value.


DEFAULT
The default value assigned to the column in the absence of a value specified on INSERT or LOAD. Do not specify DEFAULT for a ROWID column or an identity column (a column that is defined AS IDENTITY); DB2 generates default values. If a value is not specified after DEFAULT, the default value depends on the data type of the column, as follows:
Data Type Default Value
Numeric
0
Fixed-length string
Blanks
Varying-length string
A string of length 0
Date
CURRENT DATE
Time
CURRENT TIME
Timestamp
CURRENT TIMESTAMP
Distinct type
The default of the source data type


Dave
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Mon Jun 05, 2006 6:56 pm    Post subject: doubt
Reply with quote

Dave,

Thanks for you help.... i still have some clarification i will get back soon.
Back to top
View user's profile Send private message
Sahoo007

New User


Joined: 29 Jun 2006
Posts: 1

PostPosted: Thu Jun 29, 2006 11:40 am    Post subject:
Reply with quote

It is ok. But could you please tell what does the following line means?

Varying-length string
A string of length 0
Back to top
View user's profile Send private message
venktv

New User


Joined: 29 Mar 2006
Posts: 59
Location: Montreal

PostPosted: Thu Jun 29, 2006 12:35 pm    Post subject: Re: What does NOT NULL WITH DEFAULT both in a same field
Reply with quote

Hi
NULL means not a 0 or space, it means a unknown value...so NOT NULL WITH DEFAULT means, the entry should contains the data type default value like ZERO or spaces rather than having UNKNOWN VALUE.

hope this helps
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Thu Jun 29, 2006 12:41 pm    Post subject: DB2 Query
Reply with quote

Hi all,

Here i m clear with this. while altering a table with the above syntax ie., Not Null with default... It's giving error like the table cannot be altered. Can anyone try and let me know.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Thu Jun 29, 2006 9:35 pm    Post subject:
Reply with quote

Hi Khamaurutheen,

By using alter table statement; you can?t change the data type of the existing column of the table. But you can add the new column like this


Code:
ALTER TABLE EMPLOYEE                 
ADD EMPNAME2 CHAR (10) NOT NULL WITH DEFAULT;

The above statement successfully executed fine.

Actually value of new column should contain space after execution of above statement.


Note:

Code:
ALTER TABLE EMPLOYEE                 
ADD EMPNAME2 CHAR (10) NOT NULL;


It will give error .Because it is not able to assign any value (due to NOT NULL) for new column for existing rows in the table. So you will have to give NOT NULL WITH DEFAULT. Now the SPACE value will be assigned to new column for existing rows in the table.


Please correct me I am wrong

Regards,
Senthil
Back to top
View user's profile Send private message
Brian's_song

New User


Joined: 27 Dec 2005
Posts: 21

PostPosted: Sat Jul 01, 2006 11:06 am    Post subject: Re: What does NOT NULL WITH DEFAULT both in a same field
Reply with quote

Hi,
NOT NULL ==> The Field can't be 'NO' value, you should be always input data in these fields,or else it will return error code.

NOT HULL WITH DEFAUT ==> The field can't be 'NO' value, if you forget to input the value in the field,it will set the field value as its default vale,such as zero for num,space for char,00-00-00 for date.....

Above is only base on my perdonal experience.Pls correct me if wrong.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Jul 04, 2006 1:20 pm    Post subject: DB2 Doubt
Reply with quote

Hi all,

Thanks for your clarifications. I got the Point.....
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm


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