PM94201: CIUAFFRD JOB FAILS CIU6008E SQL ERROR: CIUAFFR2 FUNCTION SELECT SQL RETURN CODE-811 SECTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Your CIUAFFRD affinities reporter job fails with message:
  
  CIU6008E  SQL error: CIUAFFR2 FUNCTION   SELECT    SQL RETURN
  CODE -811 SECTION C12-START
  
  The SELECT statement in the C12-START in module CIUAFFR2
  results in the -811 SQLCODE and duplicate rows in
  CIU_REGION_INFO.
  
  EXEC SQL
       SELECT   AFF_COLL_LASTSAVE
       INTO     :WS-DB2-TIMESTAMP
       FROM     CIU_REGION_INFO
       WHERE    APPLID  = :WS-APPLID
  END-EXEC
  
  The SELECT statement needs to specify MAX so only the latest
  AFF_COLL_LASTSAVE is returned.
  
  EXEC SQL
       SELECT   MAX (AFF_COLL_LASTSAVE)
       INTO     :WS-DB2-TIMESTAMP
       FROM     CIU_REGION_INFO
       WHERE    APPLID  = :WS-APPLID
  END-EXEC
  
  
  Additional Symptom(s) Search Keyword(s): KIXREVWJB
  collection id C12-READ-REGION-INFO
  
  SQL CODE -904 REASON CODE 00E70082 - ran out of cursors
  

Local fix

• ANAUTO1
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All CICS IA V3.2 Users                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Your CIUAFFRD affinities reporter job   *
  *                      fails with message                      *
  *                                                              *
  *                      CIU6008E  SQL error: CIUAFFR2 FUNCTION  *
  *                      SELECT SQLRETURN CODE -811 SECTION      *
  *                                                              *
  *                      Also, you can receive                   *
  *                      SQLCODE -904 REASON CODE 00E70082       *
  *                      This indicates you have run out of      *
  *                      cursors.                                *
  *                      This will occur if you have a large     *
  *                      amounts of Affinities in your table     *
  ****************************************************************
  * RECOMMENDATION: Apply the PTF and perform the HOLD ACTION    *
  *                 items.                                       *
  ****************************************************************
  The CIUAFFR2 program is used to report Affinities by region.
  In the report it displays the date and time of the last update
  to the Affinity DB2 data. This data is stored in the control
  record for the region. The program obtains it form the DB2
  table CIU_REGION_INFO and expects one row for each table.
  In IA V3.2 with the addition of the column collection_id it is
  possible to have more than one row for a region in this table.
  
  If large amounts of Affinity data was being queried then DB2
  would run out of cursors and SQLCODE -904 is issued
  

Problem conclusion

• The SQL function MAX is added to the SQL query to obtain the
  region record with the latest Affinity Update.
  
  SQL CLOSE CURSOR command is added into the loop in program
  CIUAFFR2
  
  CALL STORED PROC
  ASSOCIATE CURSOR
  FETCH
  CLOSE CURSOR
  

Temporary fix

Comments

APAR Information

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

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

  PM94573 UK96544

Modules/Macros

•    CIUAFFR2
  

Fix information

• Fixed component name

  CICS INTER ANAL

• Fixed component ID

  5655U8600

Applicable component levels

• R320 PSY UK96544

     UP13/08/14 P F308

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.