Solved

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

Posted on 2007-12-05
7
985 Views
Last Modified: 2012-06-27
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
Comment
Question by:Nigelle
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Author Comment

by:Nigelle
ID: 20410399
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
 
LVL 45

Expert Comment

by:Kdo
ID: 20411054
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20411851
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Nigelle
ID: 20417401
I get -905 not -904
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20417891
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
 

Expert Comment

by:vermakrish
ID: 20418175
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
 
LVL 45

Accepted Solution

by:
Kdo earned 300 total points
ID: 20419124
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to script 14 186
Permanently set DB2CLP environment variable 3 658
DB3-913 err can anyone plz help to solve this issue 3 211
Help with DB2 Create store procedure query 4 113
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question