KB32324: Error message appears when user attempts to manipulate MicroStrategy Metadata: "ORA-“01000: maximum open cursors exceeded” if stored on an Oracle database or “No more available statements" on a DB2 database.

SYMPTOM:
 
The following error message appears when users attempt to manipulate a Strategy metadata stored on an Oracle database:
 

Error type: Odbc error. Odbc operation attempted: SQLExecute. ORA-01000: maximum open cursors exceeded ...

 
If the metadata database is stored on a DB2 database the error message is as following:
 

Error type: Odbc error. Odbc operation attempted: SQLDescribeParam. No more available statements. Please recreate your package with a larger dynamicSections value.

 
For Oracle, the entry in DSSErrors.log is similar to:
 

OdbcPreparedStatement::ExecQuery failed.

Error type: Odbc error. Odbc operation attempted: SQLExecute. ORA-01000: maximum open cursors exceeded Connection String: DSN=TS;UID=TS;ENABLENCHARSUPPORT=1;ENABLEDESCRIBEPARAM=1;HOST=TS;PORT=1521;SID=orcl;. SQL Statement: SELECT defn.OBJECT_ID, defn.DEFINITION, defn.DEFINITION_SEQ FROM DSSMDOBJDEFN defn INNER JOIN DSSMDOBJDEPN depn ON defn.OBJECT_ID=depn.DEPN_OBJID AND defn.PROJECT_ID=depn.DEPN_PRJID WHERE depn.OBJECT_ID=? AND depn.PROJECT_ID=? AND depn.DEPNOBJ_TYPE IN (?, ?, ?, ?, ?, ?, ?, ?) ORDER BY defn.OBJECT_ID ASC, defn.DEFINITION_SEQ ASC.

 
For DB2, it is similar to:

OdbcPreparedStatement::GetParameterDataTypes() failed.   

Error type: Odbc error. Odbc operation attempted: SQLDescribeParam. No more available statements. Please recreate your package with a larger dynamicSections value. For more information, consult DB2 document for error code SQL08292. Connection String: DSN=MD DSN;UID=MD901;IPADDRESS=ts;TCPPORT=50000;DATABASE=UDB;. SQL Statement: SELECT OBJECT_ID, PROJECT_ID, VERSION_ID FROM DSSMDOBJINFO WHERE OBJECT_ID IN (?, ?, ?, ?) AND PROJECT_ID IN (?, ?).

 
  
The manipulations mentioned above can include, but are not limited to: starting Strategy Intelligence Server, login to project, loading/creating/editing/saving/duplicating objects (such as different kinds of schema objects, application objects, users, database instances, projects), executing/exporting/subscribing reports/documents, importing MDX cubes or updating its structure, etc.
nbsp;
Users may encounter this error in Strategy Desktop, Strategy Web, Strategy Enterprise Manager Console, Strategy Command Manager, Strategy Object Manager, Strategy Integrity Manager, Strategy Project Builder, Strategy Project Mover, Strategy Office, Strategy Narrowcast Server, etc. 
 
CAUSE:
 
In Strategy, the frequently executed SQL statements are parameterized to take advantage of the feature called Prepared Statement in MS SQL Server community or Bind Variable in Oracle community. This brought optimal performance.
 
For Oracle databases, the ORA-01000 error occurs when, "A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user." Please refer to following page for more information: http://ora-01000.ora-code.com/
 
Oracle database keeps one cursor open per prepared statement, and it is not released until that prepared statement is cleared from the cache. The ORA-01000 error occurs each time the cached prepared statement count exceeds the OPEN_CURSORS setting. So users should make sure the database is properly configured to support all potentially-cached prepared statements.
 
The prepared statement cache pool size is 1000, so it's strongly recommended that the customer's database administrator increases the value of the OPEN_CURSORS parameter to 1000 to meet the demands of cursor expense. The default value of the OPEN_CURSORS parameter is 50.
 
For DB2 database, the error message indicates too many SQL statements have been prepared that it exceeds the number of available dynamic sections. A common scenario of this heavy load of statements is saving Warehouse catalogs or MDX cube catalog which has large amount of objects. A dynamic section is an executable object that contains the logic needed to satisfy a dynamic SQL request. A dynamic SQL statement will occupy one section within a Call Level Interface (CLI) package. The error occurs each time the cached prepared statement count exceeds the dynamic sections setting. Please refer to the following page for more information: http://www.ibm.com/developerworks/data/library/techarticle/dm-0606chun/index.html
 
The total number of available dynamic sections is controlled by the Dynamic Sections setting of the ODBC driver, and this Dynamic Sections value is spread across all database connections. Even though the prepared statement cache pool size is limited to 1000 per database connection, since database connection count is not limited, there is no single value of Dynamic Sections that guarantees the demand of dynamic sections expense will always be met. Thus the value needs to be tuned when the error happens. The default value of Dynamic Sections in Strategy DB2 ODBC Driver for DB2 is 200. 
 
ACTION:
 
For Oracle databases: 
Contact the database administrator to check the value of OPEN_CURSORS parameter and increase it to 1000. The following steps will be able to check the value and increase it:
1.     The OPEN_CURSORS parameter in INIT.ORA/ SPFILE determines the maximum number of cursors allowed per user. To check the parameter specified, the database administrator can execute the following SQL: 
Sql
SELECT * FROM v$parameter
WHERE name = 'open_cursors'
 
 Or execute the following command in SQL*Plus: 
Sql
SQL> show parameter cursors
 
 
2.     If the OPEN_CURSORS parameter is set to a value less than 1000, the database administrator should change it using one of the following ways: 
a.     Shutdown the database, edit PFILE (INIT.ORA) to set OPEN_CURSOR as 1000, and restart the database.
b.     Execute the following command in SQL*Plus (please note this way is only available for Oracle 9i and above, and ALTER SYSTEM system privilege is required):
Sql
SQL> ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;
 
For DB2 databases:
Contact the database administrator to use the tools from the ODBC driver to change the value of Dynamic Sections property on the DB2 database, and  then rebind the DB2 packages. 
 
The following steps will be able to change the value in Windows platforms:

1. Open the Microsoft ODBC Administrator. Locate the DSN for the metadata on DB2 database and click "Configure".
2. In the “ODBC DB2 Wire Protocol Driver Setup”, go to the “Modify Bindings” tab. Change the value of “Dynamic Sections” to 999. Set the value of the “Package Collection” as the DB2 database Username.
3. Click on “Modify Bindings”. In the “Login” form, set the DB2 database username and the password. Click Login. The Dynamic Sections property has been updated on the DB2 server