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

Description

Data Query

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.

Data Modification

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.

Procedure

  1. Do the following:
    Goal Action

    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.
  2. Click the Definition tab and choose the action that you want.

    Goal

    Action

    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.
  3. 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.

  4. 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.

Results

You can update the stored procedure query subject if the data source changes. See Updating query subjects.