Help With DB2 Load Utility

[jtrapat1]

I'm using the load utility in DB2 and am trying to import a flat file with fixed length fields.
The load goes fine however, I don't have a field on the file for the year that I am importing.
Is there any way I can hard-code or specify the year that I want when I import the data into the table?
Maybe I could specify this on a statement before my import?
I am running this from a command line.
Here's my import statement:
--------------------------------------------------
db2 "connect to bps"
db2 "load from Account.txt
of asc method L (1 3,5 6,8 22,24 100) messages msgs.txt insert into nysa.bp33t_dob_account (FUND_CODE, ACCT_TYPE, SHORT_NAME, LONG_NAME)"
-----------------------------------------------
Now, the first field I would like to be the year field.
In the table this is the BUDGET_YR field.

Is there any way to accomplish this?

Thanks in Advance.
John

 

[blom0344]

Hello John,

Why not do the upload first, then alter table with the extra BUDGET_YR field and finally an update SQL to fill the empty year fields with the required year.....
(while doing the ALTER step you can even specify the new column contents , so perhaps not even update SQL needed) T. Blom
Information analyst
tbl@shimano-eu.com

 

[jtrapat1]

T. Blom,
Thanks for the suggestions.
You see, the reason that we need to specify the budget year is that we will be storing successive years.
Do you know anything about using multiple files?
I was looking at the syntax for the load utility but I haven't tried it yet.
I was thinking maybe that I could have one column with the date on one file and separate my files with commas.
Then when I do the load, I go to each file to get the appropriate value.

Thanks
John
-----------------------------------------------
LOAD FROM file/pipe/dev [ {,file/pipe/dev} ... ]
OF {ASC | DEL | IXF}
[LOBS FROM lob-path [ {,lob-path} ... ] ]
[MODIFIED BY filetype-mod [ {filetype-mod} ... ] ]]
[METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
[NULL INDICATORS (col-position [ {,col-position} ... ] )]
| N ( col-name [ {,col-name} ... ] )
| P ( col-position [ {,col-position} ... ] )}]
[SAVECOUNT n]
[ROWCOUNT n] [WARNINGCOUNT n] [MESSAGES msg-file]
[TEMPFILES PATH pathname]
{INSERT | REPLACE | RESTART | TERMINATE}
INTO table-name [( insert-column [ {,insert-column} ... ] )]
[datalink-specification] [FOR EXCEPTION table-name]
[STATISTICS {YES [WITH DISTRIBUTION [AND [DETAILED] INDEXES ALL]
| {AND | FOR} [DETAILED] INDEXES ALL]}
[ {COPY {YES [USE ADSM [OPEN num-sess SESSIONS]
| TO dir/dev [ {,dir/dev} ... ]
| LOAD lib-name [OPEN num-sess SESSIONS]]}
----------------------------------------------------

 

[blom0344]

Hello John,

I do not know if you will be succesful using the load utility over a combination of files. I expect this to be impossible.
I can think of alternatives that use SQL to fetch data from
temporary tables and then combine the two sources to do an insert into the target:
(If you do not have to load millions of records)

Example:
**********************************
Insert into table TT(T_YEAR,X,Y,Z)
Select T_YEAR,X,Y,Z
From TEMP1,TEMP2
Where TEMP1.A = TEMP2.A ................

I expect the where clause to be quite a bit more complex in your case.
But how would your mechanism work in the first place to insert the proper year into the table from another file?? T. Blom
Information analyst
tbl@shimano-eu.com

 

[jtrapat1]

T. Blom,

Thanks for the help.

I never thought of using temporary tables but it sounds like it will definitely work.

Thanks Again.
John

 

[gregsimpson]

John,

why don't you define the column as not null with default and put the current year as the default. Then when you run the load and don't supply this column it will be defaulted to your requirement.

Greg