faster upload into HDAM database

Mistermind · New User Joined: 08 Feb 2008 Posts: 46 Location: Dublin

An old program is taking 65 minutes to upload 300,000 QSAM records of length 11,000 into a HDAM database (root segments only).

The PSB currently contains 2 PCB's - one for PROCOPT=L, and one for PROCOPT=D. DLET is actually not used. Could this PSB option be slowing things down during the mass insert load, causing IMS to lock segments, take syncpoints etc?

Would it be faster for the application program to output to a QSAM dataset, then feed this QSAM dataset into an IMS load utility? Such as?

Thanks in advance to those with experience in this area.

Sandy Zimmer · Posted: Fri Jan 30, 2009 12:16 am

I must have missed the D opt when I did loads? A load is a load to an empty initialized database. The first thing I would do is to regen the PSB without the D option. Make sure that your QSAM is in sequential key sequence - remember that HDAM does not have a separate index. Did you mean a record length of 11,000 or did I mis-read?

Mistermind · New User Joined: 08 Feb 2008 Posts: 46 Location: Dublin

Thanks. Input record and IMS segment are both 11,000 bytes long, which gives an excuse for slow runtime, but not 65 minutes slow.

I suspect the PSB started life with PROCOPT=L to perform loading only. Then some programmer found a need to delete a few dummy records and got DBA to add PROCOPT=D into the PSB to give a one-off chance to delete a few segments by the program. Now the delete need is gone, but I suspect the PSB is lumbered with inefficiency. Do you agree this would happen to a major extent?

I was also wondering if using an upload utility would run faster than the application assembler program, which basically performs ISRT 300,000 times. I think we have Macro4 Insync on site, possibly some other. DBA will decide.

Because the D/B is HDAM not HIDAM I presume duplicate-key is not allowed, and indeed the input is sorted ascending, without duplicate-key. Because access will be by formula computation from the keyfield towards a unique ESDS RBA location I am not quite sure why ascending sequence upload is necessary.

Sandy Zimmer · Posted: Fri Jan 30, 2009 2:52 am

First of all, I do not know of any version of IMS that allows duplicate keys or allows a load out of sequence. I really do not think that the delete option would slow down the load.

However, I think you have a MAJOR problem with an 11,000 byte record. Why so big? Are all the bytes used or perhaps a redesign would allow for variable length or even child segments? Seriously - this is a PROBLEM.

I am not familar with Macro4 Insync and I am NOT a DBA. Perhaps someone else will be able to contribute to your question.