Re: Why does USER require VARCHAR and not work with CHAR?

On 12-Oct-2017 12:47 -0600, Glenn Gundermann wrote:

IBM i 7.3, TR 2, cum level 17061, DB2 PTF group level 6

I have a CREATE TABLE statement where the create user column is
using CHAR(18) and the change user column won't allow CHAR(18) but
works if VARCHAR(18).
Why can't they both be CHAR(18)?

This won't work:

CREATE OR REPLACE TABLE glenn_test_table FOR SYSTEM NAME test_table
( create_user FOR COLUMN recrtuser CHAR(18) CCSID 37 NOT NULL
DEFAULT USER
, change_user FOR COLUMN rechguser CHAR(18) CCSID 37 NOT NULL
GENERATED ALWAYS AS (USER)
) RCDFMT RPCMMNT

SQL0574 Column, sequence, or variable attribute is not valid.

The msg SQL0574 sqlcode -574 suggests for a generated expression column ["For a generated column"], that "the data type and length must exactly match the definition of the special register, built-in global variable, or data change operation."

This does work:

CREATE OR REPLACE TABLE glenn_test_table FOR SYSTEM NAME test_table
( create_user FOR COLUMN recrtuser CHAR(18) CCSID 37 NOT NULL
DEFAULT USER
, change_user FOR COLUMN rechguser VARCHAR(18) CCSID 37 NOT NULL
GENERATED ALWAYS AS (USER)
) RCDFMT RPCMMNT

My question, why?

In this variation for the change_user column, the VARCHAR matches the USER special register.

Thus as to "why?", a restriction [as alluded by the message] for the as-generated-expression-clause, that the data type "must exactly match" the data type of the "generated value/expression" that is defined as one of a variable, special register, or data-change indicator for the value maintained for the column whenever a row is changed or added. Essentially, there is no syntax [nor underlying support] available to request to cast the result of the expression into another data type; limited, merely to specifying the keyword or identifier. In the other example, for create_use, the SQL will for the DEFAULT clause, implement the implicit-cast feature for the specified keyword USER.

(https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyauditcols.htm)[Database->Programming->SQL programming->Data definition language->Creating auditing columns]
"… There are three types of values that the system uses to maintain status information for any modification to a row: the type of data change, a special register, or a built-in global variable. You can have multiple columns in a table that track this information. Each column defined as one of these generated expression columns must have a data type that exactly matches the required definition for the item being generated. …"