Error in using ORCHESTRATE.col in Before Job SQL in ODBC Con

[user_2883]

Hi,

We have developed a job, in which we need to delete some records on some crteria from the target before loading data into target table(DB2). We are using ODBC Connector stage to connect to DB2.

Query provided in Before Job SQL:

DELETE
FROM
RDMCLUB.UNIQUE_ASSORTMENT_CLUB B
WHERE
B.POG_SUBCAT_ID IN (ORCHESTRATE.POG_SUBCAT_ID).

Job is aborting with below error:

Tgt_Unq_Asrt_Clb_Db2_Ocon: ODBC function "SQLExecDirect()" reported: SQLSTATE = 42S22: Native Error Code = -206: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]ORCHESTRATE.POG_SUBCAT_ID NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE. (CC_OdbcAdapter::preRun, file CC_OdbcAdapter.cpp, line 403)


Tgt_Proc_PogSCat_Db2_Ocon: Execute failed on statement DELETE
FROM
RDMCLUB.UNIQUE_ASSORTMENT_CLUB B
WHERE
B.POG_SUBCAT_ID IN (ORCHESTRATE.POG_SUBCAT_ID)



I doubt if we can use ORCHESTRATE.<COLUMN_NAME>, to delete incoming POG_SUBCAT_ID'S from the transformer stage before the ODBC Connecter target??

Could anyone help me on this?

Thanks & Regards
Prasanna Lakshmi
www.krascent.com

[ray.wurlod]

The ORCHESTRATE object is not available till the input link of the target stage (the one that writes to the table), so you can't use it in a Before-job subroutine.

If you're referring to Before SQL in the ODBC Connector stage, the problem is slightly different. Values of properties of the ORCHESTRATE object are delivered row by row. But the Before SQL is executed before the first row is processed. So you can't use it (the ORCHESTRATE object) in Before SQL either.