II07606: CHANGES TO DB2 APPLICATION PROGRAMMING & SQL GUIDE SC26-4889-00 THAT DID NOT MAKE VERSION 3 GA PUBS. CONTINUED IN II08058.

APAR status

• Closed as canceled.

Error description

• This info. APAR documents changes to the DB2 Application
  Programming & SQL Guide SC26488920 which did not make the
  General Availability (GA) pubs.  Continued in II08058.
  5740xyr00 R310
  ===============================================================
  Version 3 Book Title:  DB2 Application Programming and SQL
  Guide, Pages:  2-16
  Change Description:
  
  Add the following text at the bottom of the page, before
  "Using Datetime Data" on page 2-17:
  
  A WARNING ABOUT 31-DIGIT PRECISION FOR DECIMAL NUMBERS:
  DB2 allows two sets of rules for determining the precision
  and scale of the result of an operation with decimal
  numbers.
  
    * DEC15 rules allow a maximum precision of 15 digits in
      the result of an operation.  Those rules are in effect
      when both operands have precisions less than 15,unless
      one of the circumstances that imply DEC31 rules applies.
  
    * DEC31 rules allow a maximum precision of 31 digits in
      the result. Those rules are in effect if any of the
      following is true:
  
        - Either operand of the operation has a precision
          greater than 15.
  
        - The operation is in a dynamic SQL statement and
          the installation option for DECIMAL ARITHMETIC,
          chosen on panel DSNTIPF, is DEC31 or 31.
  
        - The operation is in an embedded (static) SQL
          statement that was precompiled with the option
          DEC(31), or with the default for that option when
          DECIMAL ARITHMETIC is DEC31 or 31.  (See "Step 1:
          Precompile the Application" on page 4-55 for a
          description of precompilation and a list of all
          precompiler options.)
  
  An apparently simple multiply or divide operation causes an
  error when the calculated scale of the result is less
  than 0.  That can happen with either set of rules, and you
  must correct for those errors.  But the situation is much
  more common with DEC31 rules.
  
  For example, if A and B are columns of a table with the data
  type DECIMAL, and if DEC31 rules are in effect, then
  SUM(A) / SUM(B) always creates an error.
  
  For another example, if DEC31 rules are in effect, this
  statement also causes an error:
  
    SELECT SUM(SALARY) / 52 FROM DSN8310.EMP
  
  In that case, the SALARY column has the data type
  DECIMAL(9,2).  Converted to a decimal number, 52 has type
  DECIMAL(5,0).  By DEC31 rules, SUM(SALARY) is DECIMAL(31,2)
  and the scale of the result is 30-5-(31-2+0) = -4.  The
  negative value of the scale produces an error that is
  detected when the statement is bound, for a static
  statement, or when it is prepared, for a dynamic statement.
  
  WHAT YOU CAN DO:  For static SQL statements, the simplest
  fix is to override DEC31 rules by specifying the option
  DEC(15) when precompiling.  That reduces the probability of
  errors for statements embedded in the program, but does
  nothing for dynamic statements that the program prepares.
  
  For a dynamic statement, or for a single static statement,
  use the scalar function DECIMAL to specify values of the
  precision and scale of a result that cause no errors.  For
  example, write:
  
    SELECT DECIMAL(SUM(SALARY),21,2) / 52 FROM DSN8310.EMP
  
  In that example, the function DECIMAL specifies that the
  precision and scale of the first operand are 21 and 2,
  regardless of DEC31 rules.  For the division operation,
  those rules then say that the scale of the result must be
  30-5-(21-2+0) = 6, which causes no problem.
  
  For a description of scalar functions in general, and a list
  of all of them, see "Using Scalar Functions" on page 2-19.
  ===============================================================
  Version 3 Book Title:  DB2 Application Programming & SQL
  Guide      Pages:  3-16
  Change Description:
  
  Add the following to the LRECL parm description on page
  3-16:
  
     "The calling program must allocate enough storage in the
      output message area to hold 10 messages."
  
  Please note that the Application Programming and SQL
  Reference will not state that LRECL * 10 is the right amount
  of storage.  If users specify an lrecl smaller than one of
  the messages, then the message uses more than one record
  line, and they may need more than 10 record lines to handle
  10 messages.
  ================================================================
  Version 3 Book Title:  Application Programming and SQL Guide
  Pages: 3-26
  Change Description:
  
  Change the first two sentences in the CICS specific box
  FROM:
   In CICS applications that are not pseudo-conversational,
   you can use DECLARE CURSOR...WITH HOLD to indicate that a
   cursor is not to be closed at a commit or sync point.
   However, all cursors are always closed at end-of-task
   (EOT) and on SYNCPOINT ROLLBACK.
  
  TO:
   In CICS applications, you can use DECLARE CURSOR...WITH
   HOLD to indicate that a cursor should not close at a commit
   or sync point.  However, SYNCPOINT ROLLBACK closes all
   cursors, and end-of-task (EOT) closes all cursors before
   DB2 reuses or terminates the thread.
  
  Add to the end of the paragraph in the box:
   You should always close cursors that you no longer need.
   If you let DB2 close a CICS attachment cursor, the cursor
   might not close until DB2 reuses or terminates the thread.
  ================================================================
  Version 3 Book Title:  DB2 Applicaton Programming and SQL
  Guide,  Pages:  3-44
  Change Description:
  
  Change the entries in columns 2 and 3 of Table 9, for the
  SQL data type DECIMAL(p,s) or NUMERIC(p,s), to read as
  follows:
  
    Assembler Equivalent
  
    DS PLn'value'
    DS P'value'
  
  Notes
  
  p is precision; s is scale. 1<=p<=31 and 0<=s<=p.
  'value' is a literal value that includes a decimal point.
  1<=n<=16.  You must use n, 'value', or both.
  The precision is 2n-1, if n is used; otherwise, it is
  the number of digits in 'value'.  The scale is the number
  of digits to the right of the decimal point in 'value',
  if 'value' is used; otherwise, it is 0.
  For efficient use of indexes, choose n and 'value' so that
  the calculated precision is p and the calculated scale is s.
  ================================================================
  Version 3 Book Title: DB2 Application Programming & SQL
  Guide    pages:  3-59
  Change Description:
  
    In table 12, next to INCLUDE SQLCA, add
  
        "or LINKAGE SECTION"
  ================================================================
  Version 3 Book Title:  DB2 Application Programming and SQL
  Guide      Pages:  4-17
  Change Description:
  
  Change the text in the box labeled "TSO, Batch, and CAF" to
  read as follows:
  
  -- TSO, Batch, CAF, and DDF --------------------------------
  
  When a deadlock or timeout occurs in these environments, DB2
  decides whether or not to roll back one of the application
  processes.  Your application process receives one of two
  return codes in the SQLCODE field of the SQLCA and an
  associated reason code indicating whether a deadlock or
  timeout occurred.  Both SQLCODEs are possible.  Your
  application must be prepared for either one.
  
  -911: A ROLLBACK statement was issued on behalf of your
        application process.  All updates that occurred
        during the current unit of work have been undone.
        (SQLSTATE '40000')
  
  -913: ROLLBACK was not performed.  Your application process
        is requested either to issue a ROLLBACK statement
        itself or to terminate. (SQLSTATE '40502')
  ================================================================
  Version 3 Book Title:  DB2 Application Programming and SQL
  Guide,    Pages:  4-22
  
  Change Description:
  
  Replace the paragraph headed "Cursor Stability" with the
  following paragraphs:
  
  CURSOR STABILITY: Cursor stability means that the page
  containing the row you retrieve (for example, row A1)
  might not be locked at all.  If it is locked, the lock is
  held only until you retrieve another row on another page.
  
  If the page is not locked, another process might change or
  delete the row while your cursor still points to it.  If
  your application depends on keeping the row unchanged until
  you retrieve another row, you can ensure that the page is
  locked by binding with the option CURRENTDATA(YES).
  
  If the page is locked, no other application process can
  update or delete the row. When you retrieve another row
  (for example, row A2), the lock on the page containing row
  A1 is released and the new page containing row A2 is locked,
  assuming each row is contained in a different page.  Figure
  26 on page 4-23 illustrates this situation when an index is
  used; however, for a table space scan, a page is unlocked
  before the next page is locked.
  ================================================================
  Version 3 Book Title:  DB2 Application Programming and SQL
  Guide, Pages:  4-24
  Change Description:
  
  Replace the text under the heading, "When Plan and Package
  Options Differ" with the following:
  
  A plan bound with one set of options can include packages in
  its package list that were bound with different sets of
  options.  In general, statements in a DBRM bound as a
  package use the options that the package was bound with, and
  statements in DBRMs bound to a plan use the options that the
  plan was bound with.
  
  The rules are slightly different for the bind options that
  affect locking, RELEASE and ISOLATION.  The values of those
  two options are set when the lock on the resource is
  acquired and usually stay in effect until the lock is
  released.  But a conflict can occur if a statement that is
  bound with one pair of values requests a lock on a resource
  that is already locked by a statement that is bound with a
  different pair of values.  DB2 resolves the conflict by
  resetting each option with the available value that causes
  the lock to be held for the greatest duration.
  
  As a result:
  
  IF THE CONFLICT IS BETWEEN:          THEN THE VALUE USED IS:
  
  RELEASE(COMMIT) and RELEASE(DEALLOCATE)  RELEASE(DEALLOCATE)
  
  ISOLATION(CS) and ISOLATION(RR)          ISOLATION(RR)
  ================================================================
  Book Title: Application Programming and SQL Guide
  Page Number: p. 4-61
  Change Description:
  
  Replace the description of the VERSION precompiler option with:
  
  Define the version identifier of a package, program, and the
  resulting DBRM. When VERSION is specified, a version identifier
  is created in the program and DBRM. This affects the size of the
  load module and DBRM. The version identifier is used when the
  DBRM is bound to a plan or package.
  
  If a version is not specified at precompile time, then an empty
  string is the default version identifier. If AUTO is specified,
  the consistency token is used to generate the version
  identifier. If the consistency token is a timestamp, the
  timestamp is converted into ISO character format and used as the
  version identifier. For guidance on using version, see
  "Identifying a Package Version" on page 4-73.
  ================================================================
  Book Title: Application Programming and SQL Guide
  Page Number: p. 4-79
  Change Description:
  
  Change the first example to:
  
  //jobname JOB USER=MY DB2ID
  //GO EXEC PGM=IKJEFT01,DYNAMNBR=20
  //STEPLIB DD DSN=DSN310.SDSNLOAD,DISP=SHR
  .
  .
  .
  //SYSTSPRT DD SYSOUT=A
  //SYSTSIN DD *
   DSN SYSTEM (ssid)
   RUN PROG (SAMPPGM) -
     PLAN (SAMPLAN) -
     LIB (SAMPPROJ.SAMPLIB) -
     PARMS ('/D01 D02 D03')
   END
   /*
  
  Delete the second sentence in the sixth bullet that says:
  
  The name must be enclosed between apostrophes (').
  ================================================================
  This is continued in II08058.
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• CLOSED 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