DB2 FOR LUW / DB2FORLUW / 970 - DB2

MON_GET_ACTIVITY_DETAILS Table Function fails with SQL0901N if 
used with "FOR EACH ROW" clause. 
 
We see following error: 
 
SQL0901N The SQL statement failed because of a non-severe system 
error. Subsequent SQL statements can be processed. (Reason "FOR 
EACH 
ROW OF can only be specified for a table func".) SQLSTATE=58004 
 
 
For eg: 
 
select actmetrics.application_handle, uow_id, activity_id FROM 
TABLE(MON_GET_ACTIVITY_DETAILS(for each row of (select 
application_handle, uow_id, activity_id from 
table(wlm_get_workload_occurrence_activities_v97(NULL, -1))), 
-1)) AS ACTMETRICS; 
 
 
>>> SQL0901N The SQL statement failed because of a non-severe 
system error. Subsequent SQL statements can be processed. 
(Reason "FOR EACH 
ROW OF can only be specified for a table func".) SQLSTATE=58004 
 
 
db2traces shows: 
 
29990       | | | | | | | sqlnn_cmpl  error [probe 900] [ ZRC = 
0x803100AF = -2144272209 = SQLNN_E_BADNEWS] 
29991       | | | | | | | sqlnn_cmpl exit [rc = 0x803100AF = 
-2144272209 = SQLNN_E_BADNEWS]

**************************************************************** 
* USERS AFFECTED:                                              * 
* users prior to v97fp4                                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Fixed in v97fp4                                              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v97fp4                                            * 
****************************************************************

qualify all parameters of the table function before or inside 
the FOR EACH ROW OF clause. 
 
select actmetrics.application_handle, uow_id, activity_id FROM 
TABLE(MON_GET_ACTIVITY_DETAILS(for each row of (select 
application_handle, uow_id, activity_id, -1 from 
table(wlm_get_workload_occurrence_activities_v97(NULL, -1))))) 
AS ACTMETRICS;

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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
 

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC73239 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

03.05.2010
02.05.2011
02.05.2011

9.7.FP4