Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2006
    Posts
    17

    Unanswered: Covert UDF to a SQL Procedure

    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) (http://www.dbforums.com/showthread.php?t=540155) 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)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2006
    Posts
    17
    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))

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2006
    Posts
    17
    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

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Dec 2006
    Posts
    17
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    1. 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.
    2. 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.)
    3. 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.
    4. 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
    5. 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.
    6. 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.
    7. 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.
    8. 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.
    Last edited by stolze; 02-20-08 at 07:49.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Dec 2006
    Posts
    17
    thanks knut for all your suggestions. I will test all of them and will decide which is performing the best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •