CALL

To run a stored procedure from within a QMF session, you must issue a CALL statement from the SQL Query panel. The database to which the CALL statement is directed must support the ability to call a stored procedure.

After you enter a CALL statement, a RUN command is issued to run the stored procedure.
Read syntax diagramSkip visual syntax diagramCALLprocedure_name1(2&variable3CONSTANT4NULL5)
Notes:
  • 1 This identifies the stored procedure to call.
  • 2 Parameter values can be in, out, or inout parameters.
  • 3 This identifies a QMF substitution variable to be used as input or output to the stored procedure.
  • 4 This identifies a CONSTANT to be used as input or output to the stored procedure.
  • 5 The parameter is a NULL value. The corresponding parameter of a stored procedure must be defined as IN, and the description of the stored procedure must allow for NULL parameters.

The CALL statement must be used alone in a SQL query. It cannot be combined with other statements.

How parameters are used

QMF supports up to 63 parameters on the CALL statement. Parameters on the CALL statement are used in the following way:

  • Input parameters (IN)

    Input values passed to the stored procedure.

  • Output parameters (OUT)

    The names of user-defined QMF substitution variables receive the values of the output variables that are returned from the stored procedure. Before you use the CALL statement, these names must be set by the user with the QMF SET GLOBAL command.

    You must use a QMF global variable to specify output parameters for a stored procedure if you want to view the output. The output parameters can then be displayed using the SHOW GLOBALS command. A maximum of 10 QMF global variables can be entered from the SQL Query panel. The maximum size of a QMF substitution variable is 32 KB.

    The CALL statement fails when OUT parameters defined for the stored procedure are not initialized correctly. QMF global variables whose values are copied into output parameters for the stored procedure have special initialization requirements:
    • An output parameter with a numeric data type must be initialized to 0.
    • An output parameter with a data type of CHAR must be initialized to blank or NULL.
  • Input/output parameters (INOUT)

    Can be used as input or output, and can have the behavior of either input or output parameters.

Guidelines for using the CALL statement

  • CALL statements in QMF can be directed only to Db2® for z/OS® databases.
  • QMF does not process three-part names that are referenced in CALL statements. Only stored procedures at the current location (the location to which QMF is connected) are run. If a three-part name is entered, QMF accepts it, but an error message is issued if the location entered does not match the current location.
  • If a schema name is not provided for the stored procedure name, QMF uses the value of the CURRENT SQLID register as the schema name.
  • Authorization checking is done by the database. The current SQLID must be authorized to run the stored procedure that is specified in the CALL statement.
  • Parameters that are defined with data types of DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE must have their values enclosed in single quotation marks. QMF handles these data types as character strings.
  • Data of the following types cannot be passed in a parameter on the CALL statement: BINARY, VARBINARY, VARGRAPHIC, GRAPHIC, LONG VARGRAPHIC, CLOB, BLOB, DBCLOB, ROWID, and XML. DECFLOAT data can be passed if the processor on which QMF is running supports decimal floating-point instructions.
  • QMF supports the return of the first 63 result sets when a stored procedure that returns result sets is run. Select one by setting the global variable DSQEC_SP_RS_NUM.
  • The maximum data size of a LOB column that is to be returned from a stored procedure is determined by the DSQEC_LOB_COLMAX global variable.

How to write a CALL statement with long identifiers

A single SQL query line is limited to 79 bytes on the QMF SQL Query panel. An identifier that spans more than one line in a CALL statement that is entered on the SQL Query panel must be a delimited identifier. Here are some examples that show how to code long CALL statements:
  • A long parameter as a delimited identifier that spans more than one line:
    CALL USERID.PROC (’THIS IS THE FIRST PARM’, 4, 1954, "THIS IS ANOTHER
 PARM THAT WILL SPAN TWO LINES ON THIS PANEL", 14, 99)
  • A long stored procedure name as a delimited identifier that spans more than one line:
    CALL USERID.’THIS_IS_A_REALLY_LONG_STORED_PROCEDURE_NAME_THAT_EXCEEDS_
MORE_THAN_ONE_LINE_ON_THE_QUERY_PANEL' (’PARM1’, ’ ’, 0, ’PARM4’)
  • Break the lines between identifiers:
    CALL USERID.PROC ('THIS IS THE FIRST PARM’, 4, 1964,
’THIS IS ANOTHER PARM THAT WILL NOW FIT ON THIS LINE’,
14, 99)

CALL USERID.PROC (’THIS IS THE FIRST PARM’, 333333,
123456789012345678901234567890, 200305,
’THIS IS THE LAST PARM’)
  • Use a delimiter (in this case, double quotation marks) when the text spans more than two lines:
    CALL USERID.PROC ("THIS IS THE FIRST PARM AND IT WILL NOT ONLY EXTEND
PAST THE FIRST LINE, IT WILL ALSO EXTEND BEYOND THE SECOND LINE BECAUSE
THERE ARE TOO MANY WORDS TO FIT IN TWO LINES ALONE").

Specifying a QMF form for data returned in a result set

If you do not specify a form on the RUN QUERY command that issues the CALL statement, a default form is created based on the returned result set. If the stored procedure returns more than one result set, you can display one of the result sets by specifying its number in the global variable DSQEC_SP_RS_NUM; the rest of the result sets are ignored.

If the RUN QUERY command that calls the stored procedure includes the FORM parameter, ensure that the specified form matches the data returned in the result set, or QMF issues an error message. In this case, you can load the proper form using the DISPLAY FORM command, or modify or reset the current form to match the returned data.