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
 
Update query returns -905 due to ASUTIME LIMIT

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

New User


Joined: 14 Nov 2007
Posts: 18
Location: US

PostPosted: Wed Nov 28, 2012 2:28 pm    Post subject: Update query returns -905 due to ASUTIME LIMIT
Reply with quote

Hi,

I have the following requirement

To update the column DESIG from the table TPIE.with the value from column DESPI from table TBAL

Conditions:
1. column NUMPI from TPIE and TBAL should be same
2. column TYPPI from TPIE is not equal to ABC
3. column C.DESIG <> A.DESPI
I used the following query

UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI
AND C.TYPPI <> 'ABC' AND C.DESIG <> A.DESPI)
WHERE C.NUMPI IN (SELECT D.NUMPI FROM XYX.TBAL D
WHERE C.TYPPI <> 'ABC' AND C.DESIG <> D.DESPI);

but this runs for a long time and gives -905 due to ASUTIME. I guess i need to optimize the query. Could you let me know if there is an alternate way?
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 Nov 28, 2012 9:13 pm    Post subject:
Reply with quote

Hello,

What does the output from an EXPLAIN show?

How many total rows will this query have to "touch" to process?

How many of the columns named are a key or the high-order part of a key?
Back to top
View user's profile Send private message
Simone Salzmann

New User


Joined: 26 Nov 2012
Posts: 17
Location: Switzerland

PostPosted: Thu Nov 29, 2012 3:05 am    Post subject:
Reply with quote

Two questions:

1) If there are numeric columns involved on both tables - do they have the same format?

2) Why Select Distinct?
It looks as if NUMPI may be "ambiguous" on table TBAL - what if the different occurences have different values for DESPI?

But if this is ok I'd shorten your query even like this (provided there aren't one million equals and only 10 unequals for DESPI and DESIG)

UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI)
WHERE C.TYPPI <> 'ABC';
Back to top
View user's profile Send private message
jramuk

New User


Joined: 14 Nov 2007
Posts: 18
Location: US

PostPosted: Fri Nov 30, 2012 12:30 pm    Post subject: Reply to: Update query returns -905 due to ASUTIME LIMIT
Reply with quote

How many total rows will this query have to "touch" to process?
100000 in TBAL and 400000 in TPIE

How many of the columns named are a key or the high-order part of a key?

The Key in both the tables are different. Only NUMPI and TYPPI are common fields.
Back to top
View user's profile Send private message
jramuk

New User


Joined: 14 Nov 2007
Posts: 18
Location: US

PostPosted: Fri Nov 30, 2012 12:30 pm    Post subject: Reply to: Update query returns -905 due to ASUTIME LIMIT
Reply with quote

Quote:
How many total rows will this query have to "touch" to process?

100000 in TBAL and 400000 in TPIE

Quote:
How many of the columns named are a key or the high-order part of a key?


The Key in both the tables are different. Only NUMPI and TYPPI are common fields.

Quote'd
Back to top
View user's profile Send private message
Simone Salzmann

New User


Joined: 26 Nov 2012
Posts: 17
Location: Switzerland

PostPosted: Fri Nov 30, 2012 2:51 pm    Post subject:
Reply with quote

What I don't grab from your answer:

On what positions do NUMPI and TYPPI stand in the keys of the tables?
And do they have the same format?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 30, 2012 4:43 pm    Post subject:
Reply with quote

Simone Salzmann wrote:

Code:
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT DISTINCT A.DESPI FROM XYZ.TBAL A
WHERE C.NUMPI = A.NUMPI)
WHERE C.TYPPI <> 'ABC';

This will
- set C.DESIG to NULL when there are no qualifying rows in xyz.TBAL.
=> You repeat the subquery in the where clause to avoid this.

- update rows on TPIE even if C.DESIG is already the correct value.
=> you add AND C.DESIG <> A.DESPI to the where clause of the subquery to avoid this.

So the original query was probably more correct then this one.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 30, 2012 4:50 pm    Post subject:
Reply with quote

* Criteria on the outer table should remain on the outer where clause
and
* i would try it using exists :

Code:
UPDATE XYZ.TPIE C
SET C.DESIG = (SELECT A.DESPI FROM XYZ.TBAL A
                WHERE C.NUMPI = A.NUMPI
                  AND C.DESIG <> A.DESPI
               fetch first 1 row only)
WHERE  C.TYPPI <> 'ABC'
  AND  exists (SELECT A.DESPI FROM XYZ.TBAL A
                WHERE C.NUMPI = A.NUMPI
                  AND C.DESIG <> A.DESPI)
;

and you do need an index on TBAL(NUMPI,...)
Back to top
View user's profile Send private message
Simone Salzmann

New User


Joined: 26 Nov 2012
Posts: 17
Location: Switzerland

PostPosted: Fri Nov 30, 2012 6:47 pm    Post subject:
Reply with quote

Hi, GuyC

I could live with updating equal fields, that's why I left out the <> condition.

But it's true *blush* while thinking about the DISTINCT clause I forgot my thoughts about non-matching rows on the tables.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 30, 2012 7:55 pm    Post subject:
Reply with quote

If updating non-equal fields already gives -905, how about updating all rows?

Suppose this is a correction query which would update 50 rows.

You wouldn't mind updating 400.000 rows just so you can save 3 lines of coding?
How about last update timestamps, mirrorring, triggers, .....
I thought your ambition was to become DBA. (edit <= oops, it was someone elses)
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 Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm

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