JR58358: CONNECTOR MIGRATION TOOL DOES NOT MIGRATE USER DEFINED SQL CORRECTLY WHEN TERADATA PLUGIN IS USED IN LOOKUP CONTEXT.

APAR status

• Closed as program error.

Error description

• Connector Migration Tool (CMT) does not migrate user defined SQL
  property when Teradata plugin is used in reference context.
                                                                 .
  For example, the example error occurred:
    Exception thrown from CC_TeraDBStatement::prepare, file
    CC_TeraDBStatement.cpp, line 645: RDBMS code 3706: Syntax
    error: expected something between the word 'ACCESS' and the
    'USING' keyword.   SQL statement: LOCK ROW FOR ACCESS USING
    REC_CLS_CD(CHAR(3)), ORIG_LOG_CO_CD(CHAR(3)), SEQ_ID(CHAR(7)),
    RR_ATCH_SEQ_NO(INTEGER)  LOCK ROW FOR ACCESS SELECT
    REC_CLS_CD,ORIG_LOG_CO_CD,SEQ_ID,RR_ATCH_SEQ_NO,LAST_UPD_DT,LA
    ST_UPD_TM,REV_STAT_CD,ATCH_NM,REV_NO,ATCH_ID,TSK_CD,ATCH_TYP_N
    M,ATCH_AUTH_NM,EXPT_CNTRL_CD FROM NCMV.RR_ATCH WHERE
    REC_CLS_CD = :REC_CLS_CD AND ORIG_LOG_CO_CD = :ORIG_LOG_CO_CD
    ...
  In the above failing statement, the usage of host name variable
  notation such as "REC_CLS_CD = :REC_CL_CD" instead of
  Orchstage.REC_CLS_CD causes problems.
  

Local fix

Problem summary

• Customer has used Connector migration tool to migrate jobs in
  9.1.2, One particular job which contains Teradata Common
  connector in Reference context is failing with syntax error.
  

Problem conclusion

• Code change was required and patch has been delivered on 9.1.2
  windows (9.1.2) which fixes the above issue and also contains
  fix similar issue when Teradata Connector is used in Target
  context.
  

Temporary fix

• This issue can be handled manually by changing the user SQL
  by following these steps:
  1) Remove USING and LOCK clauses.
  2) Replace host variables (:column_name) with
  ORCHESTRATE.column_name.
  3) If there are any schema reconciliation errors, change the
  unused field action to 'keep' which is under schema
  reconciliation.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R912 PSY

     UP

• RB30 PSY

     UP

• RB31 PSY

     UP

• RB50 PSY

     UP