APAR status
Closed as program error.
Error description
A SELECT with subquery on a view with UNION is slow when it is executed from a procedure. Executing the SELECT outside of the procedure shows that the subquery is applied as an index filter on the tables in the view. Executing the SELECT from a procedure shows that the view is materialized again for the main query and the subquery is applied to the temp table for the view. Example: create view myview (id,c1,c2,c3,c4) as select id,c1,c2,c3,c4 from tab1 union all select id,c1,c2,c3,c4 from tab2 union all select id,c1,c2,c3,c4 from tab3; select c4 from myview where id in (select max(id) from myview where id=40 );
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users having union all view and subquery in SPL * **************************************************************** * PROBLEM DESCRIPTION: * * When union all view is used on SPL along with subquery, IDS * * does not use available index filters inside subquery. Since * * sequential scan is used, IDS gives performance issues in SPL * * as compared to stand alone query. * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.50.xC8. * ****************************************************************
Problem conclusion
Problem is fixed in 11.50.xC8.
Temporary fix
Comments
APAR Information
APAR number
IC68045
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2010-04-20
Closed date
2010-10-06
Last modified date
2010-10-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSN
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
06 October 2010