UDF (Scalar) and IDAA

James Sidebotham

UDF (Scalar) and IDAA

Hi. I have a UDF (scalar function) that is very useful. We are in the process of integrating IDAA - however, I understand UDFs are not compatible in IDAA. Is this correct? Has anyone come up any work-arounds? Thanks

Michael Hannan

RE: UDF (Scalar) and IDAA
(in response to James Sidebotham)


Yes I read UDFs not supported too.
A work around might need us to know what your UDF does.

I hardly need UDFs at all, because I can do it in ordinary SQL. I just don't use UDFs, basically.
Worried they might not perform too for any very frequent SQLs.

Do you really need to accelerate your query using the UDF, or is the UDF use, far too widespread? 

Michael Hannan,
DB2 Application Performance Specialist

James Sidebotham

RE: UDF (Scalar) and IDAA
(in response to Michael Hannan)

Thanks for the response. The UDF is a routine that performs a set of calculations and returns a value. It's 1 for 1. It gets too complicated to be in an SQL statement outright, and I want other users to invoke it without all the extra code getting in the way. However, if that's all that can happen, it may be the only way. Would an index work?


Michael Hannan

RE: UDF (Scalar) and IDAA
(in response to James Sidebotham)

Not sure what you mean by an Index to help. Index on expression would not help. It just matches expression to what is in the SQL.   If there are a limited set of values to do the UDF calc, then you could use a lookup table. Seems unlikely.


Maybe you UDF code has to remain secret, but if it was in native SQL and not too long, we could comment further. Even to transform a HIGH2KEY for a FLOAT column to a displayable format (from internal HEX), its complex SQL and multi steps over more than 1 Common Table Expression, but I still don't use a UDF, because I don't need to.

It is possible to code a standard piece of SQL in a common table expression that could be copied by all SQL writers into their code, CTEs appear to be supported by IDAA, while UDFs are not.

Insert this CTE below into a Query that uses Common Table Expressions, while noting that if you don't want a CTE to be materialised in DB2 zOS proper, reference back to it just once and don't use materializing constructs like DISTINCT and GROUP BY.

I am not expert enough in IDAA Optimizer as yet to say much about its access paths for CTEs. It may decide to materialize them where Db2 zOS does not.

(SELECT   fancy calculation code (what the UDF was doing)

We have to code to suit both Db2 zOS and IDAA if we want the possibility to offload to IDAA, but the offload may still not happening depending on whether you used cost based decision or "Force all eligible queries"

I would like to keep all my performance measurement tables in IDAA, for fast analytic Queries, but debate with a colleague on best IDAA settings, to get offload.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 25, 2020 - 03:30 PM (Australia/Melbourne)

James Sidebotham

RE: UDF (Scalar) and IDAA
(in response to Michael Hannan)

thanks for the response. 


The function is created by:

CREATE FUNCTION Base62Decode @a_value_to_convert [char](7))

DECLARE @v_iterator int;  
DECLARE @v_length int;  
DECLARE @v_temp_char VARCHAR(1);  
DECLARE @v_temp_int bigint;  
DECLARE @v_return_dec decimal(38) = 0;  
DECLARE @v_multiplier decimal(38) = 1;  
DECLARE @v_temp_convert_val VARCHAR(256) = substring(@a_value_to_convert,1,7);

DECLARE @v_temp_ts CHAR(26); 
DECLARE @v_temp_datepart bigint;  

DECLARE @c_base62_digits VARCHAR(62) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
 SET @v_length = len( @v_temp_convert_val );  
 SET @v_iterator = @v_length; 
-- Convert 7 char Base 62 to dec Base 10
 WHILE ( @v_iterator > 0 )   
   -- The character being converted
   SET @v_temp_char = substring( @v_temp_convert_val, @v_iterator, 1 );  
   -- The index of the character being converted
   SET @v_temp_int = charindex( @v_temp_char collate  SQL_Latin1_General_CP1_CS_AS, @c_base62_digits collate  SQL_Latin1_General_CP1_CS_AS ) - 1;  
   SET @v_return_dec    = @v_return_dec + ( @v_temp_int * @v_multiplier );  
   SET @v_multiplier    = @v_multiplier * 62;  
   SET @v_iterator              = @v_iterator - 1;  
-- Construct the timestamp

 -- GET 100ths
 SET    @v_return_dec    = @v_return_dec
 SET    @v_temp_datepart = @v_return_dec / 17179869184
 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart, '00') + '0000'

 -- GET Seconds (recalc decimal string)
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 17179869184)
 SET    @v_temp_datepart = @v_return_dec / 268435456
 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart, '00') + '.' + @v_temp_ts

 -- GET Minutes (recalc decimal string)
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 268435456)
 SET    @v_temp_datepart = @v_return_dec / 4194304
 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart, '00') + '.' + @v_temp_ts

  -- GET Hours (recalc decimal string)
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 4194304)
 SET    @v_temp_datepart = @v_return_dec / 131072
 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart, '00') + '.' + @v_temp_ts

  -- GET Day (recalc decimal string)
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 131072)
 SET    @v_temp_datepart = @v_return_dec / 4096
 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart, '00') + '-' + @v_temp_ts

   -- GET Month & Year (recalc decimal string)
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 4096)
 SET    @v_temp_datepart = @v_return_dec / 256
 SET    @v_return_dec    =      @v_return_dec - (@v_temp_datepart * 256)

 SET    @v_temp_ts               =  FORMAT(@v_temp_datepart+1, '00') + '-' + @v_temp_ts
 SET    @v_temp_ts               =  FORMAT(@v_return_dec + 1900, '0000') + '-' + @v_temp_ts 

-- Return value as 'YYYY-MM-DD-hh.mm.ss.xxxxxx' (character)

  return @v_temp_ts


And I invoke it by:


select FIELD_A, base62decode(FIELD_A) as THE_TIMESTAMP from mytable