DB2 creating temporal table creating column with CURRENT PACKAGESET or CLIENT_APPNAME

Question

Does anyone know which of the DB2 special registers are allowed in CREATE TABLE statement for DB2 temporal tables or in general in CREATE TABLE statement?

I am trying to CREATE TABLE COLUMNS WITH CURRENT PACKAGESET or CLIENT_APPNAME, they are not being identified by DB2. I tried almost all combinations of key words (marked in bold).

Create table Statement

CREATE TABLE EMPLOYEE 
(EMP_NR INT NOT NULL
,FIRST_NAME CHAR(20) NOT NULL
,LAST_NAME CHAR(20) NOT NULL
,TSROWBEGIN TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN    
,TSROWEND TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END     
,TSPGMSTART TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID

**,IDTERMANV CHAR(8) GENERATED DEFAULT WITH CURRENT PACKAGESET    
,IDTERM VARCHAR(128) GENERATED DEFAULT WITH CLIENT_APPLNAME**

,STDB2ACTION CHAR(1)  GENERATED ALWAYS AS  ( DATA CHANGE OPERATION )
,PERIOD SYSTEM_TIME(TSROWBEGIN, TSROWEND)  
);

It results in

ILLEGAL USE OF KEYWORD CURRENT. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.68.61

or

ILLEGAL USE OF KEYWORD CLIENT_APPLNAME. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.68.61

Any suggestions on how to create column with default value of program name which is doing CUD operation on the table?

data_henrik data_henrik · Accepted Answer · 2020-01-22T09:09:51

You can use the special registers for current date / time / timestamp and for user information (user, session user, system user). Take a look at the CREATE TABLE reference.

The same reference also has a section about what cannot be used, in case you try to use a function or put the CREATE statement into a procedure. Among other things, the GENERATED value cannot be based on the following:

Special registers and built-in functions that depend on the value of a special register.

DB2 creating temporal table creating column with CURRENT PACKAGESET or CLIENT_APPNAME

1 Answers