Covert UDF to a SQL Procedure

anu_chiluka · #1 (**permalink**) 02-11-08, 12:47

I am running into the error (IBM][CLI Driver][DB2/LINUX] SQL0101N The statement is too long or too complex. SQLSTATE=54001
) for the SQL query below. A quick fix would be to increase the STMTHEAP by steps of 1024. But I came across this forum from 2007 (gango response at the end) (Tuning for SQL0101N errors) where it could be resolved by converting the GET_DELINQUENCY to a SQL procedure. I would like to implement this rather than increasing the STMTHEAP as right now I make only four calls to this procedure and expect to increase the number of calls in future.

First how could I make GET_DELINQUENCY()into a “procedure” and how would I call it from within a SQL statement

SELECT
scc.cltgrp AS group_number,
CASE WHEN GET_DELINQUENCY(s.gcomp, s.glink#) = 1 THEN s.gpri END AS current_bal,
CASE WHEN GET_DELINQUENCY(s.gcomp, s.glink#) = 2 THEN s.gpri END AS thirty_bal,
CASE WHEN GET_DELINQUENCY(s.gcomp, s.glink#) = 3 THEN s.gpri END AS sixty_bal,
CASE WHEN GET_DELINQUENCY(s.gcomp, s.glink#) = 4 THEN s.gpri END AS ninety_bal,
s.gpri AS principal,
s.gint AS interest,
s.gbl1 AS late
FROM intellec.tablea s
INNER JOIN intellec.tableb sct ON (s.gcomp = sct.tcomp and s.gdebt# = sct.tdebt#)
INNER JOIN intellec.tablec scc ON (s.gcomp = scc.ccomp and s.gclnt = scc.cclnt)
INNER JOIN intellec.tabled sc ON (s.gcomp = sc.ccomp and s.glink# = sc.clink#)
WHERE s.gcomp in (1,3)
AND s.gstcod not in ('B','9','C','I')
AND sct.tfndt > 0
AND s.gpri <> 0
AND scc.cltgrp IN (101160,101171)

ARWinner · #2 (**permalink**) 02-11-08, 16:19

You will not be able to call a SP from inside a query. So trying to convert the UDF would prove fruitless. How is the UDF defined? Also what DB2 version and OS?

Andy

stolze · #3 (**permalink**) 02-12-08, 00:49

If you have some complex SQL statements in your UDF, you could create a nested procedure that contains those SQL statements, and your UDF just calls this new procedure.

anu_chiluka · #4 (**permalink**) 02-12-08, 11:20

I came up with this temporary solution for now. First convert the UDF to a stored procedure then create a new udf with a call to the stored procedure. And use the new UDF in the SELECT statement. As procedures as can be invoked from anywhere that the CALL statement is supported including:

· client applications

· External routines (procedure, UDF, or method)

· SQL routines (procedure, UDF, or method)

· Triggers (before triggers, after triggers, or instead of triggers)

· Dynamic compound statements

· Command line processor (CLP))

stolze · #5 (**permalink**) 02-12-08, 11:52

Why is this a temporary solution only for you?

Something else you should consider are common table expressions.

Code:

WITH s(..., delinquency)
   AS (
      SELECT s.*, GET_DELINQUENCY(s.gcomp, s.glink#)
      FROM   intellec.tablea s )
SELECT scc.cltgrp AS group_number,
       CASE WHEN s.desinquency = 1 THEN s.gpri END AS current_bal,
       CASE WHEN s.desinquency = 2 THEN s.gpri END AS thirty_bal,
       CASE WHEN s.desinquency = 3 THEN s.gpri END AS sixty_bal,
       CASE WHEN s.desinquency = 4 THEN s.gpri END AS ninety_bal,
       s.gpri AS principal,
       s.gint AS interest,
       s.gbl1 AS late
FROM   intellec.tablea s
       ...
WHERE  ...

Or you can put the function call into a subselect in the FROM clause.

Code:

SELECT scc.cltgrp AS group_number,
       CASE WHEN d.desinquency = 1 THEN s.gpri END AS current_bal,
       CASE WHEN d.desinquency = 2 THEN s.gpri END AS thirty_bal,
       CASE WHEN d.desinquency = 3 THEN s.gpri END AS sixty_bal,
       CASE WHEN d.desinquency = 4 THEN s.gpri END AS ninety_bal,
       s.gpri AS principal,
       s.gint AS interest,
       s.gbl1 AS late
FROM   intellec.tablea s
       ...,
       LATERAL ( SELECT GET_DELINQUENCY(s.gcomp, s.glink#) AS delinquency
                 FROM   sysibm.sysdummy1 ) AS d(delinquency)
WHERE  ...

Both ways ensure that the function is only compiled oncy into the overall SQL statement.

Note that the advantage of not using a stored procedure in the way I mentioned is that DB2 may apply better optimizations knowing the internals of the DB2 in the context of the query. If the body of the UDF is put into a stored procedure, it is already compiled and the query context is not taken into consideration. Generally, this is only relevant if you want to squeeze out the last bit of performance.

anu_chiluka · #6 (**permalink**) 02-19-08, 11:54

Hi Knut,
I tried your suggestions. Pretty Cool. But in the query below the “OPEN SECTION” and “CLOSED SECTION” work separately using WITH. But now I’m trying to combing open and closed into one massive query, so that one “group name” will show both open and closed data. I’m getting the error “[IBM][CLI Driver][DB2/LINUX] SQL0101N The statement is too long or too complex. SQLSTATE=54001” again.

Any other thoughts??

This query is a beast!

WITH d(group_name, open_principal, open_delinquency, closed_principal, closed_delinquency)
AS (
-- OPEN SECTION START
SELECT
CASE
WHEN scc.cltgrp IN (101150, 100093, 100393) THEN 'TEXAS HEALTH RESOURCES'
WHEN scc.cltgrp IN (31127) THEN 'ST VINCENT HEALTH'
WHEN scc.cltgrp IN (101250) THEN 'FLORIDA HOSPITAL'
WHEN scc.cltgrp IN (100115) THEN 'NAPLES COMMUNITY HOSPITAL'
WHEN scc.cltgrp IN (31025) THEN 'MARCUS DALY MEMORIAL HOSPITAL'
WHEN scc.cltgrp IN (100024) THEN 'CARONDELET HEALTH NETWORK'
ELSE (SELECT cname FROM intellec.scclnt WHERE ccomp = scc.ccomp AND cclnt = scc.cltgrp) END AS group_name,
s.gpri AS open_principal,
GET_DELINQUENCY(s.gcomp, s.glink#) AS open_delinquency,
0 AS closed_principal,
0 AS closed_delinquency
FROM intellec.scacct s
INNER JOIN intellec.sctrnpf sct ON (s.gcomp = sct.tcomp AND s.gdebt# = sct.tdebt#)
INNER JOIN intellec.scclnt scc ON (s.gcomp = scc.ccomp AND s.gclnt = scc.cclnt)
INNER JOIN intellec.sccsilon sc ON (s.gcomp = sc.ccomp AND s.glink# = sc.clink#)
WHERE s.gcomp in (1,3)
AND s.gstcod not in ('B','9','C','I')
AND sct.tfndt > 0
AND s.gpri <> 0
-- OPEN SECTION END

UNION
-- CLOSED SECTION START
-- Regular Closed Section
SELECT
CASE
WHEN scc.cltgrp IN (101150, 100093, 100393) THEN 'TEXAS HEALTH RESOURCES'
WHEN scc.cltgrp IN (31127) THEN 'ST VINCENT HEALTH'
WHEN scc.cltgrp IN (101250) THEN 'FLORIDA HOSPITAL'
WHEN scc.cltgrp IN (100115) THEN 'NAPLES COMMUNITY HOSPITAL'
WHEN scc.cltgrp IN (31025) THEN 'MARCUS DALY MEMORIAL HOSPITAL'
WHEN scc.cltgrp IN (100024) THEN 'CARONDELET HEALTH NETWORK'
ELSE (SELECT cname FROM intellec.scclnt WHERE ccomp = scc.ccomp AND cclnt = scc.cltgrp) END AS group_name,
0 AS open_principal,
0 AS open_delinquency,
s.gpri AS closed_principal,
CASE
WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) <= 30 THEN 1
WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) > 30 AND (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) <= 60 THEN 2
WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) > 60 AND (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) <= 90 THEN 3
WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) > 90 THEN 4
END AS closed_delinquency
FROM intellec.scacct s
INNER JOIN intellec.sctrnpf sct ON (s.gcomp = sct.tcomp AND s.gdebt# = sct.tdebt#)
INNER JOIN intellec.scclnt scc ON (s.gcomp = scc.ccomp AND s.gclnt = scc.cclnt)
INNER JOIN intellec.sccsilon sc ON (s.gcomp = sc.ccomp AND s.glink# = sc.clink#)
WHERE s.gcomp IN (1,3)
AND s.gstcod IN ('C','I')
AND sct.tfndt > 0
AND s.gpri <> 0
-- PTC Section
UNION ALL
SELECT
CASE
WHEN scc.cltgrp IN (101150, 100093, 100393) THEN 'TEXAS HEALTH RESOURCES'
WHEN scc.cltgrp IN (31127) THEN 'ST VINCENT HEALTH'
WHEN scc.cltgrp IN (101250) THEN 'FLORIDA HOSPITAL'
WHEN scc.cltgrp IN (100115) THEN 'NAPLES COMMUNITY HOSPITAL'
WHEN scc.cltgrp IN (31025) THEN 'MARCUS DALY MEMORIAL HOSPITAL'
WHEN scc.cltgrp IN (100024) THEN 'CARONDELET HEALTH NETWORK'
ELSE (SELECT cname FROM intellec.scclnt WHERE ccomp = scc.ccomp AND cclnt = scc.cltgrp) END AS group_name,
0 AS open_principal,
0 AS open_delinquency,
(SELECT
COALESCE(SUM(p.payamt),0) FROM intellec.scpaym p
WHERE p.pcomp = s.gcomp
AND p.pguar = s.gdebt#
AND p.pclnt = s.gclnt
AND p.ptype IN (11,13,31)
) -
(SELECT
COALESCE(SUM(p.payamt),0) FROM intellec.scpaym p
WHERE p.pcomp = s.gcomp
AND p.pguar = s.gdebt#
AND p.pclnt = s.gclnt
AND p.ptype = 54
) AS closed_principal,
-- PTC is always considered "current"
1 AS closed_delinquency
FROM intellec.scacct s
INNER JOIN intellec.sctrnpf sct ON (s.gcomp = sct.tcomp AND s.gdebt# = sct.tdebt#)
INNER JOIN intellec.scclnt scc ON (s.gcomp = scc.ccomp AND s.gclnt = scc.cclnt)
WHERE s.gcomp IN (1,3)
-- hard coded, may need to revisit
AND scc.CSTM08 CONCAT scc.CSTM09 CONCAT scc.CSTM10 IN ('AWB','BUS','CHF','CSI','FNB','SWB','TOR')
-- start of ptc usage
AND CONVERT400DATE(s.gpldat) > '02/01/2006'
-- leave off bonner, cedars
AND scc.cltgrp NOT IN (100052,101210)
AND sct.tfndt > 0
AND (SELECT
COALESCE(SUM(p.payamt),0) FROM intellec.scpaym p
WHERE p.pcomp = s.gcomp
AND p.pguar = s.gdebt#
AND p.pclnt = s.gclnt
AND p.ptype IN (11,13,31)
) -
(SELECT
COALESCE(sum(p.payamt),0) FROM intellec.scpaym p
WHERE p.pcomp = s.gcomp
AND p.pguar = s.gdebt#
AND p.pclnt = s.gclnt
AND p.ptype = 54
) <> 0
-- CLOSED SECTION END
)
SELECT d.group_name,
COALESCE(SUM(CASE WHEN d.open_delinquency = 1 THEN d.open_principal END), 0) AS open_current_bal,
COALESCE(SUM(CASE WHEN d.open_delinquency = 2 THEN d.open_principal END), 0) AS open_thirty_bal,
COALESCE(SUM(CASE WHEN d.open_delinquency = 3 THEN d.open_principal END), 0) AS open_sixty_bal,
COALESCE(SUM(CASE WHEN d.open_delinquency = 4 THEN d.open_principal END), 0) AS open_ninety_bal,
COALESCE(SUM(d.open_principal), 0) AS open_principal,
COALESCE(SUM(CASE WHEN d.closed_delinquency = 1 THEN d.closed_principal END), 0) AS closed_current_bal,
COALESCE(SUM(CASE WHEN d.closed_delinquency = 2 THEN d.closed_principal END), 0) AS closed_thirty_bal,
COALESCE(SUM(CASE WHEN d.closed_delinquency = 3 THEN d.closed_principal END), 0) AS closed_sixty_bal,
COALESCE(SUM(CASE WHEN d.closed_delinquency = 4 THEN d.closed_principal END), 0) AS closed_ninety_bal,
COALESCE(SUM(d.closed_principal), 0) AS closed_principal
FROM d
GROUP BY d.group_name
ORDER BY COALESCE(SUM(d.open_principal), 0) DESC

stolze · #7 (**permalink**) 02-19-08, 12:10

I have not yet looked at it in detail because I have a more basic question first: you did already increased the statement heap (STMTHEAP) as the User Response in the explanation for SQL101 instructs?

The query is long but doesn't look overly complex. If you have the default size for the statement heap (which is rather small to begin with), the easiest approach may just be to increase the statement heap and see if the problem still occurs. If it does, we can have a detailed look at the query and probably we can simplify the statement.

anu_chiluka · #8 (**permalink**) 02-19-08, 12:44

Right now its the default
SQL statement heap (4KB) (STMTHEAP) = 2048
But I was looking to teak the query itself as much as possible and only change the STMTHEAP as my last resort.

stolze · #9 (**permalink**) 02-20-08, 06:40

First: Run the DB2 Configuration Advisor. It will give you a good base level configuration for your overall DB2 system. I assume that you will have a much bigger statement heap after that anyway.

I had another look at your query. It is a straight-forward query and not overly complex. What makes it complex for DB2 is that it's just long. You can probably tweak it a little here and there, but I don't think you can easily consolidate it into just 10 lines or so.

Also just remember the basic principle of SQL: tell the system "what" you want. The system can then figure out the best way on "how" to get it. So it is usually not such a great idea to come up with some tricky way to write the query - and no one understands it afterwards. Even if some people believe that this is an "art" and shows how cool they are, that is just bad engineering...

A few thoughts, however:

Can you use UNION ALL instead of UNION between open section and closed section? I'm asking because a UNION must eliminate duplicates, which is just more work for DB2 if you don't need that.
Depending on how many distinct values you have for "scc.cltgrp", it may be worthwhile to put those in a lookup table to map them to a string and, thus, avoid those CASE expressions. (You don't have to materialize this lookup table and could put it into a table UDF or a separate CTE.)
Using a function on "s.gpldat" typically prevents DB2 from applying an index scan here because DB2 can't look "through" the function and cannot make such a predicate index-searchable. I don't know what the function CONVERT400DATE is doing, but you may want to avoid its usage or apply it to the date value '02/01/2006' in a sensible way.
Along the same lines, I usually recommend to stick with ISO date formats, i.e. YYYY-MM-DD to prevent any problems with different ideas of the order of months and days with different date separators because pretty much all possible combinations exist, i.e. DD/MM/YYYY as well as MM/DD/YYYY, which makes it hard to understand the query again - if not even causing wrong query results
Yet another idea would be to do a single table scan over the joined table only (to avoid the two UNION [ALL] steps) and add another column in the CTE that distinguishes between the three cases. This will only work if a single row in the joined table cannot be open and closed at the same time - or you will have to rephrase the CASE statement to cover this case.
You can rewrite the "COALESCE(SUM(...), 0)" to something like this:
Code:
```
SUM(CASE WHEN closed_delinquency = 4 THEN closed_principal ELSE 0 END)
```
The advantage is that you do not have the COALESCE. Whether this would make the query perform better, I do not know for sure.
Finally, the SQL standard mandates that CTEs are evaluated before the main body of the query is run. DB2 tries to push-down predicates into CTEs/subselects if possible. But it may very well be that CTEs interfere at some points with this push-down, so avoiding CTEs if you do not really need them and the query is still readable may be beneficial from a performance point of view.
Depending on the semantics of your schema/query, it may also be possible to combine those 30/60/90 information by applying some simple mathematical calculations (I only consolidated this by using a BETWEEN predicate).

With all those, I came up with the following (completely untested) query:

Code:

WITH groupMap(groupId, string)
   AS (
      VALUES ( 101150, 'TEXAS HEALTH RESOURCES' ),
             ( 100093, 'TEXAS HEALTH RESOURCES' ),
             ( 100393, 'TEXAS HEALTH RESOURCES' ),
             (  31127, 'ST VINCENT HEALTH' ),
             ( 101250, 'FLORIDA HOSPITAL' ),
             ( 100115, 'NAPLES COMMUNITY HOSPITAL' ),
             (  31025, 'MARCUS DALY MEMORIAL HOSPITAL' ),
             ( 100024, 'CARONDELET HEALTH NETWORK' )
   )
SELECT group_name,
       SUM(CASE WHEN type = 1 AND open_delinquency = 1 THEN open_principal ELSE 0 END) AS open_current_bal,
       SUM(CASE WHEN type = 1 AND open_delinquency = 2 THEN open_principal ELSE 0 END) AS open_thirty_bal,
       SUM(CASE WHEN type = 1 AND open_delinquency = 3 THEN open_principal ELSE 0 END) AS open_sixty_bal,
       SUM(CASE WHEN type = 1 AND open_delinquency = 4 THEN open_principal ELSE 0 END) AS open_ninety_bal,
       SUM(CASE WHEN type = 1 AND open_delinquency BETWEEN 1 AND 4 THEN open_principal ELSE 0 END) AS open_principal,
       SUM(CASE WHEN type = 2 AND closed_delinquency = 1 THEN closed_principal WHEN type = 3 AND closed_delinquency = 1 THEN closed_principal_ptc ELSE 0 END) AS closed_current_bal,
       SUM(CASE WHEN type = 2 AND closed_delinquency = 2 THEN closed_principal WHEN type = 3 AND closed_delinquency = 2 THEN closed_principal_ptc ELSE 0 END) AS closed_thirty_bal,
       SUM(CASE WHEN type = 2 AND closed_delinquency = 3 THEN closed_principal WHEN type = 3 AND closed_delinquency = 3 THEN closed_principal_ptc ELSE 0 END) AS closed_sixty_bal,
       SUM(CASE WHEN type = 2 AND closed_delinquency = 4 THEN closed_principal WHEN type = 3 AND closed_delinquency = 4 THEN closed_principal_ptc ELSE 0 END) AS closed_ninety_bal,
       SUM(CASE WHEN type = 2 AND closed_delinquency BETWEEN 1 AND 4 THEN closed_principal WHEN type = 3 AND closed_delinquency BETWEEN 1 AND 4 THEN closed_principal_ptc ELSE 0 END) AS closed_principal
FROM (
      -- OPEN SECTION START
      SELECT COALESCE( ( SELECT string
                         FROM   groupMap
                         WHERE  groupId = scc.cltgrp ),
                       ( SELECT cname
                         FROM   intellec.scclnt
                         WHERE  ccomp = scc.ccomp AND
                                cclnt = scc.cltgrp ) ) AS group_name,
             CASE
                -- open
                WHEN s.gstcod NOT IN ('B','9','C','I') AND
                     s.gpri <> 0
                THEN 1
                 -- closed, non-ptc
                WHEN s.gstcod IN ('C','I') AND
                     s.gpri <> 0
                THEN 2
                 -- closed, ptc
                WHEN -- hard coded, may need to revisit
                     scc.CSTM08 CONCAT scc.CSTM09 CONCAT scc.CSTM10 IN ('AWB','BUS','CHF','CSI','FNB','SWB','TOR') AND
                     -- start of ptc usage
                     CONVERT400DATE(s.gpldat) > '02/01/2006' AND
                     -- leave off bonner, cedars
                     scc.cltgrp NOT IN (100052,101210) AND
                     ( SELECT COALESCE(SUM(p.payamt), 0)
                       FROM   intellec.scpaym p
                       WHERE  p.pcomp = s.gcomp AND
                              p.pguar = s.gdebt# AND
                              p.pclnt = s.gclnt AND
                              p.ptype IN (11,13,31) ) <>
                     ( SELECT COALESCE(sum(p.payamt), 0)
                       FROM   intellec.scpaym p
                       WHERE p.pcomp = s.gcomp AND
                             p.pguar = s.gdebt# AND
                             p.pclnt = s.gclnt AND
                             p.ptype = 54 )
                THEN 3
             END AS type,
             -- opened data
             s.gpri AS open_principal,
             GET_DELINQUENCY(s.gcomp, s.glink#) AS open_delinquency,
             -- closed data
             s.gpri AS closed_principal,
             CASE
                WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) BETWEEN 0 AND 30
                THEN 1
                WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) BETWEEN 31 AND 60
                THEN 2
                WHEN (DAYS(CURRENT DATE) - DAYS(CONVERT400DATE(GSTTAT)) - 1) BETWEEN 61 AND 90
                THEN 3
                -- #days difference > 90
                ELSE 4
             END AS closed_delinquency,
             -- closed-ptc data
             ( SELECT COALESCE(SUM(p.payamt), 0)
               FROM   intellec.scpaym p
               WHERE  p.pcomp = s.gcomp AND
                      p.pguar = s.gdebt# AND
                      p.pclnt = s.gclnt AND
                      p.ptype IN (11,13,31) ) -
             ( SELECT COALESCE(SUM(p.payamt), 0)
               FROM   intellec.scpaym p
               WHERE  p.pcomp = s.gcomp AND
                      p.pguar = s.gdebt# AND
                      p.pclnt = s.gclnt AND
                      p.ptype = 54 ) AS closed_principal_ptc,
             -- PTC is always considered "current"
             1 AS closed_delinquency_ptc
      FROM   intellec.scacct s
                INNER JOIN
             intellec.sctrnpf sct ON (s.gcomp = sct.tcomp AND s.gdebt# = sct.tdebt#)
                INNER JOIN
             intellec.scclnt scc ON (s.gcomp = scc.ccomp AND s.gclnt = scc.cclnt)
                INNER JOIN
             intellec.sccsilon sc ON (s.gcomp = sc.ccomp AND s.glink# = sc.clink#)
      WHERE  s.gcomp IN (1,3) AND
             sct.tfndt > 0
   ) AS d(group_name, type, open_principal, open_delinquency,
     closed_principal, closed_delinquency, closed_principal_ptc, closed_desiquency_ptc)
GROUP BY group_name
ORDER BY COALESCE(SUM(open_principal), 0) DESC

Note: some comments are really necessary to explain what this statement is doing (see above). But if performance is not of such a high concern, I would definitively go with the UNION [ALL] variation because it is just simpler to understand.

You should definitively test the different variations in your system to verify that the performance is satisfactory. In any case, I cannot say whether my variation has less requirements on the statement heap.

If the statement heap is too small, you may actually hinder DB2 in finding the most optimal access plan - even if the error you saw is not raised, DB2 may not have enough memory to consider all possible alternatives for the access plan because it may not have the capacity to hold all alternatives to do the costing and cost comparisons.

anu_chiluka · #10 (**permalink**) 02-20-08, 11:00

thanks knut for all your suggestions. I will test all of them and will decide which is performing the best.

Internet Services

Web Development

Digital Marketing

Consumer Tech