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: 1534
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: 1534
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: 1280
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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