03-06-2018 09:39 AM - edited 03-06-2018 09:40 AM
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.
03-06-2018 03:41 PM
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.
03-06-2018 08:23 PM - edited 03-06-2018 08:24 PM
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;
^ 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.
03-07-2018 09:02 AM
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.