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

Question

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.

Answer 1

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.