Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1566
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: 1566
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: 1281
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 Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm
No new posts Generate SQL query dynamically using ... vnktrrd DB2 7 Tue Aug 28, 2018 8:11 pm
No new posts Query for fetching matching data in t... Poha Eater DB2 10 Mon Jul 09, 2018 6:06 pm
No new posts Varying List Select SQL Query In DB2 ... NikhilGuptaGaya DB2 5 Wed Jun 27, 2018 11:01 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us