SQL Db2 for IBM i - introduction to UDTF

udtf-intro.sql

-- SQL Db2 for IBM i - introduction to UDTF
--
-- This is a tutorial starting with SQL functions, over User defined Table Function (UDTF) 
-- and procedures. It covert the basic features and some common pitfalls.

-- By using IBM i SQL service as the vehicle for making service calls over http and integrate data 
-- from Db2 on the IBM i makes this tutorial super relevant.
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2022-2024
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------



-- First; Let's have something to play with:
--------------------------------------------
drop schema udtfdemo; 
create schema udtfdemo; 
set schema udtfdemo; 

--drop table udtfdemo.product;

create or replace table udtfdemo.product (
  id int generated always as identity,
  description varchar(40),
  price_DKK decimal(13, 2)
);

insert into udtfdemo.product 
( description                             , price_DKK) 
values
('Panasonic Lumix XLR1 til GH5'           , 3269.0  ),
('Kodak Mini shot Combo 3 Retro White'    , 1049.0  ),
('Kodak Mini shot Combo 2 Retro White'    , 1049.0  ),
('Ricoh Theta Z1'                         , 7599.0  ),
('Panasonic Lumix GH5 M2 Body'            , 11799.0 ),
('Panasonic Lumix GH5 M2 12-60 L'         , 17999.0 ),
('M200 BK M15-45 ESS GAMING KIT'          , 5199.0  ),
('Insta360 GO2 Standard Edition'          , 2599.0  ),
('Canon Powershot G7X MKIII BK'           , 5259.0  ),
('Panasonic TZ-200 Black'                 , 4969.0  ),
('EOS RP + RF 24-105mm F4-7.1 IS STM'     , 9599.0  ),
('KODAK STEP TOUCH WHITE'                 , 1149.0  ),
('Denver Wildlife Cam 8MP'                , 449.0   ),
('DJI Action 2 Power Combo'               , 2999.0  ),
('DJI Action 2 Dual-Screen Combo'         , 3299.0  ),
('GoPro Hero8 Black'                      , 2333.0  ),
('Denver Wildlife Cam 8MP WIFI'           , 749.0   ),
('TZ90 Black. 30x Optic zoom 4K'          , 2799.0  ),
('GoPro HERO10 Black'                     , 3399.0  ),
('Olympus PEN E-PL10 Value Kit BK'        , 5999.0  ),
('Olympus PEN E-PL10 Value Kit WH'        , 5999.0  ),
('POLAROID MINT CAMERA WHITE'             , 497.0   ),
('POLAROID NOW WHITE'                     , 749.0   ),
('EOS M200 BK M15-45 EU26'                , 4999.0  ),
('KODAK MINI SHOT COMBO 2 WHITE'          , 999.0   ),
('PANA G100 MFT/12-32/3,5-5,6'            , 5499.0  ),
('Sony A6400 Kit 16-50'                   , 7899.0  ),
('Panasonic Lumix S5 Body'                , 15999.0 ),
('Polaroid Go White'                      , 949.0   );
commit;

------------------------------------------------------------------------------
--
-- Problem:
-- 
-- I have a table with product prices
-- Unfortunately prices are in Danish kroner. 
-- EUR is required for our web-shop 
-- So what to do?  
Select * from udtfdemo.product; 

------------------------------------------------------------------------------
-- Step 1; Typical use-case: 
-- create a function that converts DKK to EUR and use it in a view;
create or replace function  udtfdemo.dkk_currency_converter (
    dkk decimal (19, 2)
)
returns decimal (19, 2)
begin
    declare dkk_to_eur float default 0.136054421768707482; 
    return dkk * dkk_to_eur;
end;

--does it work? 
values udtfdemo.dkk_currency_converter (dkk => 735) ; 

-- Now use it with the table
Select 
    id,
    description, 
    price_DKK, 
    udtfdemo.dkk_currency_converter (dkk => price_DKK) as price_EUR
from 
    udtfdemo.product;

-- ... and place it in a view:

create or replace view udtfdemo.product_and_price as (
    Select 
        id,
        description, 
        price_DKK, 
        udtfdemo.dkk_currency_converter (dkk => price_DKK) as price_EUR
    from 
        udtfdemo.product
);

select * 
from udtfdemo.product_and_price;

-- Even from ol'Query :) In this case ACS have better integration that VSCode
cl:RUNQRY QRYFILE((UDTFDEMO/PRODU00001));

------------------------------------------------------------------------------
-- Step 2; Having the rate as a const in the function - I don't like it;
-- Lets use a global variable 

create or replace variable udtfdemo.dkk_to_eur float default 0.136054421768707482;

-- Step 2.1; create a function that converts DKK to EUR
create or replace function  udtfdemo.dkk_currency_converter (
    dkk decimal (19, 2)
)
returns decimal (19, 2)
begin
    return dkk * udtfdemo.dkk_to_eur;
end;

--does it work? 
values udtfdemo.dkk_currency_converter (dkk => 735) ; 


------------------------------------------------------------------------------
-- Step 3; Perhaps there is a JSON service on the internet 
-- that can dynamic return the rate? 
-- https://www.floatrates.com/
-- http://www.floatrates.com/daily/dkk.json 
-- Note the new: qsys2.http_get: https://www.ibm.com/docs/en/i/7.5?topic=functions-http-get
-- To set up SSL: https://www.ibm.com/docs/en/i/7.5?topic=programming-http-functions-overview#rbafyhttpoverview/HTTP_SSL

-- get the clob from the internet: 
values qsys2.http_get ('http://www.floatrates.com/daily/dkk.json');


-- Perhaps it would be more usefull as a SQL table: 
Select * 
from json_table (
    qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
    'lax $.*'
    columns (             
        code char(3)     path '$.code',
        name varchar(32) path '$.name',
        rate float       path '$.rate'
    )
);

-- You can still use the "old" Java version - 
-- But notice: You can only have ONE PASE envirinment open at the time i the job !!: 
Select * 
from json_table (
    systools.httpGetClob ( url => 'http://www.floatrates.com/daily/dkk.json' , httpheader => NULL),
    'lax $.*'
    columns (             
        code char(3)     path '$.code',
        name varchar(32) path '$.name',
        rate float       path '$.rate'
    )
);



-- Now a CTE so i filter only EUR; 
with rates as (
    Select * 
    from json_table (
        qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
        'lax $.*'
        columns (             
            code char(3)     path '$.code',
            name varchar(32) path '$.name',
            rate float       path '$.rate'
        )
    )
) 
Select * 
from rates
where code = 'EUR';

-- Now lets put that into a function; 
create or replace function  udtfdemo.exchange_rate ()
returns float 
begin
    declare dkk_currency_converter float; 
    
    with rates as (
        Select * 
        from json_table (
            qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
            'lax $.*'
            columns (             
                code char(3)     path '$.code',
                name varchar(32) path '$.name',
                rate float       path '$.rate'
            )
        )
    ) 
    Select rate
    into   dkk_currency_converter
    from   rates
    where  code = 'EUR';

    return dkk_currency_converter; 
end;

-- does it work? 
values udtfdemo.exchange_rate ();

-- And use it in our first function; 
create or replace function  udtfdemo.dkk_currency_converter (
    dkk decimal (19, 2)
)
returns decimal (19, 2)
begin
    return dkk * udtfdemo.exchange_rate();
end;

--does it work? 
values udtfdemo.dkk_currency_converter (dkk => 735) ; 

-- also in our view? 
select * 
from udtfdemo.product_and_price;

-- Hmmm !! Yes but now it slow!! What to do? 

------------------------------------------------------------------------------
-- Step 4: Determinism !!
-- is values(2+2) deterministic?
-- is values(current timestamp) deterministic?
-- Is it global or statement deterministic?
create or replace function  udtfdemo.exchange_rate ()
returns float 
statement deterministic
--global deterministic
reads sql data
no external action
begin
    declare dkk_currency_converter float; 
    
    with rates as (
        Select * 
        from json_table (
            qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
            'lax $.*'
            columns (             
                code char(3)     path '$.code',
                name varchar(32) path '$.name',
                rate float       path '$.rate'
            )
        )
    ) 
    Select rate
    into   dkk_currency_converter
    from   rates
    where  code = 'EUR'
    with ur;

    return dkk_currency_converter; 
end;

-- Check the speed
Select 
    id,
    description, 
    price_DKK, 
    udtfdemo.dkk_currency_converter (dkk => price_DKK) as price_EUR
from 
    udtfdemo.product;


-- Hmm, still slow?? what if we put it on the first level of the query ??

Select 
    id,
    description, 
    price_DKK, 
    cast ( udtfdemo.exchange_rate() * price_DKK as dec(15, 2))  as price_EUR
from 
    udtfdemo.product;

-- Now deterministic works !! Perhaps IBM can look into that ... 


------------------------------------------------------------------------------
-- Step 5: Polymorphism
-- What if i want any other currency than EUR? 
-- Here is the service provide that
-- ( Note: Still converting from Danish Kroner - we'll come back to that
create or replace function  udtfdemo.exchange_rate (
    to_currency_code char(3)
)
returns float 
--statement deterministic
global deterministic
reads sql data
no external action
begin
    declare dkk_to_any float; 
    
    with rates as (
        Select * 
        from json_table (
            qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
            'lax $.*'
            columns (             
                code char(3)     path '$.code',
                name varchar(32) path '$.name',
                rate float       path '$.rate'
            )
        )
    ) 
    Select rate
    into   dkk_to_any
    from   rates
    where  code = to_currency_code;

    return dkk_to_any; 
end;

-- Does this work ?
values udtfdemo.exchange_rate();

-- Does this work ?
values udtfdemo.exchange_rate (to_currency_code => 'EUR');
values udtfdemo.exchange_rate (to_currency_code => 'USD');

-- What !! Wait a minute !! Why did the first one still work? I just changed it to cater for the parameter??
-- What functions do we have?  
Select * from qsys2.sysroutines
where specific_schema = 'UDTFDEMO';  

-- Polymorphisms can be useful when handling different data types, however, in this case it is painful
-- We have to drop each version ( signature) separate: 
drop function  udtfdemo.exchange_rate();
drop function  udtfdemo.exchange_rate(char(3));
 
-- And Recreate it with:
-- 1) default
-- 2) specific name
-- 3) options
create or replace function  udtfdemo.exchange_rate (
    to_currency_code char(3) default 'EUR'  --<== default to EUR if parameter is not given
)
returns float 
specific EXCHRATE --<== Specific name will specify the physical service program object name  
statement deterministic
--global deterministic
reads sql data
no external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso --<== options so we can debug it later
begin
    declare dkk_to_any float; 
    
    with rates as (
        Select * 
        from json_table (
            qsys2.http_get ('http://www.floatrates.com/daily/dkk.json'),
            'lax $.*'
            columns (             
                code char(3)     path '$.code',
                name varchar(32) path '$.name',
                rate float       path '$.rate'
            )
        )
    ) 
    Select rate
    into   dkk_to_any
    from   rates
    where  code = to_currency_code;

    return dkk_to_any; 
end;

Select * from qsys2.sysfuncs
where specific_schema = 'UDTFDEMO';  

-- Does this work with the default?
values udtfdemo.exchange_rate();

-- Does this work ?
values udtfdemo.exchange_rate (to_currency_code => 'EUR');
values udtfdemo.exchange_rate (to_currency_code => 'USD');


-- Our dkk_currency_converter now also need to pass that parameter, 
-- and it can do it with "default" so it is backwards compatible; 
-- But first we need to drop te original signature 
drop  function  udtfdemo.dkk_currency_converter (decimal (19, 2));

create or replace function  udtfdemo.dkk_currency_converter (
    dkk decimal (19, 2),
    to_currency_code char(3) default 'EUR'
)
returns decimal (19, 2)
specific CURREXCH
statement deterministic
reads sql data 
no external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso
begin
    return dkk * udtfdemo.exchange_rate (to_currency_code => to_currency_code) ;
end;

-- Is it ok? 
values udtfdemo.dkk_currency_converter (dkk => 735);
values udtfdemo.dkk_currency_converter (dkk => 735 , to_currency_code => 'EUR');
values udtfdemo.dkk_currency_converter (dkk => 735 , to_currency_code => 'USD');

-- Now our view gets better; 
create or replace view udtfdemo.product_and_price as (
    Select 
        id,
        description, 
        price_DKK, 

        udtfdemo.dkk_currency_converter (
            dkk => price_DKK,
            to_currency_code => 'EUR'
        ) as price_EUR,
        

        udtfdemo.dkk_currency_converter (
            dkk => price_DKK,
            to_currency_code => 'USD'
        ) as price_USD
    from 
        udtfdemo.product
);

-- How does it look? 
select * from udtfdemo.product_and_price; 

-- Arghh - we did our best but is still slow .. why?? 
-- So Determinism don't work between functin calls - only i resultset. And RFE/Idea for IBM perhaps? 

-- Note the usage:
--    specific CURREXCH
-- If you forgot to set the specific, the you can still remove it with :
-- ( This works for both functions and procedures) 
Select * from qsys2.sysroutines
where specific_schema = 'UDTFDEMO';  
 
-- find the specific name, and then drop it by object name:  
--drop specific routine CURREXCH;





------------------------------------------------------------------------------
-- Step 6: UDTF User Defined Table Functions
-- Perhaps the usages of the function in the above could be more
-- usable if we provided is as a table function we can join into our SQL table:
-- Note: The "returns table" is the big difference here
create or replace function  udtfdemo.exchange_rate (
    from_currency_code char(3), 
    to_currency_code char(3) default null --<== when not give it will return all
)
returns table ( 
    code char(3),     
    name varchar(32),
    rate float
)

specific EXCHRATEFN
statement deterministic
reads sql data
no external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso
begin
    
    return 
        with rates as (
            Select * 
            from json_table (
                qsys2.http_get ('http://www.floatrates.com/daily/' || lower( from_currency_code) || '.json'),
                'lax $.*'
                columns (             
                    code char(3)     path '$.code',
                    name varchar(32) path '$.name',
                    rate float       path '$.rate'
                )
            )
        ) 
        Select code , name , rate
        from   rates
        where  to_currency_code is null 
        or     to_currency_code = code;

end;
-- does it work;
select * from table  (udtfdemo.exchange_rate ( 
    from_currency_code => 'DKK', 
    to_currency_code  => 'EUR'
)); 
-- Get All, by leaving out the "to_currency_code"
select * from table  (udtfdemo.exchange_rate ( 
    from_currency_code => 'DKK'
)); 

select * from table  (udtfdemo.exchange_rate ( 
    from_currency_code => 'EUR', 
    to_currency_code  => 'DKK'
)); 

select * from table  (udtfdemo.exchange_rate ( 
    from_currency_code => 'EUR'
)); 

-- Now with our view!!
create or replace view udtfdemo.product_and_price as (
    Select 
        id,
        description, 
        price_DKK,
        cast ( price_DKK * eur.rate as dec (15, 2) ) as price_EUR,
        cast ( price_DKK * usd.rate as dec (15, 2) ) as price_USD
    from 
        udtfdemo.product
    left join table (udtfdemo.exchange_rate ( 
        from_currency_code => 'DKK',
        to_currency_code => 'EUR'
    )) eur on 1=1 
    left join table (udtfdemo.exchange_rate ( 
        from_currency_code => 'DKK',
        to_currency_code => 'USD'
    )) usd on 1=1 
);

-- And the "deterministic" works perfect? 
-- Question: Why left join?
select * from udtfdemo.product_and_price;


------------------------------------------------------------------------------
-- Step 7: Stored procedures
-- Can be "called" and can return cursor(s)
-- But the logic is the same
-- Lets implement a procedure similar to our UDTF
create or replace procedure udtfdemo.exchange_rate (
    in from_currency_code char(3), 
    in to_currency_code char(3) default null --<== when not give it will return all
)

    specific EXCHRATEPR
    dynamic result sets 1
    -- global  deterministic  --<== why does that not work? 
    reads sql data
    no external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso

begin
    
    declare rates_cursor cursor with return for
        with rates as (
            Select * 
            from json_table (
                qsys2.http_get ('http://www.floatrates.com/daily/' || lower( from_currency_code) || '.json'),
                'lax $.*'
                columns (             
                    code char(3)     path '$.code',
                    name varchar(32) path '$.name',
                    rate float       path '$.rate'
                )
            )
        ) 
        Select code , name , rate
        from   rates
        where  to_currency_code is null 
        or     to_currency_code = code;

    open rates_cursor;

end;
 
-- Does it work =
call udtfdemo.exchange_rate (
    from_currency_code => 'EUR', 
    to_currency_code  => 'DKK'
);

-- So what is the big difference? 
-- 1) You can not "join" a procedure cursor with another 
-- 2) Functions can not pass parameters back and forth

------------------------------------------------------------------------------
-- Step 8: Better use-case for procedures
-- Enable users the by accident is disabled: 

-- The CL command:
cl: CHGUSRPRF USRPRF(JOHN)  STATUS(*DISABLED);
cl: CHGUSRPRF USRPRF(NIELS) STATUS(*DISABLED);

-- The query:
select * 
from qsys2.user_info 
where status = '*DISABLED' 
and authorization_name not like 'Q%';


-- The procedure
create or replace procedure udtfdemo.enable_user (
    in user_id char(10) default '*ALL'
)
    specific ENABLUSER
    modifies sql data
    external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso

begin
    for select authorization_name 
        from   qsys2.user_info 
        where  status = '*DISABLED' 
        and    authorization_name not like 'Q%'
        and    (user_id = '*ALL' or user_id = authorization_name)
    do
        call qcmdexc ('CHGUSRPRF USRPRF(' || authorization_name || ') STATUS(*ENABLED)');   
    end for;

end;

-- does it work?
call  udtfdemo.enable_user ();

select * 
from qsys2.user_info 
where authorization_name in ( 'JOHN' , 'NIELS');


cl: CHGUSRPRF USRPRF(JOHN)  STATUS(*DISABLED);
cl: CHGUSRPRF USRPRF(NIELS) STATUS(*DISABLED);

call  udtfdemo.enable_user (
    user_id => 'JOHN'
);
-- What does the joblog say?



-- This can be run from CL ( or job scheduler
cl:RUNSQL SQL('call  udtfdemo.enable_user (user_id => ''JOHN'')') COMMIT(*NONE); 

------------------------------------------------------------------------------
-- Step 9: Debug 
--    specific ENABLUSER
-- That is the object name. 
-- Function are ILE service programs 
-- Procedures are ILE programs  
--
-- So start the debugger: 
--    Run->System Debugger
--    Add prgram:
--      ENABLUSER 

-- Now debug it;
cl: CHGUSRPRF USRPRF(JOHN)  STATUS(*DISABLED);
cl: CHGUSRPRF USRPRF(NIELS) STATUS(*DISABLED);

call  udtfdemo.enable_user ();


-- A udtf to list disabled users

create or replace function udtfdemo.disabled_users (
    user_id char(10) default '*ALL'
)
returns table ( 
   user_id char (10)
)   
    specific DISABDUSR
    modifies sql data
    external action
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso

begin
    return 
        select authorization_name 
        from   qsys2.user_info 
        where  status = '*DISABLED' 
        and    authorization_name not like 'Q%'
        and    (user_id = '*ALL' or user_id = authorization_name);

end;

-- does it work?
select * from table( 
   udtfdemo.disabled_users ( 
      user_id => '*ALL'
   )
);

-- A cool open source project that exposes UDTF, functions and procedures as WEB-API 
-- https://github.com/sitemule/noxDbApi

 
------------------------------------------------------------------------------
-- Step 10: Where to get more info
-- Gist's that get you started:
--
-- https://gist.github.com/forstie
-- https://gist.github.com/BirgittaHauser
-- https://gist.github.com/NielsLiisberg
--
-- IBM official:
-- https://www.ibm.com/docs/en/i/7.5?topic=reference-sql-procedural-language-sql-pl
-- https://www.ibm.com/docs/en/i/7.5?topic=reference-statements
-- 
-- Redbook:
-- https://www.redbooks.ibm.com/abstracts/sg248326.html?Open