IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Need help on DB2 load in Mainframe


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: :: View next topic  
Author Message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Tue Mar 24, 2020 2:08 am
Reply with quote

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

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Tue Mar 24, 2020 3:56 pm
Reply with quote

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

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Tue Mar 24, 2020 7:25 pm
Reply with quote

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

Active Member


Joined: 29 Apr 2008
Posts: 876
Location: Maryland

PostPosted: Tue Mar 24, 2020 9:56 pm
Reply with quote

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

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Thu Mar 26, 2020 12:13 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2502
Location: NY,USA

PostPosted: Thu Mar 26, 2020 12:19 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Proc print in Mainframe All Other Mainframe Topics 4
No new posts Need opinion on mainframe to cloud mi... General Talk & Fun Stuff 4
No new posts Job is loading load modules several t... COBOL Programming 12
No new posts Looking for Mainframe Project Manager Mainframe Jobs 0
No new posts Mainframe jcl job with href links not... All Other Mainframe Topics 3

Back to Top