Reporting Against a DB2 Stored Procedure

In this section:

You can use a reporting tool, such as a SELECT statement or TABLE command, to execute DB2 stored procedures and report against the output parameters and answer set of a procedure. Among the benefits of this method of executing a stored procedure are:

To report against a stored procedure:

  1. Generate a synonym for the stored procedure answer set, as described in Generating a Synonym for a Stored Procedure.
  2. Create a report procedure, as described in Creating a Report Against a Stored Procedure.
  3. Run the report. This executes the stored procedure and reports against any output parameters (OUT and INOUT in OUT mode), and any answer set fields, specified in the report.
Top of page
x
Generating a Synonym for a Stored Procedure

Reference:

A synonym describes the stored procedure parameters and answer set.

An answer set structure may vary depending on the input parameter values that are provided when the procedure is executed. Therefore, you need to generate a separate synonym for each set of input parameter values that will be provided when the procedure is executed at run time. For example, if users may execute the stored procedure using three different sets of input parameter values, you need to generate three synonyms, one for each set of values. (Unless noted otherwise, "input parameters" refers to IN parameters and to INOUT parameters in IN mode.)

There is an exception: if you know the internal logic of the procedure, and are certain which range of input parameter values will generate each answer set structure returned by the procedure, you can create one synonym for each answer set structure, and for each synonym simply provide a representative set of the input parameter values necessary to return that answer set structure.

A synonym includes the following segments:

In i5/OS, the metadata of a stored procedure that has an answerset will include column descriptions. These will appear as title fields in the synonym, as shown in the following example:

FILENAME=TEST_DESCRIPTIONS, SUFFIX=DB2 , $   
  SEGMENT=INPUT, SEGTYPE=S0, $   
    FIELDNAME=, ALIAS=DUMMY, USAGE=A1, ACTUAL=A1, MISSING=ON, $   
  SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $   
    FIELDNAME=STORECODE, ALIAS=STORECODE, USAGE=A6, ACTUAL=A6,   
      TITLE='Store,Code', $   
    FIELDNAME=STORENAME, ALIAS=STORENAME, USAGE=A30, ACTUAL=A30,   
      MISSING=ON, TITLE='Store,Name', $   
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A15, ACTUAL=A15,   
      MISSING=ON, TITLE='Country', $   
    FIELDNAME=REGION, ALIAS=REGION, USAGE=A25, ACTUAL=A25,   
      MISSING=ON, TITLE='Region', $
Example: Synonym for DB2 Stored Procedure CustOrders

The following synonym describes a stored procedure, DB2SPR04 SP, with IN/OUT OUTPUT parameters.

FILENAME=SDB2SPR04, SUFFIX=DB2     , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=PARM1, ALIAS=P0001, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM2, ALIAS=P0002, USAGE=I6, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM5, ALIAS=P0005, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM6, ALIAS=P0006, USAGE=HHIS, ACTUAL=HHIS,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM7, ALIAS=P0007, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM11, ALIAS=P0011, USAGE=P17.5, ACTUAL=P8,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM13, ALIAS=P0013, USAGE=P20, ACTUAL=P10,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM15, ALIAS=P0015, USAGE=F9.2, ACTUAL=F4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=PARM16, ALIAS=P0016, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
  SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=RETURN_CODE, ALIAS=P0000, USAGE=I11, ACTUAL=I4,
      MISSING=ON, TITLE='Return Code', $
    FIELDNAME=PARM1, ALIAS=P0001, USAGE=I11, ACTUAL=I4, MISSING=ON, $
    FIELDNAME=PARM2, ALIAS=P0002, USAGE=I6, ACTUAL=I4, MISSING=ON, $
    FIELDNAME=PARM3, ALIAS=P0003, USAGE=I11, ACTUAL=I4, MISSING=ON, $
    FIELDNAME=PARM4, ALIAS=P0004, USAGE=I6, ACTUAL=I4, MISSING=ON, $
    FIELDNAME=PARM5, ALIAS=P0005, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $
    FIELDNAME=PARM6, ALIAS=P0006, USAGE=HHIS, ACTUAL=HHIS,
      MISSING=ON, $
    FIELDNAME=PARM7, ALIAS=P0007, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=PARM8, ALIAS=P0008, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $
    FIELDNAME=PARM9, ALIAS=P0009, USAGE=HHIS, ACTUAL=HHIS, MISSING=ON, $
    FIELDNAME=PARM10, ALIAS=P0010, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=PARM11, ALIAS=P0011, USAGE=P17.5, ACTUAL=P8, MISSING=ON, $
    FIELDNAME=PARM12, ALIAS=P0012, USAGE=P17.5, ACTUAL=P8, MISSING=ON, $
    FIELDNAME=PARM13, ALIAS=P0013, USAGE=P20, ACTUAL=P10, MISSING=ON, $
    FIELDNAME=PARM14, ALIAS=P0014, USAGE=P20, ACTUAL=P10, MISSING=ON, $
    FIELDNAME=PARM15, ALIAS=P0015, USAGE=F9.2, ACTUAL=F4, MISSING=ON, $
    FIELDNAME=PARM16, ALIAS=P0016, USAGE=D20.2, ACTUAL=D8, MISSING=ON, $
    FIELDNAME=PARM17, ALIAS=P0017, USAGE=F9.2, ACTUAL=F4, MISSING=ON, $
    FIELDNAME=PARM18, ALIAS=P0018, USAGE=D20.2, ACTUAL=D8, MISSING=ON, $
x
Reference: Synonym Creation Parameters for Stored Procedures

This chart describes the parameters that are available when you create a synonym for a stored procedure.

Parameter/Task

Description

xRestrict Object Type to

Select Stored Procedures.

xFilter by Owner/Schema and Object name

(all platforms except i5/OS)

Selecting this option adds the Owner/Schema and Object Name parameters to the screen.

Owner/Schema. Type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select tables or views whose owner/schema begin with the letters ABC; %ABC to select tables or views whose owner/schema end with the letters ABC; %ABC% to select tables or views whose owner/schema contain the letters ABC at the beginning, middle, or end.

Object name. Type a string for filtering the procedure names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all procedures whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

xLibrary

Object Name (i5/OS)

To avoid the return of an extremely large and potentially unmanageable list, always supply a value for Library or Object Name:

  • Library. Type a string for filtering the Library (or DB2 Collection), inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select tables or views whose owner IDs begin with the letters ABC; %ABC to select tables or views whose owner IDs end with the letters ABC; %ABC% to select tables or views whose owner IDs contain the letters ABC at the beginning, middle, or end.
  • Object name. Type a string for filtering the table, view, or object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all tables, views, or objects whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

Select

Select a procedure. You may only select one procedure at a time since each procedure will require unique input in the Values box on the next synonym creation pane.

Synonym Name

The name of the synonym, which defaults to the stored procedure name.

Select Application

Select an application directory. The default value is baseapp. The synonym will be stored in the baseapp application directory.

xPrefix/Suffix

If you have stored procedures with identical names, assign a prefix or a suffix to distinguish their corresponding synonyms. Note that the resulting synonym name cannot exceed 64 characters.

If all procedures have unique names, leave the prefix and suffix fields blank.

Overwrite Existing Synonyms

To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box.

xCustomize data type mappings

To change the data type mappings from their default settings, select this check box. The customizable mappings are displayed. For information about them, see Data Type Support .

Values

Select the check box for every parameter displayed for the specified procedure.

Note the following before you enter parameter values: if the procedure you selected has input parameters (IN parameters and/or INOUT parameters in IN mode), you will be prompted to enter values for them. However, the need for an explicit Value entry depends on the logic of the procedure and the data structures it produces. Therefore, while you must check the parameter box, you may not need to enter a value. Follow these guidelines:

  • Explicit input values (and separate synonyms) are required when input parameter values cause answer sets with different data structures, which vary depending on the input parameters provided.
  • Explicit input values are not required when you know the procedure's internal logic and are certain that it always produces the same data structure. In this situation, only one synonym needs to be created and you can leave the Value input blank for synonym creation purposes.

If a Value is required, enter it without quotes. Any date, date-time, and timestamp parameters must have values entered in an ISO format. Specify the same input parameters that will be provided when the procedure is executed at run time if it is a procedure that requires explicit values.
x
Creating a Report Against a Stored Procedure

How to:

You can report against a stored procedure's answer set using the same facilities you use to report against a database table:

When joining from or to a stored procedure answer set, you can:
x
Syntax: How to Report Against a Stored Procedure Using the TABLE Command

To execute a stored procedure using the TABLE command, use the following syntax

TABLE FILE synonym 
PRINT [parameter [parameter] ... | *]
[IF in-parameter EQ value]
  .
  .
  .
END

where:

synonym

Is the synonym of the stored procedure you want to execute.

parameter

Is the name of a parameter whose values you want to display in the report. You can specify input parameters, output parameters, or input and output parameters.

If the stored procedure does not require parameters, specify an asterisk (*). This displays a dummy segment, created when the synonym is generated, to satisfy the structure of the SELECT statement.

*

Indicates that you want to display all indicated parameters, or that there are no required parameters.

IF

Is an IF or WHERE keyword. Use this to pass a value to an IN parameter or an INOUT parameter in IN mode.

in-parameter

Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.

Note: The length of in-parameters cannot exceed 1000 characters if the adapter is configured for Unicode support.

value

Is the value you are passing to a parameter.
x
Syntax: How to Report Against a Stored Procedure Using SELECT
SQL
SELECT [parameter [,parameter] ... | *] FROM synonym 
[WHERE in-parameter = value]
  .
  .
  .
END

where:

synonym

Is the synonym of the stored procedure that you want to execute.

parameter

Is the name of a parameter whose values you want to display in the report. You can specify input parameters, output parameters, or input and output parameters.

If the stored procedure does not require parameters, enter an asterisk (*) in the syntax. This displays a dummy segment, created during synonym generation, to satisfy the structure of the SELECT statement.

*

Indicates that you want to display all indicated parameters, or that there are no required parameters.

WHERE

Is used to pass a value to an IN parameter or an INOUT parameter in IN mode. You must specify the value of each parameter on a separate line.

in-parameter

Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.

value

Is the value you are passing to a parameter.

iWay Software