CODEWITHSUNDEEP
sqldb2db2-400
iiSiggi
iiSiggi

Reputation: 175

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)
);

Upvotes: 0

Views: 748

Answers (3)

mao
mao

Reputation: 12267

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.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

NOTE: This answer is for DB2 LUW, not DB2-400. Maybe the same reasoning applies.

DB2 prevents you to add a column inadvertently because this can be a very heavy operation. Instead DB2 forces you to:

  • Disable the constraints.
  • Add the columns.
  • Enable the constraints again.

As a DB2 SQL Compiler Developer states (sic):

Adding/altering a generated column is the only table action which physically updates the table. To be more precise it will likely update all zillion rows of it (which can be a lot in Viper with range partitioning).

So rather than quitely filling up peoples logspace (or stressing the auto-archival option) we decided it would be appropriate to perform such heavy operations while the table is in check pending.

Therefore, you can do it as:

set integrity for schema.table off;

alter table schema.table
  add column name1_v generated as (trim(name1) || ' ' || trim(vt_alt))
  add column vtKuTx_v generated as (trim(vtKuTx) || ' ' || trim(vt_alt))
;

set integrity for schema.table immediate checked force generated;

See running example at db<>fiddle.

Upvotes: 0

marcothesane
marcothesane

Reputation: 6721

Try :

ALTER TABLE schema.table
  ADD COLUMN name1_v  DEFAULT trim(name1) || ' ' || trim(vt_alt)
  ADD COLUMN vtKuTx_v DEFAULT trim(vtKuTx) || ' ' || trim(vt_alt)
;

Here, I found a tutorial to that ... https://www.db2tutorial.com/db2-basics/db2-alter-table-add-column/

Upvotes: 0

Related Questions