Rogue Wave Knowledge Base
Search:     Advanced search
Browse by category:
Knowledgebase | Glossary | Ask a Question |

How to create and run Stored Procedures in DB2

Article ID: 904
Last updated: 01 Feb, 2008
Revision: 1
Views: 102399
Posted: 24 Jun, 1999
by Dean J.
Updated: 01 Feb, 2008
by Dean J.
Problem


How do I write a stored procedure in DB2?
How do I call a DB2 stored procedure through DBTools?
How would I write a native test case?


Cause


DB2 stored procedures work differently from stored procedures in our other supported databases. They must be written in C, C++, or Java using embedded SQL or the DB2 CLI and then registered with the database. All of this must be done outside of DBTools before you can call the stored procedure in a DBTools application.


Action


Quick Table of Contents

  1. Important Things To Note
  2. Prerequisites for building a stored procedure
  3. Stored Procedure Example
  4. Registering your stored procedure
  5. DBTools Application to call stored procedure
  6. A native DB2 test case to test your stored procedure
Important Things To Note Important Things to Note
  • DB2 stored procedures can not return result sets if you write them in embedded SQL. You will therefore be limited to output variables for returning data.
  • You will need to use extern 'C' to prevent name mangling from C++ compilers.
  • As of now, I have not been able to use procedures from the DB2 command prompt. You can use them through DBTools and the native test cases below.
  • Any tables that the stored procedure will access must already exist on the database before you can compile your stored procedure.
  • You will notice the files util.h and util.C referenced in this document. They can be found in the directory $DB2DIR/samples/cpp
Prerequisites for building a stored procedure
  1. You will need a build script from your DB2 installation to perform the pre-compiling and then compiling of your stored procedure. These scripts can be found in the following directory (for C++): $DB2DIR/samples/cpp/. You will want to use the file bldCCsrv (if you are using Sun Solaris) to compile your stored procedure.
  2. You will also need write permissions on the directory $DB2DIR/sqllib/function
Stored Procedure Example

Your stored procedure should be in a file that has the extension .sqC. The build script will pass this to the precompiler, generate a .C file and then compile the stored procedure.

SQL_API_RC and SQL_API_FN are macros that will be expanded during pre-compilation for the platform you are compiling on.

All stored procedures must have the following four parameters: void *reserved1, void *reserved2, struct sqlda *inout_sqlda, struct sqlca *ca. You can name the sqlda and sqlca whatever you like (this example uses inout_sqlda and ca), but reserved1 and reserved2 must appear in the argument list. They are not used for DB2 stored procedures but must be present to provide support for an earlier stored procedure calling convention which is still supported by DB2.

Call the build script in the following manner:

ksh bldCCsrv <.sqC file without extension>    

*************Stored Procedure starts here**************
#include #include #include #include #include #include #include extern C SQL_API_RC SQL_API_FN outtest( void *reserved1, void *reserved2, struct sqlda *inout_sqlda, struct sqlca *ca) { EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; short num_records; char stmt[512]; EXEC SQL END DECLARE SECTION; /* Declare Miscellaneous Variables */ int counter = 0; EXEC SQL WHENEVER SQLERROR GOTO error_exit; EXEC SQL WHENEVER SQLWARNING CONTINUE; EXEC SQL DECLARE c1 CURSOR FOR s1; /* Prepare a Statement to Obtain and Order all Salaries */ /*Table OUTSTRING: ID(integer) NAME(varchar(700)) 101 Mickey Mouse */ strcpy( stmt, SELECT name FROM OUTSTRING WHERE name='Mickey Mouse' ); EXEC SQL PREPARE s1 FROM :stmt; EXEC SQL OPEN c1; EXEC SQL FETCH c1 USING DESCRIPTOR :*inout_sqlda; EXEC SQL CLOSE c1; EXEC SQL COMMIT; memcpy( ca, &sqlca, sizeof( struct sqlca ) ); return(SQLZ_DISCONNECT_PROC); error_exit: /* An Error has occurred -- ROLLBACK and return to Calling Program */ EXEC SQL WHENEVER SQLERROR CONTINUE; memcpy( ca, &sqlca, sizeof( struct sqlca ) ); EXEC SQL ROLLBACK; return(SQLZ_DISCONNECT_PROC); } *************Stored Procedure ends here**************

Now change to the directory to which the stored procedure was copied, and set its permissions to at least 755 so the database (and anyone else) can execute it.

Registering your stored procedure

After successfully invoking the build script and compiling without error, the script should copy your stored procedure to the $DB2DIR/sqllib/function directory. Now you will need to register the stored procedure. Log into the DB2 server command prompt.

If you would like other users than yourself to be able to execute this stored procedure you will need to grant public permission on the stored procedure package you created with the command:

grant execute on package  to public
The executable will be the name of your .sqC file (without the extension).

To register the stored procedure with the database, use the CREATE PROCEDURE command as in the following example:

create procedure outtest(OUT out_string VARCHAR(700)) EXTERNAL NAME 'drprocs!outtest' FENCED LANGUAGE C PARAMETER STYLE DB2DARI NULL CALL
In this example: You should now be able to use your stored procedure.

DBTools Application to call stored procedure

This is a fairly basic program that will create an RWDBStoredProc, bind an RWCString to the stored procedure, and then call it.

*************DBTools code starts here**************
/* File: db2_Shared.cpp
**
** First attempt at connecting to database
*/

#include #include #include #include void errorHandler (const RWDBStatus& aStatus) { // Print out the error. cout << Error code: << (int) aStatus.errorCode() << endl << Error message << aStatus.message() << endl << Is terminal: << (aStatus.isTerminal() ? Yes : No) << endl << Vendor error 1: << aStatus.vendorError1() << endl << Vendor error 2: << aStatus.vendorError2() << endl << Vendor message 1: << aStatus.vendorMessage1() << endl << Vendor message 2: << aStatus.vendorMessage2() << endl; } int main() { RWDBDatabase::connect(FALSE); RWDBManager::setErrorHandler(errorHandler); RWDBDatabase dataBase = RWDBManager::database(db27d.dll,DatabaseName,User,Password,); ofstream outsql(outsql.txt); RWDBTracer& tracer = dataBase.tracer(); tracer.setOn(RWDBTracer::SQL); tracer.stream(cout); // Check to see if connection was made to database if(dataBase.isValid()) { cout << Connected to database << endl; } else { cout << dataBase.status().message() << endl; } RWDBConnection conn = dataBase.connection(); RWDBStoredProc proc; { proc = dataBase.storedProc(OUTTEST,conn); } RWCString output; proc << &output; RWDBResult res = proc.execute(conn); proc.fetchReturnParams(); RWDBTable table = res.table(); cout << Out is << output << . << endl; cout << String length is << output.length() << endl; return 0; } *************DBTools code ends here**************

A native DB2 test case to test your stored procedure

You will need another build script to build the native test application. Again these scripts can be found in the following directory (for C++): $DB2DIR/samples/cpp/. You will want to use the file bldCC (if you are using Sun Solaris) to build your test case.

Call the build script in the following manner:

ksh bldCC <.sqC file without extension>    

The following test case will create a native DB2 application that uses the call command to execute a stored procedure.

*************DB2 Native test case starts here***********
#include #include #include #include #include #include #include util.h #define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1; int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char database[9]; char userid[9]; char passwd[19]; /* Declare a Local Variable for Holding the Procedure's Name */ char procname[51] = OUTTEST; /* Declare Local Variables for Holding Returned Data */ char string[701]; EXEC SQL END DECLARE SECTION; /* Declare the output SQLDA */ struct sqlda *inout_sqlda = (struct sqlda *) malloc(SQLDASIZE(1)); /* Declare the SQLCA */ struct sqlca sqlca; if (argc != 4) { printf ( USAGE: nativeDB2 remote_database userid passwd ); return 1; } strcpy (database, argv[1]); strcpy (userid, argv[2]); strcpy (passwd, argv[3]); /* Connect to Remote Database */ printf(CONNECT TO Remote Database. ); EXEC SQL CONNECT TO :database USER :userid USING :passwd; CHECKERR (CONNECT TO SUPPORT); /******************************************************** * Call the Remote Procedure via CALL with Host Variables * ********************************************************/ printf(Use CALL with Host Variable to invoke the Server Procedure named outtest ); EXEC SQL CALL :procname (:string); CHECKERR (CALL WITH HOST VARIABLES); printf(Server Procedure Complete. ); /* Print Name Returned in The Host Variables */ printf(name = %s , string ); /* Disconnect from Remote Database */ EXEC SQL CONNECT RESET; CHECKERR (CONNECT RESET); return 0; } *************DB2 Native test case ends here***********
This article was:   Helpful | Not helpful
Prev   Next
How do I delete a row in a cell grid?     In zHelp, the <A NAME=> tag does not work correctly

Others in this category
document Internationalization of Blend.J TextField
document How do I add a subject header to my SMTP application?
document Core dumps described in the Tools.h++ Professional readme document
document Failure building Standard C++ Library with aCC 3.25
document How can I print charts from Chart.J?
document Installation of StudioJ on Unix or Mac
document The StockCursor SizeCursor is not working in zApp
document Catching exceptions from the inet module in Tools.h++ Professional
document Can Money.h++ be used in multi-threaded applications?
document Objective Grid/J: Database Connectivity
document Software Parts Manager Builds on Unsupported/Non-GUI platforms
document Standard C++ Library manual examples will not build on Solaris
document Using custom build configurations
document The Use of Virtual Destructors in Tools.h++ classes
document Runtime error in DBTools.h++
document How to create a custom overlay
document The addSubItem() method causes the Tree application or applet to freeze at runtime
document RWMutexLock Recursive Acquisition error in Debug - Not True! - Threads 2.0.1 only
document Using DBTools.J with the Oracle Driver: Objects Are Not Getting Released Correctly
document JDK 1.1.6 JIT update and later versions cause Labels to disappear in StudioJ
document How do I create a list of lists?
document UnsatisfiedLinkError when installing StudioJ
document StudioJ product unlock code not accepted during installation
document Column titles using center justification disappear when using JFC/Swing
document Retrieving return parameters from oracle Storedprocedures
document Specifying a frozen column attribute does not lock the selected column
document Building native Win32 applications with Visual Cafe using Grid.J.
document When trying to establish a connection to my database using DBTools.h++, I get the error: DB NOT FOUND
document How do I use binaryStoreSize and recursiveStoreSize?
document Using the clipboard paste function with a JMaskEdit control
document Passing strings containing quotes causes problems with DBTools.J
document Location of Visual CASE
document Unlock keys do not work
document Retrieving Blobs from Oracle using DBToolsJ
document RW virtual streams and RWCollectableString ambiguity error
document TreeControl image not updating correctly in JWidgets
document RWDBTracer and asString() method behave differently in DBTools 4.0.0
document Objective Grid/J: Footprint
document Throwing exceptions using Sybase or MS SQL Server corrupts connections
document Changing the state of a GXCheckBox with a single click when the control does not have focus?
document I cannot execute the install.sh script for Software Parts Manager
document Submitting HTML GET/POST Requests to a CGI Program
document Undefined Symbol RWAll when Linking Application
document What relation does the RW Standard C++ Library 1.x have to the ANSI Standard?
document Informix stored procedure does not execute an INSERT
document How to avoid a database hit when instantiating a RWDBStoredProc
document The commit / rollback transaction request has no corresponding BEGIN TRANSACTION
document Trapping shift, escape and return keys for a ComboBox
document How to add ESQL function calls to the Informix Access Library
document Using char datatype in C++ Application for char(1) datatype of the database
document LAPACK.h++ with Math.h++ v0613 gives dependancy check failure on Solaris 2.5 and SunOS 4.1.x
document Using the schema from an existing table to create a new table
document Creating custom configurations with SPM 1.4
document RWTime/RWDate displays incorrect time and date
document The zApp isShown() function does not work correctly
document Creating image buttons of the correct size
document Adding a scroll bar to a non-overlay chart in JChart 2.1
document How do I trap the enter key in zComboBox (any type, portable solution)?
document How RWDecimal classes cast ints and doubles
document Modifying zApp 3.0 Makefiles to use Borland v5.02 compiler
document Horizontal Scroll Bars on Combo Boxes
document Can I use DBTools.h++ 3.1.0 with MSVC 5.0?
document Memory Leak in Threads.h++ with POSIX Threads
document Control object minimizes or disappears when moving to another window or dialog.
document Re-definition of parent table alias inside the sub-query of DML
document How can I do Oracle row-level locking with DBTools.h++?
document RAISE ERROR statement in my RWDBStoredProc never catches that error.
document Having problems with the server when using callbacks.
document Default and Explicit Connections and the DBTools Connection Pool
document Can't compile Math.h++ source files on SunOS4.1.3 with SunPro 4.0.1 compiler
document Why does JFactory flicker between the object manager and the integrated editor when I'm trying to edit the source?
document JFactory won't open; gives error
document In Chapter 4 of the tutorial, why can't I find the Menu Edit button?
document When compiling, why can't JDK find LogFontLayout.class?
document SPM doesn't install correctly under Windows
document 'Tried to dereference a null RWENode handle' error running SPM
document 'Unresolved external symbol opinit' runtime error
document Porting Tools Pro.h++ 1.1.2 to Linux
document Command line builds for Threads 2.0.1 on Solaris7 with SunPro 4.2
document When bulk reading a database column that allows NULL values, you must check for null in the receiving vector object.
document Improving the performance of an application that uses a lot of RWCStrings on the heap.
document Resizing a connection pool with the defaultConnections() method does not work
document Changing source code to support link time template instantiation
document Can't Enter Information Into Dialog Boxes
document can't insert an RWDBDateTime object into the database, formatted as a UTC time.
document How to deal with lost database connections?
document Euro conversion rates as of Jan 1, 1999.
document Error when Building DBTools Access library for Oracle8 with Microsoft Standard Library
document Rounding Errors in RWDecimal<T>
document I get error C2065: 'localtime_r' : undeclared identifier
document Tools.h++ compile error: C2065: 'localtime_r' : undeclared identifier
document Why won't the InfoBrowser contents update in response to selection changes in ClassView or FileView?
document I've installed Visual CASE and loaded a project. Now what?
document Why aren't the ATL and MFC libraries just loaded at startup?
document Blend.J's ShortcutBar demo throws exceptions
document Blend.J vertically-oriented sliders don't respond correctly
document How can I tell if my socket's peer closes the socket?
document RWCString::operator[] doesn't compile in MSVC
document Software Parts Manager doesn't run
document hasChanged() method never set to TRUE
document How do I kill all the running threads in RWThreadPool?
document No insertion (<<) or extraction (>>) operator to stream a bool
document Having problems using Standard C++ Library 2.x with Orbix 2000
document Building Tools.h++ 7.1.1 with Visual Age 5.x
document Linux multithreaded limitation with the Oracle8 805 client
document RWMetro Supported Platforms, Metro-Platforms
document SQL Server Access Library 3.x does not support bulk operations
document Color printouts of Grid.J lose the background colors
document Using Chart.J with Swing/jfc
document Using RWDBConnection in nested RWDBReaders
document Running setup.exe to install StudioJ
document getNumberRowCol() method in Grid.J throwing exceptions
document Assertion failure with DBTools.h++ examples
document Problems Compiling Math.h++ 6.x on HP, DEC and Windows 3.1
document Binary Files downloaded using Tools.h++ Professional are the Wrong Size
document How to build an RWDBCriterion with dynamic restrictions
document Oracle8 AL: How can I use a RWDBBlob with LONG RAW or BLOB columns that hold 32k or more
document How Do I Send a Basic Password to a Web Page?
document Building .h++ products with +DAportable
document Building zApp 3.0 as a DLL under Windows 16-bit crashes with Borland C++ 4.52
document Understanding Rogue Wave Build Codes
document Trying to rollback a transaction and it is not working
document Building on Solaris with the g++ compiler
document Sybase AL: syntax error when trying to bulk insert a vector object of size 1
document Unresolved symbol opinit at runtime
document Establishing a connection to Personal Oracle 8
document Compile problems with View.h++ v.1.3.0
document Building zApp 3.1 with MSVC 6.0
document Viewing a JFactory-made ComboBox with Netscape
document Adding a Timeout to an RWSocket::connect() Call
document Creating a Global Database Object
document Application crashing in the RWDBBlob destructor in DBTools 3.1.3 version
document RWZone::os() Returns Unexpected Result
document Errors installing products with SPM and RedHat Linux
document Accessing _servInst in zApp 3.x on NT
document Error compiling on SGI
document Unresolved symbols during linking with DBTools static libraries
document I get the error: [DBNOTFOUND] No access library
document Transaction control with DBTools 3.1
document Porting Math 6.1.8 to Redhat Linux 6.0 (Linux 2.2.5-15)
document Assertion failed, attempting to add intrusive link...
document How can I force RWDate to display a four digit year?
document Undefined symbols when linking the Math.h++ on SGI
document How Do Math.h++ Random Number Classes Generate Random Numbers?
document Compiling Math.h++ 6.x with MSVC 4.2 results in errors in the file <tt>rwlpbla.cpp</tt>
document Compiler trouble on IRIX 5.3
document My Sun compiler reports a Sig 10 error when building with Tools.h++
document When using Money.h++ as a DLL in my application, the symbol NaN is undefined during a link.
document Warning messages in link phase using Math.h++ in OWL project
document RWTime Can Only Represent Times Up to the Year 2037
document Visual CASE and Visual C++ 6 Service Pack 3
document How do I construct an unbounded Producer-Consumer Queue (or Stack)
document RWDBTracer and asString() method behave differently in DBTools 4.0.0
document Assigning the Empty String to an RWCString
document Fix for modeless MDI dialog when modal non-MDI Dialog is opened
document Table will not accept data
document How can I enable and disable radio buttons in a zRadioController?
document Accelerator keys in modal dialog
document Detecting changes in text in an edit line
document SPM error - file truncated
document How can I have a timestamp inserted with a row into my Oracle table
document JChart 1.0 will not print charts to the printer
document Compiler errors involving min() and max() macros
document Problems compiling .c files in the rogue/mathsrc directory
document RWDBCursor write fails at runtime sometimes with a syntax error
document MDI menus appending items instead of inserting
document Assertion failure in rw/tislist.cc, line 0
document Does DBTools.h++ support the Oracle Array type?
document Installing products with SPM as root accidentally deletes device /dev/null
document How do I stop a thread that is blocked on a system call or performing I/O?
document Trouble transferring binary files with FTP examples
document Beanbox integration for JWidgets
document Error installing parts using SPM
document Borland C++ 5.02 and SPM 1.3 Edition 5, November 1997
document Stored procedure on Sybase returns empty result set.
document How To build RW-Metro examples on WINNT4-MSVC6.0 ?
document Will DBTools.h++ operate properly with WIN32s?
document Is Informix transaction logging required?
document Memory leaks reported on different calls to DBTools.h++ or Tools.h++ methods.
document My RWIFtpClient is behaving erratically.
document Tools.h++ PC Patches
document _ex_keylock undefined symbol problem
document How do I access tables on different databases on the same server?
document Library Mismatch with MSVC4.x Compiler
document Installation order of Math.h++ and Tools.h++ is critical
document Moving Unix applications to Windows when using RPC
document Can DBTools.h++ run under DOS?
document Problems Running the Software Parts Manager (Unix)
document Link Error when Building DBTools Application
document When installing JFactory from InstallShield, why does it start installing other programs?
document Memory leaks in Tools.h++ code?
document DBTools.h++ generates a unique table alias each time a select statement is executed
document RWCString::toLower() function call creates runtime error.
document How to use the Rogue Wave Informix Access Library for DBTools.h++ in a multi-threaded application
document Creating Charts using ChartJ Component Beans
document How do I implement a select count (*) in DBTools.h++?
document Informix DB-Access Library fails to build on HP/aCC platforms.
document Integrating JWidgets into Symantec Visual Cafe version 2.0
document Error ORA-12154 while connecting to ORACLE via DBTools.h++.
document Executing multiple SQL statements with RWDBDatabase::executeSql() on ORACLE
document Does DBTools.h++ support Dynamic SQL?
document Error when executing a stored procedure.
document Establishing connectivity between an RDBMS and DBTools.h++
document Error ORA-06401 while connecting to ORACLE via DBTools.h++
document Are project files (IDE files) provided for building DBTools.h++ under Windows?
document Objective Grid/J: Which .class files do I deploy with OG/J?
document Using RWDBBlob with a stored procedure on Sybase
document rwMakeThreadFunctions and Passing Parameters By-reference
document Problems Running the Software Parts Manager (Windows)
document DB2 AL guide: RWDate should map with DB2 DATE type, instead of RWDBDateTime
document How can I get the port number from an RWSocket?
document Building Rogue Wave Products on Solaris 2.6 with SunPro 5.2
document How to produce an interleaved output in a multithreaded program?
document RWDBInserter.isValid() always returns OK even the SQL generated is incorrect
document Symbol Table Program fails to compile
document Reading a Sybase user defined type results in an error
document Installing a DBTools error handler
document The Software Parts Manager(SPM) cannot find CL.EXE
document JChart 2.1 Java Beans do not work well with certain Java IDEs
document How to change the font in a zTable cell and column and row label
document Determining what style bit is set from inside your program
document How do I move to the end of my grid
document Repository Read Error in Unix for SPM
document The details of RW_CENTURY_REQD
document Math.h++ Allocates Excessive Memory for Vector, Matrix, and Array Classes
document Distributing Visual CASE Diagrams and Projects
document Visual CASE Errors After Installation of Another Stingray Product
document Refreshing the Contents in the UML View
document Why is the UML InfoBrowser no longer updating its contents in response to selection changes?
document No UML Diagraming Without an Active Project
document Base Class Shows Up Empty in UML Diagram
document No Visual CASE Tab in the Tools|Options Property Sheet
document Removing UML modeling Information from a Workspace
document Add UML Model and Integrated UML Modeling Buttons
document Location of MFC and ATL UML Libraries
document Location of Visual CASE Project Files
document All Visual CASE Menus and Toolbars Disabled
document Using absolute paths with a URL in SerializableImage
document Control Size Problems from Win16 to Win32
document JChart pie piece value = 0 gives error message
document zApp 3.0.3 Online Documentation for Windows
document zHelp crashes when F1 is pressed twice
document What platforms and compilers will View.h++ build on?
document Adding a menu item dynamically when ID of DropDown is not known
document Assertion Failure Running DBTools Examples
document Drag-and-drop columns not working in cellgrid
document Which Tools.h++ classes require the Standard C++ Library?
document Why am I still connected to my database after my RWDBConnections have gone out of scope?
document How do I use an RWDBCursor for Write?
document Putting a static frame around a zStaticText
document Compiler error when building zApp as shared library
document Help and On Apply buttons call the same function from zNoteBookDialog
document zComboBoxFull on tab stop on zNoteBkDialog prevents tab from reaching any farther
document zFont does not display the size I specified.
document Unresolved externals when trying to link zApp application with MSVC4.2
document Building zApp with the CTL3D option turned on
document Multiple radio buttons are checked and will not disable.
document How to store/retrieve large blobs in SybaseCT
document Second constructor for zFormDialog
document How do I delete a row in a cell grid?
document In zHelp, the <A NAME=> tag does not work correctly
document Radio buttons on zNotebookDialog pages
document Making a vertically scrolling zEditBox
document How to call Oracle PL/SQL functions from DBTools
document Building Tools.h++ as a DLL with Borland C++ Builder
document zApp Error Codes
document Restore button does not work in MDI apps
document Memory leak when using zNotebook and zWizard dialogs
document Placing combo boxes on the tool bar
document NullPointerException with JWidgets 3.0 and Netscape 4.04
document Accessing command line arguments in zApp
document RWDBDateTime is Returning an Incorrect Time
document Sybase warning: Changed database context
document Updating sliders after calling setCurrent()