Haste makes Waste - Stored Procedure Recompilation

During the study of the release notes of the new IDS 10.00.FC8 version I noticed a long anticipated feature that already made it into IDS 10.00.FC7:

Enhanced Concurrency after Recompilation of SPL Routines

The problem of locked rows in the dictionary table sysprocplan is something that comes up in many IDS installations that work intensively with stored procedures. The recommendation to reoptimize the stored procedures manually after DDL changes - update statistics for procedure - helps to avoid the problem but is more a kind of a workaround and is often forgotten by the administrator or application developer.

So I'm really happy that IBM paid attention to this concurrency problem and fixed it in IDS 10.00.FC7 and newer releases. You can download the release notes thru the following URL:

http://publibfp.boulder.ibm.com/epubs/html/i1183151.html

Here is an excerpt from the release notes covering the new stored procedure re-optimization functionality:

1.5.1 New Features in 10.00.xC7 
  
1.5.1.1 Enhanced Concurrency after Recompilation of SPL Routines 
  
The UPDATE STATISTICS statement can explicitly recompile the statements 
in an SPL routine and update the execution plan in the system catalog. 
After an SPL routine has been registered in the database, however, DDL 
statements, such as ALTER FRAGMENT, ALTER TABLE, CREATE INDEX, DROP 
INDEX, or UPDATE STATISTICS can modify the schema of tables that the 
routine references. When executing an SPL routine, Dynamic Server 
detects changes to tables referenced in statements of the routine, and 
automatically recompiles the statements referencing those tables. 
  
If an SPL routine is invoked within a transaction, explicit or automatic 
recompilation of the routines updates the system catalog within an open 
transaction, In earlier releases of Dynamic Server, locking conflicts in 
this context caused operations in concurrent sessions that attempted to 
access the sysprocplan system catalog table to fail with error -211: 
  
-211 Cannot read system catalog 'informix'.sysprocplan 
  
After automatically recompiling the SPL routine and reoptimizing any of 
its DML statements that use modified objects or statistics, Dynamic 
Server updates the 'informix'.sysprocplan system catalog table with the 
new routine execution plans and query plans, and immediately makes the 
plans available to other sessions to use. 
  
When you explicitly recompile SPL routines using the UPDATE STATISTICS 
statement, Dynamic Server takes these actions: 
*  compiles each SPL routine 
*  writes the new plan to the system catalog 
*  immediately makes the plans available to other sessions to use 
  
This feature avoids -211 locking errors and improves concurrency for 
sessions that operate on the same database or on different databases of 
the same Dynamic Server instance. It has no effect, however, in 
databases that do not support transaction logging, or in sessions that 
have opened databases in EXCLUSIVE mode, or when an SPL routine is 
created or dropped in a transaction. It does not prevent SPL routines 
that reference a dropped table from failing with an error during 
recompilation.