Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need help on DB2 load in Mainframe

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need help on DB2 load in Mainframe
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    Post subject:
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: 1344
Location: Israel

PostPosted: Tue Mar 24, 2020 7:25 pm    Post subject:
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: 775
Location: Maryland

PostPosted: Tue Mar 24, 2020 9:56 pm    Post subject:
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    Post subject: Reply to: Need help on DB2 load in Mainframe
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

Senior Member


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

PostPosted: Thu Mar 26, 2020 12:19 pm    Post subject:
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    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 Mainframe ASSEMBLER LEARNING MANUAL w... upendrasri PL/I & Assembler 2 Tue May 05, 2020 11:42 am
No new posts Typing the CENT sign ¢ on mainframe,... Rod Hittle All Other Mainframe Topics 2 Wed Apr 15, 2020 7:06 pm
No new posts Trigger mainframe job , when file pla... Nandhinee All Other Mainframe Topics 2 Thu Apr 09, 2020 10:30 pm
No new posts Mainframe Storage HSM upendrasri IBM Tools 5 Thu Feb 20, 2020 5:51 pm
No new posts mainframe ps file to unix box alpit All Other Mainframe Topics 4 Fri Feb 07, 2020 11:19 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us