Fixes are available
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 number
IC83744
Reported component name
WMQ FILE TRANSF
Reported component ID
5724R1000
Reported release
704
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2012-05-24
Closed date
2012-07-19
Last modified date
2012-07-20
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
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEP7X","label":"WebSphere MQ File Transfer Edition"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0.4","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]
Document Information
Modified date:
20 July 2012