We are moving from DataMigrator 7.1.4 to version 7.7.04 and have problems with ‘subselects’ in a DB2 query.
Here are some general notes:
1. We are running the exact same code and metadata in the new environment (which is DataMigrator 7.7.04) that we were in the old environment (which is DataMigrator 7.1.4).
2. We are accessing the exact same database.
3. The data base is DB2 version 9.7.
4. We brought the code into the GUI tool (Data Management Console) and re-saved it thinking that possibly the syntax has changed from one version to the next but that did not make any difference.
5. The release of the DataMigrator and the Data Management Console we are having a problem with is: 'R717704B' and Gen Number 643.
Here is the query in the flow that has the problem:
SQL PREPARE SQLIN FROM
(MF_DTL_POL_STG1A T1 INNER JOIN POLICY_FORMS T2
T1.SOURCE_POLICY_ID = T2.POLICY_ID AND
T1.LOB_SUB_CD IN ( 'FP' , 'SF' , 'PL' , 'MH' , 'FR' , 'HP' , 'ACV' , 'APV' ) AND
T1.POL_EXP_DT = T2.PLN_EXP_DT AND
T2.EFFECTIVE_DT <'&&TOO_DATE' AND
NOT ( T1.POL_STATUS_CD = 'I' AND
T2.EFFECTIVE_DT = T2.EXPIRATION_DT AND
T2.EFFECTIVE_TYPE_CD IN ( 'A' , 'X' ) AND
T2.LAST_MDF_ACY_TS <'&&TOO_DATE_TS' ) AND
T2.ISSUE_ACY_TS = ( SELECT
T3.POLICY_ID = T2.POLICY_ID AND
T3.PLN_EXP_DT = T2.PLN_EXP_DT AND
T3.EFFECTIVE_DT <'&&TOO_DATE' AND
T3.FORM_ID = T2.FORM_ID AND
T3.FRM_SEQ_NBR = T2.FRM_SEQ_NBR AND
Here is the error I am getting:
10/26/2012 07:09:55 IRFD0 Successfully connected as user "isp3489"
10/26/2012 07:10:50 IRFD0 (ICM18122) Request - __WCFEX (Owner: isp3489) submitted.
10/26/2012 07:10:50 IRFD0 (ICM18015) DEP_1: procedure irf_amp_vars started.
10/26/2012 07:10:50 IRFD0 (ICM18039) DEP_1 irf_amp_vars Return Code = 0
10/26/2012 07:10:50 IRFD0 (ICM18742) irf_policy_form type DB2 Existing target
10/26/2012 07:10:50 IRFD0 (ICM18429) Issuing PREPARE
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.POLICY_ID
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.PLN_EXP_DT
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.FORM_ID
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.FRM_SEQ_NBR
10/26/2012 07:10:50 IRFD0 (ICM18040) Return Code = 14010
10/26/2012 07:10:50 IRFD0 (ICM18076) Request: __WCFEX - finished processing
10/26/2012 07:10:50 IRFD0 (ICM18007) CPU Time : 47
It appears to have a problem with the columns referenced in the subselect.
Thank you for your help.
Windows, All Outputs
Windows, All Outputs