Creating or modifying stored procedure query subjects
After you import or create a stored procedure query subject, you can modify it. To avoid inconsistencies, the modified query subject should return the same result set structure as the original stored procedure.
IBM® Cognos® Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.
There are different types of stored procedures:
Type of Stored Procedure
Issues a read-only transaction
If you have a stored procedure with its type set to Data Query, the stored procedure issues a read-only transaction. When you run the stored procedure in Event Studio, an error message says that the stored procedure wants to update the database. The reason for the error is that the stored procedure contains a passive transaction that is supported by the underlying database. The solution is to click OK so that the stored procedure updates the database. No other action is required.
Writes a record to the data source. Use this type when you want to use the stored procedure in Event Studio.
If you want Event Studio users to be able to select a parameter in a task, you must put quotation marks around the parameter.
Warning: Testing a data modification stored procedure in the Edit Definition dialog box results in data being written to the data source. You cannot roll back transactions to the data source in Framework Manager. If undesired data is written to the data source as a result of testing the stored procedure, a rollback can be done by the database administrator if the data source is configured to support it. To test the stored procedure without data being written to the data source, click Test from the Tools menu.
You can also create data source query subjects, which directly reference data in a single data source Data source query subjects, and model query subjects, which are based on metadata that exists in your model Model query subjects.
- Do the following:
Create a stored procedure query subject
Select the namespace folder and, from the Actions menu, click Create, Query Subject.
In the Name box, type a name for the new query subject.
Click Stored Procedure, and click OK.
Complete all the steps in the New Query Subject wizard.
Modify a stored procedure query subject
Select the stored procedure query subject that you want to modify.
From the Actions menu, click Edit Definition.
- Click the Definition tab and choose
the action that you want.
Use a different stored procedure
In the Stored Procedure Name box, type the name of the stored procedure.
Change the type of the stored procedure
From the Type box, select Data Query or Data Modification.
Change which data source the stored procedure is in
Click the ellipsis (...) button next to the Data Source box.
When you import a stored procedure, a new data source is created. You can point to the original data source and delete the new one.
Edit an argument
Click the argument and click the ellipsis (...) button.
The Syntax box in the Query Subject Definition dialog box shows the correct syntax to use.
Generate the projected query items
Click the Test tab. See Testing query subjects or query sets.
- Click OK.
Framework Manager runs the stored procedure and, if the query subject returns a result set, validates the query subject.
If the stored procedure does not return a result set, the query subject becomes an invalid query subject if saved in the model. If the invalid query subject is included in the published package, the invalid query subject cannot be used in a report.
- Ensure that the Usage and Regular
Aggregate properties are set correctly for each newly
created query item.
For example, a query item may be set as a fact when it is an identifier.