Using IQ UTILITIES to Create Your Own Stored Procedures

The system stored procedures provided in Sybase IQ are implemented in SQL, using the methods described in the rest of this chapter.

You must use the local temporary table and IQ UTILITIES statement in exactly the same way as system stored procedures:

Warning! Violating these rules can cause serious problems for your IQ server or database.

All SQL code for procedures is encrypted and compiled into the shared library libiqscripts15_r.so file on Unix and iqscripts15.dll file on Windows.

You can view the stored procedures code by using Sybase Central or by entering sp_helptext 'owner.procname' in Interactive SQL.

The syntax for IQ UTILITIES is:

IQ UTILITIES MAIN INTO local-temp-table-name arguments

The IQ UTILITIES command is only documented in Reference: Statements and Options due to strict requirements for its use and the risk to system operations if it is used incorrectly.

You may want to create your own variants of some of these procedures. For example:

Create a procedure that calls a system stored procedure.
Create a procedure that is independent of the system stored procedures but performs a similar function.
Create a procedure that uses the same structure as the system stored procedures but provides additional functionality. For example, you might want to display procedure results in graphical form in a front-end tool or browser rather than as text.
If you choose the second or third option, you need to understand the IQ UTILITIES statement and the strict requirements for using it.

How IQ Uses the IQ UTILITIES Command
IQ UTILITIES is the underlying statement that executes whenever you run most IQ system procedures. In most cases, users are unaware that IQ UTILITIES is executing. The only time IQ UTILITIES is issued directly by users is to run the IQ buffer cache monitor.
Choosing Procedures to Call
You can safely use IQ UTILITIES to create your own versions of documented system procedures that report on information in the database.
Numbers Used by IQ UTILITIES
The following table lists the numbers used as arguments in the IQ UTILITIES command and the system procedure where each number is used.
Procedure Testing
Always test your procedures in a development environment first. Test procedures before you run them in a production environment to help maintain the stability of your IQ server and database.

Parent topic: Using Procedures and Batches