In a database application environment, many situations are repetitive; for example, receiving a fixed set of data, performing the same multiple requests against a database, or returning a fixed set of data. Stored procedures permit one call to a remote database to execute a preprogrammed procedure. One call may represent several accesses to the database.
Processing a single SQL statement for a remote database requires sending two transmissions: one request and one receive. However, an application can contain many SQL statements. Without stored procedures, many transmissions are required for an application to complete its work.
When a database client uses a stored procedure, it requires only two transmissions for the entire process, thereby reducing the number of network transmissions. To invoke a stored procedure, the requesting application must connect to the database containing the procedure before calling it.
Typically these stored procedures are run in processes separate from the database agents. This separation requires that the stored procedure and agent processes must communicate through a router. To obtain the best possible performance for a stored procedure, it is possible to identify a stored procedure as being "trusted", or "not fenced", and as a result, run the procedure directly in the database agent process. What do we mean by "trusted" and "not fenced"?
Both of these terms mean the same thing, that is, if your stored procedure is "not fenced", then your stored procedure is "trusted". Due to the associated risk of damaging your database, you should only use not fenced stored procedures when you need to obtain the maximum possible performance benefits. In addition, you should ensure that the procedure is well coded and has been thoroughly tested before allowing it to run as a not fenced stored procedure. If a fatal error does occur while running one of these not fenced stored procedures, the database manager will determine whether the error occurred in the application or database manager code, and perform the appropriate recovery.
There are two ways to create a stored procedure as being not fenced:
To run a stored procedure, the end-user running the application that calls the procedure must have one of the following privileges at run time:
For information on writing programs using stored procedures, refer to the Embedded SQL Programming Guide manual.