Static SQL SELECT FROM SYSTABLES with CREATOR and NAME = *doesn't*use index?

[Gorilla]

I bound my package with EXPLAIN(YES), and it's got the following static
SQL in it:

EXEC SQL SELECT CARDF, RECLENGTH
INTO :CARDF,:RECLENGTH
FROM SYSIBM.SYSTABLES
WHERE NAME = :TBNAME
AND CREATOR = :TBCREATOR

The explain shows that it does a *full table scan* on SYSIBM.SYSTABLES!
And the execution time seems to show that is what it is doing, it
takes about 3 seconds to get this 1 row executing this static SQL.

Using Visual Explain and putting in:

SELECT CARDF, RECLENGTH FROM SYSIBM.SYSTABLES
WHERE NAME = 'X' AND CREATOR = 'Y'

or:

SELECT CARDF, RECLENGTH FROM SYSIBM.SYSTABLES
WHERE NAME = ? AND CREATOR = ?

Both show an index scan (IXSCAN) to get the one row answer.

What is going on here? How could the query optimizer *not* be deciding
to use the primary key on CREATOR and NAME?

Here are some details to help:

1. DB2 7.1 on z/OS
2. Code written in C
2. bind variable TBCREATOR null terminated, defined as:
char TBCREATOR[9];
3. bind variable TBNAME is a VARCHAR type:
struct
{ short int TBNAME_len;
char TBNAME_data[18];
} TBNAME;
4. DB2 defines SYSIBM.SYSTABLES with a primary key on CREATOR and NAME
5. I have not gathered stats (RUNSTATS) on SYSIBM.SYSTABLES, so the
stats are all defaults (-1) on both the table and its indexes.

Any help would be appreciated.

- Gorilla

[Gorilla]

I found the problem. The details below were not quite right.

The TBNAME and TBCREATOR bind variables were changed to work with DB2
8.1 as well (which has 128 byte names).

But this test was with DB2 7.1 which still has CREATOR AS CHAR(8) and
NAME as VARCHAR(18).

Due to the difference in the size of the bind variables, DB2 7.1's query
optimizer didn't use the primary index and reverted to a full table scan.

There are two possible ways out of this:

1. If you need to support both DB2 8.1 and DB2 7.1 (and earlier) with
the shorter names, have two code paths, one with 128 byte bind
variables and one with 8/18 length bind variables. I tested this,
and the access path is through the primary key.
2. Add FETCH FIRST ROW ONLY (or OPTIMIZE FOR 1 ROW if you need to
return multiple rows).

The second case here does something funny, but it's better than a full
table scan.

The PLAN_TABLE shows ACCESSTYPE=I and MATCHCOLS=0 which indicates a
nonmatching index scan.

So, because the bind variables were larger than the corresponding index
columns, DB2 reads every entry in the index (a "full index scan"),
returns the values from the index and evaluates the WHERE clause.

This would normally read the entire index, but that is probably faster
than a full table scan on the data. And, with FETCH FIRST ROW ONLY, it
will at least stop after the first row. Not too bad.

- Gorilla

Gorilla wrote:

I bound my package with EXPLAIN(YES), and it's got the following static
SQL in it:

EXEC SQL SELECT CARDF, RECLENGTH
INTO :CARDF,:RECLENGTH
FROM SYSIBM.SYSTABLES
WHERE NAME = :TBNAME
AND CREATOR = :TBCREATOR

The explain shows that it does a *full table scan* on SYSIBM.SYSTABLES!
And the execution time seems to show that is what it is doing, it takes
about 3 seconds to get this 1 row executing this static SQL.

Using Visual Explain and putting in:

SELECT CARDF, RECLENGTH FROM SYSIBM.SYSTABLES
WHERE NAME = 'X' AND CREATOR = 'Y'

or:

SELECT CARDF, RECLENGTH FROM SYSIBM.SYSTABLES
WHERE NAME = ? AND CREATOR = ?

Both show an index scan (IXSCAN) to get the one row answer.

What is going on here? How could the query optimizer *not* be deciding
to use the primary key on CREATOR and NAME?

Here are some details to help:

1. DB2 7.1 on z/OS
2. Code written in C
2. bind variable TBCREATOR null terminated, defined as:
char TBCREATOR[9];
3. bind variable TBNAME is a VARCHAR type:
struct
{ short int TBNAME_len;
char TBNAME_data[18];
} TBNAME;
4. DB2 defines SYSIBM.SYSTABLES with a primary key on CREATOR and NAME
5. I have not gathered stats (RUNSTATS) on SYSIBM.SYSTABLES, so the
stats are all defaults (-1) on both the table and its indexes.

Any help would be appreciated.

- Gorilla