This topic only applies if you query a table containing a super or sub descriptor with the NU (Null Suppression) attribute or  NC (SQL Null capable) attribute defined on one of the descriptor constituent fields.

 

To improve performance, descriptors that have NU or NC constituent fields could omit or skip records when the NU/NC column is blank.  This descriptor behavior makes them unusable from an ANSI SQL perspective, because in SQL, records should always be returned even if a null value exists in a particular field. But not using these descriptors can significantly slow performance.  

 

The following describes situations where NU/NC super descriptors will be used in SQL optimization, and what can be done to force the use of these performance enhancing descriptors.

 

CONNX does not allow partial key searches if an Adabas super descriptor has a NU (Null Suppressed) or NC (SQL Null capable) constituent field.  Super descriptors that contain NU or NC constituent fields will be used only if criteria for every constituent fields is specified in the SQL statement.  This applies to L3 calls, Sx calls and usage of super descriptors for ordering (Order by).

 

There are three ways to use a super descriptor when there are unknown criteria for some constituent fields:

 

1) Specify column IS NOT NULL on the constituent fields of the super descriptor where the value is unknown. This is the best solution.

The following example will illustrate how a super descriptor will be used by the Adabas SQL Gateway.

The example setup:

a) Create a table with three NC (SQL Null Capable) fields and create a super descriptor on those three fields.

create table nullsuppressiontest10 (col1 char(3), col2 char (3), col3 char(3) , index idx1 (col1, col2, col3))

 

b) Insert a value into one of the columns, and SQL NULL into the remaining fields.

insert into nullsuppressiontest10  values ('AAD', NULL, NULL)

 

Example #1:

select * from  nullsuppressiontest10 order by col1

The SQL statement above will not use the super descriptor to optimize the "order by" because the criteria has not been specified against the col2 and col3 fields, which are both NC.  If the SQL Gateway used the super descriptor for ordering, records could be missed because they are not present in the super descriptor.

 

Example #2:

select * from  nullsuppressiontest10 where col1 is not null and col2 is not null and col3 is not null order by col1

The SQL statement above will use the super descriptor to optimize ordering.  It is safe to use the super descriptor because the criteria states we are not interested in rows that contain a null value.

 

Example #3:

select * from  nullsuppressiontest10 where col1 = 'AAD'

 

The SQL statement above will not use the super descriptor to optimize result retrieval, because the criteria hasn't been specified against the constituent fields col2 and col3.  If records exist in the file where these fields are null, they will be skipped by Adabas and incorrect SQL results will be returned.

 

Example #4:

select * from  nullsuppressiontest10 where col1 = 'AAD' and col2 is not null and col3 is not null

The SQL statement above will the super descriptor to optimize result retrieval.  It is safe to use the super descriptor because the criteria states we are not interested in rows that contain a null value.
 

2) Suffix the escape clause {forceadanukey} to the end of the SQL statement.  The SQL Gateway will use the super descriptor even though it may not return the correct "SQL" results.

 

The example setup:

a) Create a table with three NC (SQL Null Capable) fields and create a super descriptor on those three fields.

create table nullsuppressiontest10 (col1 char(3), col2 char (3), col3 char(3) , index idx1 (col1, col2, col3))

 

b) Insert a value into one of the columns, and SQL NULL into the remaining fields.

insert into nullsuppressiontest10  values ('AAD', NULL, NULL)

 

Example #1:

select * from  nullsuppressiontest10 where col1 = 'AAD'

 

The SQL statement above will not use the super descriptor to optimize result retrieval, because the criteria hasn't been specified against the constituent fields col2 and col3.  If records exist in the file where these fields are null, they will be skipped by Adabas and incorrect SQL results will be returned.

 

Example #2:

select * from  nullsuppressiontest11 where col1 = 'AAD' {forceadanukey}

 

The SQL statement above will the super descriptor to optimize result retrieval.  Even though no criteria was specific for constituent columns col2 and col3, the SQL Gateway will use the super descriptor anyway because of the {forceadanukey} clause.  This SQL statement may not return the correct ANSI SQL results, so care should be taken when using this escape clause.

 

 

3) Enable the global configuration setting FORCEADANUKEY in the CONNX Configuration Manager or the SQLRegistry. This will cause the SQL Gateway to use super descriptors regardless of null suppression status for all queries.  Once you enable this global setting, use the escape clause {forceadanonukey} to turn off this behavior for specific queries.