Understanding and Using Informix Stored Procedures
Copyright Dataspace Consulting Pty Ltd, 1995
Since the introduction of stored procedures in version five of the Informix engine there has been a lot of discussion about their use, benefits, limitations and their impact on the applications' performance. They have gained acceptance in the client/server environment but their usefulness in the single host type environment has been seriously underestimated. As with any tools, before using them it is important to consider whether it is the right tool for the job at hand. To be in the position to make this decision you need to understand its workings, how well it integrates with your environment and development tools, the type of application you are developing and whether it is a small scale application or large mission critical OLTP system. When used appropriately stored procedures can be a very powerful tool both in the client/server environment and single host type environment.
The aim of this article is to provide a high level overview of stored procedures and their features, particularly the advantages and disadvantages of their use from the two perspectives: client/server and single host type environment. I will also discuss issues to consider before using stored procedures on a large scale. Hopefully by the end of this article you will be in the position to decide whether stored procedures are the right for you.
Overview of Stored Procedures
Stored procedures can be thought of as user defined program modules stored in the database and executed by the database engine. They are the simplest form of remote procedure calls where the remote client sends a message to the server, which then executes the procedure. Stored procedures are written in Stored Procedure Language which is a superset of SQL with additional looping and conditional statements. When a stored procedure is created it's source and the compiled executable is stored in the system catalog tables. From the administration point of view they are like any other database objects which can be created and dropped using 'create' and 'drop' statements. Their permissions can be administered using grant statements. The source of stored procedures can be extracted from the system catalogs using 'dbschema -f' utility. To fully appreciate the benefits and shortcomings of stored procedures it is important to understand what happens at the time stored procedure is created and executed.
Stored procedures are created using a 'create procedure' statement. The following steps take place at the time of creation:
1. The source is parsed and stored in the SYSPROCBODY system table.
2. The SQL is extracted from the source of stored procedure and a dependency list built. The dependency list is an index to all database objects accessed by the stored procedure. It is used by Informix at the time of execution to verify that all objects referenced still exist and have not changed.
3. The extracted SQL is optimised to produce a query plan, just like any other SQL.
4. The dependency list and query plan are stored in the system table: SYSPROCPLAN. Note that while the stored procedure is created Informix places exclusive locks on some of the system tables.
Stored procedures are executed using 'execute procedure' statement. Following steps take place during execution:
1. Retrieve the dependency list, pcode and query plan for the stored procedure from the system catalog tables if they are not already in the stored procedure cache.
2. Verify that the objects in the dependency list have not changed since the time the stored procedure was created.
3. If no changes were detected execute procedure.
4. If there were changes to the database object, the SQL is re-optimised and a new query access plan is stored in the catalog tables. The procedure is then executed.
The SQL in the stored procedures is optimised at the time of its creation except in the following cases:
1. When the structure of the tables referenced by the procedure or their indices have changed it will be re-optimised at the first execution after the change.
2. If stored procedure uses temporary tables it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x
3. If the stored procedure does not contain any DML statements (e.g. select, insert, update, delete) it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x. The re-optimisation of SQL during stored procedure execution should be avoided as it results in exclusive locks on the SYSPROCBODY table and can lead to serious concurrency problems.
Stored Procedures System Tables
The following system tables are used to store information about stored procedures:
Security and Stored Procedures
Stored procedures can operate in two security modes at the time of execution:
1. Owner Privileged Procedures
These are stored procedures created by the user with 'resource' authority on the database. The stored procedure inherits all the permissions on the objects owned by the creator plus permissions on other objects that been granted to the creator with the grant option. In case of owner privileged stored procedures Informix will check all permissions on referenced objects at the time of execution.
2. DBA Privileged Procedures
These are stored procedures created by the user with 'dba' authority on the database or with a dba keyword in the create statement. In this case the stored procedure has 'dba' privileges for the duration of it's execution. When dba privileged procedure is executed there is no need to check the permissions on referenced objects.
Benefits of Using Stored Procedures
For the purpose of this article it is useful to look at the benefits of using procedures from the perspective of the environment you will use to deploy your application. That is whether it is client/server or a single host type environment. Note that the benefits gained from using stored procedures in a single host type environment are also applicable to client/server.
Advantages in Client/Server Environment
1. Provide means for partitioning the application logic between the server and client. For example CPU intensive work such as calculations could be implemented in the stored procedures and the results returned back to the client for presentation.
2. Improve application performance by reducing network traffic. One of the biggest performance bottlenecks in the client /server environment is the network. With use of stored procedures you can substantially reduce network traffic. There are two reasons for this:
a. Instead of sending whole SQL statements only the stored procedure name and arguments have to be sent to the server.
b. In a situation where you have a transaction which consists of multiple SQL statements the intermediate results from the SQL have to be sent between the server and the client. If stored procedures are used then all transaction work can be done on the server and only the final result returned to the client.
3. Allow encapsulation and enforcement of business rules on the server. For example if business rules are controlled through stored procedures then they can be enforced regardless of what tool is used to update the database.
4. Improved application maintenance. If business rules and application logic are implemented in the stored procedures then any changes to business rules are localised. This means that application changes can be done on the server without a need to distribute them to all the clients.
5. Provide more robust transaction control. When using stored procedures transaction control can be implemented on the server instead of the client. If transactions are controlled on the client there exists a possibility of transactions being left open if the client crashes. Open transactions might prevent archiving of logical logs and lead to long transactions. This problem has been addressed in later versions of release 5 where Informix will terminate any open sessions without client connection.
6. Allow to extend the functionality of some 4GL development tools. Some of the Client/Server development tools are not very good in handling complex database interactions such as denormalised data.
7. Improve application security. In ODBC environments any application users can access the database using desktop tools. Using stored procedures you can eliminate the need to grant direct access to all tables and control it through the procedures.
8. Can be used to invoke Unix programs from the PC client on the server using SPL 'SYSTEM' command. For example a stored procedure can be used to send Unix mail or update files on the Unix host.
Advantages in Single Host Environment
Note that all the benefits of stored procedures in the single host environment are also applicable to client/server environment
1. Allow implementation of business rules and database integrity checking in the database by disallowing programs and users from accessing the tables directly.
2. Encourage code reuse. Commonly used functionality can be implemented in the stored procedures which can be reused by any programs.
3. Improve performance. When SQL is executed through 4GL or E/SQL, the SQL statement is sent through a pipe to the Informix engine and then parsed, optimised and security checked before it's execution. With stored procedures the above work needs to done only at the time of it's creation. Generally stored procedures with four or more SQL statements provide best improvements.
4. Improve application security. If database access is encapsulated within the procedures then it is no longer necessary to grant privileges to users on the tables.
5. Allow to extend the functionality provided by standard SQL functions. Stored procedures can be used to create user defined functions that are transparently utilised within the SQL statements, for example: if a measurement is stored on the database in inches. A function could be written which will convert inches to centimetres. This function can be then used as if it was built-in SQL function.
6. Extend functionality provided by the database triggers. The language used to write triggers is limited to simple type operation.
7. Hide the complexity of the underlying database structures from the users and programmers, for example stored procedures can be written to perform standard reports which are then invoked by GUI end-user reporting tools.
8. Aid in application's performance tuning and monitoring. Procedures provide ability to perform SET EXPLAIN on SQL with out first executing it. Since the stored procedure's SQL is optimised when the procedure is created, it is possible to obtain access paths chosen by the optimiser at this time. In the case of SQL executed from E/SQL or 4GL it is not possible to see the explain output unless the SQL has been run against the database which might not be always possible or practical in a production environment.
9. Can be used increase application's independence from the underlying database structures, therefore, isolating the programs from the database structure changes, for example: data denormalisation.
Disadvantages of Stored Procedures
Most of the disadvantages of stored procedures are due to an extra layer of complexity and lack of support by development and administration tools
1. Some of the 4GL development tools are designed to work best by accessing the database tables directly. If you are using stored procedures then some of the default functionality provided by the development tool might be lost. For example most of 4GL tools will automatically implement optimistic locking (checking for lost updates). If you choose to perform updates through stored procedures then in most cases you will lose the functionality provided by the tool which will result in decreased productivity.
2. For the above reason, when using stored procedures you might lose the database independence provided by your 4GL tools. Also the stored procedure's code is not portable to other DBMS platforms.
3. More complicated program debugging.
4. Before version seven of the engine, stored procedures are buffered in the sqlturbo processes hence each user holds their own copy of the stored procedures cache. As a result of this your application might require more memory for each sqlturbo process and you will lose the benefits of sharing buffered procedures across different database sessions. This is no longer a problem since version seven has a common stored procedure cache for all the users.
5. Extra dependency is introduced into the applications. It necessary to ensure that the programs execute correct versions of the procedures. Configuration management is a major issue. 6.In some situations the use of stored procedures might degrade the performance. As stated earlier, when stored procedure is executed the Informix engine has to check the system tables to ensure that the tables used by the procedure have not changed.
7. The stored procedure language does not support dynamic SQL and versions prior to seven did not support access to SQLCA.
Using Stored Procedures
As illustrated, many benefits can be gained from using stored procedures. When embarking on their use, consider the impact on the developers due to the extra layer of complexity in the environment. It is best to carefully plan how they will be utilised and administered. Following is an overview of issues that should be considered before using stored procedures:
1. Devise naming standards. Good naming standards are important with a large number of stored procedures. They will improve the maintainability of the application by allowing easy identification of stored procedure function and the tables it accesses.
2. Document SPL coding standards. The standards should address issues such as:
a. Guidelines on when to use stored procedures. You might decide that only certain type of work should be done in stored procedures. Good candidates are: complex reports and queries, access to sensitive data, processing of large cursors, any SQL that is used repetitively, transactions which perform more then four SQL statements.
b. Document conventions for parameter passing between the application programs and stored procedures.
c. Define how the stored procedures will communicate back with the application.
d. Provide guidelines for transaction control. Transaction control can be either done from the application program or within the stored procedure. The choice will largely depend on the development tools at use. Try to stick to one consistent method.
3. Error handling. How will the errors handled and communicated back to the application programs.
4. Workout how the dependencies between the procedures and programs will be tracked.
5. Update configuration management procedures.
6. Management of SYSPROCBODY and SYSPROCPLAN tables. When a large number of stored procedures is used these tables can grow to many extents which can effect the performance of loading the procedures into memory. This can be eliminated by altering the 'next extent' to a larger size.
7. Security administration.
8. Remember that by default stored procedures are created with execute to public permission. When migrating stored procedures to production execute to public should be revoked.
Summary and Conclusion
As you can see stored procedures are very powerful tools when used appropriately. The decision to use stored procedures should depend on your organisation, development environment and the type of application being developed. All of the benefits of store procedures can be fully realised when building large mission critical applications, where database integrity, security and performance with a large number of concurrent users are critical. Stored procedures should be also given serious consideration when building client/server applications which will be deployed over the wide area
networks where the amount of network traffic will have a significant impact on your application's performance. In most cases you will find yourself using stored procedures just for a specific tasks which cannot be easily done using your current development tools or in situations where central enforcement of business rules and security is critical to the application.
Rafal Czerniawski is director of Dataspace Consulting Pty Ltd. He specialises in
logical and physical database design, performance tuning, database administration,
enterprise client/server design and CASE tools, his experience includes Informix, Sybase
and DB2. You can contact him through email at: