Loading a new column into DB2 Table with Default value

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

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.

wanderer · Posted: Tue Jul 03, 2007 3:52 am

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.

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

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????

wanderer · Posted: Tue Jul 03, 2007 4:18 am

Yes you can.

But you don't have to, if the column defination in table already has default enabled.

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

Can I do that in JCL if the column value is not defaulted in the table????

wanderer · Posted: Tue Jul 03, 2007 5:19 am

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.

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

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

somnath · New User Joined: 21 Mar 2005 Posts: 18 Location: India

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.

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

My intention is not to touch the input file or create another .I just put a default value in the table

wanderer · Posted: Tue Jul 03, 2007 10:53 pm

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.