Loading flat file into DB2 using load utility

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Hi,

I will try to explain this as best as I can...

I have a flat file, which has dates in packed format (CYYMMDD) among other fields, and I would like to load this file into my DB2 table. I would like to be able to convert the 'DATE' on the fly to DB2's DATE format (MMDDYYYY) while doing the loading. Is this possible?

My second question is, is there anyway to specify that for the whole flat file, if there is a field that is all X'FF' then it should be treated as NULL inside DB2?

Hopefully, it is not confusing...

Thanks,
Nirav

jainrahul · New User Joined: 21 Feb 2008 Posts: 1 Location: bangalore

hi,
You can do this by the help of some changes in the LOAD card
use the DATE EXTERNAL as an attribute for that particular coloum like

LOAD DATA
INTO <table>
( <col 1> <position()> <type>,
.........................
<col n> <position()> DATE EXTERNAL)
this will tell DB2 that the particular coloumn contains the DATE and DB2 will store it in the required format.

For your second question use NULLIF() = ' ' with the column attribute like <col> <position> <type> NULLIF ( ) = ' '

since 4F is actually blank. DB2 will take the input as NULL in the case when IF condition is true.

Thanks,
- rahul

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Hi, thanks for the suggestion.

I am little confused. How would DB2 recognize CYYMMDD needs to be converted to MMDDCCYY? I mean, will it correctly convert 3080221 to 02212008? In my shop, 3 means 20 and 2 means 19...

Also, do I need to specify every column that need not be converted as well as the date columns?

-----

As for NULLIF, when you say 4F did you actually mean X'FF' ? Sorry, I have never seen 4F before...

Appreciate the help!

Craq Giegerich · Posted: Thu Feb 21, 2008 6:10 pm

1. DB2 will not convert CYYMMDD to an acceptable format you will have to handle that before hand.

2. Niether x'FF' nor x'4F' is a blank, you would have to use a hex compare NULLIF((1:1) = X'FF')

Nirav721 · New User Joined: 11 Feb 2008 Posts: 53 Location: NJ

Oh okay, thanks. I was afraid to hear that answer!!