Skip to content
Advertisement

How to create generated columns in DB2/400

I wanna create a virtual column which concatenates two columns to 1. My attempt was:

--result sqlstate 42601 -104 (Token not valid: (. Valid token: IDENTITY)
alter table schema.table
  add column name1_v generated always as (trim(name1) || ' ' || trim(vt_alt))
  add column vtKuTx_v generated always as (trim(vtKuTx) || ' ' || trim(vt_alt))
;

This should work according to the docs. ( https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzpdf.pdf?view=kc ) (page 851). Has anyone an idea how to get this done?

Thanks in advance.

edit: I doublechecked it. This doesn’t work either.

create or replace table table.schema (
  number int default 2,
  square int generated always as (number * number)
);

Advertisement

Answer

Always give your Db2 version (or in this case , the version of the i series ) when asking for help.

As you are using Db2 for i, you should study the documentation for ALTER TABLE for that i series platform , and then choose the correct version of the i series software on that page.

For the GENERATED clause of ALTER TABLE the i-series documentation specifies the following restriction in Note 5:

5 GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), the column is an identity column, identity-options are specified, as-row-transaction-timestamp-clause is specified, as-row-transaction-start-id-clause is specified, or the column is a row change timestamp.

That may be the reason for your -104 exception. So you will need to find an alternate method to achieve your goal.

2 People found this is helpful
Advertisement