Getting error message during data load to Db2 from SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Getting error message during data load to Db2 from SAS

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

 


Accepted Solutions
Solution
‎03-09-2017 10:38 PM
Esteemed Advisor
Posts: 6,646

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

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/msq...?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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

Occasional Contributor
Posts: 8

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

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.

Esteemed Advisor
Posts: 6,646

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 6,646

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

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 5,198

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

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).

Data never sleeps
Solution
‎03-09-2017 10:38 PM
Esteemed Advisor
Posts: 6,646

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

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/msq...?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 121 views
  • 0 likes
  • 4 in conversation