MVSFORUMS.com

pradeepg1

Hi,

I have a general question about data loading into DB2 tables. We are generating a new system in our organization and for that we have developed new DB design and the source of data for these tables are from
a different system. We are getting a flat file from that system which will include data for all new DB2 tables. And we have a Cobol conversion program to format that huge file according to new DB2 tables.
Now my question is what is the best way to load those tables, is it by having SQL INSERT statements in the conversion program itself OR by creating different flat files for each new DB2 table and loading them by running DB2 load utility. Which is the best industry practise and please let me know pros and cons of each approach by performnace wise and cost wise.

I would really appreciate your thoughts.

Thanks,
Pradeep

kolusu · Posted: Wed Jan 22, 2003 10:42 am Post subject:

Pradeep,

I would swing to the idea of loading the flat file using DB2 utilities. It is not only faster as well avoid locks being held for a longer time.Use the cobol/eztrieve/sort programs to format the records and load it. We load 22 million records in less than 2 hours using load utility. If the same thing is to be done via program it will take forever.

Hope this helps....

cheers

kolusu

pradeepg1 · Posted: Wed Jan 22, 2003 11:46 am Post subject:

Hi kolusu,

DB2 LOAD utility itself taking 2 hrs. time for 22 million records really scared me, because we are estimating approximately around 200 million records to load. So there is no way we use load by using INSERT statements with in the program.
I was under the same imperssion but just want to get expert's opinion before I propose this to my team. Thanks again for your valuable input.

- Pradeep

ajeykumar · Posted: Thu Jan 23, 2003 1:31 am Post subject:

HI Pradeep and Kolusu,

Just as a thought, switching OFF the constraints before running the load utility and then turning them ON again once the load is done may make your load utility run faster.

Thanks
Ajey

pradeepg1 · Posted: Thu Jan 23, 2003 10:15 am Post subject:

Hi Ajey,

You mean to say when we switch off the constraints, it is programmer's responsibility to check the data validity (Primary key, foriegn key relations ships etc....). Is that a true statement?

Pradeep

bidpar · Posted: Fri Jan 24, 2003 3:00 am Post subject:

Hi pradeep
Do you have check and referential integrity constraints in your table? If not then you can run load with 'enforce no' option. Sometimes DBA's discourage to have referential integrity constraint maintained at database level as load and recovery might become problematic. But if you have these constraints then you may filter the rows in the Cobol conversion programs. If the utility places the TBs on check pending then run the 'repair set' utility. Also load with 'log no' option and later take an imagecopy of all the TBs. For full description please see the load utility manual. I think the time kolusu has given is total elapsed time and not CPU time. In this case you can tell you operators to increase the priority of your job or you can submit at a time when there are less activities going on. If your database is designed such that there are no referential integrity, you may run all the load jobs in parallel. Also run reorg, runstart and then bind all the programs after the load is over. Consult your DBA if you face problem.
Regards
Bidpar

pradeepg1 · Posted: Fri Jan 24, 2003 10:10 am Post subject:

Yes we have referential integrity constraints on our tables. About the loading process, thanks for your valuable inputs.

Thanks everyone for all your thoughts and inputs. Load utility manual has gave me lot of info about data loading. I might post few more questions which I may encounter as I go through with loading process.

Have Good week end everyone !!

- Pradeep

ajeykumar · Posted: Tue Jan 28, 2003 12:48 am Post subject:

HI Pradeep,

It is possible the DBAs give you a good earful for running the REPAIR utility to remove the check pending status.

Instead of incorporating the constraints' check and RI check into your application programs, you could first load all the tables by switching OFF the constraints and then switch it ON once your load is done. You should then run the "CHECK DATA" utility on all tables so as to delete all the corrupted rows in the tables. This is the more conventional way of doing things and will also ensure that all checks have been done.

Thanks
Ajey