A fix is available
APAR status
Closed as program error.
Error description
Incorrect output (less rows found) may occur when running a query to reference a TABLE UDF (user defined function) more than one time. It is an inline UDF defined with recursive CTE that causes the problem. Additional keywords: SQLUDF SQLTUDF SQLTABLEUDF SQLCTE SQLINCORR SQLINCORROUT DB2INCORR/K
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 for z/OS users of * * SQL inline table user defined * * function and common table * * expression. * **************************************************************** * PROBLEM DESCRIPTION: Incorrect out may occur for an * * SQL statement that satisfies the * * following conditions: * * 1. The SQL statement references to * * an SQL inline table user defined * * function multiple times with * * different arguments. * * 2. The SQL inline table user defined * * function contains a common table * * expression which references to * * the parameter of the SQL inline * * table user defined function. * **************************************************************** * RECOMMENDATION: * **************************************************************** Incorrect out may occur for an SQL statement that satisfies the following conditions: 1. The SQL statement references to an SQL inline table user defined function multiple times with different arguments. 2. The SQL inline table user defined function contains a common table expression which references to the parameter of the SQL inline table user defined function. For Example, CREATE FUNCTION TUDF_CTE (P1 INT) RETURNS TABLE (C1 INT) LANGUAGE SQL SPECIFIC MYTUDF2 NOT DETERMINISTIC READS SQL DATA RETURN WITH CTE_T (C1) AS ( SELECT P1 FROM T1 UNION ALL SELECT C1+1 FROM CTE_T WHERE C1 < 3 ) SELECT C1 FROM CTE_T; SELECT * FROM TABLE (TUDF_CTE(2))TX, TABLE (TUDF_CTE(3))TZ; Incorrect output may occur for the SELECT statement above because the common table expression inside the SQL inline table user defined function is not handled correctly.
Problem conclusion
DB2 was fixed to handle the common table expression inside the SQL inline table user defined function correctly. Additional Key words: SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K SQLCTE SQLINLINE SQLTABLEUDF
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PM54034
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2011-12-09
Closed date
2012-03-16
Last modified date
2012-05-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK77038
Modules/Macros
DSNXOACR DSNXOB2 DSNXODSO DSNXOEXC DSNXOFD DSNXOIWS DSNXOOS2 DSNXOP0 DSNXOVC DSNXOVD DSNXOV0 DSNXOV1
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK77038
UP12/04/04 P F204
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 May 2012