# Used of Super Descriptor to Optimize SQL Query **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\ **Created:** [November 21, 2012, 7:30pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784 "2012-11-21T19:30:39Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [November 21, 2012, 7:30pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/1 "2012-11-21T19:30:39Z")
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 ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [November 21, 2012, 9:40pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/2 "2012-11-21T21:40:12Z")
Min, please check into the   {usekey}   function ---
**Author:** ![Brian\_Johnson1](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/brian_johnson1/32/163_2.png) [@Brian\_Johnson1](https://techcommunity.softwareag.com/u/Brian_Johnson1)\ **Post date:** [November 21, 2012, 10:16pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/3 "2012-11-21T22:16:56Z")
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. ---
**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\ **Post date:** [November 21, 2012, 10:32pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/4 "2012-11-21T22:32:09Z")
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. ---
**Author:** ![ChristianHeld](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/christianheld/32/138_2.png) [@ChristianHeld](https://techcommunity.softwareag.com/u/ChristianHeld)\ **Post date:** [December 22, 2012, 4:11pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/5 "2012-12-22T16:11:28Z")
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 ---
**Author:** ![Larry\_McGhaw](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/larry_mcghaw/32/562_2.png) [@Larry\_McGhaw](https://techcommunity.softwareag.com/u/Larry_McGhaw)\ **Post date:** [December 22, 2012, 4:20pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/6 "2012-12-22T16:20:55Z")
The technique that Christian describes is a standard feature. ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 3:02pm UTC](https://techcommunity.softwareag.com/t/used-of-super-descriptor-to-optimize-sql-query/101784/8 "2021-04-02T15:02:35Z")