MVSFORUMS.com

[dubasir]

I have a requirement where i have to process input file of 30,000 to 40,000 records and for every record read i have to open, fetch, close a cursor which is taking huge CPU time.

I tried to open and close the cursor once before (open) and after (close)reading the file and have only fetch statement for every record.
I have to use with hold option since i have DB2 commits in between.

When i used the above logic, i am not able to fetch the records from cursor for second record onwards. I guess the cursor is not triggered for
the second record onwards. Please suggest me how to resolve this.

Thanks in Advance,
Ramki

[dbzTHEdinosauer]

how many rows do you need to select for each of the 30-40,000 trigger records of the qsam file?

what does your cursor declaration look like?
are you re-populating the host-declarations before each cursor open?

obviously, you don't want to open/close the cursor for each of the trigger records;
but you have to give us a little info before we can offer suggestions.

answer all the above questions, please.
_________________
Dick Brenholtz
American living in Varel, Germany

[dubasir]

I can have maximum of 10 records for each of 30-40,000 trigger records of the qsam file

My Cursor declaration as follows
DECLARE CSR_NAME CURSOR WITH HOLD FOR


Yes, I am re-populating the host-declarations before each cursor open


Thanks,
Ramki

[papadi]

You need to post the complete sql of the declare. What has been posted for the declare provides basically nothing
_________________
All the best,

di

[dubasir]

Here is my complete cursor declaration

[dbzTHEdinosauer]

you are correct; with the current WHERE conditions, you will need to open and close the cursor each time.

i have no idea on the relationship between any of the host variables and any order by, thus just a plain cursor for a match merge is not going to work either.

you are not UPDATing the DB2 table.
have you thought of unloading the table
using some criteria for where condition,
you will have to unload all columns referenced in your where clause.
and make your process a qsam match merge.

not knowing the process leaves us in the dark on what to suggest.

you could add a FOR READ ONLY clause, thus there would be no ambiguity and
it could process faster, but i doubt it.

AND (DATE_START <> :HV-DATE-START OR DATE_END <> :HV-DATE-END)
precludes
(DATE_START = :HV-DATE-START AND
DATE_END < :HV-DATE-END)
OR (DATE_START > :HV-DATE-START AND
DATE_END = :HV-DATE-END)
OR (DATE_START = :HV-DATE-START AND
DATE_END > :HV-DATE-END))

from ever being true.

You are either going to have to live with your OPEN/CLOSE/OPEN.... logic
(need indexes on date's)
or figure out how to unload and process qsam files - which would probably be faster
than 30-40,000 cursor open/closes.
_________________
Dick Brenholtz
American living in Varel, Germany

[dubasir]

Thanks for the reply

Here is my requirement
I have to read all the records from database A and for each record read i have to look for a match based on some key and the date conditions in the
same database A and some other database B.

For this i have unloaded table A to a qsam file and for every record read
i have used open/fetch/close cursors to determine matched records from
both the tables A and B.

Table A can have 30,000 to 40,000 records and Table B can have millions of records.

Please suggest me the efficient approach for this.

Thanks in advance,
Ramki

[dbzTHEdinosauer]

you can prepass the qsam file with either a sort or cobol program
to determine what limits can be imposed on the unload or an all inclusive cursor.

You could unload table B - limiting the unload based on the prepass

or just simply expand your cursor to include everything - again based on a prepass of the qsam.

a really huge cursor probably would not be worse than 30-40,000 opens/closes.
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

dubasir,

I just looked at your query and aren't all the WHERE conditions for DATE check mutually exclusive?

I think all of the DATE conditions can be simply modified to

[jsharon1248]

Take your input file and load it into a DECLARED GLOBAL TEMPORARY TABLE and then modify the query to join to the DECLARED table.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/5.43?DT=20050325102208