Rogue Wave Knowledge Base
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
print  Print
share  Share
Views: 106002
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 Report an issue


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