topic Troubles accessing DB2 in SAS Programming

Troubles accessing DB2

SvenWagner — Fri, 26 Jun 2020 12:30:38 GMT

Hi,

I'm running SAS 9.4 (M3) on a Windows 2016 Server. When accessing a DB2 Database (DB2 v11.1.4.5) on AIX 7.2 via SAS/ACCESS Interface to DB2, I get the following warning message:

WARNING: During read: [IBM][CLI Driver] CLI0182W Fractional truncation.SQLSTATE=01S07 : [IBM][CLI Driver] CLI0182W Fractional truncation.

(The message is repeated some more times. )

Occasionally, an additional error message occurs:

ERROR: An error occurred when translating from one type of character encoding to another.

I guess, the messages have something to do with encoding issues. While our database uses UTF-8, SAS uses WLATIN1. But changing the encoding within SAS is not an option. A "special feature": The same code sometimes results in warnings or error message, and sometimes it does not.

I found http://support.sas.com/kb/64/478.html, but this does not work within our environment.

Has anybody an idea how to solve this issue?

Re: Troubles accessing DB2

s_lassen — Fri, 26 Jun 2020 16:37:11 GMT

As far as I can fathom, the "fractional truncation" message comes when you try to store a decimal (or perhaps a floating point?) value in an integer column. But I am not sure how that would come about if you are only reading from the DB2 database.

Could you show the query that generates the message?

The encoding problem cannot be solved, unless you store your SAS data in a format that accepts all the possible values for character variables, there are many characters in UTF-8 that are not represented in a simple 8-bit character format such as WLATIN1. Even if you are not allowed to change the encoding for the SAS session, you can still use the ENCODING= dataset option on the table that you extract from DB2.

Re: Troubles accessing DB2

ChrisNZ — Sun, 28 Jun 2020 02:17:22 GMT

The truncation message could be due to numeric precision issues. If the DB2 mantissa in longer than 15 digits, you must drop some of them when using Windows.

The character set issue only seems solvable by using the encoding= data set option in your case. Of course, you will not be able to visualise the data set's contents if the SAS session uses the wrong encoding.

Re: Troubles accessing DB2

SvenWagner — Mon, 29 Jun 2020 11:14:44 GMT

Hi,

the query is a quite simple DATA STEP as you can see in the attachment.

Btw.: There is some more / other database return in the log than I would expect, or I'm used to. Do you have any clue what this means?

Best, Sven

Re: Troubles accessing DB2

ChrisNZ — Mon, 29 Jun 2020 22:58:03 GMT

It's not about the code it's about the data. Also did your see this page about SQLSTATE 01S07? https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0058997.html