Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting Time out error in COBOL DB2 SP.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Tue May 31, 2011 12:24 am    Post subject: Getting Time out error in COBOL DB2 SP.
Reply with quote

Hi

I have a stored proc which has a db2 select cursor on a table with two non unique indexes. The SP works fine if i pass input values for these two indexes used in the where clause in the case of the result of large number of rows. However if i pass an input which has to fetch 10 records the SP fails with the Time out error. imit = "0000000002" CPU seconds ("0000110000" service units) derived from "". SQLSTATE=57014 .

i executed the query of the cursor in command editor and it gave me results for all sets of inputs.

I am not able to understand how the cursor fails when it can retrieve 1000 rows for one set of inputs and give a time out error when it has to fetch lesser number of rows.

The table has been inserted with some 10000 rows just a day back, will a
reorg on the table help? How can i correct this problem to make the SP work for all sets of inputs?


Please help me with this.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 31, 2011 2:58 am    Post subject:
Reply with quote

Hello,

If the problem query needs to search 10 million rows (10000x10000) this may contribute to the time-out. . . .

Quote:
I am not able to understand how the cursor fails when it can retrieve 1000 rows for one set of inputs and give a time out error when it has to fetch lesser number of rows.
The 1000 row "hit" may be direct while the problem run generates a Cartesian Product . . .

Possibly i misunderstand.
Back to top
View user's profile Send private message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Tue May 31, 2011 11:05 am    Post subject:
Reply with quote

The Cursor in this SP actually has a where clause with the two index fields in an IN condition, (each IN condition having a sub query). IT is like

select * from table A
where A.sub_id in (select sub_id from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id)
and A.cnt_id in (select cnt_id from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id)

If this query forms a cartesian product for the desired result set, can anyone suggest me a way to avoid this?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 31, 2011 2:30 pm    Post subject:
Reply with quote

doesn't following SQL return the same result ?
Code:
select * from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id


which idot coded this :
A.cnt_id in (select cnt_id from table A where sub id = :ws-sub-id and cnt_id = :ws-cnt-id)

it's like : give me all colors where color = 'BLACK'. I can tell you upfront only 'BLACK' will be returned. Only you will read through 10000 rows, return 2000 times 'BLACK' and then sort those 2000 to give the result : 1 time 'BLACK'.


The number of rows returned are usually irrelevant for the CPU-usage. It's the number of rows evaluated that matters.
Back to top
View user's profile Send private message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Tue May 31, 2011 3:40 pm    Post subject:
Reply with quote

GuyC

i missed to include some more, its not tat simple.
The table has data stored in diff ways. the where clause ll use four diff fields to fetch data. all rows will have values for first two fields, sub_id and cnt_id. the other two rows cntl_no and ps_id might hve or might not have values. The actual query ll be like this.

select * from table A
where

A.sub_id in

(select sub_id from table A
where
(sub id = :ws-sub-id
or :ws-sub-id = ' ')

and (cnt_id = :ws-cnt-id
or :ws-cnt-id = ' ')

and (cntl_no = :ws-cntl-no
or :ws-cntl-no = ' ')
and (ps_id = :ws-ps-id
or :ws-ps-id = ' ')
)
and A.cnt_id in

(select cnt_id from table A
where
(sub id = :ws-sub-id
or :ws-sub-id = ' ')

and (cnt_id = :ws-cnt-id
or :ws-cnt-id = ' ')

and (cntl_no = :ws-cntl-no
or :ws-cntl-no = ' ')
and (ps_id = :ws-ps-id
or :ws-ps-id = ' ')
)

If the input is given only for cntl-no then it has to fetch all the rows for the corresponding cnt_id and sub_id.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 31, 2011 4:37 pm    Post subject:
Reply with quote

Well no wonder it isn't performant.
each subselect is probably a complete Tablescan of TableA building two resultsets.
And then for each row of tableA, you probably have a scan through one or two of those resultsets.

I stil don't understand why you use those in-constructs instead of simply putting the criteria in the main where.
Back to top
View user's profile Send private message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Tue May 31, 2011 7:36 pm    Post subject:
Reply with quote

GuyC

The requirement needs the query to be this way, mostly because the table has been designed that way and data is inserted in two ways in it. Instead of having a new table design, inserting data differently has been followed. That is why the query looks like this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 31, 2011 7:54 pm    Post subject:
Reply with quote

Suppose your table has following rows (I left out ws-cntl-id)
Code:
Sub cnt ps
 A   1   X
 A   2   Y
 A   3   Z
 B   1   Z
 B   2   Y
 B   3   X
now your query with as input parm :
ws-sub = ''
ws-cnt = ''
ws-ps = 'X'
will return 4 rows:
Code:
Sub cnt ps
 A   1   X
 A   3   Z
 B   1   Z
 B   3   X
 
Are you sure that is what you want ?

the following query will return only two rows:
Code:
select * from table A
where (sub id  = :ws-sub-id  or :ws-sub-id  = ' ')
  and (cnt_id  = :ws-cnt-id  or :ws-cnt-id  = ' ')
  and (cntl_no = :ws-cntl-no or :ws-cntl-no = ' ')
  and (ps_id   = :ws-ps-id   or :ws-ps-id   = ' ')
output
Code:
Sub cnt ps
 A   1   X
 B   3   X
 


anyway, instead of having this (col = :hv or :hv = '' ) you can always have multiple SQL in your code and execute the correct one
if hv = ''
exec sql1
else
exec sql2
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 31, 2011 8:08 pm    Post subject:
Reply with quote

Hello,

Quote:
The requirement needs the query to be this way,
I suspect it is no part of the "requirement". . .

I suspect the requirement is that the query return the proper result(s) and not dim the data center lights due to excessive resource usage. . .
Back to top
View user's profile Send private message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Wed Jun 01, 2011 11:02 am    Post subject:
Reply with quote

GuyC

Yes the query should return 4 rows just like your example, the query i have put here will return 4 rows as IN condition is used in the sub query.

Your suggestion of using multiple queries instead of the existing one will improve the performance? Are you sure about it?

Dick

As i said the insertion of data into the table has been done in a way that is not in accordance with the table definition, this was done to minimize the cost and now the issue surfaces while retrieving the records, that too after a large number of data has been inserted into the table using one shot queries.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 01, 2011 12:45 pm    Post subject:
Reply with quote

it's not my job to be sure.
You're lucky I'm giving suggestions. It's your job to evaluate , implement and test.
Back to top
View user's profile Send private message
ajaikumar andy robert
Warnings : 1

New User


Joined: 01 Dec 2006
Posts: 23
Location: India

PostPosted: Wed Jun 01, 2011 3:20 pm    Post subject:
Reply with quote

Thank you.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 01, 2011 8:56 pm    Post subject:
Reply with quote

Hello,

Quote:
As i said the insertion of data into the table has been done in a way that is not in accordance with the table definition,
Today feels like another Monday. . . icon_smile.gif

The table definition is what it is. An insert will do just that - insert a row. How can data be inserted that is not in accordance with the table definition?

Sounds like more than a little is somewhat out of control and will probably worsen unless it is reviewed and corrected. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us