Table Procedure Parameters

The parameters associated with a table procedure are treated like columns of a table. You can specify them within the column list of a SELECT or INSERT statement, the SET clause of an UPDATE statement, the ORDER BY clause of a SELECT statement or the search criteria of a WHERE clause. Additionally, you can specify parameter values within the table procedure reference itself.
idmscu
The parameters associated with a table procedure are treated like columns of a table. You can specify them within the column list of a SELECT or INSERT statement, the SET clause of an UPDATE statement, the ORDER BY clause of a SELECT statement or the search criteria of a WHERE clause. Additionally, you can specify parameter values within the table procedure reference itself.
Column List, SET and ORDER BY References
Parameters referenced in
Specify
Column list of a SELECT statement
The columns that are returned to the invoking application
Column list of an INSERT statement
The columns having values that are supplied in the subsequent VALUES clause or query-specification
SET clause of an UPDATE statement
Columns which are assigned new values during the update operation
ORDER BY clause of a SELECT statement
The order the result rows of the procedure are returned to the requesting application
WHERE Clause References
WHERE clause references to parameters are used to filter the output of the table procedure. Each time a table procedure returns a set of output values, they are evaluated against the selection criteria specified in the WHERE clause and non-conforming "rows" are ignored.
The WHERE clause parameter references is used to pass input values to the table procedure. If the set of column values provided on a particular call to the table procedure matches the columns defined in a KEY on the same table procedure, the ESTIMATED ROWS and ESTIMATED I/OS specified for that KEY are used by the optimizer when the table procedure is joined with other tables or views. If you specify selection criteria in the form of an "=" comparison (that is,
parameter
=
value
),
value
is passed to the table procedure. Other types of selection criteria such as IN predicates or comparison predicates with > or < operators have no effect on the value of the parameters passed to the table procedure.
For more information about defining keys, see CREATE KEY.
Specifically, a reference to a parameter in a WHERE clause results in an input value being passed to the table procedure only if:
  • It appears within an equality test
  • The equality test is not combined with other predicates in the WHERE clause through the use of the OR operator
  • The equality test is not preceded by the NOT operator
WHERE Clause Parameter References
The following examples illustrate how a parameter reference in a WHERE clause affects the value passed to the table procedure:
     WHERE clause               Parameter value                                  P1       P2        P1 = 1                     1     -null-        P1 < 1                   -null-  -null-        P1 = C1                    C1    -null-        P1 = 2 AND P2 = 3          2       3        P1 = 2 AND P2 > 3          2     -null-        P1 = 2 OR  P2 = 3        -null-  -null-        P1 IN (2, 3, 8)          -null-  -null-
Parameters in Table Procedure References
You can also specify input parameter values within the table procedure reference itself. You can specify them on any reference to a table procedure except within an INSERT statement.
You specify parameter values supplied on a table procedure reference either positionally or as keyword/value pairs. You can combine them with WHERE clause references to form the set of values that are passed to the table procedure.
Examples of Specifying Parameter Values
The following example shows all the ways you can specify input parameter values.
   SELECT * FROM EMP.ORG (MGR_ID = 7, EMP_ID = 127)    SELECT * FROM EMP.ORG (CAST(NULL AS NUM(4,0)),                           CAST(NULL AS SMALLINT),                           7,                           CAST(NULL AS CHAR(25)),                           127)    SELECT * FROM EMP.ORG (MGR_ID = 7) WHERE EMP_ID = 127
Difference Between Table Procedure Reference and WHERE Clause
One difference exists between parameter values specified through a WHERE clause and those specified within the table procedure reference. Parameter values specified within the table procedure reference are not used to filter the output from the table procedure as is the case for those specified within the WHERE clause. Parameter values specified within the table procedure reference affect only the input to the table procedure and not the output from the table procedure. Therefore, the above three select statements are equivalent only if the table procedure enforces the conditions specified through the table procedure reference.
The sample table procedure in Sample COBOL Table Procedure does not enforce any criteria other than those that it uses to navigate the database.
Statistics and Optimization
Ideally, a table procedure should be written such that when certain sets of column values are provided (either through a WHERE clause or a procedure reference), the most efficient path can be used to access the data or join the table procedure to another data source.
If the set of column values provided on a particular call to the table procedure matches the columns defined in a KEY on the same table procedure, the ESTIMATED ROWS and ESTIMATED I/Os defined for that KEY are used during optimization; otherwise, if the ESTIMATED ROWS and ESTIMATED I/Os are defined for the table procedure, they are used. If the ESTIMATED ROWS and ESTIMATED I/Os are not specified, the optimizer defaults to 1000 and 100 respectively.
Normally, these statistics are used when the table procedure is the object a simple select statement. However, the optimizer also uses them internally when the table procedure is joined with other tables or views. If the nature of the join is such that the values for columns (defined as a keys) are passed to the table procedure, the statistics from the appropriate key are used when choosing an access plan.