Alter GENERATED ALWAYS Column in Table

[Fin]

I have a table defined say as follows with a generated always column :

CREATE TABLE STOCK_DATA (
STOCK_ID INTEGER NOT NULL WITH DEFAULT 0 ,
OPEN DECIMAL(7,2) NOT NULL ,
HIGH DECIMAL(7,2) NOT NULL ,
LOW DECIMAL(7,2) NOT NULL ,
CLOSE DECIMAL(7,2) NOT NULL ,
NEW_STOCK_ID INTEGER NOT NULL GENERATED ALWAYS AS
( CASE WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) >= 1 THEN STOCK_ID +
40000
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) BETWEEN .01 AND .99 THEN
STOCK_ID + 10000
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) <= -1 THEN STOCK_ID + 50000
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) BETWEEN -.99 AND -.01 THEN
STOCK_ID + 20000
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) = 0 THEN STOCK_ID + 30000
END ) )
COMPRESS YES
IN RAWDATA
INDEX IN INDEXES
NOT LOGGED INITIALLY ;

Now I need to change the calculated values for the BETWEEN clauses as
follows;
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) BETWEEN .01 AND .99 THEN
STOCK_ID + 60000
WHEN DEC(((CLOSE-OPEN)/OPEN)*100,5,2) BETWEEN -.99 AND -.01 THEN
STOCK_ID + 70000

and have it recalculate the generated column for the entire table.

I've read it is possible but I'm unsure as the impact. I have many
MANY functions and Stored Procedures that reference this column, so is
it possible to do without dropping ?

Many thanks, Tim

[Fin]

Update: I tried to alter as above, but it failed with a SQL0270N
reason code 21 which related means : A column cannot be dropped or
have its length, data type, or nullability altered on a table which is
a base table for a materialized query table.

Now I have several summary tables that reference this column, so if I
drop the summary tables that mean I can do this ?

[Serge Rielau]

Yes, you can. You also need to put eeh table into SET INTEGRITY OFF
Then alter the expression and then SET INTEGRITY again with the FORCE
GENERATED options.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[Fin]

Thanks for the response Serge, thats a great relief.

Much appreciated.

[Fin]

Just for future reference if others need to change a generated always
column attribute, you also need to add DROP EXPRESSION prior to the
SET GENERATE ALWAYS AS (Expression).

Otherwise it fails with an SQL0190N error.

ie:
SET INTEGRITY FOR x OFF;
ALTER TABLE x ALTER COLUMN y DROP EXPRESSION SET GENERATED ALWAYS AS
(expression).
SET INTEGRITY FOR x IMMEDIATE CHECKED FORCE GENERATED;

Thanks again Serge.