Hi!

I need to make an update to a field and it's value must be equal to another field in another table. The sql begin's to execute but then it gives an error:

"Result of SELECT more than one row."

I think it's normal, that he return's more than one row but how can i get it done?

The sql statement:
UPDATE bibl/FGDVLD001 SET ESCRITORIO = (SELECT A0AREA
FROM bibl/APOL00 WHERE MOD=A0$RAM AND NAPO=A0NAPO AND MULTI='N'
) WHERE EXISTS (SELECT * FROM ACOGISD#E/APOL00 WHERE
MOD=A0$RAM AND NAPO=A0NAPO AND MULTI='N')


In the detail's of the message, it says:
For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row.



But, isn't what i'm doing?

Looks like this clause is invalid because the select returns more than one row (and therefore more than one value is returned for the SET clause)

SET escritorio =
(
SELECT
a0area
FROM bibl/apol00
WHERE mod=a0$ram
AND napo=a0napo
AND multi='N' )

You might try adding SELECT FIRST 1 ROW ONLY to the select clause above.

Sorry Marcus_A, but i don't understand!
How do i the sql statement?

In the SELECT A0AREA
FROM bibl/APOL00 WHERE MOD=A0$RAM AND NAPO=A0NAPO AND MULTI='N'
there is more than one row, but they have the same value for the field that i want to update.

It doesn't matter if they are the same value, you can only have one of them in the other half of the "SET escritorio = " statement.

You can try the following (but I am not sure if it will work):

UPDATE
bibl/fgdvld001
SET escritorio =
(
SELECT
a0area
FROM bibl/apol00
WHERE mod=a0$ram
AND napo=a0napo
AND multi='N'
SELECT
first 1 row only
)

WHERE exists
(
SELECT
*
FROM acogisd#e/apol00
WHERE mod=a0$ram
AND napo=a0napo
AND multi='N'
)

You can format your SQL here:
http://www.sqlformatter.de/

Also, don't use Select * in the last SELECT. Use SELECT '1' or SELECT one of the columns in the WHERE clause.

Hi Marcus,

When comparing explain plans, it doesn't seem to matter if SELECT * or SELECT 1 is used. Probably the optimizer understands what the select is supposed to do. But, saying that, maybe we are talking about different DB2's?

kind regards, Gert

The optimizer determines the access path (whether to perform table space scan, use an index, etc.). It will not show differences in performance of how many columns are selected.

There is no reason to select all the columns of table when used in a WHERE EXISTS clause, it just takes extra work for DB2 to perform at run time. The difference in performance may not be significant in all cases, but clearly “select *” does use unnecessary resources.

It does not matter which DB2 version or platform is being used.

Hi Marcus,

The explain output from db2expln as well as the command center also displays the number of columns returned from the tables.
Because the query optimization step (which is executed before the acces plan generation) probably finds out that no real column data is needed when optimizing the query it replaces the SELECT * with SELECT <some internal value>. This way the optimizer prevents the use of unecessary resources.

Kind regards, Gert

Maybe you are correct (or maybe not). The part of the query with the WHERE EXISTS clause does not return any columns to the user, it only returns columns to the other part of the query. So I don't know if you can tell for sure what is happening internally and whether extra resources are used with the SELECT *. IMO it is not good practice to use SELECT * in any situation, especially with a WHERE EXISTS.

Thank you, for your help! I got the idea and resolved in this way:

UPDATE bibl/FGDVLD001 SET ESCRITORIO = (SELECT DISTINCT A0AREA
FROM ACOGISD#E/APOL00 WHERE MOD=A0$RAM AND NAPO=A0NAPO and multi='N'
) WHERE exists
(SELECT distinct a0area FROM ACOGISD#E/APOL00 WHERE
MOD=A0$RAM AND NAPO=A0NAPO and multi='N')

Hi,

Gerd is right, modern optimizers spot patterns where retrieval of attributes is not necessary, so using * in the given situation is more or less a matter of style.

Actually, other people, including Joe Celko in his book SQL for Smarties (no advertising), make heavy use of *, not because they wouldn't know better, but because it is easy to spot to the human eyes as well.

That doesn't mean you should use * anywhere without thinking.

Just my 2 ct

Johann

Is that for all optimization levels, or just 5 or higher? Are you sure?

Hi Marcus,

I have never seen a convincing reason to switch to another optimization level in DB2, and so I can't be sure about that.

Anyway, I got the impression that IBM does most of its development and testing with optimization level 5 and would not recommend working with other levels to anyone.

Have you seen sites with other levels than 5?

Johann

An optimization class of less than 5 should be "considered" where there is a lot of dynamic SQL in a high transaction rate OLTP system. This is partly because of the amount of time it takes to optimize the SQL when using a higher class (although package cache may alleviate this in some situations).

Here is what the "Administrative Guide: Performance" says about optimization classes 2 and 3:

Optimization Class 2
This class directs the optimizer to use a degree of optimization significantly higher than class 1, while keeping the compilation cost significantly lower than classes 3 and above for complex queries. This optimization class has the following characteristics:

- All available statistics, including both frequency and quantile non-uniform distribution statistics, are used.
- All query rewrite rules are applied, including routing queries to materialized query tables, except computationally intensive rules that are applicable only in very rare cases.
- Greedy join enumeration is used.
- A wide range of access methods are considered, including list prefetch and materialized query table routing.
- The star-join strategy is considered, if applicable.

Optimization class 2 is similar to class 5 except that it uses Greedy join enumeration instead of Dynamic Programming. This class has the most optimization of all classes that use the Greedy join enumeration algorithm, which considers fewer alternatives for complex queries, and therefore consumes less compilation time than classes 3 and above. Class 2 is recommended for very complex queries in a decision support or online analytic processing (OLAP) environment. In such environments, specific queries are rarely repeated exactly, so that a query access plan is unlikely to remain in the cache until the next occurrence of the query.

Optimization Class 3
This class requests a moderate amount of optimization. This class comes closest to matching the query optimization characteristics of DB2 for MVS/ESA, OS/390, or z/OS. This optimization class has the following characteristics:

- Non-uniform distribution statistics, which track frequently occurring values, are used if available.
- Most query rewrite rules are applied, including subquery-to-join transformations.
- Dynamic programming join enumeration, as follows:
+ Limited use of composite inner tables
+ Limited use of Cartesian products for star schemas involving look-up tables
- A wide range of access methods are considered, including list prefetch, index ANDing, and star joins.

This class is suitable for a broad range of applications. This class improves access plans for queries with four or more joins. However, the optimizer might fail to consider a better plan that might be chosen with the default optimization class [class 5].
------------------------------------------

Conversely, for complex queries (where optimization time is usually irrelevant) I would consider optimization class 7. IBM uses class 7 for its TPC-H ad-hoc query benchmarks.