DbExecute system function

Use the DbExecute system function to execute a stored procedure in a specified database.

Before you run the workflow with the DbExecute system function, be sure to define the database connection using vwtool. See dbconfig in the online Help for vwtool.

To specify a DbExecute system function

  1. From the General System Palette, drag a DbExecute step onto the workflow map.

    See About system steps for information about including multiple system functions in a single step.

  2. Enter the database name.
  3. Enter the name of the stored procedure to execute.

    NOTE   The name used to create a stored procedure determines how it must be called. Process Designer does not allow quoted stored procedure names for the DBExecute system function, so the names of stored procedures must not be enclosed in quotes.

  4. Any database stored procedure that will be called by a System step must have all of its parameters declared as follows:

    This means that the stored procedure can update the value of the parameter. The stored procedure will return the (possibly updated) values of all parameters to the caller. The updated values will be reflected in values stored in the data fields that were passed in as parameters to the stored procedure.

    Define data fields in the workflow properties and specify the appropriate data fields as parameters for the various steps.

    The datatypes must match the types specified in the stored procedure and may be of the following types:

    Designer Param Type Oracle Param Type SQL Server Param Type DB2 Param Type
    String varchar varchar varchar
    Integer number int int
    Boolean number bit number
    Float number float float
    Time date datetime timestamp

Parameters for a stored procedure cannot be arrays. For example, you can pass in fifteen separate string parameters, but you cannot pass in an array of fifteen strings as a single parameter. You can use a single element in an array by specifying its index. For example, the syntax for specifying the first element in an array is fieldname[1].

Handling parameters returned as null from stored procedures

Restrictions

  Oracle SQL Server DB2
Maximum number of parameters in procedure 1024 1024 1024
String parameter maximum characters 4000 255 4000

Best practices using stored procedures

Database stored procedures can throw exceptions, so you should consider handling exceptions in the stored procedure or in an exception map in the workflow.

If you have upgraded from FileNet P8 version 3.5.2 or earlier and you are using Microsoft SQL Server, you might see exception in delete or update statements that you did not see before. The database will return an exception when a delete or update statement that contains a Where clause does not select any records. For example, if you are deleting all records where Name=Don, if there are no records where Name=Don, the stored procedure receives an error from the database. If you don't catch these exception in the stored procedure, then the work object receives the exception and goes to either your exception map or to the Malfunction queue.

Troubleshooting workflows using DbExecute

If a problem occurs in a running workflow at the DbExecute system function, you can find information about the error in the system message log. See FileNet message log files.

If the stored procedure does not return a message within the time limit, the timeout expires and the work object gets an exception and goes to the Conductor queue. The default timeout value is 3600 seconds (one hour). See Process Task Manager Advanced Usage Technical Notice. To download this document from the IBM support page, see Accessing IBM FileNet Documentation.