SEO

#1 (**permalink**) 02-27-2008, 01:11 PM

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

#2 (**permalink**) 02-27-2008, 01:11 PM

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.

#3 (**permalink**) 02-27-2008, 01:11 PM

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

#4 (**permalink**) 02-27-2008, 01:11 PM

>"BD" <robert.drea@gmail.com> wrote in message
>Follow-up. Found the syntax guide on publib.boulder.ibm.com - it
>mentions no ALLOW REVERSE SCAN in the CREATE INDEX syntax.

A quick look at the DB2 for z/OS V9 SQL Reference does not show ALLOW
REVERSE SCAN(s) in the CREATE INDEX syntax (unless I missed it). It is not
needed on DB2 for z/OS because it allows that by design.

In any case, ALLOW REVERSE SCANS on DB2 for LUW only helps in certain
situations such as ORDER BY in descending sequence where it is using an
index in ascending sequence (but since it is very low cost, it should be
used by default in DB2 for LUW any time you create an index). It is the
default in DB2 LUW V9.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

QUESTION: Does db2 8.2 for z/OS support ALLOW REVERSE SCAN onindexes?