DB2 FOR LUW / DB2FORLUW / A10 - DB2

SQL20524 rc=3 is returned when one of the following is used 
as a period-specification: 
 
- (inlined) SQL function parameter 
- (inlined) SQL function variable 
- (inlined) trigger transition variable 
 
For example: 
 
create table cust ( 
  eff_start_dt date not null with default '2013-01-01', 
  eff_end_dt date not null with default '2014-01-01', 
  period business_time(eff_start_dt, eff_end_dt) 
); 
 
create or replace function custfunc (eft_date date) 
 returns int 
 language sql 
begin atomic 
 return ( select count(*) 
          from cust 
          for business_time as of eft_date ); -- 
end;

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 10.1 Fix Pack 5.  This is a server side   * 
* fix.                                                         * 
****************************************************************

Change the function or trigger from inlined to compiled. 
Alternatively, create a separate stored procedure to update a 
global variable. 
 
-- Create global variable 
create or replace variable V date; 
 
-- Create stored procedure to set the global variable 
create or replace procedure setPDate(d date) 
language sql 
deterministic 
no external action 
begin 
  set V=d; -- 
end; 
 
create or replace function custfunc (eft_date date) 
 returns int 
 language sql 
begin atomic 
 call setPDate(eft_date); -- 
 return ( select count(*) 
          from cust 
          for business_time as of V ); -- 
end;

First fixed in v10.1 Fix Pack 5.

SEE LOCAL FIX.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

29.09.2014
13.07.2015
13.07.2015