Used of Super Descriptor to Optimize SQL Query

, , , , ,
1

We have an Adabas file that looks like this:

REPORT
name (a5) - a descriptor
used-information - PE
used-code (a1)
used-date (n8 )

This file has a superdescriptor name-used-code-sp which consists of the field name and used-code

This file is translated to 2 tables in CONNX:

REPORT
REPORT-used-information

when I do an execution plan (via {statistics}) on the following SQL:

select name, used_date from REPORT a, REPORT_used_information b
where a.isn_report = b.isn_report and name = ‘111’ and used_code = ‘9’

I thought CONNX would attempt to use the name-used-code-sp index. The index list for REPORT_used_information actually shows name and used_code as indexes when used together.

Instead, it uses only the name index.

What can I do to make CONNX use the combined superdescriptor?

We have a lot of Adabas files where with just the name index, the query will start with 1 million rows but if the combined index is used, the query will start with just 5000 rows.

Please advise.

Thanks.
Min

2

Min,

please check into the   {usekey}   function

3

Hmmm… we had a problem like this which was resolved using {forceadanukey}.

But from what I have read about {usekey} that’s a more direct way to resolve the issue of ensuring Adabas SQL Gateway uses the available and correct superdescriptor as its index.

4

Typically the SQL gateway will automatically use the super descriptor as long as it is possible following SQL rules. Does the super descriptor in question consist of more than just the two fields mentioned? If there is a 3rd or 4th field to that super descriptor, and no criteria was specified for them then the super descriptor is ineligible for use by “SQL” rules. To make the super descriptor eligible, specify “is not null” for the remaining unused fields of the super descriptor. You can also use {foradanukey} but this is not recommended as this syntax may not be possible with applications that dynamically generated SQL.

5

Hi Min!

As far as I understood, you have the following situation

ADABAS FDT

1 NAME A5
1 PE GROUP
2 USED-CODE A1
2 USED-DATE N8

1 SP DESCRIPTOR [NAME,USED-CODE-USED-DATE]

If this is correct, then you’ll end up having a CLUSTER, holding the base_table (with NAME) and a sub_table (with USED-CODE, USED-DATE) plus the foreignKey adabas_isn and an idx to indicate the occurrence number of the row inside the PE-Group.

Subtable DDL fragement:

adabas_isn
, idx
, used_code
, used_date

If all fields are defined with adabas null suppression, then the SPD can be used if you specify a NOT NULL for each SPD element. This is the theory. But how to express the NOT NULL on two(!) tables AND convince the SQL Gateway to use the SPD?

We tried this with ESQ and ACE and we failed.

But there is hope: Larry offered a “trick”: Append the “NAME” field to the subtable DDL.
The subtable DDL would look like this:

adabas_isn
, idx
, name /* here is the trick */
 , used_code
, used_date

Based on this “NAME” will occur two times: one time in the base table and one time additional in the sub table.

If you query the the sub table fields, the the SPD should be used. At least on our side this worked. The optimizer can now look at the SPD elements as “all elements are within one table”.

I do not know whether this was a special lab version or whether this is a standard feature withe the sql gateway.
Maybe Larry can give further information on this.

Regards. Chrisitan

6

The technique that Christian describes is a standard feature.