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
 

 

Bulk DB2 Load around 90 Million

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
meetsupriyo

New User


Joined: 19 May 2009
Posts: 11
Location: Hyderabad

PostPosted: Thu Sep 20, 2012 7:41 pm    Post subject: Bulk DB2 Load around 90 Million
Reply with quote

Hi,

I have a Requirement to Load around 90 Million records from a PS File into a DB2 Table.
I am thinking the below three options: Please suggest which one will provide more performance benifits:

Option 1: Build a one-off COBOL program that will read the data from File and will load into DB2 tables via Insert SQL. The program supports programmatic restart, say first day I have loaded 5M records and store the last record key into a parameter table. next Day, the program will skip 5M records by reading the file sequentially and start loading the records after 5M.


Option 2: Copy the PS File into a KSDS VSAM File so that the records skipping can be done by a single Start Command. Alll other process will be same as option 1


Option 3: Break the Large File into smaller number of files say Files of 10M each and use option 1 without restart logic.


Any other option other than DB2 load is very much appreciated. DB2 Load is not an option for us as it will issue a table level lock and the table would not be accessible during Load process. The Table should be available 24/7.

Thanks,
Supriyo.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Sep 20, 2012 7:45 pm    Post subject: Reply to: Bulk DB2 Load around 90 Million
Reply with quote

Quote:
Any other option other than DB2 load is very much appreciated. DB2 Load is not an option for us as it will issue a table level lock and the table would not be accessible during Load process. The Table should be available 24/7.


using proper terminology is essential for effective communication .
looks like Your understanding of <load> is pretty foggy

during a load the table is anyway unavailable to other <transactions>
so why do You worry?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 20, 2012 8:10 pm    Post subject:
Reply with quote

you could always CREATE a new table and use the db2 load to load that table.

the advantages are:

1. the load would happen rather quickly.

2. you would know what kind of space you need.

3. you would not affect the production table in the event of an abend - which would require a rollback, which is very resource intensive.

4. all the rows would be in a table,
which you could then RUNSTATS
and compare against the production table runstats.

After doing any reconfiguration necessary to the production table
in anticipation of adding 90 million rows,
you could accomplish the load from new to production
via sql.
using a where clause to limit each sql insert.
or
use MERGE if you happen to have a decent level of db2.

Blindly loading 90 million rows to a production table
w/o knowing the effect you will cause
is like stepping on the accelerator and just holding on.

hopefully you have online-reorg available,
as you will need to reorg your production table, runstats,
the whole kitten-kaboodle.

even if your prod db is in the giga row range,
90 million rows is a large increase.
you have to plan for it, and without the intermediate 'new' table,
you will be having more problems that providing for 24/7 availablility.

i don't imagine you have partitioned tables?

if you have to ask on an internet forum,
you obviously do not have the talent in-house
to properly plan for this size of an increase.
Back to top
View user's profile Send private message
meetsupriyo

New User


Joined: 19 May 2009
Posts: 11
Location: Hyderabad

PostPosted: Thu Sep 20, 2012 9:40 pm    Post subject: Reply to: Bulk DB2 Load around 90 Million
Reply with quote

Dear enrico,

The underline DB2 Table must be available during this Load process, so, I can't go with DB2 utility load.

Hope this makes sense.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Fri Sep 21, 2012 12:12 am    Post subject: Re: Reply to: Bulk DB2 Load around 90 Million
Reply with quote

meetsupriyo wrote:
Dear enrico,

The underline DB2 Table must be available during this Load process, so, I can't go with DB2 utility load.

Hope this makes sense.
Have you looked at the SHRLEVEL(CHANGE) option of the LOAD utility?

Note also that your application program will be taking a lot of locks, even if you are loading "only" 5M rows at a time. Unless you take frequent Commits, you may run into lock escalation.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Sun Sep 23, 2012 5:49 pm    Post subject:
Reply with quote

Putting 90M rows in a table via an application program will log everything, another reason to use the LOAD utility...
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: Mon Sep 24, 2012 6:22 am    Post subject:
Reply with quote

Hello,

Quote:
The underline DB2 Table must be available during this Load process, so, I can't go with DB2 utility load.
I believe this is overstated. . . Why does someone believe the entire table must be "available" DURING THIS LOAD?

What % of the data in the table does the 90million represent?

If the inserts are being done, for the duration of the process, the table content is not reliable as there can be rows inserted that some process "see" and some don not.

In a test environment, how long does it take to insert 90million individual rows? How long to LOAD the same with this the only active process?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Sep 24, 2012 9:38 pm    Post subject:
Reply with quote

Quote:

The program supports programmatic restart, say first day I have loaded 5M records and store the last record key into a parameter table. next Day, the program will skip 5M records by reading the file sequentially and start loading the records after 5M.


In how many days you are planning to load the data? 90M/5M = 18 Days??

Is this data not critical?? Is it some kind of history data and not used by any application reports that data load can span for 18 days?.

Have you tried this process in test to see how much time this process takes?
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Sat Sep 29, 2012 12:47 am    Post subject:
Reply with quote

If you use the SHRLEVEL CHANG load utility parms
Code:
LOAD DATA RESUME YES SHRLEVEL CHANGE LOG YES

then it functions just like a program by doing a series of sql inserts instead of a load. Below excerpt is from the DB2 Utilities manual
SHRLEVEL CHANGE "Specifies that applications can concurrently read from and write to the table space or partition into which LOAD is loading data."
I am not sure if it commits each row as it is inserted or if DB2 waits until the end to insert.

Some issues to consider:
  • is the table clustered? If so then are you adding data in the middle or the end? You may need to do a reorg
  • is the table partitioned? If so then you can do the load into one partitiion at a time thus leaving the other partitions free.


Consider using ICETOOL to break up the file into smaller files.

If you use a COBOL program then it will need to do frequent commits so that the table doesn't get locked.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Copybook structure in Load module Vai123 COBOL Programming 7 Fri Sep 16, 2016 8:29 pm
This topic is locked: you cannot edit posts or make replies. ERROR WHILE SUBMITTING LOAD JOB ANURADHA NEELAKANTAN JCL & VSAM 12 Thu Aug 25, 2016 11:50 am
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
No new posts Search a Load Module sreekanth1984 TSO/ISPF 8 Tue Jun 07, 2016 8:45 pm


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