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
 

 

Query getting timed out

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sumit agarwalla

New User


Joined: 13 Nov 2008
Posts: 17
Location: hyderabad

PostPosted: Wed Nov 28, 2012 1:28 pm    Post subject: Query getting timed out
Reply with quote

I was trying to update one column for TABLE1 as in below query with 'Y'. The ACC_No in Table1 has 10 character for prod code 'XXX' where as in Table2 it is of 9 Characters. So i have used the CONCAT function to append a single '0' for ACC_NO. Now the problem is that i am not able to run this query as it times out due to large number of records. I later on tried writing a eazytrieve with cursors in which i select each row and try to update later which too is getting timed out. Now i am all stuck here. Can anyone please suggest me an alternative on how to proceed on this one? Appreciate your help on this.

Code:


UPDATE
                TABLE1
SET
                COL5 = 'Y'
WHERE
                PROD_CD = 'XXX'
                AND LTRIM(ACC_NO) IN
                (
                                SELECT
                                                '0' CONCAT LTRIM(ACC_NO)
                                FROM
                                                TABLE2
                                WHERE
                                                REGN_ID = '100'
                                                AND PROD_CD = 'XXX'
                )
[/code]
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 28, 2012 1:39 pm    Post subject:
Reply with quote

When you say timeout are you getting the SQLCODE -905?
Back to top
View user's profile Send private message
sumit agarwalla

New User


Joined: 13 Nov 2008
Posts: 17
Location: hyderabad

PostPosted: Wed Nov 28, 2012 1:40 pm    Post subject: Reply to: Query getting timed out
Reply with quote

Yes Pandora
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 28, 2012 2:05 pm    Post subject:
Reply with quote

You get -905 because you saturate ASULIMIT

You might need to work to split your query into smaller chunks than doing it in one go

But before that talk to your DBAs

And why you need LTRIM(ACC_NO) ???
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 28, 2012 2:16 pm    Post subject:
Reply with quote

It all depends on available indexes and cardinalities, but you could try not exists() instead of not in()
Code:
UPDATE
                TABLE1 A
SET           COL5 = 'Y'
WHERE    PROD_CD = 'XXX'
                AND not exists (
                ( SELECT 1
                      FROM TABLE2 B
                   WHERE B.REGN_ID = '100'
                        AND B.PROD_CD = 'XXX'
                        and b.acc_no = substr(a.acc_no,2))
Back to top
View user's profile Send private message
sumit agarwalla

New User


Joined: 13 Nov 2008
Posts: 17
Location: hyderabad

PostPosted: Wed Nov 28, 2012 4:59 pm    Post subject:
Reply with quote

Pandora,

The ACC_NO field is of 23 characters and we have 10 characters in Table 1 and 9 Characters in table 2 for PROD_CD = XXX which is right justified. So i have used the LTRIM to remove the spaces.

GuyC,

Thanks for the query. I am trying this out. I hope this query doesnt get timed out.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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