| View previous topic :: View next topic |
| Author |
Message |
dubasir
Beginner
Joined: 13 Dec 2004
Posts: 20
Topics: 9
|
 Posted: Fri Jul 16, 2010 1:28 am Post subject: Help on Fetch cursor in a loop |
 |
|
Hi,
I have a requirement 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.
The cursor declaration to fetch records from table A and table B looks as below. Every fetch can have maximum of 20 records.
| Code: |
EXEC SQL
DECLARE PRFRCST_CSR CURSOR WITH HOLD FOR
SELECT DISTINCT A,B
FROM Table
WHERE A <> :WS-A
AND C = :HV-C
AND D = :HV-D
AND E = :HV-E
AND F = :HV-F
AND G = :HV-G
AND (DATE_START <> :HV-DATE-START
OR DATE_END <> :HV-DATE-END)
AND ((DATE_START BETWEEN :HV-DATE-START AND
:HV-DATE-END)
OR (DATE_END BETWEEN :HV-DATE-START AND
: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)
OR (DATE_START = :HV-DATE-START AND
DATE_END > :HV-DATE-END))
AND X > 0
QUERYNO 2
END-EXEC.
|
Cursor defined on Table B is always fast. Table A is the new table and after creating index on one of the key colums this is doing fast. Table A data is changed daily and requires to run reorg, runstats and bind the program each time.
Is there anyway i can reorg, runstats, bind the program every day before running the program.
Is the above approach efficient for more number of records in Table A or do i need to change the design.(With the current design for 30000 records the program completed in 2 minutes of CPU time and 3 minutes of Elapsed time).
Please suggest
Thanks in advance,
Ramki |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 16, 2010 10:22 am Post subject: Re: Help on Fetch cursor in a loop |
|
|
| dubasir wrote: |
Is there anyway i can reorg, runstats, bind the program every day before running the program.
|
Dubasir,
Why would you that? You really don't need to reorg everyday. You shouldn't be worried 30,000 - 50,000 row inserts per day. Only if the the inserts in the range of a 500K - 1 million, you should think of reorg'ing it everyday. In your case a weekend Re-org is more than enough.
I suggest you read chapter 3 in detail here
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNPFK14/CCONTENTS
Kolusu |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Fri Jul 16, 2010 3:15 pm Post subject: |
|
|
| Quote: |
I have a requirement 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.
|
Suggest you consider unloading both tables and doing the "match" in batch - outside of the database.
_________________
All the best,
di |
|
| Back to top |
|
|
dubasir
Beginner
Joined: 13 Dec 2004
Posts: 20
Topics: 9
|
| Posted: Mon Jul 19, 2010 12:10 am Post subject: |
|
|
In my requirement table A is refreshed daily. Please suggest if weekly REORG is still sufficient.
I can have 40,000 records in table A. As of know with index created and reorg executed on the table, the program is doing fast( 7 mins of total CPU and Elapsed times). With out index or reorg the program took 60 mins of total CPU and elapsed times.
Please suggest if i still need to change the design to unload both the tables and avoid using cursors.
Thanks in advance,
Ramki |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Mon Jul 19, 2010 3:12 pm Post subject: |
|
|
As i mentioned earlier - suggest you unload both tables and do the "match" outside the database. . .
Many (most) times this will run considerable faster than using db2/sql for the match. . .
_________________
All the best,
di |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Thu Jul 22, 2010 7:54 am Post subject: |
|
|
| How is table A refreshed daily? If it's a DB2 Load, then a Reorg is not necessary. If it's INSERTs, then a Reorg could help if the data is not INSERTed in the clustering sequence. For the matching to rows in Table B, consider Declaring a Global Temp table. No special authorization is required to create one. Then you can code a cursor to join the tables. You might need to CREATE and Index on the Global Temp table if you're not getting acceptable performance. I don't know how much time I'd personally spend on a process that runs in 7 minutes, but that's your decision. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
