Invoking stored procedure for Db2

Edit online

You can invoke stored procedures by using a CALL statement in a registered subsystem. When invoking a stored procedure, you need to provide the appropriate values for the variables in the CALL statement.

Before you begin

Note: A combination of CALL statement and SQL query cannot work together.

Procedure

To invoke a stored procedure in a subsystem, complete the following steps:
  1. From the navigation menu, click Run > SQL.
    The SQL page opens.
  2. Select a subsystem from the Connection drop-down list.
  3. Perform any of the following actions:
    • To run a new CALL statement, on the New statements tab, type or paste the CALL statement for the stored procedure into the editor.
      Note: The editor dynamically validates the query for invalid syntax and displays the icon if errors are found.
      Important: In a CALL statement, for example, CALL CSIZPB.ADB2RE(?,?,?,?,?,?), the count of question mark (?) must match the number of parameters defined in the stored procedure.
      For example, in the below CALL statements,
      • A question mark (?) is treated as a variable. 
        CALL CSIZPB.ADB2RE(?,?,?,?,?,?);
      • Any value (for example, ABC) that is prefixed with a colon (:) is treated as a variable. 
        CALL SDFGTS.ADB2RE(:ABC,:ABC,:ABC,:ABC,:ABC,:ABC);
      • Any value that you specify within single quotes (' ') is considered as the value for the variable. 
        CALL SDFGTS.ADB2RE('VALUE1','VALUE2','VALUE3','VALUE4','VALUE5','VALUE6');
      Note: You can combine a text within single quotes (' ') with either a question mark (?) or a value prefixed with a colon (:) in your CALL statement.
      Important: You cannot use the following combinations together in the same CALL statement:
      • A value prefixed with a colon (:) and a question mark (?).
      • A text within single quotes (' '), a value prefixed with a colon (:), and a question mark (?).
    • To reuse a CALL statement that you previously submitted, open the Recent statements tab, and click the CALL statement that you want to reuse.

      The selected CALL statement is automatically displayed on the New statements tab. You can use or change it as necessary.

    Important: You can only call one stored procedure at a time.
  4. Click Run. The Add variable value page opens.
    Note: To move the Add the required values for the variables section to the bottom, click or to the right, click .
  5. Enter the appropriate variable value in the Value field or select the Is null checkbox if the value is NULL for the parameter in the stored procedure. The status of the statement on the right-corner changes to Visited.
    Important: The variable value that you specify in the Value field is case-sensitive.
    Note: If the required value is not entered, the status remains as Not visited and the Run button will be disabled.
  6. Click Run to rerun the CALL statement for stored procedure. The results appear as follows:
    • The Variables tab displays the variables given to call the stored procedures.
    • The Result set tab displays the results based on the variables given.