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
977 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now