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:
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:
If there are no IN parameters or INOUT parameters in IN mode, the segment describes a single dummy field.
If there are no OUT parameters or INOUT parameters in OUT mode, the segment is omitted.
If there is no answer set, the segment is omitted.
In IBM i, 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', $
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, $
The following list describes the synonym creation parameters for for a stored procedure.
Select Stored Procedures.
Selecting this option adds the Owner/Schema and Object Name parameters to the screen.
To avoid the return of an extremely large and potentially unmanageable list, always supply a value for Library or Object Name:
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.
The name of the synonym, which defaults to the stored procedure name.
Select an application directory. The default value is baseapp.
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.
To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box.
Note: The connected user must have operating system write privileges in order to recreate a synonym.
To change the data type mappings from their default settings, select this check box. The customizable mappings are displayed.
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:
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:
Is the synonym of the stored procedure you want to execute.
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.
Is an IF or WHERE keyword. Use this to pass a value to an IN parameter or an INOUT parameter in IN mode.
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.
Is the value you are passing to a parameter.
SQL SELECT [parameter [,parameter] ... | *] FROM synonym [WHERE in-parameter = value] . . . END
where:
Is the synonym of the stored procedure that you want to execute.
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.
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.
Is the name of an IN parameter, or INOUT parameter in IN mode, to which you want to pass a value.
Is the value you are passing to a parameter.
iWay Software |