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
 

 

Loading a new column into DB2 Table with Default value

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

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Tue Jul 03, 2007 3:34 am    Post subject: Loading a new column into DB2 Table with Default value
Reply with quote

Hi,

I run load the data from sequential file into DB2 by using LOAD Jcl.

Please help me whether I can use the below scenario

The length of the input file is 374 .I have added a new column onto the DB2 table at position 375 which is not present in the input file.I just wanted to use it in the DB2 table for my reference.I want to use this scenario in my Load Jcl.
Back to top
View user's profile Send private message

wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 3:52 am    Post subject:
Reply with quote

Not exactly sure I understand what you are saying, but if you have a new column in table with default enabled and the punch file as well as the data file don't have it, then it is OK. You can load what you have and DB2 will assign default values to the new column for all rows you load.
Back to top
View user's profile Send private message
mfstudent1
Currently Banned

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Tue Jul 03, 2007 4:06 am    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

What I was trying to ask is that whether we can specify the default values for the columns of a DB2 table irrespective of whether that particular exists in input file using Load JCL????
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 4:18 am    Post subject:
Reply with quote

Yes you can.

But you don't have to, if the column defination in table already has default enabled.
Back to top
View user's profile Send private message
mfstudent1
Currently Banned

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Tue Jul 03, 2007 4:23 am    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

Can I do that in JCL if the column value is not defaulted in the table????
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 5:19 am    Post subject:
Reply with quote

There is an option called DEFAULTIF that you can use in the load card column defination. All you have to do is find a position in your input record file that has a constant value like 'space' for example, in all records. Then map your new column to that and use

COLUMN1
CHAR( 15)
DEFAULTIF (pos1:pos2)=' '.

This is just an example to show how it might work. If you do not have such a constant in input file anywhere, then it will not be possible to use it. You can also add such a constant in your input file by regenerating from your source.

More information on DEFAULTIF is available in Utility Guide under LOAD chapter.
Back to top
View user's profile Send private message
mfstudent1
Currently Banned

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Tue Jul 03, 2007 4:29 pm    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

Thanks ...

But that particular field position is not there in input file but i want it in the table.
What I meant was that I want to get a constant value in the column irrespective of the input file
Back to top
View user's profile Send private message
somnath

New User


Joined: 21 Mar 2005
Posts: 18
Location: India

PostPosted: Tue Jul 03, 2007 4:45 pm    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

Write a sort before loading the file into the table. Use OUTREC to have the record from the input file and the new field with the default value. This will convert your input file into the format ready for loading into the table along with the fixed value in the new field. Use this file to load your table.

Thanks,
Somnath.
Back to top
View user's profile Send private message
mfstudent1
Currently Banned

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Tue Jul 03, 2007 4:55 pm    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

My intention is not to touch the input file or create another .I just put a default value in the table
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 10:53 pm    Post subject: Re: Loading a new column into DB2 Table with Default value
Reply with quote

If you don't want to touch the input file, don't have such a constant value column in input file and the table column doesn't have default enabled, then as far as I know, you have run out of options.
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts loading a Modified Mapset Susanta CICS 2 Tue Mar 14, 2017 5:43 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


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