|
Problem IC81696
|
Status: Closed |
FUNCTIONS WITH ROW RETURN TYPES EXECUTE MULTIPLE TIMES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When a function with a row return type is used as the source
expression for an INSERT or SET
statement, the function may be executed once per row field.
This will occur when:
- the row is used as a source value in an INSERT statement, or
- the row is used as a source value in a SET statement, and
either the source or target has an anchored row
data type.
This may cause unexpected performance problems, if the function
executed multiple times is complex. It could also result in
wrong results, if for example the function executed multiple
times modifies a global variable, the modification to the global
variable would also occur multiple times.
For example,
create table t1 (c1 int, c2 int);
DB20000I The SQL command completed successfully.
create type row1 as row (f1 int, f2 int);
DB20000I The SQL command completed successfully.
create function foo (a int) returns anchor row t1
begin
declare v1 row1;--
set v1 = (a, a + 1);--
set gv1 = gv1 + 1;--
return v1;--
end
DB20000I The SQL command completed successfully.
create variable gv1 int default 0;
DB20000I The SQL command completed successfully.
create variable gvRow1 row1;
set gvRow1 = foo(1)
DB20000I The SQL command completed successfully.
values gv1
1
-----------
2
1 record(s) selected.
The global variable gv1 was incremented twice even though the
function was only specified in the SET statement once. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of DB2 LUW version 9.7 *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 LUW version 9.7 fix pack 6. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Fixed in DB2 LUW version 9.7 fix pack 6. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.02.2012
06.06.2012
06.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
