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
 

 

Load data into a DB2 table

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

New User


Joined: 30 Jun 2006
Posts: 3

PostPosted: Thu May 03, 2007 11:36 pm    Post subject: Load data into a DB2 table
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 03, 2007 11:44 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
viswam_s

New User


Joined: 30 Jun 2006
Posts: 3

PostPosted: Fri May 04, 2007 12:12 am    Post subject:
Reply with quote

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)?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 04, 2007 5:07 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
wanderer

Active User


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

PostPosted: Fri May 04, 2007 6:05 am    Post subject: Re: Load data into a DB2 table
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 04, 2007 6:56 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
viswam_s

New User


Joined: 30 Jun 2006
Posts: 3

PostPosted: Fri May 04, 2007 7:40 pm    Post subject:
Reply with quote

Dick & Wanderer,

thanks so much for your kind suggestions !!

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

thanks
Viswam
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 04, 2007 8:17 pm    Post subject:
Reply with quote

Cool. . . .

Thank you for letting us know icon_smile.gif
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 Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Copy load modules and alias vasanthz IBM Tools 5 Thu Apr 20, 2017 6:31 am


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