Load data into a DB2 table

viswam_s · New User Joined: 30 Jun 2006 Posts: 3

I need to export data into DB2 tables.
If the Db2 table contains CHAR columns its not an issue, but problem comes when table has INTEGER,DECIMAL type columns.

Is there any format conversion that we can specify on LOAD card?

If anybody did this before, please let me know.

Appreciate your help.

Thanks...
Viswam

dick scherrer · Posted: Thu May 03, 2007 11:44 pm

Hello,

In Excel, create a tab-delimited text file, ftp that to the mainframe, then use that as input to a program or a utility load.

viswam_s · New User Joined: 30 Jun 2006 Posts: 3

Dick,

Would LOAD work if DB2 has a DECIMAL column & data for the corresponding column on the file is DISPLAY NUMERIC(as entered on spreadsheet)?

dick scherrer · Posted: Fri May 04, 2007 5:07 am

Hello,

Look at INDDN in the DB2 utility manual linked to from this site. That will show you how you may define fields in the external file to load. The manual also shows how to specify delimited-data (FORMAT DELIMITED COLDEL ',' - for comma-delimited data - you don't have to use the comma, that is the example in the manual). It is rather common to load data that is in one format in an external file, but a different format in a database table.

Also, a COBOL program to insert rows in the table or reformat the external records for a load would not be a lot of work. If the only task is to get the spreadsheet data into the table, i'd spend a bit of time setting up the load. If other processing logic is needed, creating a load file programmatically would be my recommendation. As my last choice (unless there were very few rows to add), i'd do the inserts in the COBOL program.

Please let us know if anything needs clarification.

wanderer · Posted: Fri May 04, 2007 6:05 am

Just to add to what Dick already said, if you are planning on using delimited charaters in input file, then FYI "FORMAT DELIMITED COLDEL" is available only in DB2 V8.

dick scherrer · Posted: Fri May 04, 2007 6:56 am

Hello,

Yup, if you are not on DB2 V8, reading the tab-delimited file with COBOL and using UNSTRING to build individual fields from the "whole" records, you could then write out fixed format records to load or possibly do the insert directly in the COBOL code.

viswam_s · New User Joined: 30 Jun 2006 Posts: 3

Dick & Wanderer,

thanks so much for your kind suggestions !!

In fact, I actually tried to LOAD with COLDEL option. It worked perfectly !!

thanks
Viswam

dick scherrer · Posted: Fri May 04, 2007 8:17 pm

Cool. . . .

Thank you for letting us know