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
 

 

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: 1278
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: 1278
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: 1278
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 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
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


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