IC83744: MIGRATION OF DATABASE LOGGER FROM 7.0.3 TO 7.0.4 DOES NOT CREATE FOREIGN KEY IN MONITOR_EXIT_RESULT TABLE

WebSphere MQ File Transfer Edition V7.0 Fix Pack 7.0.4.2
WebSphere MQ V7.5 Fix Pack 7.5.0.1

APAR status

• Closed as program error.

Error description

• When migrating a WebSphere MQ FTE Database Logger DB2 database
  from 7.0.3 to 7.0.4, the EXITRSLT_IN_ACTION foreign key
  constraint on the MONITOR_EXIT_RESULT table is not recreated
  by the ftelog_tables_db2_703-704.sql SQL after that table is
  dropped.
  
  There is no obvious symptom of this problem other than the
  absence of a foreign key that is intended to be present. The
  ftelog_tables_db2.sql SQL does not have the same problem. That
  is, the EXITRSLT_IN_ACTION is created if the
  ftelog_tables_db2.sql SQL is used to create the table rather
  than migrating it from 7.0.3.
  

Local fix

• Add SQL needed to create the EXITRSLT_IN_ACTION constraint in
  the table "FTELOG"."MONITOR_EXIT_RESULT" to the
  ftelog_tables_db2_703-704.sql before it is used.ﾠ The SQL from
  the ftelog_tables_db2.sql that creates the EXITRSLT_IN_ACTION
  constraint can be used:
  
  ALTER TABLE "FTELOGﾠ "."MONITOR_EXIT_RESULT"
  ADD CONSTRAINT "EXITRSLT_IN_ACTION" FOREIGN KEY
  ("ACTION_ID")
  REFERENCES "FTELOGﾠ "."MONITOR_ACTION"
  ("ID")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
  ENFORCED
  ENABLE QUERY OPTIMIZATION;
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  All users of WebSphere MQ File Transfer Edition migrating a
  database logger using DB2 from version 7.0.3. z/OS DB2 databases
  are unaffected.
  
  
  Platforms affected:
  AIX, HP-UX Itanium, HP-UX PA-RISC, HP-UX OpenVMS, IBM iSeries,
  Linux on Power, Linux on S390, Linux on x86, Linux on x86-64,
  Linux on zSeries, Solaris SPARC, Solaris x86-64, Windows,
  MultiPlatform
  
  ****************************************************************
  PROBLEM SUMMARY:
  The ftelog_tables_db2_703-704.sql SQL migrates a DB2 database
  logger database from 7.0.3 to 7.0.4. Part of that migration
  involved dropping and recreating the MONITOR_EXIT_RESULT
  table. The EXITRSLT_IN_ACTION foreign key constraint that
  previously existed (and that exists when creating the database
  using ftelog_tables_db2.sql SQL) is not recreated after the
  MONITOR_EXIT_RESULT table is created again.There is no
  significant problem as a result of this. It is only the database
  logger that should insert rows into MONITOR_EXIT_RESULT and
  there is no reliance on the constraint being present under
  normal operation.
  

Problem conclusion

• This APAR corrects the problem in the
  ftelog_tables_db2_703-704.sql by creating the EXITRSLT_IN_ACTION
  foreign key constraint.
  
  Users that have detected this problem after migrating can use
  the following SQL to correct the table EXITRSLT_IN_ACTION.
  Note that the statement termination character needs to be
  changed to '@' in order to execute this. Use "db2 -td@" when
  using the command line.
  
  SET SERVEROUTPUT ON@
  BEGIN
  IF (NOT EXISTS (SELECT 1 FROM SYSIBM.SYSRELS WHERE
  RELNAME='EXITRSLT_IN_ACTION')) THEN
  EXECUTE IMMEDIATE(' ALTER TABLE FTELOG.MONITOR_EXIT_RESULT '||
  ' ADD CONSTRAINT "EXITRSLT_IN_ACTION" FOREIGN KEY
  ("ACTION_ID") '||
  ' REFERENCES "FTELOG"."MONITOR_ACTION" ("ID") '||
  ' ON DELETE NO ACTION '||
  ' ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION');
  call DBMS_OUTPUT.PUT_LINE('EXITRSLT_IN_ACTION constraint
  added.');
  ELSE
  call DBMS_OUTPUT.PUT_LINE('No constraint added - exists
  already!');
  END IF;
  COMMIT WORK;
  END@
  
  When executed, there will be one of two outputs:
  
  1) "EXITRSLT_IN_ACTION constraint added." - the problem
  described by this APAR was present but was successfully fixed.
  2) "No constraint added - exists already!" - the problem
  described by this APAR was not present and no changes were made.
  
  It is safe to execute this SQL against a database that does
  not have the problem described by this APAR.
  ---------------------------------------------------------------
  The fix is targeted for delivery in the following PTFs:
  
  Platform           v7.0
  --------           --------------------
  Multiplatforms     7.0.4.2
  
  Platform           v7.5
  --------           --------------------
  Multiplatforms     7.5.0.1
  
  The latest available maintenance can be obtained from
  'WebSphere MQ Recommended Fixes'
  http://www-1.ibm.com/support/docview.wss?rs=171&uid=swg27006037
  
  If the maintenance level is not yet available information on
  its planned availability can be found in 'WebSphere MQ
  Planned Maintenance Release Dates'
  http://www-1.ibm.com/support/docview.wss?rs=171&uid=swg27006309
  

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

  WMQ FILE TRANSF

• Fixed component ID

  5724R1000

Applicable component levels

• R704 PSY

     UP