Issue in appending data into DB2

Reply
Occasional Contributor
Posts: 11

Issue in appending data into DB2

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.

Grand Advisor
Posts: 9,571

Re: Issue in appending data into DB2

Did you try the SQL ?

 

proc sql;

insert into db2.xxx

select * from sas.xxx;

quit;

Occasional Contributor
Posts: 11

Re: Issue in appending data into DB2

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

Grand Advisor
Posts: 9,571

Re: Issue in appending data into DB2

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;

Occasional Contributor
Posts: 11

Re: Issue in appending data into DB2

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

Ask a Question
Discussion stats
  • 4 replies
  • 99 views
  • 0 likes
  • 2 in conversation