Query getting timed out

sumit agarwalla · New User Joined: 13 Nov 2008 Posts: 17 Location: hyderabad

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.

Pandora-Box · Posted: Wed Nov 28, 2012 1:39 pm

When you say timeout are you getting the SQLCODE -905?

sumit agarwalla · New User Joined: 13 Nov 2008 Posts: 17 Location: hyderabad

Yes Pandora

Pandora-Box · Posted: Wed Nov 28, 2012 2:05 pm

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) ???

GuyC · Posted: Wed Nov 28, 2012 2:16 pm

It all depends on available indexes and cardinalities, but you could try not exists() instead of not in()

sumit agarwalla · New User Joined: 13 Nov 2008 Posts: 17 Location: hyderabad

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.