II06379: SQLCODE -805 WHEN ACCESSING SQL/DS SERVER AND THE DB2 PLAN PKLIST DOES CONTAIN THE CORRECT INFORMATION.

APAR status

• Closed as canceled.

Error description

• The following describes an SQL/DS problem that affects DB2
  users. Please see SQL/DS APAR PN28514.
  
  Message DSNL031I IFCID 0191 and SQLCODE -805, or -918, or
  CICS ABEND ASP7 (when CICS application does a SYNCPOINT)
  when a DB2 application is accessing an SQL/DS server
  database and the the first candidate in the DB2 Plan
  Package List is not the correct package.
  
  The user binds a package at an SQL/DS database with the
  name GOODCOL.PGMA (CollectionID=GOODCOL and the program
  name=PGMA).
  Then a DB2 plan (say PLANA) is bound with a PKLIST of
  '(*.JUNKCOL.*,*.GOODCOL.*)'.
  When an SQL statement in PGMA is executed (under Plan PLANA),
  DB2 must tell SQL/DS the full program name (collection ID and
  program name). DB2 obviously knows the program name but DB2
  must use the PKLIST to determine the collection ID.  DB2 will
  search the PKLIST looking for possibilities.  A collection ID
  of JUNKCOL is a possibility (the 1st '*' indicates any location
  and the second '*' indicates any program) so DB2 sends the SQL
  request to SQL/DS indicating the program name is JUNKCOL.PGMA.
  In this case, the program name will not be found.
  
  If the server does not find the program name and returns a -805
  SQLCODE, DB2 will continue searching the PKLIST for other
  possible matches until the statement works or the end of the
  PKLIST is reached.  That is, if DB2 gets an indication that
  JUNKCOL.PGMA is not found at the server, DB2 will continue
  searching the PKLIST for another match and find that
  GOODCOL.PGMA can be attempted which would work. This does in
  fact work when a DB2 subsystem is the server. However, an
  SQL/DS server considers this a severe error and does a
  rollback of existing work and tells the DB2 requester that
  it did a rollback (DDM ABNUOWRM - ABNormal Unit Of Work Reply
  Message).
  DB2 considers the unexpected rollback by the SQL/DS database a
  DRDA exception condition and generates message DSNL031I with
  an IFCID 191 diagnostic trace record (in case it is needed).
  As a result of the unexpected rollback, DB2 cannot continue
  with the PKLIST scan and so it returns a -805 or -918 SQLCODE
  to the application.  Commits (CICS SYNCPOINT) will also fail
  as a result of the SQL/DS rollback.
  
  This is an SQL/DS problem. SQL/DS APAR PN28514 has been opened
  to address the issue. SQL/DS should not rollback when a -805
  condition is encountered which will allow DB2 to continue its
  PKLIST scan.  Please see SQL/DS APAR PN28514.
  
  As a circumvention, users should modify their DB2 Plan PKLIST so
  that the correct collection ID will be used by DB2 the first
  time. In other words, the above PKLIST could be modified to
  '(*.GOODCOL.*,*.JUNKCOL.*)'.
  
  **************************
  Additional symptoms and keywords:
  5740XYR00 DB2 DB2DDF DB2DRDA1
  DSNL031I DSNL031 MSGDSNL031I MSGDSNL031
  IFCID0191 IFCID191 IFCID 191 0191
  SQLCODE805N SQLCODE0805N SQLCODE805 -805
  SQL805N     SQL0805N     SQL805
  SQLCODE918N SQLCODE0918N SQLCODE918 -918
  SQL918N     SQL0918N     SQL918
  ABENDASP7
  Package list.
  568810301 ARIXELX
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• Closing informational APAR as CAN.
  

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