CTE Failures for v8

jsharon1248 · #1 (**permalink**) 11-02-11, 15:01

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.

Code:

 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 prod version also fails when using '2011-11-02', but starts working for '2011-11-03'. We tried several months, with the same pattern, the 1st and 2nd fail, but the query completes successfully from the 3rd through the end of the month. All values work successfully in the test environments.

The failure appears in the DB2 master log as this:

Code:

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

00C90101 specifies that the 'data manager detected an internal error within DB2'. Our DBA's are reviewing the reasons listed.

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?

tonkuma · #2 (**permalink**) 11-02-11, 16:54

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

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech