Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68

    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.

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •