MVSFORUMS.com

nivedita · Beginner Joined: 04 Nov 2003 Posts: 22 Topics: 15

Hi,

I need to do a mass insert into a DB2 table(version7).
I have to insert 250 million records into a table. I am doing it using a cobol program. Some people suggested to use rexx and put the data in a flat file and then load it.

How will it retrieve the data in formatted way?
There are 3 foreign keys aand a primary key, based no certain condition, we need to increment the foreign keys. I am doing this through COBOL-DB2 prgram(using INSERT)

Any other easy way for this??? Now its taking 4 hours to load the data.

sri_naveen · Posted: Tue Dec 30, 2003 10:32 am Post subject:

Hi vani,
250 million records..sounds a really robust application...for this .. you need to use a cursor on the table for updates....thats the best bet db2 can offer for huge records.. talking about data retrieval, it will be formatted only if u store it appropriately...just a primary key and 3 foreign keys will eat up a lot of time...you can organise it better if u store it as a vsam Key sequential (KSDS) or Entry sequential (ESDS)...
if u have decided to use db2 anyway....populate the tables in the batch mode using JCLs...it will be faster ..and the time doesnt matter...
hope it helps..
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation

--To err is human, to err again is more human--

vani · Beginner Joined: 28 May 2003 Posts: 51 Topics: 30

"if u have decided to use db2 anyway....populate the tables in the batch mode using JCLs...it will be faster ..and the time doesnt matter... "

I want to know what is this batch mode of JCLs to populate the table.

kolusu · Posted: Tue Dec 30, 2003 11:18 am Post subject:

Vani,

With data that huge I wouldn't even consider coding an application programming especially for doing inserts.

You can code an utility program(dfsort, easytrieve, sas,rexx ...) to format the data and write out to a flat file.

Take this flat file and use the standard load utilities of DB2 or BMC and load the tables.

check this link for DB2 LOAD utility which can be in run in batch. It also has some examples

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGH13/2.12?DT=20020826194002

Hope this helps...

cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

sri_naveen · Posted: Tue Dec 30, 2003 11:51 pm Post subject:

Hi vani,
I meant you can write your insert/update logic inside an element and link it from inside a JCL
After the EXEC stt..templink your element..

INCLUDE SYSLIB(Element)
INCLUDE LOAD(Element's load module)
ENTRY (entry pt element)
MODE AMODE(31),RMODE(ANY)
NAME CKBATCHM(R)

it will look something like this...you can submit this JCL and put it in a loop.
Time taken will reduce drastically.
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation

--To err is human, to err again is more human--

kolusu · Posted: Wed Dec 31, 2003 6:30 am Post subject:

sri_naveen,

Can you elaborate more on your proposed solution? what is different here from a stand-alone pgm? Basically you are running an application program to perform the inserts. Are you hinting to code a subroutine to perform the inserts?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu