CODEWITHSUNDEEP
functiondb2constraintszos
Chris Golledge
Chris Golledge

Reputation: 392

Should it be possible to execute an SQL function in a check constraint within DB2 z/OS

Simple version of the DDL:

create function rm00dv1.no_concurrent_schedules() 
returns integer
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
BEGIN
    declare num_overlaps integer;

    select count(*)
    into num_overlaps
    from 
        rm00dv1.schedules a
    where
        a.id != 0
        and
        exists (
            select 1
            from rm00dv1.schedules b
            where
                b.id = 0 -- matches the key of a given record
                and rm00dv1.isConcurrent(b.schdl_eff_dt, b.schdl_trm_dt, a.schdl_eff_dt, a.schdl_trm_dt) != 0
        );

    return num_overlaps;    
end;

Table: 

create table rm00dv1.schedules (
    id int not null,
    schdl_eff_dt date not null,
    schdl_trm_dt date not null,
    info_chg_ts timestamp(6) not null with default
    )
in RM00DV1.TSRMDV01 ;


alter table rm00dv1.schedules add constraint no_schedule_overlap
check ((schdl_trm_dt < '01/01/2015')
       or
       rm00dv1.no_concurrent_schedules() <= 0);

I am getting an SQL00551N - no execution privilege and that is odd because I can execute the function in a select statement.

Any idea to solve this problem? Thanks.

Upvotes: 0

Views: 739

Answers (2)

Chris Golledge
Chris Golledge

Reputation: 392

I learned that AFTER triggers do not get a -746 like BEFORE triggers do. I had really wanted to use a CONSTRAINT because that best captures the intent for people who come after me, with a BEFORE trigger to terminate the active schedules. But, it looks like a sequence of triggers is going to be the way to go. It is a bit clunky because the triggers all have to be created separately and you have to look at them together to get the intent, and because correct behavior is dependent on their creation order. Yes, it is documented that they will be executed in the order of their creation.

Happy path termination of rows without a specified termination date:

CREATE TRIGGER terminate_no_trm
after 
INSERT ON schedules
referencing new as new
FOR EACH ROW 
MODE DB2SQL
BEGIN ATOMIC
update schedules 
set 
    schdl_trm_dt = max(schdl_eff_dt, new.schdl_eff_dt - 1 days) -- prob not necessary, but don't set the trm before the eff
    , info_chg_ts = new.info_chg_ts
where
    new.keyCombo = keyCombo
    and
    schdl_trm_dt = '9999-12-31'
    and schdl_eff_dt < new.schdl_eff_dt;

end

Prevent insert of rows if that insert causes an overlap:

CREATE TRIGGER no_overlapping_schedules_i
after 
insert ON schedules
referencing new as n
FOR EACH ROW
MODE DB2SQL

when (num_concurrent_schedules(n.keyCombo) > 0)
begin atomic
SIGNAL SQLSTATE '75001' (
    'Concurrent schedules detected: ' 

    concat ' ' concat cast(n.keyCombo as varchar(32))
    concat ':  ' concat cast(n.schdl_eff_dt as varchar(32))
    concat ' to ' concat cast(n.schdl_trm_dt as varchar(32))
    );
end

and prevent UPDATE if that would result in an overlap

CREATE TRIGGER no_overlapping_schedules_u
after 
update ON schedules
referencing new as n
FOR EACH ROW
MODE DB2SQL

when (num_concurrent_schedules(n.keyCombo) > 0)
begin atomic
SIGNAL SQLSTATE '75001' (
    'Concurrent schedules detected: ' 
    concat ' ' concat cast(n.keyCombo as varchar(32))
    concat ':  ' concat cast(n.schdl_eff_dt as varchar(32))
    concat ' to ' concat cast(n.schdl_trm_dt as varchar(32))
    );
end

Thanks for the ideas.

Upvotes: 0

David
David

Reputation: 180

Looks like you can't. I'm looking at the DB2 10 for z/OS reference for ALTER TABLE reference and it says the following under CHECK (check-condition): "A check-condition is a search condition, with the following restrictions: ... must not contain... Built-in or user-defined functions...".

Since your function looks like it won't convert to a check condition, defining triggers on the table might be the next best option.

Upvotes: 1

Related Questions