• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

I am getting timeout errors in my cobol db2 stored procedures.I am incresing the routine asulimit but then it again timesout

I am getting timeout errors in my cobol db2 stored procedures.I am increasing the routine asulimit but then it again timesout for another input.please respond soon.
0
Nigelle
Asked:
Nigelle
  • 2
  • 2
  • 2
  • +1
1 Solution
 
NigelleAuthor Commented:
I am having something like this
EXEC SQL
  DECLARE AB_HIST_CSR CURSOR WITH ROWSET POSITIONING FOR
  SELECT
    A.AAA
    B.BBB,
    B.CCC
    B.DDD,
    B.EEE,
    B.FFF,
    B.GGG
    B.HHH
    FROM XXXX A,
         YYYY B
WHERE A.AAAI= :WS-IN-AAAA
 AND  B.BBB= :WS-IN-BBBB
 AND  B.CCC = :WS-IN-CCCC
 AND  B.DDD= :WS-IN-DDDD
 AND MONTH(B.EEE) BETWEEN
   MONTH(A.BEG) AND MONTH(A.END)
END-EXEC.
here eee,beg and end are dates
This cursor is fetched and inserted into a temp table tmp1
and the following operation is done there
EXEC SQL
  DECLARE MO_SMRY_CSR CURSOR WITH ROWSET POSITIONING FOR
  SELECT
    A.AAA,
    A.BBB,
    A.CCC,
    A.DDD,
    A.EEE,
    SUM ( CASE WHEN MONTH(B.BEG) =  MONTH(B.END)
          THEN A.GGG/ C.WK_Q * :WS-WK-DIFF
          ELSE
          (CASE WHEN MONTH(A.FFF) = MONTH(B.BEG)
          THEN A.GGG/ C.WK_Q * (C.WK_Q - :WS-WK-I-BEG)
          ELSE
          (CASE WHEN MONTH(A.FFF) = MONTH(B.END)
          THEN
          A.GGG/ C.WK_Q * :WS-WK-I-END
ELSE A.GGG END) END) END)
        AS A.GGG,
SUM ( CASE WHEN MONTH(B.BEG) =  MONTH(B.END)
          THEN A.HHH/ C.WK_Q * :WS-WK-DIFF
          ELSE
          (CASE WHEN MONTH(A.FFF) = MONTH(B.BEG)
          THEN A.HHH/ C.WK_Q * (C.WK_Q - :WS-WK-I-BEG)
          ELSE
          (CASE WHEN MONTH(A.FFF) = MONTH(B.END)
          THEN
          A.HHH/ C.WK_Q * :WS-WK-I-END
          ELSE A.HHH END)END)END)
        AS A.HHH
       FROM TMP1 A,
                  XXXX B,
                  ZZZZ C
WHERE B.AAAI= :WS-IN-AAAA
 AND  A.BBB= :WS-IN-BBBB
 AND  A.CCC = :WS-IN-CCCC
 AND  A.DDD= :WS-IN-DDDD
 AND MONTH(A.EEE) BETWEEN
   MONTH(B.BEG) AND MONTH(B.END)
 AND  A.EEE = C.A_DT
END-EXEC.








0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Nigelle,

I suspect that you're creating a very large joined result set and that is the cause of the delay.

You're joining TMP1 to XXXX and ZZZZ.  The query establishes a join key the join between TMP1 and ZZZZ, but does not define the relationship between XXXX and either other table.  So when DB2 performs the join, a full cartesian is created where every row in XXXX is joined to every row in the other table.  If each table contains 10,000 rows the result will be a 100,000,000 million row derived table which, by definition, can not be indexed.

I suspect that the omission of the join criteria for XXXX is an oversite.  I've seldom had to join tables with full cartesians and when I did, I knew the source data and expected results.  That said, I'm not sure what order the three tables are joined.  XXXX may be joined to TMP1 before ZZZZ is joined, or TMP1 and ZZZZ may be joined before the XXXX cartesian is joined.  I suspect the latter.

Check your tables and determine the join requirements of XXXX


Good Luck,
Kent
0
 
momi_sabagCommented:
when you say that you receive a timeout what do you mean by that ?
do you recieve a -904 sqlcode of a lock wait timeout (00c9008e) or do you recieve
sqlcode -905 which means you exceeded the asu limit that was allowed by the resource limit facility
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
NigelleAuthor Commented:
I get -905 not -904
0
 
momi_sabagCommented:
well
you have 2 options
1) turn of the limit in the rlf for that package, this way you won't get any timeouts
2) better way - try to tune the query so it will consume less resources
i can try to help you with that if you'll post here some data such as tables and indexes structures + amount of rows in each table / index
0
 
vermakrishCommented:
Hi,
   I would agree with both above .but would add that instead of using CASE i guess you could easily use this logic while fetching the cursour and adding the respective counters to get the sum.
   Both of your queries need to be tuned cos both of them are fetching the cartesian product or rows. and then again working on these and again going for cartesain.Plus you have coloumn and conditonal functions defined for each row,which is quite ineficient unless really required.
   Try using index columns in the where predicates.plus use if/else logic iafter fetchiung the rows.This would help reduce the complexity of the query and time consumed .
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Nigelle,

Can you run a couple of quick queries to give us an idea of how much data is involved?

WHERE B.AAAI= :WS-IN-AAAA
 AND  A.BBB= :WS-IN-BBBB
 AND  A.CCC = :WS-IN-CCCC
 AND  A.DDD= :WS-IN-DDDD
 AND MONTH(A.EEE) BETWEEN
   MONTH(B.BEG) AND MONTH(B.END)
 AND  A.EEE = C.A_DT

SELECT count (*) from TMP1;
SELECT count (*) from XXXX;
SELECT count (*) from ZZZZ;
SELECT count (*) from TMP1, ZZZZ WHERE TMP1.EEE = ZZZZ.A_DT;

Also, if you're familiar with them, an EXPLAIN PLAN is a wonderful thing right about now.  It's probably the best tuning tool available to you.


Kent
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now