Alter table ADD COLUMN using UDF problem

[p175]

People, I have the following table [5m records] and I need to add a
column that uses a the listed UDF to count the number of days within
specific quarters by year.

CREATE TABLE HISTORY_MB (
ID INTEGER NOT NULL,
DAYS_RL INTEGER NOT NULL,
DAYS_RH INTEGER NOT NULL,
DAYS_RB INTEGER NOT NULL,
QTR SMALLINT NOT NULL,
YR SMALLINT NOT NULL,
CONSTRAINT HIST_PK_01 PRIMARY KEY(ID, QTR, YR))
NOT LOGGED INITIALLY
IN RESULTS INDEX IN INDEXES;

-- returns a count of the number of days within a qtr by year
CREATE FUNCTION GET_NO_TRANS_QTR( V_YEAR INTEGER,
V_QTR INTEGER)
RETURNS INTEGER
READS SQL DATA
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT COUNT(ACT_DATE)
FROM CALENDAR
WHERE YEAR(ACT_DATE) = V_YEAR
AND QUARTER(ACT_DATE) = V_QTR ;

ALTER TABLE HISTORY_MB
ADD COLUMN NO_TRANS INTEGER GENERATED ALWAYS AS
(GET_NO_TRANS_QTR(YR,QTR));

I am getting the following error however:

SQL0548N A check constraint or generated column that is defined with
"GET_NO_TRANS_QTR" is invalid. SQLSTATE=42621

I've tried all manner of alternatives but cannot figure what I am doing
wrong. I've also tried turning off integrity, but still no good. Any
help would be greatly appreciated.

Many thanks,

Tim

[Serge Rielau]

p175 wrote:

People, I have the following table [5m records] and I need to add a
column that uses a the listed UDF to count the number of days within
specific quarters by year.

CREATE TABLE HISTORY_MB (
ID INTEGER NOT NULL,
DAYS_RL INTEGER NOT NULL,
DAYS_RH INTEGER NOT NULL,
DAYS_RB INTEGER NOT NULL,
QTR SMALLINT NOT NULL,
YR SMALLINT NOT NULL,
CONSTRAINT HIST_PK_01 PRIMARY KEY(ID, QTR, YR))
NOT LOGGED INITIALLY
IN RESULTS INDEX IN INDEXES;

-- returns a count of the number of days within a qtr by year
CREATE FUNCTION GET_NO_TRANS_QTR( V_YEAR INTEGER,
V_QTR INTEGER)
RETURNS INTEGER
READS SQL DATA
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT COUNT(ACT_DATE)
FROM CALENDAR
WHERE YEAR(ACT_DATE) = V_YEAR
AND QUARTER(ACT_DATE) = V_QTR ;

ALTER TABLE HISTORY_MB
ADD COLUMN NO_TRANS INTEGER GENERATED ALWAYS AS
(GET_NO_TRANS_QTR(YR,QTR));

I am getting the following error however:

SQL0548N A check constraint or generated column that is defined with
"GET_NO_TRANS_QTR" is invalid. SQLSTATE=42621

I've tried all manner of alternatives but cannot figure what I am doing
wrong. I've also tried turning off integrity, but still no good. Any
help would be greatly appreciated.

Many thanks,

Tim

db2 "? SQL0548"
<blah>
o the definition contains a user defined function with the
CONTAINS SQL or READS SQL DATA option
<blah>

Generated Columns are half trigger, half check constraint.
How can you have a check constraint which's correctness is based on
values in another table? (well one could semnatically, but it's one heck
of a maintenance job)

You could conceivably use an MQT or a view....
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab