topic Getting error message during data load to Db2 from SAS in SAS Programming

Getting error message during data load to Db2 from SAS

rara — Wed, 01 Mar 2017 10:39:30 GMT

HI There,

I have a table already available in Db2 and i am running my sas code to load into this table from another sas table, using pass through facility.

And it is a proc append running behind.

The columnnames are same both in sas and DB2.

Now i am getting some unusual error from Db2 on one column name in the table, and the error message is as folows,

ERROR: [IBM][CLI Driver][DB2/AIX64] SQL0206N "column_name" is not valid in the context

Someone please help me on this.

Regards,

Ratheesh Rajan

Re: Getting error message during data load to Db2 from SAS

RW9 — Wed, 01 Mar 2017 11:01:31 GMT

Can you post the SQL, and a describe table from the database. Without seeing either its hard to say.

Re: Getting error message during data load to Db2 from SAS

Kurt_Bremser — Wed, 01 Mar 2017 11:57:03 GMT

Have you already checked all possible reasons for this message by consulting https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00206n.html?

Re: Getting error message during data load to Db2 from SAS

rara — Wed, 01 Mar 2017 11:57:13 GMT

Thanks for ur respnse..

please find below the table structure in Db2 and the piece of sas code to load the data used with passthorugh.

CREATE TABLE "SOR_IDW"."table_name" (

"COMPANY" VARCHAR(7),

"SPLITCLMTYPE" CHARACTER(2),

"HAZARDS" VARCHAR(10),

"DATEOCCURREDFROM" DATE,

"DATEOCCURREDTO" DATE,

"POSTCODE" VARCHAR(42),

"EXCLUDING" VARCHAR(21),

"EXCLUDING_WORDS" VARCHAR(151),

"ADDITIONAL_WORDS_TO_SEARCH_WORDS" VARCHAR(71),

"AUTHOR" VARCHAR(15),

"DATE_CREATED" DATE

)

SAS code :

proc append DATA=&sourceLib..&sourceDataset.

BASE= &TargetLib..&TargetTable. (BULKLOAD= YES BL_METHOD=CLILOAD) force;

run;

And the issue is with the column ADDITIONAL_WORDS_TO_SEARCH_WORDS.

Re: Getting error message during data load to Db2 from SAS

Kurt_Bremser — Wed, 01 Mar 2017 12:03:42 GMT

Are those quotes around column names really necessary? I never see them in SQL query statements run on our UDB/DB2.

Since ADDITIONAL_WORDS_TO_SEARCH_WORDS is already 32 bytes, the additional quotes might trigger a length restriction somewhere on the way between SAS and DB2.

Re: Getting error message during data load to Db2 from SAS

Kurt_Bremser — Wed, 01 Mar 2017 12:06:59 GMT

PS Moved the topic to "General Programming", as it is neither a installation nor administration issue.

Re: Getting error message during data load to Db2 from SAS

LinusH — Wed, 01 Mar 2017 13:22:12 GMT

ADDITIONAL_WORDS_TO_SEARCH_WORD is longer than 32 chars, something that SAS does not support.

A PROC CONTENTS in the DB2 libref should reveal that (and compare with the source SAS data set).