BD wrote:
> On Feb 18, 3:12 pm, BD <robert.d...@gmail.com> wrote:
>> Hey, all.
>>
>> Subject line says it all.
>>
>> But for background:
>>
>> I'm developing on UDB for Windows. The production application is on z/
>> OS.
>>
>> I'm using some Quest tools (SQL Optimizer) to review some queries, and
>> it suggests that creating some indexes with the ALLOW REVERSE SCANS
>> option will increase performance.
>>
>> I'm managing the data model with ERwin. In the configuration of ERwin,
>> I do see an option to ALLOW REVERSE SCANS on my indexes when the data
>> model is being generated for UDB LUW, but I do not see the option when
>> I'm working with a z/OS-supported data model.
>>
>> Not quite sure where to start digging here, but I'm hauled down a few
>> IBM PDFs, and have found no conclusive evidence that this parameter is
>> supported under z/OS - but I have not yet stumbled upon the full-on
>> syntax guide.
>>
>> I will continue to do so - but thought I might poll the group. I've
>> seen several differences in terms of what is supported under the
>> various flavors of db2...
>>
>> Thanks!!
>>
>> BD
>
> Follow-up. Found the syntax guide on publib.boulder.ibm.com - it
> mentions no ALLOW REVERSE SCAN in the CREATE INDEX syntax.
That doesn't necessarily say much. In DB2 for LUW this clause is now
obsolete since all indexes allow reverse scans.

I would run a simple test:
CREATE TABLE T(c1 INT);
CREATE INDEX i1 ON T(c1 ASC)
<fill with rows and runstats>
SELECT * FROM T ORDER BY c1 DESC FETCH FIRST ROW ONLY

Check the plan....

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab