Thread: CTE Failures for v8
11-02-11, 16:01 #1Registered User
- Join Date
- Apr 2007
Unanswered: CTE Failures for v8
Prod environment is DB2 OS/390 v8.1.5, and test environments are DB2 z/OS v9.1.0 compatibility mode. We coded and tested a recursive CTE in the test environments and promoted it to the prod environment on 10/20. The query worked fine in both environments until the base date changed to the first day of the month. At that point, the query started failing in the prod environment, but continues to complete successfully in the test environments.
This is the problem part of the query. I substituted sysdummy1 and date literals to eliminate our application tables, but the results are the same. The requirement is to create a 'bucket' for every day of the month up to the previous day. For the first day of the month, a 'bucket' is created for the first day of the month only.
WITH DATES_TBL (LEVEL, D_AS_OF, DAY_OF_MONTH) AS ( SELECT 1 , DATE(SUBSTR('2011-11-01',1,8)||'01') , DAY('2011-11-01') FROM sysibm.sysdummy1 T1 UNION ALL SELECT P.LEVEL + 1 , P.D_AS_OF + 1 DAYS , DAY_OF_MONTH FROM DATES_TBL P WHERE P.LEVEL + 1 < DAY_OF_MONTH ) select * from dates_tbl for fetch only with ur
The failure appears in the DB2 master log as this:
DSNI014I -DSNA DSNIRSET DATA IN USE DURING ABEND 096 REASON 00C90101 ERQUAL 5006 TYPE 00000302 NAME DSNADB07.DSNTMP28.X'00000002' CONNECTION-ID=DSNA CORRELATION-ID=xxx LUW-ID=GA586E4F.B99E.111102182941=69177
Has anybody else run into problems with recursive CTE's working in v8, but causing problems in v9 compatability mode? Any ideas why it fails in v8 in the first place?
11-02-11, 17:54 #2Registered User
- Join Date
- Feb 2008
Apart from your issue,
it may be better to add a condition " AND P.LEVEL < 31" to the where clause in second subselect.
DB2 Version 9.1 for z/OS SQL Reference
When developing recursive common table expressions, remember that an infinite
recursion cycle (loop) can be created. Check that recursion cycles will terminate.
This is especially important if the data involved is cyclic. A recursive common
table expression is expected to include a predicate that will prevent an infinite
loop. The recursive common table expression is expected to include:
v In the iterative fullselect, an integer column incremented by a constant.
v A predicate in the WHERE clause of the iterative fullselect in the form of
″counter_col < constant″ or ″counter_col < :hostvar″. A warning is issued if this
syntax is not found.