DSXchange

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;

And your question is?

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

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

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

All Connectors use the ORCHESTRATE object.

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

Figured that was going to be the answer.

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

Assuming that the date format in your flat file is MM-DD-YYYY, try the below SQL
If the date format in your flat file is something else, use the appropriate mask in the TO_DATE function.

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