JR28797: REPLICATION MIGRATION V9.5 SCRIPT NEEDS TO INCLUDE AN ALTER ADD COLUMN IGNTRAN_TIME TO IBMQREP_IGNTRANTRC TABLE.

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If customers had created the v9.1 control tables using
  Replication center or asnclp in v9.1 GA then the DDL used to
  create the IBMQREP_IGNTRANTRC would be missing the IGNTRAN_TIME
  column.
  In FP1 this was fixed so control tables created after GA will
  not have problems.
  If customer with v9.1 GA created tables migrate to v9 they will
  see error messages like -
  
  2008-03-13-12.20.55.423809 <sqlStmt::prepStmt> ASN0552E "Q
  Capture" : "ASN" : "PruneThread" : The program encountered an
  SQL error. The server name is "SV019003". The SQL request is
  "PREPARE". The table name is "*". The SQLCODE is "-206". The
  SQLSTATE is "42703". The SQLERRMC is "IGNTRAN_TIME". The SQLERRP
  is "SQLNQ075".
  
  2008-03-13-12.20.55.423809 SQL0206N "Q Capture" : "ASN" :
  "PruneThread" : "IGNTRAN_TIME" is not valid in the context where
  it is used. SQLSTATE=42703
  
  This is because of the missing IGNTRAN_TIME.
  

Local fix

• Possible ways to correct the error are -
  
  - Modify the migration script to have an alter add column for
  IGNTRAN_TIME TIMESTAMP  NOT NULL  WITH DEFAULT CURRENT TIMESTAMP
  to the IBMQREP_IGNTRANTRC table.
  The migration script is in
   sqllib/samples/repl/mig9/q/asnqcapluw.sql as per
  
  https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?
  topic=/com.ibm.swg.im.iis.repl.mig9.doc/topics/iiyrcmig9qcapstep
  sluw95.html
  
  - Alter add the column after migration
  
  - Drop and recreate the table if empty
  The table DDL will be similar to
  
  CREATE TABLE !capschema!.IBMQREP_IGNTRANTRC
  ( IGNTRAN_TIME TIMESTAMP  NOT NULL  WITH DEFAULT CURRENT
  TIMESTAMP,
   AUTHID CHARACTER(128),
   AUTHTOKEN HARACTER(30),
   PLANNAME CHARACTER(8),
   TRANSID CHARACTER(10) FOR BIT DATA NOT NULL,
   COMMITLSN CHARACTER(10) FOR BIT DATA NOT NULL
  )  IN !tsname!;
  

Problem summary

• A NEW COLUMN IS MISSING IN THE MIG SCRIPT.
  

Problem conclusion

• FIXED
  

Temporary fix

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

  WS Q-REPLIC LUW

• Fixed component ID

  5724N9801

Applicable component levels

• R950 PSY

     UP