topic Re: Issue in appending data into DB2 in SAS Programming

Issue in appending data into DB2

Ishaan — Thu, 06 Apr 2017 11:58:08 GMT

I am moving the data from sas to DB2 using proc append. I use BULKLOAD, BL_METHOD and DBCOMMIT options.

I have not created any primary key on DB2 table and variable can contain null value.

While appending the data its reading all the observations from SAS datasets but its not writing all the observation into DB2 table.

I have looked into the rejected obs and I don't see any issue with the data.

Log:

NOTE: There were 10911 observations read from the data set sas_lib.abc.
NOTE: 10911 observations added.
NOTE: The data set db2_lib.abc has . observations and 12 variables.
NOTE:

Results for CLI LOAD operation:
10900 rows loaded.
0 rows skipped.
0 rows rejected.
0 rows deleted as duplicates.
10900 rows committed.

Please advise.

Re: Issue in appending data into DB2

Ksharp — Thu, 06 Apr 2017 12:51:26 GMT

Did you try the SQL ?

proc sql;

insert into db2.xxx

select * from sas.xxx;

quit;

Re: Issue in appending data into DB2

Ishaan — Thu, 06 Apr 2017 13:47:48 GMT

Yes, I tried using the proc sql. Still I am getting issue.

While loading I get error.

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

Re: Issue in appending data into DB2

Ksharp — Thu, 06 Apr 2017 14:04:14 GMT

or upload sas table into db2 and

use Pass-Through method.

proc sql;

connect to db2(.............);

execute( inset into .....db2 sql here. )

by db2;

quit;

Re: Issue in appending data into DB2

Ishaan — Thu, 06 Apr 2017 15:13:59 GMT

Thank you. Its resolved. It had length mismatch between the values in one of the var in sas and var in db2 table.