Question Re: IQ null values management in Technology Q&A

IQ null values management

Former Member — Tue, 29 Sep 2015 12:54:58 GMT

Hi,

I'm facing an issue with SAP Data Services & IQ with null values management.

I have a column containing nulls.

If i try to use a select * from table where column is null, i have a correct output with DBISQL.

When i use SAP Data Services with function like NVL() or restrictions like COLUMN IS NULL, i have no correct output.

It seems like DS can't identify null values.

Is there any configuration to be done in SAP IQ platform to correct this behavior ?

Thanks for your help,

Guillaume

Re: IQ null values management

tayeb_hadjou — Tue, 29 Sep 2015 13:36:18 GMT

Hi Guilaume,

I suggest look in sql traces in IQ server and check the options and exact statement sent by SAP DS to IQ. If tracing is not enabled, you can enable it like this :

- Connect with dbisql

CALL sa_server_option( 'RequestLogFile', 'trace_file_name.out');
CALL sa_server_option( 'RequestLogging', 'ALL' );
-Test the query from Data services
-Disable tracing :
CALL sa_server_option( 'RequestLogging', ‘NONE' );

Regards,

Re: IQ null values management

saroj_bagai — Tue, 29 Sep 2015 13:44:30 GMT

I would not recommend using

sa_server_option ('RequestLogFile', 'trace_file_name.out')

it is better to log in the .srvlog, so that you can get timestamps as well

Re: IQ null values management

Former Member — Tue, 29 Sep 2015 13:59:09 GMT

Hi Tayed,

Thanks for your help, i investigated this issue and found that the value is EMPTY and not null.

If i check the data in SAP IQ, it's not null, the length is 0.

The value is BLANK..

The data comes from a CSV file loaded through SAP Data Services.

For INTEGER, NUMERIC or DATE fields there is no issue, a null value is inserted.

But when it comes to Varchar values, a blank is inserted instead of a null.

Any hints about that ?

Thanks,

Guillaume

Re: IQ null values management

tayeb_hadjou — Tue, 29 Sep 2015 14:04:57 GMT

You may need adjust option LOAD_ZEROLENGTH_ASNULL (off by default) or NON_ANSI_NULL_VARCHAR (off by default)

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38151.1604/doc/html/san1278453569135.html

Re: IQ null values management

saroj_bagai — Tue, 29 Sep 2015 14:09:56 GMT

Behavior you are seeing for load for Zero length strings into a Null char column in expected.

To achieve the behavior you want , you will need to do following

1. Set option public.Load_ZEROLENGTH_ASNULL=on

2. Modify the load statement to direct IQ load NULL when there is a blank data.

e.g

, col3 NULL(BLANKS)

Both conditions are essential for the desired effect

Re: IQ null values management

markmumy — Tue, 29 Sep 2015 14:11:05 GMT

When you load character or binary based data, an empty string is considered data. It is a string of zero length. If it were null, usually the data contains some string of characters denoting that it is a NULL value (the keyword NULL, for instance). If you want to treat all empty strings a NULL data, you simply need to set the options Load_ZeroLength_AsNULL to ON and NON_ANSI_NULL_VARCHAR to ON, then follow some basic configurations:

This option specifies LOAD statement behavior under these conditions:

inserting a zero-length data value into a column of data type CHAR, VARCHAR, LONG VARCHAR, BINARY, VARBINARY, or LONG BINARY, and
a NULL column-spec; for example, NULL(ZEROS) or NULL(BLANKS) is also given for that same column.

Set LOAD_ZEROLENGTH_ASNULL ON to load a zero-length value as NULL when the above conditions are met.

Set LOAD_ZEROLENGTH_ASNULL OFF to load a zero-length value as zero-length, subject to the setting of option NON_ANSI_NULL_VARCHAR.

Mark

Re: IQ null values management

ChrisBaker — Tue, 29 Sep 2015 15:26:14 GMT

Also confirm your setting of the option 'ansinull'. Depending on how DS is connecting (ODBC or OpenClient), the option may be set wrong.

ansinull = 'off' will interpret '<col> is null' the same as 'col = null' whereas ansinull = 'on' will give an indeterminate result for '=' and '!=' and comparisons should use 'is null' only.

The default is 'On' for ODBC and JDBC/ODBC bridge and off for OpenClient and jConnect JDBC connections.

What connection type/driver are you using for DS?

Chris

Re: IQ null values management

Former Member — Wed, 30 Sep 2015 14:32:58 GMT

Thanks ,

This solved the issue and i can now properly insert null values.and use nvl, IS NULL, etc...

We are using ODBC connection with SAP Data Services, for now it seems like it's working fine.

Thanks a lot for your efforts !