Prev: Adjusting the IDENTITY next-value counter in version 7?
From: Fin on 12 Aug 2010 16:20
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
From: Fin on 12 Aug 2010 17:45
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 ?
From: Serge Rielau on 12 Aug 2010 22:45
On 8/12/2010 5:45 PM, Fin wrote:
> 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 ?
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

From: Fin on 13 Aug 2010 10:48
Thanks for the response Serge, thats a great relief.

Much appreciated.
From: Fin on 13 Aug 2010 12:06
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.
 | 
Pages: 1
Prev: Adjusting the IDENTITY next-value counter in version 7?