Debugging routines

This is especially important for routines that need to be registered as NOT FENCED because they have unrestricted access to the database manager's memory, its databases, and database control structures. FENCED THREADSAFE routines also demand close attention because they share memory with other routines.

Checklist of common routine problems

To ensure that a routine executes properly, check that:

• The routine is registered properly. The parameters provided in the CREATE statement must match the arguments handled by the routine body. With this in mind, check the following specific items:
  • The data types of the arguments used by the routine body are appropriate for the parameter types defined in the CREATE statement.
  • The routine does not write more bytes to an output variable than were defined for the corresponding result in the CREATE statement.
  • The routine arguments for SCRATCHPAD, FINAL CALL, DBINFO are present if the routine was registered with corresponding CREATE options.
  • For external routines, the value for the EXTERNAL NAME clause in the CREATE statement must match the routine library and entry point (case sensitivity varies by operating system).
  • For C++ routines, the C++ compiler applies type decoration to the entry point name. Either the type decorated name needs to be specified in the EXTERNAL NAME clause, or the entry point should be defined as extern "C" in the user code.
  • The routine name specified during invocation must match the registered name (defined in the CREATE statement) of the routine. By default, routine identifiers are folded to uppercase. This does not apply to delimited identifiers, which are not folded to uppercase, and are therefore case sensitive.

    The routine must be placed in the directory path that is specified in the CREATE statement, or if no path is given, where the database manager looks for it by default. For UDFs, methods, and fenced procedures, this is: sqllib/function (Linux® and UNIX) or sqllib\function (Windows). For unfenced procedures, this is: sqllib/function/unfenced (Linux and UNIX) or sqllib\function\unfenced (Windows).

• The routine is built using the correct calling sequence, precompile (if embedded SQL), compile, and link options.
• The application is bound to the database, except if it is written using CLI, ODBC, or JDBC. The routine must also be bound if it contains SQL and does not use any of these interfaces.
• The routine accurately returns any error information to the client application.
• All applicable call types are accounted for if the routine was defined with FINAL CALL.
• The system resources used by routines are returned.
• If you attempt to invoke a routine and receive an error (SQLCODE -551, SQLSTATE 42501) indicating that you have insufficient privileges to perform this operation, this is likely because you do not have the EXECUTE privilege on the routine. This privilege can be granted to any invoker of a routine by a user with SECADM authority, ACCESSCTRL authority, or by any user with EXECUTE WITH GRANT OPTION privilege on the routine. The related topic on authorizations and routines provides details on how to effectively manage the use of this privilege.

Routine debugging techniques

To debug a routine, use the following techniques:

• The Development Center provides extensive debugging tools for SQL-bodied and Java™ procedures.
• It is not possible to write diagnostic data to screen from a routine. If you intend to write diagnostic data to a file, ensure that you write to a globally accessible directory such as \tmp. Do not write to directories used by database managers or databases.

  For procedures, a safe alternative is to write diagnostic data to an SQL table. The procedure you are testing must be registered with the MODIFIES SQL DATA clause in order to be able to write to an SQL table. If you need an existing procedure to write data (or no longer write data) to an SQL table, you must drop and re-register the procedure with (or without) the MODIFIES SQL DATA clause. Before dropping and re-registering the procedure, be aware of its dependencies.

• You can debug your routine locally by writing a simple application that invokes the routine entry point directly. Consult your compiler documentation for information on using the supplied debugger.