A fix is available
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