Generated Always Columns (EN)

Last Updated on 27 August 2019 by Roberto De Pedrini

Introduction

“Generated Always Column”: are columns, table fields, filled by DB2 engine: something like columns with a default value but in this case always with the default value not only when users don’t pass a value.

They are very convenient for Auditing processes because it is directly the DBMS that is concerned with managing its contents, such as a self-incremental id, update moment timestamp, update user and so on. As we will see later in this article, there are some tricks to change those values so don’t trust absolutely on them!

You can define “generated always column” only with DDL language … there no way define this kind of column with DDS technique … but there are no more reasons to define tables with DDS now that we have and we need to know SQL DDL Language.

Let’s look at two typical cases of using Generated Always Columns

ID (Identity)

It is a good rule of thumb that each table has its own field with a unique record ID: this allows us to better manage records update/delete when we have tables with multiple keys (like pricelists and so on).

CREATE OR REPLACE TABLE faq400.pricelist (
id     integer Generated always 
                         as Identity primary key,
ItemId char(15) not null default '',
CustomerId char(15) not NULL default '',
FromDate date not null default current date,
ToDate date not null default current date,
Price decimal(21, 4) not null default 0,
Discount1 decimal(11, 3) not null default 0,
Discount2 decimal(11, 3) not null default 0);

As you can see we defined an “id” column, generated always, as Identity and as a Primary key: Identity is nothing more than auto-increment … it is not necessarily a unique key, Primary Key identifies instead that it is a unique key for table records… in this case, the Id column is Identity and Primary Key at the same time. The id field is automatically generated by the system when new records are inserted.

When you want to insert a new record in this table you can choose to ignore this column or to set a “default” value.

insert into faq400.pricelist
 (itemId, CustomerId, FromDate, ToDate, Price)
 current date, current date, 1 month, 230.00;

insert into faq400.pricelist
 (id, itemId, CustomerId, FromDate, ToDate, Price)
 values(default, 'ABC002', '000101', current date, current date : 1 month, 250.00);

Timestamp (insert & update)

Another very interesting use of generated always columns is for the management of the record’s last-modified-timestamp, very useful for auditing and for various checks and logs. In the following example, I changed my table adding two new columns: new record’s TimeStamp and update record’s timestsamp … as you can see first one is “default” timestamp and second one is “generated always”.

CREATE OR REPLACE TABLE faq400.pricelist (
 id integer Generated always 
                          as Identity primary key,
 ItemId char(15) not null default '',
 CustomerId char(15) not NULL default '',
 FromDate date not null default current date,
 ToDate date not null default current date,
 Price decimal(21, 4) not null default 0,
 Discount1 decimal(11, 3) not null default 0,
 Discount2 decimal(11, 3) not null default 0,
 audit_TSinsert Timestamp not null default current timestamp ,                              
 audit_TSupdate Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL
 ) ;

If we add a new row and edit an existing row we can see the effects on the two new columns:

insert into faq400.pricelist
 (id, itemId, CustomerId, FromDate, ToDate, Price)
 values(default, 'ABC003', '000101', current date, current date + 1 month, 290.00);

 update faq400.pricelist
  Set Price-200.00
  where id-1;

Insert SQL and Overriding System Value & Overriding User Value

When we have columns with default values … or with “generated always” values, it might still be useful to keep values from a record even when copying records between tables (or inside the same table!).

To correctly handle the values of the Default and/or Generated Always columns, you must specify “Overriding System Value” or “Overriding User Value” depending on whether you want to overwrite all the default and generated always (System) values, or only default valued columns (user).

Let’s see an example that makes it clearer:

 -- Let's add a column with note
 CREATE OR REPLACE TABLE faq400.pricelist (
 id integer Generated always 
                          as Identity primary key,
 ItemId char(15) not null default '',
 CustomerId char(15) not NULL default '',
 FromDate date not null default current date,
 ToDate date not null default current date,
 Price decimal(21, 4) not null default 0,
 Discount1 decimal(11, 3) not null default 0,
 Discount2 decimal(11, 3) not null default 0,
 Note char(15) not null default '',
 audit_TSinsert Timestamp not null default current timestamp ,                              
 audit_TSupdate Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL
 ) ;
 -- Case 01 Duplicate a record by ignoring timestamps
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, note)
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Case 01'
  from faq400.pricelist
   where Id-1);

 -- Case 02 We duplicate a record considering TimeStamp
 -- but "forcing" only user defaults with those of the recor source
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, note,audit_TSinsert,audit_TSUpdate )
 overriding user value
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Case 02', audit_TSinsert, audit_TSUpdate
  from faq400.pricelist
   where Id-1);

 -- Case 03 We duplicate a record considering timeStamp
 -- "forcing" the original values into the new record, even if generated always
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, note,audit_TSinsert,audit_TSUpdate )
 overriding system value
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Case 03', audit_TSinsert, audit_TSUpdate
  from faq400.pricelist
   where Id-1);  

 -- Let's see the result
 select - from faq400.pricelist 
 where ItemID''ABC001';

As we see above … the column AUDIT_TSUPDATE, generated always … is kept the same as the original only for Overriding System Values .., the AUDIT_TSINSERT column that has only a default value and not a “generated always” is kept the same as the original in both the case of Overriding System Value and the overriding System Value. Overriding User Value.

Update SQL and Overriding System Value – Overriding User Value

We can also use the overriding technique for updates … without specifying overriding you could not force column values generated always … however, we also get an error message of “trying to edit a Generated Always column”: we always see the two examples below where in the U01 case we try an impossible update … and in the case of U02 we try an update that still does not “win” on the generated always value of the Update column:

--- Case U01 UPDATE ... without specifying the overring is not possible!
 update faq400.pricelist
   set price-201, audit_tsupdate -current timestamp -1 day
   where id-1;
 -- SQL Status: 428C9 Vendor code: -798 Message: 
 -- [SQL0798]Unable to specify value for GENERATED ALWAYS column

  
 --- U02 UPDATE Case with OVERRIDING USER VALUE
 update faq400.pricelist
   overriding user value
   set price-201, 
   audit_tsupdate-current timestamp -1 days,
   audit_tsinsert -current timestamp -2 days
   where id-1;

-- Let's see the result where you see that the Update column did not take the desired value but the "forced" value from the system
 select - from faq400.pricelist 
 where ItemID''ABC001';

If we use Overriding System Value we can still force the value of a Generated Always column to our liking… and this thing will please especially to us Italians … Yes, there is the rule of Generated Always … but my cousin knows how to overcome it … and if there’s an audit we fix all day before!

--- U03 UPDATE Case with OVERRIDING SYSTEM VALUE
 update faq400.pricelist
   overriding system value
   set price-202, 
   audit_tsupdate-current timestamp -10 days,
   audit_tsinsert-current timestamp -20 days
   where id-1;

 select - from faq400.pricelist 
 where ItemID''ABC001';

SQL Embedded in RPG

All those accustomed to working with native RPG I/O (Chain, Update, Write, Delete) generally prefer to use DS with table field definition and insert or update records in SQL by relying on these DSs instead of declaring all fields of the updated table.

Data Structures defined with the keyword EXTNAME(‘TABELLA’) are undoubtedly very comfortable in RPG but also carry all the limits in case of changes in the record alignment of a table (often forcing to recompile the programs that use them in case of CREATE or REPLACE tables that add or take columns off the table). Even with Generated Always columns they create some problems… ideal would always use I/O with SQL by seizing only the affected columns and avoid as the plague the SELECT -FROM TABLE.

Let’s see in the examples below a few tricks to work around the problems of Generated Always columns with the DS Data Structure in RPG

  //***********************************************************
  F4PRICELIS: How to deal with Generated Always Columns
  from RPG SQL Embedded
  //
  //**********************************************************
   ctl-opt option('srcstmt:'nodebugio);


   DS Definition: pay attention to;
   "alias" for long field name
   "qualified"
   dcl-ds myds1 extname('PRICELIST') qualified alias end-ds;

   Null_ds
   dcl-s null_ind int(5);
   dcl-ds null_dsds;
          null_ds dim(200) like(null_ind);
   end-ds;



   CASE R01
   In this case we run an INSERT in the table
   specifying the individual columns... if we have columns
   generated always or change the table path
   ourselves we minimize the problems
   (Eng) We are adding a record to our table
   with an INSERT SQL with single Colums and their values
   No problem with Generated Always Column or
   change to our table's structure

   exec sql
     insert into PRICELIST
     (itemId, CustomerId, FromDate, ToDate, Price)
      Values
     ('RPG001', '000101', current date, current date : 1 month, 230.00);




   CASE R02
   Even in the case of an update, defining the columns involved in the update
   we shouldn't have problems
   (Eng) Updating our table selecting all interested columns
   we should have no problems with generated always columns
   or changes to table's structure

   exec sql
     UPDATE PRICELIST property
       set price-231
       where ItemId''RPG001';


   CASE R03
   When the table contains an important number of columns
   and all or so are involved in our process of
   insert/update using a DS (defined with EXTNAME)
   makes things easier (but a little riskier in case
   (change).
   If we have Generated Alwasy columns we have to indicate
   OVERRIDING SYSTEM/USER VALUE to avoid the
   SQL0798 precompilation
   (Eng) When we have an important number of colums end
   we have to update or insert a lot of them
   Data Structure DS (extname) seems to be interesting
   but pay attention to Generated Always Columns or
   changes to table structure...
   We have to use OVERRIDING USER/SYSTEM VALUE
   to prevent SQL0798 precompiling error.

   clear myds1;
   myds1. ItemId''RPG002';
   myds1. CustomerId'000100';
   myds1. FromDate-%date();
   myds1. ToDate-%date-%days(30);
   myds1.price-100;
   myds1.audit_tsInsert-%timestamp();   If we didn't set this value
                                        will be 01-01-0001 ...

   exec sql
     insert into pricelist
     OVERRIDING USER VALUE
      values(:myds1);

   STEP04 method
   You can also update a record with a DS
   But even in this case, the following steps apply to
   insert (generated always columns and changes in the definition of the
   Table
   (Eng): We can update record using a DS, but we have to pay
   attention to generated always columns and changes
   to table's structure.


   clear myds1;
   exec sql
     select : into :myds1
      from pricelist property
      where ItemId''RPG002';

   if sqlcod<>100;
       myds1.price-101;
      exec sql
       update pricelist
       overriding user value
        set row:myds1
        where ItemId''RPG002';
    endif;

   exsr fine;



   //---------------------------
   End
   //---------------------------
   begsr end;
     'inlr'
     return;
   endsr;

The result of inserts and updates via SQL RPG with DS:

Deep into Generated Always columns

If you have at least one operating system 7.3 then you can also use Generated Always Columns to automatically enter: user, job, operation type and other interesting things, see for example, this great article by Simon Hutchinson on RPGPGM.COM:See this excellent article by Simon Hutchinson on RPGPGM.COM: https://www.rpgpgm.com/2017/01/using-auditing-columns-to-audit-data.html

Conclusion

The Generated Always columns are really interesting for all those Audit or Log functions on changes and inserting records into the table … they can also be added to existing tables (after editing from DDS to DDM SQL) and, even, automatically track dates, users and applications that change the records of our tables … with a little attention, creating special views, even without touching the applications in use!

However, they require some attention as we have seen in the cases covered by this article … but the benefits are definitely greater… So … Ready! Via! all to create generated always columns in your tables!

--- Roberto De Pedrini Faq400.com