IZ12946: MAXIMUM OPEN CURSORS EXCEEDED ISSUES

APAR status

• Closed as program error.

Error description

• Maximum open cursors exceeded issues
  
  The client Perstorp Oxo in Sweden are having problems with
  blanket
  orders and release orders made from the most common blanket
  orders. The
  problems can be divided into two issues but they have the same
  end result; a
  database exception showing that the maximum open cursors have
  been exceeded.
  The temporary fix for the client is to raise the setting in the
  database,however the maximum open cursors setting cannot be
  raised infinitely.
  
  
  Issue 1:
  When a release is approved Maximo calculates the total release
  base
  cost.
  While doing that the number of open cursors goes up for each
  release
  made of a blanket order until the maximum open cursors is
  reached in the
  database. If the maximum open cursors setting in the database is
  larger then the
  number of release orders created for the blanket order this
  error does not
  appear.
  
  Issue 2:
  When showing the relase POs for a selected blanket order there
  is an
  exception when the number of open cursors is reached.
  
  
  Details Issue 1
  When approving a release the POStatusHandler class in the
  approve
  method calculates the total release base cost by looping through
  all releases
  made from the blanket order. For each release the internal
  status is
  fetched which opens a cursor to the postatus table. As the
  cursor isn't
  immediately closed the number of open cursors goes up rapidly.
  
  This is the exception:
  
  Thu Sep 01 11:16:46 CEST 2005 java.sql.SQLException: ORA-01000:
  maximum open cursors exceeded
  
  Here are steps to reproduce the problem and some additional
  comments why Maximo breaks:
  
  1. Set OPEN_CURSORS in the Oracle Database to 300.
  2. Restart the database
  3. Start the Maximo server and log in
  4. Create a blanket PO
  5. Approve the blanket PO
  6. Create 300 release POs for this blanket. (Yes each release
  has its own number)
  7. Approve the 300 release POs
  7. Create release PO number 301
  8. When you try to approve release 301 Maximo will break
  9. Log in to Maximo again
  10. Retrieve the blanket PO
  11. Choose the Action "View Blanket PO" and Maximo will break
  
  The reason that Maximo breaks is because of the method used in
  the code to calculate the total amount released for the Blanket
  PO.
  A MBO set is created for all the PO releases for the Blanket PO
  then there is a loop that will fetch the status (postatus MBO)
  for each release PO to check if it's a valid staus. This loop is
  the culprit. For each postatus MBO opened a new cursor is
  opened. When the number of records in the set exceeds the number
  of available open cursors for the session the database generates
  an exception.
  
  If the developers instead selects the release POs for the
  blanket PO that have the right status then the loop is not
  necessary. Then use the sum method on the MBO set to get amount
  released for the blanket.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Maximo Users on 5.2                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: Maximum open cursors exceeded issues    *
  *                                                              *
  *                      The client Perstorp Oxo in Sweden are   *
  *                      having problems with blanket            *
  *                                                              *
  *                      orders and release orders made from the *
  *                      most common blanket orders. The         *
  *                                                              *
  *                      problems can be divided into two issues *
  *                      but they have the same end result; a    *
  *                                                              *
  *                      database exception showing that the     *
  *                      maximum open cursors have               *
  *                                                              *
  *                      been exceeded.                          *
  *                                                              *
  *                      The temporary fix for the client is to  *
  *                      raise the setting in the                *
  *                                                              *
  *                      database,however the maximum open       *
  *                      cursors setting cannot be               *
  *                                                              *
  *                      raised infinitely.                      *
  *                                                              *
  *                      Issue 1:                                *
  *                                                              *
  *                      When a release is approved Maximo       *
  *                      calculates the total release base cost. *
  *                                                              *
  *                      While doing that the number of open     *
  *                      cursors goes up for each release        *
  *                                                              *
  *                      made of a blanket order until the       *
  *                      maximum open cursors is reached in the  *
  *                                                              *
  *                      database. If the maximum open cursors   *
  *                      setting in the database is              *
  *                                                              *
  *                      larger then the number of release       *
  *                      orders created for the blanket order    *
  *                      this                                    *
  *                                                              *
  *                      error does not appear.                  *
  *                                                              *
  *                      Issue 2:                                *
  *                                                              *
  *                      When showing the relase POs for a       *
  *                      selected blanket order there            *
  *                                                              *
  *                      is an exception when the number of open *
  *                      cursors is reached.                     *
  *                                                              *
  *                      Details Issue 1                         *
  *                                                              *
  *                      When approving a release the            *
  *                      POStatusHandler class in the approve    *
  *                                                              *
  *                      method calculates the total release     *
  *                      base cost by looping through all        *
  *                      releases                                *
  *                                                              *
  *                      made from the blanket order. For each   *
  *                      release the internal                    *
  *                                                              *
  *                      status is fetched which opens a cursor  *
  *                      to the postatus table. As the           *
  *                                                              *
  *                      cursor isn't immediately closed the     *
  *                      number of open cursors goes up rapidly. *
  *                                                              *
  *                      This is the exception:                  *
  *                                                              *
  *                      Thu Sep 01 11:16:46 CEST 2005           *
  *                      java.sql.SQLException: ORA-01000:       *
  *                                                              *
  *                      maximum open cursors exceeded           *
  *                                                              *
  *                      Here are steps to reproduce the problem *
  *                      and some additional                     *
  *                                                              *
  *                      comments why Maximo breaks:             *
  *                                                              *
  *                      1. Set OPEN_CURSORS in the Oracle       *
  *                      Database to 300.                        *
  *                                                              *
  *                      2. Restart the database                 *
  *                                                              *
  *                      3. Start the Maximo server and log in   *
  *                                                              *
  *                      4. Create a blanket PO                  *
  *                                                              *
  *                      5. Approve the blanket PO               *
  *                                                              *
  *                      6. Create 300 release POs for this      *
  *                      blanket. (Yes each release has its own  *
  *                      number)                                 *
  *                                                              *
  *                      7. Approve the 300 release POs          *
  *                                                              *
  *                      7. Create release PO number 301         *
  *                                                              *
  *                      8. When you try to approve release 301  *
  *                      Maximo will break                       *
  *                                                              *
  *                      9. Log in to Maximo again               *
  *                                                              *
  *                      10. Retrieve the blanket PO             *
  *                                                              *
  *                      11. Choose the Action "View Blanket PO" *
  *                      and Maximo will break                   *
  *                                                              *
  *                      The reason that Maximo breaks is        *
  *                      because of the method used in           *
  *                                                              *
  *                      the code to calculate the total amount  *
  *                      released for the Blanket PO.            *
  *                                                              *
  *                      A MBO set is created for all the PO     *
  *                      releases for the Blanket PO             *
  *                                                              *
  *                      then there is a loop that will fetch    *
  *                      the status (postatus MBO)               *
  *                                                              *
  *                      for each release PO to check if it's a  *
  *                      valid staus. This loop is               *
  *                                                              *
  *                      the culprit. For each postatus MBO      *
  *                      opened a new cursor is                  *
  *                                                              *
  *                      opened. When the number of records in   *
  *                      the set exceeds the number              *
  *                                                              *
  *                      of available open cursors for the       *
  *                      session the database generates          *
  *                                                              *
  *                      an exception.                           *
  *                                                              *
  *                      If the developers instead selects the   *
  *                      release POs for the                     *
  *                                                              *
  *                      blanket PO that have the right status   *
  *                      then the loop is not                    *
  *                                                              *
  *                      necessary. Then use the sum method on   *
  *                      the MBO set to get amount               *
  *                                                              *
  *                      released for the blanket.               *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  ****************************************************************
  MAXIMUM OPEN CURSORS EXCEEDED ISSUES
  

Problem conclusion

• Fix available in 5.2 Patch
  

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:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  PREVENTIVE MAIN

• Fixed component ID

  5724R46PM

Applicable component levels

• R520 PSY

     UP