Command blocks

Connection command blocks are intended to change the session state on a connection that is opened on a data source. The statements that can be used in the command blocks depend on the statements supported by database vendors and user's permissions for those statements. Statements in command blocks can be parameterized by using IBM® Cognos® session variables and macro functions.

Command blocks are executed as IBM Cognos software opens and closes database connections or sessions on connections. You can use command blocks to run native SQL commands, for example, to run a stored procedure when a session is opened.

The following types of command blocks are available:

  • Open connection commands
  • Open session commands
  • Close session commands
  • Close connection commands

As an administrator, you must know when a command block is executed for a database connection. It is often best to define the database statements in an open session command block. Open database connections execute less frequently because IBM Cognos pools and reuses database connections. Use open session command blocks if the application context of a database connection changes frequently.

Command blocks should not include Cognos session variables or macros that change values frequently. These types of session variables or macros increase the command block execution frequency and number of data source caches, and reduce the result set cache reuse.

When creating your command blocks, consider the following database connection settings:

  • What are the database connection pool settings specified for the report servers in the CQEConfig.xml file?
  • Does the database have aggressive idle connection timeout settings?
  • Does the query engine have aggressive idle connection timeout settings?
  • Is the period between requests longer than the timeout settings?
  • Are there any requests routed to different report servers that must create new connections?

The following diagram shows an example of interaction between the four types of command blocks. The interaction starts when a query for user one arrives. It is assumed that a connection to the database does not exist.

Figure 1. Example of interaction between command blocks
Diagram showing an example of the interaction between the four available command blocks, starting when query for user one arrives

Macro functions

The macro functions available in IBM Cognos software can provide information in a command block about users and reporting application objects, such as packages, reports, or queries. All macro functions can return values when referenced from a command block, which allows for application context to be passed to the database from a command block. Macro functions that reference parameter maps in a model can also be used.

Considerations

  • You cannot test the command blocks for connections that use the Test the connection link on the connection properties page. If Software Development Kit is installed, you can ensure that your XML code validates against the schema file c10_location/webapps/p2pd/WEB-INF/classes/ DataSource.xsd.
  • The command structure is the same for all data sources. However, the specific database commands can vary depending on which database you are using. In this section, the examples use Oracle and IBM Db2® commands.
  • The commands in the blocks are vendor-specific and must be enclosed in the <sqlCommand> tag.
  • Depending on your settings, the query engine might open new connections more rapidly than in a normally loaded application, which might create a false impression that information is reset for each request that is executed. To control this behavior, consider using the (DQM) Cache is sensitive to connection command blocks governor. For more information, see the topic about Framework Manager governors for the dynamic query mode in the IBM Cognos Framework Manager User Guide.
  • The open-session command blocks are evaluated more frequently with the dynamic query mode than with the compatible query mode.