|
View previous topic :: View next topic
|
| Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi All,
I need one small suggestion on Loading data into DB2 on Mainframe. I will try to explain the scenario below.
1. We have one COBOL program which reads more than one file (file comes from different sources), then process those files and writes them into one o/p file. So this COBOL program creates one output file.
2. Now that output file we have to load into DB2 tables. Assumption is COBOL program follow the table structure and will create the output file as per that.
Issue because this output file pretty big so it is taking long time to load the file into DB2 tables (It's a partition table). So while checking we got a suggestion that instead of loading this output file directly into DB2 better we should convert the file into DB2 internal format and load that converted file into DB2 so it will be very fast.
Now I haven't done in the past. What I did earlier is while Unloading the data from DB2 I used FORMATINTERNAL and the same I loaded into DB2 table using FORMATINTERNAL.
But here the requirement is different cause the file gets created by COBOL program and we need to convert that into Db2 internal format.
So could someone please help me by sharing suggestion on how to convert the file into DB2 internal format before loading into table?
Thanks |
|
| Back to top |
|
 |
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi,
Got below link but not fully sure on what should be my approach to create/covert the input to DB2 internal format (or if that can be done at all)
Link - https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/ugref/src/tpc/db2z_loadformatinternalprep.html
Thanks,
Subrata |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
I haven't found anything about the DB2 internal format on Google but, assuming that IBM allows "DB2 internal format input records that are not generated by UNLOAD", I would use the FORMAT INTERNAL unload and then analyse the output and the SYSPUNCH.
If the table is partitioned, can you split the file with sort and load each partition separately ?
How are the indexes ? I seem to recall that DROP INDEX, LOAD TABLE and then CREATE INDEX and BUILD INDEX might be faster (in some cases) |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Marso wrote: |
| I haven't found anything about the DB2 internal format on Google but, assuming that IBM allows "DB2 internal format input records that are not generated by UNLOAD", I would use the FORMAT INTERNAL unload and then analyse the output and the SYSPUNCH. |
Your COBOL program must use the record layout (a copybook) for the records id created. I hope so?
When trying to UNLOAD the DB2 table where you plan to load your data, there is a different copybook created in //SYSPUNCH, describing the structure of DB2 record in INTERNAL unloaded format.
Now you need to convert the records created by COBOL program from one copybook to another one.
Available options are:
1) update your COBOL program to create its output exactly in DB2-matching INTERNAL format
2) create a "conversion" program in any available language (COBOL, REXX, PL/I, … Assembler) - to re-create the output from your COBOL
3) use any of available utilities (DFSORT, SYNCSORT, FileAid, others) to perform the same conversion in the most simple and effective way
| Marso wrote: |
| If the table is partitioned, can you split the file with sort and load each partition separately ? |
The output can be easily split into any parts using any of methods 1-3 above
| Marso wrote: |
| How are the indexes ? I seem to recall that DROP INDEX, LOAD TABLE and then CREATE INDEX and BUILD INDEX might be faster (in some cases) |
This is the second level question.
Based on the initial question, I'd recommend you:
1) create a small test copy of your huge file to be loaded
2) create your own version of DB2 table, probably without any indexes, before you can understand all the problems with data format conversion
3) try to convert your test data, and load it into your test table
4) if something goes wrong, do all fixes and updates using your own test data, and your own test table
5) when all conversion issues are resolved, start thinking about DROP INDEX, and further bullshit. This is a completely separate question, suitable for a separate topic.
Do not try to resolve all problems together, in one shot. |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi sergeyken and Marso,
Thanks! for your suggestion. Yes I am going through all the points you have detailed below and will come back soon after checking all these points and see which one fits correctly.
Thanks
Subrata |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Quote: |
Issue because this output file pretty big so it is taking long time to load the file into DB2 tables (It's a partition table). So while checking we got a suggestion that instead of loading this output file directly into DB2 better we should convert the file into DB2 internal format and load that converted file into DB2 so it will be very fast.
|
One working way is to split data set by partition and have those many load jobs run parallel and each on is loading specific partition. |
|
| Back to top |
|
|
|
|
