Extending Informix Dynamic Server 2000 Improving UDR Performance

The database server stores user-defined routines and triggers in the following system catalog tables:

• sysprocbody
• sysprocedures
• sysprocplan
• sysprocauth
• systrigbody
• systriggers

These system catalog tables can grow large with heavy use of UDRs in a database. You can tune the key system catalog tables as you would any heavily utilized data tables. To improve performance, use the following methods:

• Isolate system catalog tables.
• Balance the I/O activities.

If your database server has multiple physical disks available, you can isolate your system catalog tables on a single device and place the tables for your application in a separate dbspace that resides on a different device. This separation reduces contention for the same device.

If you have a large number of UDRs that span multiple extents, you can spread the system catalog tables across separate physical devices (chunks) within the same dbspace to balance the I/O activities.

1. Create the dbspace for the UDR system catalog tables with several chunks. Create each chunk for the dbspace on a separate disk.
2. Use the CREATE DATABASE statement with the IN dbspace clause to isolate the system catalog tables in their own dbspace.
3. Load approximately half of your UDRs with the CREATE PROCEDURE or CREATE FUNCTION statement.
4. Create a temporary table in the dbspace with an extent size large enough to use the remainder of the disk space in the first chunk.
5. Load the remainder of the UDRs. The last half of the routines should spill into the second chunk.
6. Drop the temporary table.