[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
1,014 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 46

Expert Comment

by:Kent Olsen
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 46

Accepted Solution

by:
Kent Olsen earned 1200 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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