IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
20 July 2012