GENERATED ALWAYS field gives SQLCODE = -798 in RC/Edit
Article ID: 131788
Updated On:
Products
CA RC/Migrator
CA Endevor SCM Interface
DB2 Administration
CA RC Compare for DB2 for z/OS
CA RC Extract for DB2 for z/OS
CA RC/Query
CA RC Secure for DB2 for z/OS
CA RC Update for DB2 for z/OS
CA Database Analyzer for DB2 for z/OS
CA Fast Unload for DB2 for z/OS
CA Fast Check for DB2 for z/OS
CA Fast Index for DB2 for z/OS
CA Fast Load for DB2 for z/OS
CA Rapid Reorg for DB2 for z/OS
Issue/Introduction
A SQLCODE = -798 sql code is seen when inserting or updating a row in a table where a value was specified for a column that is defined with GENERATED ALWAYS attributes and its value is not being set with the DEFAULT keyword. GENERATED ALWAYS columns should not be specified in the column list for an insert operation, or in the SET clause for an update operation unless the keyword DEFAULT is specified.
In RC/Update for Db2 for z/OS (RCQ), by default, all columns are selected therefore the best way around this is to unselect a column.
In RC/Edit we are getting the following error message: SQLCODE = -798, ERROR: A VALUE CANNOT BE SPECIFIED FOR COLUMN TSCDC WHICH IS DEFINED AS GENERATED ALWAYS
The field is defined as follows: TSCDC TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN
In RC/Update for Db2 for z/OS (RCQ), by default, all columns are selected therefore the best way around this is to unselect a column.
In RC/Edit we are getting the following error message: SQLCODE = -798, ERROR: A VALUE CANNOT BE SPECIFIED FOR COLUMN TSCDC WHICH IS DEFINED AS GENERATED ALWAYS
The field is defined as follows: TSCDC TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN
Environment
Db2 for z/OS
Resolution
In order to avoid referencing this generated DB2 field in your edit , RC/Update has the ability to ignore this column. This will allow DB2 to handle it as it should when a record is inserted in the table.
The column can be un-selected for the edit.
When an Edit of the table is started the RC/Edit Options screen is displayed, Use the EQF to un-select this column. Enter "Y" in the Where field displayed.
Data Selection
Where ==> Y
Then the Data Query Edit screen is displayed.
Sample column list below. Note column TSCDC. This one is a generated column defined as:
TSCDC TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN
Overwrite the "S" in the "SEL" column with a space on the line containing column TSCDC which you don't want to edit anyway.
PF3 back to the edit data screen where new rows can be inserted as normal and column TSCDC will be handled by DB2.
New rows can then be added. In order to use the EQF again it can be saved by giving it a name and making it the Default EQF for this table when next it is editted.
The column can be un-selected for the edit.
When an Edit of the table is started the RC/Edit Options screen is displayed, Use the EQF to un-select this column. Enter "Y" in the Where field displayed.
Data Selection
Where ==> Y
Then the Data Query Edit screen is displayed.
Sample column list below. Note column TSCDC. This one is a generated column defined as:
TSCDC TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN
Sel C Ord Column Name Type
S __ 1 GROUP_ID D(6,0)
S __ 2 PLATFORM_CD C(1)
S __ 3 TSCDC TIMEST>
S __ 4 GROUP_NAME C(60)
S __ 5 GRP_MBR_COUNT D(7,0)
S __ 6 CALC_COV_TYPE_IND C(1)
S __ 7 TERM_DEP_IND C(1)
Overwrite the "S" in the "SEL" column with a space on the line containing column TSCDC which you don't want to edit anyway.
Sel C Ord Column Name Type
S __ 1 GROUP_ID D(6,0)
S __ 2 PLATFORM_CD C(1)
__ 3 TSCDC TIMEST>
S __ 4 GROUP_NAME C(60)
S __ 5 GRP_MBR_COUNT D(7,0)
S __ 6 CALC_COV_TYPE_IND C(1)
S __ 7 TERM_DEP_IND C(1)
PF3 back to the edit data screen where new rows can be inserted as normal and column TSCDC will be handled by DB2.
New rows can then be added. In order to use the EQF again it can be saved by giving it a name and making it the Default EQF for this table when next it is editted.
Additional Information
Feedback
Powered by
