II10880: CHANGES TO DB2 ADMINISTRATION GUIDE VOL. 2 SC26-3265-00, THAT DIDN'T MAKE V4.1 GA PUBS. CONT.II09143,II09480,II10644,II11267

APAR status

• Closed as canceled.

Error description

• 5740xyr00 DB2 R410 V4
  This APAR documents changes to the DB2 Admin. Guide Vol. 2
  SC26326500 which did not make Version 4.1 GA pubs.
  continued in II09143, II09480, II10644 & II11267
  ==============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages: 5-121
  Change Description:
  
  Modify the description of commit phase 2 and terminate thread
  to make it more clear that certain activities occur at commit
  time if the bind option RELEASE(COMMIT) is used.
  
    2.  Commit phase 2
  
  In commit phase 2 (IFCID 0070), DB2 writes a beginning of
  phase 2 record to the log.  Again, the trace shows two I/Os.
  Page and row locks (except those protecting the current
  position of cursors declared with the WITH HOLD option), held
  to a commit point, are released.  An unlock (IFCID 0021) with
  a requested token of zeros frees any lock for the specified
  duration.  A summary lock record (IFCID 0020) is produced,
  which gives the maximum number of page locks held and the
  number of lock escalations.  DB2 writes an end of phase 2
  record to the log.
  
  # If RELEASE(COMMIT) is used, the following events also occur:
  
  # o Table space locks are released
  
  # o All the storage used by the thread is freed, including
      storage for control blocks, CTs and PTs, and working areas.
  
  # o The use counts of the DBDs are decreased by one.
      If space is needed in the EDM pool,
      a DBD can be freed when its use count reaches zero.
  
  # o Those table spaces and index spaces that are defined
      with CLOSE(YES) and have a DBD with a use count of zero
      are placed on the queue for deferred close.
  
    3.  Thread termination
  
  When the thread is terminated, the accounting record is
  written.  It does not report transaction activity that
  takes place before the thread is created, and hence
  does not include processor time and I/Os for the
  events shown in Figure 112 before IFCID 0073.
  
  #If RELEASE(DEALLOCATE) is used, this is when table
  #space locks are released, the DBD use count is decreased,
  #the thread storage is released, and eligible page sets
  #are placed on the queue for deferred close.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages: 5-156
  Change Description:
    The description of data page or row locks taken for RR
    table space scan for a SELECT with FOR UPDATE OF is
    incorrect. No data page or row locks are taken when
    the gross lock is taken on the table or table space.
    Replace 'S, U, X(10), or n/a' with just 'n/a'.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages: 5-168
  
  Change Description:
   Modify the description of the ACQUIRE and RELEASE
  bind parameters to indicate that these options are
  not for row or page locks.
  
   The ACQUIRE and RELEASE options of bind operations
   determine when DB2 locks a table or table space
   your application uses and when it releases the lock.
  |(The ACQUIRE and RELEASE options do not affect row or page
  |locks.) The options apply to static SQL statements,
   which are bound before your  program executes.
   If your program executes dynamic SQL statements,
   the objects they lock are locked when first accessed
   and released at the next commit point.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages: 5-181
  
  Add the following information after the existing information
  on 'Utility Operations with Nonpartitioned Indexes'.
  
  SQL Claims on Nonpartitioning Indexes:
  
  If DB2 must access the nonpartitioning index to process SQL
  statements, it tries to claim the entire nonpartitioning index.
  If it cannot get a claim on the entire nonpartitioning index
  (perhaps because of a drain or an exception state on one or
  more of the logical partitions), DB2 claims just the logical
  partitions that it needs to process the SQL.
  
  What this means is that the order of events can lead to
  different activities being allowed to complete.
  For example, if an SQL application that accesses the
  nonpartitioning index is started before
  a utility job is started, the SQL application
  claims the entire nonpartitioning index and a
  utility must wait if that utility needs
  any part of that nonpartitioning index.
  
  On the other hand, if the utility job starts first, and it
  needs only some of the partitions in the nonpartitioning
  index, the SQL application only claims the logical
  partitions that it needs.
  
  If the entire nonpartitioning index is drained, or if there is
  an incompatible exception state on each logical partition, DB2
  does not try to claim logical partitions for SQL. If the
  entire nonpartitioning index is drained, the application can
  time out on the drain lock.  If there is an incompatible
  exception state on every logical partition, the application
  receives a "resource unavailable" SQLCODE.
  ============================================================
  Version 4 Book Title:  Administration Guide, Volume 2
  Pages:  5-205
  Change Description:
  Add this example under Examples of Predicate Properties:
  
  - WHERE T1.COL1=T2.COL1 AND T3.COL2=T4.COL2
  
  Assume that T1.COL1 and T2.COL1 have the same data types,
  and T3.COL2 and T4.COL2 have the same data types.
  If T1.COL1 and T2.COL1 have different nullability
  attributes, but T3.COL2 and T4.COL2 have the same
  nullability attributes, and DB2 chooses a merge scan
  join to evaluate the compound predicate, the compound
  predicate is stage 1.  However, if T3.COL2 and T4.COL2
  also have different nullability attributes, and DB2
  chooses a merge scan join, the compound predicate
  is not stage 1.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages: 5-270
  Change Description:
  
  When Views or Nested Table Expressions are Materialized
  
  In general, DB2 uses materialization to satisfy a reference
  to a view or nested table expression when there is aggregate
  processing (grouping, column functions, distinct), indicated
  by the defining subselect, in conjunction with either
  aggregate processing indicated by the statement referencing
  the view or nested table expression, or by the view or nested
  table expression participating in a join.  Table 79 indicates
  some cases in which materialization occurs.  DB2 can also use
  materialization in statements that contain multiple outer
  joins, or outer joins combined with inner joins.
  
  Cases when DB2 Performs View or Table Expression
  Materialization.  The "X" indicates a case of materialization.
  Notes follow the table.
  
                   VIEW DEFINITION OR NESTED TABLE EXPRESSION
  A SELECT FROM                USES...(2)
  A VIEW OR A
  NESTED TABLE     GROUP BY DISTINCT  Column   Column   Outer
  EXPRESSION                          function function join
  USES...(1)                                   DISTINCT
  -----------------------------------------------------------
   Inner join         X       X         X         X        X
  -----------------------------------------------------------
   Outer join (3)     X       X         X         X        X
  -----------------------------------------------------------
   GROUP BY           X       X         X         X        X
  -----------------------------------------------------------
   DISTINCT           -       X         -         X        -
  -----------------------------------------------------------
   Column function    X       X         X         X        X
  -----------------------------------------------------------
   Column function    X       X         X         X        X
   DISTINCT
  -----------------------------------------------------------
   SELECT subset of   -       X         -         -        -
   view or nested
   table expression
   columns
  
  NOTES TO TABLE 79:
  
  1.If the view is referenced as the target of an INSERT,
    UPDATE, or DELETE, then view merge is used to satisfy
    the view reference.  Only updatable views can be the
    target in these statements.  See Chapter 6 of SQL
    Reference for information on which views are read-only
    (not updatable). An SQL statement can reference a particular
    view multiple times where some of the references can be
    merged and some must be materialized.
  
  2.If a SELECT list contains a host variable in a nested
    table expression, then materialization occurs.  For example:
  
          SELECT C1 FROM
             (SELECT :HV1 AS C1 FROM T1) X;
  
  3. Additional details about materialization with outer joins:
  
   o If a WHERE clause exists in a view or nested table
     expression, and it does not contain a column,
     materialization occurs.  For example:
  
              SELECT X.C1 FROM
                 (SELECT C1 FROM T1
                   WHERE 1=1) X LEFT JOIN T2 Y
                                ON X.C1=Y.C1;
  
   o If the outer join is a full outer join and the
     SELECT list of the view or nested table expression
     does not contain a standalone column
     for the column that is used in the outer join ON clause,
     then materialization occurs. For example:
  
           SELECT X.C1 FROM
              (SELECT C1+10 AS C2 FROM T1) X FULL JOIN T2 Y
                             ON X.C2=Y.C2;
  
   o If there is no column in a SELECT list of a view or nested
     table expression, materialization occurs.  For example:
  
       SELECT X.C1 FROM
         (SELECT 1+2+:HV1. AS C1 FROM T1) X LEFT JOIN T2 Y
                      ON X.C1=Y.C1;
  
   o Most cases of nested outer join statements cause
     views and nested table expressions to be materialized.
  
   o If the result of an outer join undergoes another join
     of any type, the result of the first outer join is
     materialized before the next join begins.
  
   o If the result of an inner join undergoes a further
     outer join, the result of the first inner join is
     materialized before the next join begins.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  pages: 5-277
  Change Description:
         Remove the following items from the
         table of parallelism restrictions:
  
  - Correlated subquery
      This item causes confusion. The outer query does spawn
      parallel tasks, each of which runs on a range of the
      inner table.
  
  - Updatable or ambiguous cursor with CURRENTDATA(YES)
      Move to the 'Turning on Parallel Processing' section on
       5-275.
  
  - Unions across query blocks
     Another misleading and confusing item. Each query in the
     union is running in parallel; therefore, this item should
     not be in the table.
  ============================================================
  Version 4 Book Title: Administration Guide, Volume 2
  Pages:  x-126
  
  Change Description:
   The IFCADD diagnostic information area has been expanded
   in the table as follows:
  
  Name    Offset  Data Type  Description
  -------------------------------------------------
  IFCADD  64      Character, Diagnostic
                  80 bytes   Information
  
                           - IFCAFCI, offset 64, 6 bytes
                             This contains the RBA of the
                             first CI in the active log if
                             IFCARC2 is 00E60854.
                             See "Reading Specific
                             Log Records (IFCID 0129)"
                             for more information.
                           - IFCAGBPN, offset 74, 8 bytes
                             This is the group buffer
                             pool name in error if IFCARC2 is
                             00E60838 or 00E60860
                           - IFCABSRQ, offset 88, 4 bytes
                             This is the size of
                             the return area
                             required when the reason
                             code is 00E60864.
                           - IFCAHLRS, offset 8C, 6 bytes
                             This field can contain
                             the highest LRSN
                             or log RBA in the
                             active log (when
                             WQALLMOD is 'H'.).
  ============================================================
  continued in II11267
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• close for Internet viewing
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels