Using Command Blocks
Use the connection wizard to specify database commands that run when an Oracle connection or session is opened or closed, or when a DB2, Microsoft SQL Server, or Teradata session is opened.
For example, use a database command to set up proxy connections, see Example - Using Command Blocks for Proxy Connections or for virtual private databases for an Oracle database, see Example - Using Command Blocks for Virtual Private Databases for Oracle.
You can use commands to run native SQL commands when you open a session, such as running a stored procedure.
When to Use Command Blocks
Command blocks execute as IBM® Cognos® software opens and closes database connections or a session on a connection. You can use the IBM Cognos session variables and macro functions to parameterize the commands.
As an administrator, you must know when a command block executes 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 re-uses a database connection. Use open session command blocks if the application context of a database connection changes frequently.
If a database connection times out during testing, it may not indicate that another open database connection is needed. Consider the following questions when deciding how frequently to use open database command blocks:
- 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 have to create new connections?
Example of interaction between command blocks
Database commands are included in command blocks which are formatted using XML.
The following diagram shows an example of interaction between four command blocks. The interaction starts when query for user one arrives. It is assumed that a connection to the database does not exist.
Considerations
- You cannot test the command blocks for connections using the Test the connection link on the connection string page. If you have Software Development Kit installed, you can ensure that your XML code validates against the schema file named 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 DB2 commands.
- The commands in the blocks are vendor-specific and must be included in an <sqlCommand> tag.
- Depending on your settings, the query engine may open new connections more rapidly than may occur in a normally loaded application. This may create the false impression that information is being reset for each request that is executed.