topic Re: Reading a numeric variable with length 38,0 from an IBM Db2 database in SAS Programming

Reading a numeric variable with length 38,0 from an IBM Db2 database

ErikNM — Tue, 06 Mar 2018 14:40:13 GMT

When trying to read (or open) a table from an IBM Db2 database (via DI Studio), we get the following errors:

ERROR: CLI open cursor error: [IBM][CLI Driver] SQL30020N Execution of the command or SQL statement failed because of a syntax error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements: Reason Code "0x220A"("0100")"". SQLSTATE=58009

ERROR: CLI close cursor error: [IBM][CLI Driver] CLI0108E Communication link failure. SQLSTATE=40003

This is because of 1 specific numeric variable. If we stop reading it, then everything else loads correctly.

The numeric variable is likely troublesome because it's stored with the length 38,0 in the source database.

Do you have any advice on how we might remedy this? I'm hoping there's some kind of library setting or precode we might use to force the variable into an acceptable numeric format.

Re: Reading a numeric variable with length 38,0 from an IBM Db2 database

Tom — Tue, 06 Mar 2018 20:41:34 GMT

Does DI give you any option to tell SAS to convert it to a string? Using the DBSASTYPE dataset option?

There is no way to store 38 digits accurately in a numeric variable.

Most likely the value is being used as an identifier and not an actual number with 38 significant digits so you would want to convert it to a string anyway.

Re: Reading a numeric variable with length 38,0 from an IBM Db2 database

ChrisNZ — Wed, 07 Mar 2018 01:24:37 GMT

DB2 can store no more than 19 digits accurately afaik, just like SAS (on PC/Unix) can store no more than 16 accurate digits (mainframes can store a few more as they use a longer mantissa).

So any 32-digit number in DB2 will almost certainly be different from the number stored. This can be seen in SAS too.

data _null_;
  A=12345678901234567890123456789012;
  put A= 32.0; 
run;

A=12345678901234566802609541218304

^ the digits after this point are inaccurate

A precision of 38 is silly, and creates issues, even within the IBM world. See link below.

You best bet is to create a view or a nickname. See here.

Re: Reading a numeric variable with length 38,0 from an IBM Db2 database

Patrick — Wed, 07 Mar 2018 14:02:32 GMT

Using DBSASTYPE like @Tom suggests set as an option on your table metadata objects is certainly worth a try (using CHAR(38)). If you also write to the table then set also the DBTYPE option.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371572.htm