Null Supression Handling

[Patricio]

Hi folks,

I have a quick question.

Can Adabas fields be marked as null supressed within Attunity?

Thanks in advance,
Patricio

[Andre]

Hello Patricio

FYI, yes.

Adabas fields can be marked in Adabas as 'null-suppressed'. The Attunity Connect Adabas data source driver executes an LF command on every Adabas file the first time it is accessed in a server. The NULL suppressed fields are marked as such and handled by Attunity Connect in the following manner:
■ Exposed as NULLABLE fields.
■ Depending on their datatype, either zeros or spaces are treated as the NULL value. That means that if a field of type A has spaces in it, an SQL user selecting data from it will get a NULL value.
■ When the Attunity query optimizer considers possible execution strategies for a query, it will rule out any execution strategy that uses a descriptor in which at least one NULL suppressed field is not qualified. The reason for this behavior is that Adabas does not include all records in every descriptor inverted list. NULL suppressed fields with 'NULL values' (zeros or spaces) will cause the record to be excluded from any inverted list based on this field.

For example, suppose a table has text fields - AA and AB with superdescriptor S1 defined on AA, AB and AB is NULL suppressed. The user issues the following query:

select * from a_table where aa='XYZ'

The optimizer will not use the S1 superdescriptor for this query because AB is NULL suppressed. Had it used S1 it would not have read records where AA='XYZ' and AB is empty. Because of the NULL suppression, Adabas does not include these records in the S1 inverted list. Because AB is not qualified, the Attunity optimizer knows that this is not a valid execution plan.
On the other hand, the following query would use the S1 superdescriptor:
select * from a_table where aa='XYZ' and ab is not null.

This is because ab was qualified in the query. Note that qualifying the field with the NULL value (ab=0 or ab=' ') is not considered valid and is not supported. So qualifying a text field with ab<> may return incorrect results whereas ab is not null will be handled correctly.

The behavior of the Adabas driver regarding NULL suppressed fields can be modified using the nullSuppressionMode configuration attribute. It can be partially or fully disabled. Care must be taken when deciding to disable the default NULL suppression handling as incomplete results may be returned for queries, as the above example showed.

Hope this helps.

Regards,
Andre