DB2 connector look up stage fails

[sunil2020]

Hi I get the following error in the Server job

DSP.Open GCI $DSP.Open error -100.|
I am using the below SQL in the DB2 connector stage

SELECT a.PERSON_ID,a.CLIENT_NAME,a.AFFL_START_DATE,a.AFFILIATION_TYPE,
a.CLIENT_REF_NUMBER,a.UNIT_ID,a.SUB_UNIT_ID,a.VIP_INDICATOR,
a.AFFL_END_DATE,a.TERM_REASON_CODE,a.ACQUISITION_DATE,
a.EXPIRATION_DATE,a.ANTCPT_RETIRE_DATE,b.PAYING_MEMBER_CODE,
b.PYMT_METHOD_CODE,b.PYMT_FREQ_CODE,b.PAID_THRU_DATE,
b.AFF_STATUS_CODE,b.ASSN_MBRSHP_CATEGORY,b.PARTICIPATION_CODE,
b.SERVICE_BRANCH_CODE,b.MILITARY_RETIREMENT_DATE,b.MILITARY_PROMOTION_DATE,
b.MILITARY_ACADEMY_NAME,b.MILITARY_GRADUATION_DATE,
b.MILITARY_COMPONENT_CODE,
b.MILITARY_STATUS_CODE,
b.ASSN_DESIGNATION,
c.MEMBERSHIP_CLASS,c.RANK_CODE,c.PAY_GRADE
FROM #schema#.AFFILIATION a,#schema#.MILITARY_ASSN_AFFL b,#schema#.MILITARY_CHAR c
WHERE a.PERSON_ID=b.PERSON_ID
AND a.PERSON_ID=c.PERSON_ID
AND a.CLIENT_NAME=b.CLIENT_NAME
AND a.CLIENT_NAME=c.CLIENT_NAME
AND a.AFFL_START_DATE=b.AFFL_START_DATE
AND b.AFFL_START_DATE=c.AFFL_START_DATE
AND a.PERSON_ID= ORCHESTRATE.PERSON_ID
AND a.CLIENT_NAME= ORCHESTRATE.CLIENT_NAME
AND (a.AFFL_START_DATE= ORCHESTRATE.AFFL_START_DATE OR a.AFFL_END_DATE is NULL)
ORDER BY coalesce(a.AFFL_END_DATE,'0001-01-01') ASC, c.MLTRY_CHAR_START_DATE DESC fetch first row only;

[ray.wurlod]

And your question is?

Note carefully the reference to DSP.Open in the error message.
What does that suggest to you?

[chulett]

I'm curious how a Server job would have references to ORCHESTRATE in its SQL:

Code: Select all

AND a.PERSON_ID= ORCHESTRATE.PERSON_ID

[sunil2020]

Hi craig,

We were using ? instead of ORCHESTRATE. We opened a PMR to IBM and they suggested to use ORCHESTRATE instead of ? and it worked fine....

Actually i corrected the above issue, one coulmn definition was wrong.. thanks for your reply

[ray.wurlod]

All Connectors use the ORCHESTRATE object.

Connectors are available in server jobs as well as in parallel jobs.

[chulett]

Figured that was going to be the answer.

[sunil2020]

Hi craig,

I have removed all the fields, now only it has 2 fields. It fails with the following error message

CopyOfAffilInsUpdFlCreate..AFProcess: Exception thrown from CC_DB2DBStatement::executeSelect, file CC_DB2DBStatement.cpp, line 1958: SQLExecute reported: SQLSTATE = 22007: Native Error Code = -181: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

SQL used:
-----------
SELECT a.PERSON_ID,a.AFFL_START_DATE
FROM #schema#.AFFILIATION a WHERE a.PERSON_ID= ORCHESTRATE.PERSON_ID AND a.affl_start_date = ORCHESTRATE.AFFL_START_DATE


When i changed the SQL with values and the job executes fine

SELECT a.PERSON_ID,a.AFFL_START_DATE
FROM #schema#.AFFILIATION a WHERE a.PERSON_ID=100
AND a.affl_start_date = '2012-11-16'


The transformer takes 2 inputs and 1 output
1) Input 1 - Flat file - AFFL_START_DATE is defined as char 10, display 10
2) Input 2 - Look up from datbase - AFFL_START_DATE is as DATE field in database.
However it is defined as char 10 in column definition( it was char before migration )
3) Output - Flat file - AFFL_START_DATE is defined as char 10, display 10

I changed look up table from char to date etc.. no help,it still fails

Thank you,
Sunil

[jerome_rajan]

Assuming that the date format in your flat file is MM-DD-YYYY, try the below SQL

Code: Select all

SELECT a.PERSON_ID,a.AFFL_START_DATE 
FROM #schema#.AFFILIATION a WHERE a.PERSON_ID= ORCHESTRATE.PERSON_ID AND a.affl_start_date = To_DATE(ORCHESTRATE.AFFL_START_DATE,'MM-DD-YYYY')

If the date format in your flat file is something else, use the appropriate mask in the TO_DATE function.

[sunil2020]

Date format is 'YYYY-MM-DD' in flat file .. I chnaged the query as you said and now it fails with this message

CopyOfAffilInsUpdFlCreate..AFProcess: Exception thrown from CC_DB2DBStatement::executeSelect, file CC_DB2DBStatement.cpp, line 1958: SQLExecute reported: SQLSTATE = 42818: Native Error Code = -401: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818