DB2 connector look up stage fails
Moderators: chulett, rschirm, roy
DB2 connector look up stage fails
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;
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;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
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.
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')
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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
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