DB2 - Problembeschreibung
Problem IC74565 | Status: Geschlossen |
DB2 might abend while creating MQT whose definition is recursive and has common table expression as well as IN predicate | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 | |
Problembeschreibung: | |
MQT creation might trap with infinite recursive calls of sqlnq_reroutable_ast_rec in the trap file. This happens only when MQT definition has the following characteristics: 1. MQT definition has CSE (Common Sub Expression), called V1 2. MQT has recursive UNION ALL construct, called V2. V2 has two union all branches. The first union branch references V1 and the second branch references V1 and V2 of course. 3. V2 second branch has IN predicate and the element list consists of multiple values or expressions. Example: -- create a base table create table t1(c1 int, c2 int); -- mqt creation traps create table mqt as ( with v1(c1, c2) as (select * from t1), v2(level, c1, c2) as (select 1, v1.* from v1 union all select parent.level+1, parent.c1, parent.c2 from v2 parent, v1 child where parent.c1 = child.c1 and child.c2 in (1,2) and parent.level < 10) select c1, c2 from v2 ) data initially deferred refresh deferred; | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See error description above. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.1 Fix Pack 11. * **************************************************************** | |
Local-Fix: | |
Make V1 a regular view and change MQT definition to reference V1 instead of common table expression. | |
verfügbare FixPacks: | |
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows | |
Lösung | |
First fixed in DB2 Version 9.1 Fix Pack 11. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC78347 IC78348 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 18.02.2011 08.12.2011 08.12.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.1.FP11 | |
Problem behoben lt. FixList in der Version | |
9.1.0.11 |