IBM Support

PI99084: NEW FUNCTION

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • New Function
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 for z/OS users of system-period                   *
    * temporal table and the built-in SYSPROC.                     *
    * SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY                     *
    * stored procedure                                             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * This APAR addresses several problems:                        *
    * 1. Code change to support future new                         *
    * function.                                                    *
    * 2. Incorrect output may occur when                           *
    * using the DEFAULT value to update a                          *
    * nullable transaction-start-ID column                         *
    * in a system-period temporal table that                       *
    * contains non-null values.                                    *
    * 3. An IFCID 376 trace record with the                        *
    * new value 1203 in QW0376FN will be                           *
    * written for a CALL statement that                            *
    * invokes the SYSPROC.SET_MAINT_MODE_                          *
    * RECORD_NO_TEMPORALHISTORY stored                             *
    * procedure. This new value in QW0376FN                        *
    * helps to identify the application that                       *
    * issues the CALL.                                             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    This APAR addresses several problems:
    
    1. Code change to support future new function.
    
    2. Incorrect output may occur when using the DEFAULT
    value to update a nullable transaction-start-ID column
    in a system-period temporal table that contains
    non-null values.
    
      Here is an example:
    
      -- create a regular table and insert one row
      CREATE TABLE INSURANCE_POLICY
       (POLICY_ID CHAR(4) NOT NULL,
        COVERAGE INT NOT NULL,
        SYS_START TIMESTAMP(12) NOT NULL,
        SYS_END TIMESTAMP(12) NOT NULL,
        CREATE_ID TIMESTAMP(12));
    
      INSERT INTO INSURANCE_POLICY
             VALUES ('AB01', 1000,
                     '2018-01-01-00.00.00.000000000000',
                     '2020-12-31-24.00.00.000000000000',
                     '2018-01-01-00.00.00.000000000000');
      COMMIT;
    
      -- convert the table into system-period temporal table
      ALTER TABLE INSURANCE_POLICY ALTER COLUMN SYS_START
        SET GENERATED AS ROW BEGIN;
    
      ALTER TABLE INSURANCE_POLICY ALTER COLUMN SYS_END
        SET GENERATED AS ROW END;
    
      ALTER TABLE INSURANCE_POLICY ALTER COLUMN CREATE_ID
        SET GENERATED AS TRANSACTION START ID;
    
      ALTER TABLE INSURANCE_POLICY
        ADD PERIOD SYSTEM_TIME(SYS_START, SYS_END);
    
      COMMIT;
    
      SELECT * FROM INSURANCE_POLICY;
        +-----------------------------
        | POLICY_ID |    COVERAGE    |
        +-----------------------------
      1_| AB01      |           1000 |
        +-----------------------------
        +-----------------------------------
        |            SYS_START             |
        +-----------------------------------
      1_| 2018-01-01-00.00.00.000000000000 |
        +-----------------------------------
        +-----------------------------------
        |             SYS_END              |
        +-----------------------------------
      1_| 2020-12-31-24.00.00.000000000000 |
        +-----------------------------------
        -----------------------------------+
        |            CREATE_ID             |
        -----------------------------------+
      1_| 2018-01-01-00.00.00.000000000000 |
        -----------------------------------+
    
      -- now update column COVERAGE. column SYS_START,
      -- SYS_END and CREATE_ID will be updated as well
      -- with the DEFAULT values
      UPDATE INSURANCE_POLICY
         SET COVERAGE = COVERAGE * 2
       WHERE POLICY_ID = 'AB01';
    
      SELECT * FROM INSURANCE_POLICY;
        +-----------------------------
        | POLICY_ID |    COVERAGE    |
        +-----------------------------
      1_| AB01      |           2000 |
        +-----------------------------
        +-----------------------------------
        |            SYS_START             |
        +-----------------------------------
      1_| 2018-07-05-18.49.10.723938942000 |
        +-----------------------------------
        +-----------------------------------
        |             SYS_END              |
        +-----------------------------------
      1_| 9999-12-30-00.00.00.000000000000 |
        +-----------------------------------
        -----------------------------------+
        |            CREATE_ID             |
        -----------------------------------+
      1_| 2018-07-05-18.49.10.723938942000 |
        -----------------------------------+
    
      -- the updated row shown above contains incorrect value
      -- for column CREATE_ID, since the null value should be
      -- assigned to the transaction-start-ID column if the
      -- column is nullable.
    
      -- after applying this PTF, the following row will be
      -- returned after executing the above UPDATE statement:
      SELECT * FROM INSURANCE_POLICY;
        +-----------------------------
        | POLICY_ID |    COVERAGE    |
        +-----------------------------
      1_| AB01      |           2000 |
        +-----------------------------
        +-----------------------------------
        |            SYS_START             |
        +-----------------------------------
      1_| 2018-07-05-18.49.10.723938942000 |
        +-----------------------------------
        +-----------------------------------
        |             SYS_END              |
        +-----------------------------------
      1_| 9999-12-30-00.00.00.000000000000 |
        +-----------------------------------
        -----------------------------------+
        |            CREATE_ID             |
        -----------------------------------+
      1_| ?                                |
        -----------------------------------+
    
    3. An IFCID 376 trace record with the new value 1203
    in QW0376FN will be written for a CALL statement that
    invokes store procedure
    SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY.
    This new value in QW0376FN helps to identify the
    application that issues the CALL.
    

Problem conclusion

Temporary fix

Comments

  • Code to support future new function.
    
    Additional Keywords: INCORROUT SQLSPTT
    

APAR Information

  • APAR number

    PI99084

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-06-12

  • Closed date

    2018-07-16

  • Last modified date

    2018-08-02

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

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

    UI57294

Modules/Macros

  • DSNXRIHR DSNRRPRC DSNXRIHS DSNXOMRG DSNXOST  DSNINUKY DSNUEXUP
    DSNXOVC  DSNXOP1  DSNTIAM  DSNRNTFY DSNXECK  DSNXRIHB DSNXEAU2
    DSNLZOGV DSNUECMI DSNXRIHD DSNXOIN  DSNGEDLC DSNUECSE DSNXRBND
    DSNARIB  DSNXLDBD DSNXEBR  DSNTIA1  DSNXECWU DSNILREP DSNXORSO
    DSNX9RIB DSNIRELK DSNXOPTH DSNDLVL  DSNXFNLV DSNXENR  DSNIDLER
    DSNITUPD DSNDQW05 DSN7GCMD DSNITDLE DSNXELX  DSNUECM0 DSNXGRTM
    DSNUECM2 DSNXERT2 DSNXOFF  DSNXGRM1 DSNXESQL DSNXOSTP DSNXOCAS
    DSNXOFMG DSNISRTI
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI57294

       UP18/07/27 P F807

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 August 2018