IV65958: TBSM DATABASE QUERIES THAT USE MAX, MIN, DISTINCT, CAUSES DB2 ERROR, SQL0243N

7.1.0-TIV-NCI-ZLINUX-FP0006
7.1.0-TIV-NCI-WINDOWS-FP0006
7.1.0-TIV-NCI-SOLARIS-FP0006
7.1.0-TIV-NCI-LINUX-FP0006
7.1.0-TIV-NCI-AIX-FP0006
6.1.1-TIV-BSM-FP0004-windows
6.1.1-TIV-BSM-FP0004-zlinux
6.1.1-TIV-BSM-FP0004-solaris
6.1.1-TIV-BSM-FP0004-linux
6.1.1-TIV-BSM-FP0004-aix
7.1.0-TIV-NCI-ZLINUX-FP0005
7.1.0-TIV-NCI-LINUX-FP0005
7.1.0-TIV-NCI-WINDOWS-FP0005
7.1.0-TIV-NCI-SOLARIS-FP0005
7.1.0-TIV-NCI-AIX-FP0005
6.1.1-TIV-BSM-FP0003-aix
6.1.1-TIV-BSM-FP0003-zlinux
6.1.1-TIV-BSM-FP0003-windows
6.1.1-TIV-BSM-FP0003-solaris
6.1.1-TIV-BSM-FP0003-linux
IBM Tivoli Business Service Manager V6.1.1 Fix Pack 3(6.1.1-TIV-BSM-FP0003)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 3(7.1.0-TIV-NCI-FP0003)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 5(7.1.0-TIV-NCI-FP0005)
IBM Tivoli Business Service Manager V6.1.1 Fix Pack 4(6.1.1-TIV-BSM-FP0004)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 6(7.1.0-TIV-NCI-FP0006)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 7(7.1.0-TIV-NCI-FP0007)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 8(7.1.0-TIV-NCI-FP0008)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 9(7.1.0-TIV-NCI-FP0009)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 10(7.1.0-TIV-NCI-FP0010)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 11(7.1.0-TIV-NCI-FP0011)
IBM Tivoli Business Service Manager V6.1.1 Fix Pack 5(6.1.1-TIV-BSM-FP0005)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 12 (7.1.0-TIV-NCI-FP0012)
IBM Tivoli Netcool/Impact V7.1.0 Fix Pack 13 (7.1.0-TIV-NCI-FP0013)

APAR status

• Closed as program error.

Error description

• Product: IBM Netcool Impact
  Release: 6.1.1 FP2
  Severity: 3
  
  PROBLEM DESCRIPTION: TBSM database queries that use max, min,
  distinct, causes DB2 error, SQL0243N Caused by: Use of MAX
  function in query where scrollable cursor in use
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Impact and TBSM users                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Errors like the following show up in the impact server logs. *
  *                                                              *
  * Message: SQL0243N  SENSITIVE cursor "SQL_CURSH200C2" cannot  *
  * be defined                                                   *
  * for the specified SELECT statement.  SQLSTATE=36001          *
  *                                                              *
  * A SENSITIVE cursor is being used when trying to to use       *
  * queries with max, distinct or if they are accessing database *
  * views, this is not possible with this cursor type.The        *
  * application does not suffer an loss of functionality as it   *
  * tries with an INSENSITIVE cursor once this error is          *
  * detected. However, the error may mislead customers and       *
  * support teams into thinking there is a more serious problem. *
  * Also there may be a increased usage of database resources.   *
  *                                                              *
  * This APAR only covers the queries Impact and TBSM use by     *
  * default. Customer queries used in data fetchers, policies or *
  * similar features that allow the customer to use custom SQL   *
  * are not covered by this APAR.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Recommend applying the following:                            *
  *                                                              *
  * The fix for this APAR is contained in the following          *
  * maintenance packages:                                        *
  * |Fix Pack | 6.1.1-TIV-BSM-FP0003                             *
  ****************************************************************
  Problem is due to the type of cursor the Impact uses to read
  data. For most queries the scrollable cursor type is used and
  works fine. However when functions like MAX, MIN, DISTINCT or
  the accessing of database view are used, errors will occur as
  the cursor type is sensitive. The application then tries with a
  non scrollable cursor so data is still retrieved, however there
  is an error in the logs and customers and support teams may find
  this error misleading.
  

Problem conclusion

• The fix for this APAR is contained in the following maintenance
  packages:
  |Fix Pack | 6.1.1-TIV-BSM-FP0003
  

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

• UNKNOWN
  

Fix information

• Fixed component name

  NETCOOL/IMPACT

• Fixed component ID

  5724O59IS

Applicable component levels

• R611 PSY

     UP