MVSFORUMS.com

[nadh]

Dear Kolusu,

I have gone through the link you provided. Got something. Herewith I'm providing the full details of the Scenario. Please give me the exact answer.It's very important for me.

Here is the problem definition.

Name Datatype Null Option Primary key Foreign key
PTY_ID CHAR(12) NOT NULL PK --
ACCT_CO_CD CHAR(2) NOT NULL -- --
CHK_DIG CHAR(2) NOT NULL -- --
PTY_SUR CHAR(30) NOT NULL -- --

Query
DECLARE TA02513A-CURP2 CURSOR FOR
SELECT PTY_ID,
ACCT_CO_CD,
CHK_DIG,
PTY_SUR
FROM DB2.TA02513A
WHERE PTY_ID >=:W-VAL1-SSA1-DB75B
AND PTY_ID < :W-VAL2-SSA1-DB75B
ORDER BY SUBSTR(PTY_ID,11,2)
OPTIMIZE FOR 16 ROWS

I got the coment from the client as
About, the TA02513A-CURP2, the usage of OPTIMIZE for 16 Rows is in conflict with the sort !!

After gone thru the URL you have specified I understood that in that query i should not use optimize for 16 rows, since DB2 ignores the OPTIMIZE FOR n ROWS clause, when there is a ORDER BY used, and there is no index that can give the ordering necessary.

Why is specified optimize for 16 rows is that in online only 16 rows are displayed at a time. so i specified that clause.
So since the select fetches all the rows pertaining to the where clause and then sorts the records. Because Sort is taking place there is no use of optimize for 16 rows. Am I correct.

Pls. confirm me.

Thanks in advance
nadh

[CZerfas]

The trick with the combination of ORDER BY and OPTIMZE FOR n ROWS lies in different approaches for access paths, if the needed ordering can be accomplished by using an appropriate index on that table.

If the optimizer has to assume, that you want to read the whole answer set (which is the normal way for DB2), the optimizer could choose a tablespace scan for collecting all the qualifying rows and then sorting them.

If you have an index definition equal to the ORDER BY clause, the optimizer could choose to search for qualifying rows following the sort oder of the index entries. This will almost for shure lead to more read I/Os, if your table data is not clustered following this index. BUT, if you only want to fetch a limited number of rows, this approach can be cheaper. In that case the optimizer will follow the leave pages of your index and can than skip the additional sort step, since the retrieved rows are already in the desired sequence.

To give the optimizer a strong hint into this direction, you should use OPTIMIZE FOR 1 ROW. This does not limit you fetching more often from that cursor.

hope this helps
Christian

[nadh]

Hi,
Thanks for clarification. Can i modify my cursor as below?

DECLARE TA02513A-CURP2 CURSOR FOR
SELECT PTY_ID,
ACCT_CO_CD,
CHK_DIG,
PTY_SUR
FROM DB2.TA02513A
WHERE PTY_ID >=:W-VAL1-SSA1-DB75B
AND PTY_ID < :W-VAL2-SSA1-DB75B
OPTIMIZE FOR 16 ROWS
ORDER BY SUBSTR(PTY_ID,11,2)