Embedded SQL Programming Guide

Resolving Problems

If a stored procedure application fails to execute properly, ensure that:

• The stored procedure is built using the correct calling sequence, compile options, and so on.

• The application executes locally with both client application and stored procedure on the same workstation.

• The stored procedure is installed in the proper location in accordance with the instructions in the DB2 SDK Building Applications book for your operating system.

  For example, in an OS/2 environment, the dynamic link library containing the stored procedure is located in a directory identified in the LIBPATH statement in the CONFIG.SYS file of the database server, or the calling function provides a full path. In an AIX- or UNIX-based environment, the library containing the stored procedure is located in the $HOME/sqllib/function directory of the DB2 instance owner, or the calling function provides a full path.

• The application is bound to the database.

• The stored procedure accurately returns any SQLCA error information to the client application.

• The stored procedure should not allocate its own SQLDA or sqlvar storage. The stored procedure should use the storage passed into the procedure by parameters.

• Stored procedure function names are case-sensitive, and must match exactly on client and server.

You can use the debugger supplied with your compiler to debug a stored procedure as you would any other application. Consult your compiler documentation for information on using the supplied debugger.

As an example, the following are the high level steps for using the IPMD debugger supplied with IBM VisualAge C++ on OS/2:

• Compile the source file for the stored procedure DLL with the -Ti+ and -o- flags, and then link the DLL using the -DEBug option.

• Copy the resulting DLL to the %DB2PATH%\DLL directory (or any other directory specified by the LIBPATH environment variable).

• The stored procedure is run as a not-fenced stored procedure.

• Debug the stored procedure on the database server by invoking the client application on the same machine. Assuming the client is OUTCLI and the server is OUTSRV, the steps are as follows:

    ipmd outcli.exe
  

• After the debugger starts up, click on BREAKPOINTS and then LOAD OCCURRENCE.

• Enter the name of the DLL in LOAD OCCURRENCE; for example, OUTSRV.

• Click on RUN. The debugger will stop when the DLL is loaded.

• At this point, you can set the break point in the stored procedure (OUTSRV.DLL).

Refer to the VisualAge C++ product documentation for further information on using the IPMD debugger.

[ DB2 List of Books | Search the DB2 Books ]