topic Re: DB2 to SAS appending issue. Retriview DB2 session log file. in SAS Programming

DB2 to SAS appending issue. Retriview DB2 session log file.

Yura2301 — Thu, 08 Aug 2013 08:59:41 GMT

Hi,

So I try to append SAS data set with a lot of columns(~100) to DB2 table:

LIBNAME db2Lib DB2 DBCOMMIT=10000 ...;

proc append base=db2Lib.db2Tab data= SASData;

run;

For some portion of SAS data append works ok and add needed data to DB2 table, but for another portion - it failed with CLI error:

ERROR: CLI execute error: [IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003

In SAS log file no info in what exact numeric column issue happened, I also don't like idea sequentially append separate columns for finding needed one...

On DB2 forums I found that the columns can be easy detected from DB2 session log, but I haven't appropriate rights for accessing DB2 log file, all what I can do - it's query(or insert,update) DB2 table from SAS.

So if there are some smart way for detecting the column in which similar issue happened?

May be there is possibility somehow direct DB2 log into some SAS server folder etc.? I tried sastrace option but it didn't helped...

Thanks!

Re: DB2 to SAS appending issue. Retriview DB2 session log file.

LinusH — Thu, 08 Aug 2013 09:06:54 GMT

From the SAS perspective, I don't think there a "smart way", you need more or less manually detect which column/value pair that is causing the error.

Guessing that the easiest way is getting help from your DB2 DBA.

Yet a reason for not having wide tables... 😉

Re: DB2 to SAS appending issue. Retriview DB2 session log file.

LinusH — Thu, 08 Aug 2013 09:10:05 GMT

What you could do is to get the max value from each column, and then match that with the DB2 data types, could at least save some work...

Re: DB2 to SAS appending issue. Retriview DB2 session log file.

Yura2301 — Thu, 08 Aug 2013 09:13:15 GMT

Hi Linus,

Thanks for the answer:)...

DB2 DBA is on long vacation:)

I've just made small script that replace all numeric values in table by "1" value, and tried append the test data to DB2 table - same error...

I thought I'll simply exclude statements : "numColName=1;" from the script and in such way will define needed column, but looks like issue isn't so obvious as I thought...May be there are some problem with nulls,date(datetime) columns also...

Will look deeper to the issue...I just feel that I spend time on things that can be done more efficient, but I'm not sure:)

Re: DB2 to SAS appending issue. Retriview DB2 session log file.

jakarman — Thu, 08 Aug 2013 10:53:38 GMT

Check the value of the numeric value in DB2. It can easily contain many many digits. You can be fooled by intrepretation issues.
And by the way IBAN (International Bank Account Number - Wikipedia, the free encyclopedia) is no number.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition (Data Types for DB2 under z/OS)

Not all datatypes of DB/2 are supported. The question will be weather you are able to see some layout as DB/2 knows it.

It could be a bug like: IBM JR34268: CLI0111E NUMERIC VALUE OUT OF RANGE WHEN EXTRACTING DECIMAL DATA USING DB2 CLI ODBC DRIVER - United States . As you have worked before with the data and tools it is not likely. This one is specific to a ODBC type.

The most reliable way to connect to DB2 is using DB2 with SAS on mainframe. What SAS version / server you are using?

42119 - An error is issued when you attempt to register a DB2 table in metadata (bigint issue translation on numbers)

Re: DB2 to SAS appending issue. Retriview DB2 session log file.

Yura2301 — Thu, 08 Aug 2013 11:03:48 GMT

Hi Jaap,

I still use SAS 9.1.3, your remark regarding numeric columns with many digits looks like possible reason of the issue...

Now I'll working on script that will automatically find column in SAS dataset that has value biggest then maximal allowed for needed DB2 datatype(decimal, smallint etc.).

Thanks for help!