Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Delivered
Created by Guest
Created on Nov 5, 2021

Temporal Base Table SQLOUT INSERTS have generate always columns as part of the insert

The timestamp columns supporting temporal should not be part of the INSERT. Also anytime a generate always column is excluded as it should have been in this run, columns must be added to the INSERT statement.

--UNDO SQL FOR SUBSYSTEM: DAA1

--LRSN:00DA7AEA46E5238BB600 URID:000000006E00700E0F73

--#00000001 *UNDO DELETE* DATE/TIME:2021-10-18/06.53.42

INSERT INTO

"TOTC"."ECRN"

VALUES(

'KA0 '

,+287

,'2020'

,'1'

,'F'

,'KA0REMIT-FED '

,'2021-10-14-06.25.15.511368025000'

,'9999-12-30-00.00.00.000000000000'

,'2021-10-14-06.25.15.511368025000'

);

SET CURRENT SQLID='TOTC';

CREATE TABLE TOTC.ECRN

(CO_C CHAR(6) FOR SBCS DATA NOT NULL,

EMP_FILE_NB DECIMAL(9, 0) NOT NULL,

CAL_4_DGT_YR_D CHAR(4) FOR SBCS DATA NOT NULL,

QTR_NB CHAR(1) FOR SBCS DATA NOT NULL,

REMT_TYP_C CHAR(1) FOR SBCS DATA NOT NULL,

REMT_NB CHAR(30) FOR SBCS DATA NOT NULL,

SYS_BEG_D TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL

GENERATED ALWAYS AS ROW BEGIN,

SYS_END_D TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL

GENERATED ALWAYS AS ROW END,

TRANS_ID_D TIMESTAMP (12) WITHOUT TIME ZONE NOT NULL

GENERATED ALWAYS AS TRANSACTION START ID,

PERIOD SYSTEM_TIME (SYS_BEG_D, SYS_END_D),

CONSTRAINT CO_C

PRIMARY KEY (CO_C,

EMP_FILE_NB,

CAL_4_DGT_YR_D,

QTR_NB,

REMT_TYP_C))

IN DTOTCEM1.SEMECRN

PARTITION BY SIZE

AUDIT NONE

DATA CAPTURE NONE

WITH RESTRICT ON DROP

CCSID EBCDIC

NOT VOLATILE

APPEND NO ;

COMMIT;

Needed By Quarter
  • Admin
    SOO KIM
    Reply
    |
    Oct 31, 2022

    Feature delivered in PH43957 APAR / UI81330 PTF

  • Guest
    Reply
    |
    Jan 25, 2022

    FYI to anyone - remember UNDO has it's issues as stated in the manual. LAT as stated in the manual is not a recovery tool. But if you must use UNDO, use SET SYSIBMADM.REPLICATION_OVERRIDE = 'Y'; in ADBTEP2 or DSNTEP2.

  • Guest
    Reply
    |
    Dec 17, 2021

    We just ran into the same situation for a production recovery following a bad spufi update ad-hoc that ran on a temporal table. we needed to recover the updates. although producing the Undo SQL is how we thought we could resolve quickly, we found out that we would need to modify the thousands of statements to remove the TRANSI_ID_D. We had to try to find a synch point to recovery to, since the user made no backup prior to their update.

    We have used the undo several times in the past and it saved us big time. but this time it was useless. Temporal Tables are REQUIRED by IBM to contain this TRANS_ID_D generated always timestamp column. So although IBM is treating this AHA as an enhancement, We see this as a DEFECT! All IBM tools should support all types of DB2 objects that IBM introduces. Temporal has been around for a while and has since matured. Why wasn't log analyzer tested for support of temporal tables for undo/redo sql. they would have quickly found out that IT DOES NOT WORK!. bottom line, the tools should support all DB2 objects, no different than how it supports LOB , XML, etc. it should support TEMPORAL tables. It's not like we had an option on whether or not to create this GENERATED ALWAYS TIMESTAMP for these types of tables, it is a REQUIREMENT.


    I am hoping this gets resolved sooner than later. THIS IS A DEFECT!!!!

  • Guest
    Reply
    |
    Nov 15, 2021

    This issue is not restricted to temporal base tables. It also happens to with generated UNDO SQL by log analyzer for any table with generated always columns.

    The issue is described without a solution. I assume the "idea" for log analyzer is to have a parameter to not generate these columns in the insert in the generated sql. correct?