View previous topic :: View next topic |
Author |
Message |
nivedita Beginner
Joined: 04 Nov 2003 Posts: 22 Topics: 15
|
Posted: Tue Dec 30, 2003 9:52 am Post subject: Mass Insert into a table |
|
|
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. |
|
Back to top |
|
|
sri_naveen Beginner
Joined: 29 Oct 2003 Posts: 10 Topics: 0 Location: Indore, India
|
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-- |
|
Back to top |
|
|
vani Beginner
Joined: 28 May 2003 Posts: 51 Topics: 30
|
Posted: Tue Dec 30, 2003 10:51 am Post subject: |
|
|
"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. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
|
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 |
|
Back to top |
|
|
sri_naveen Beginner
Joined: 29 Oct 2003 Posts: 10 Topics: 0 Location: Indore, India
|
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-- |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
|
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 |
|
Back to top |
|
|
|
|