| | | | Browse by category |
Article ID: 904
Last updated: 01 Feb, 2008
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
DBTools Application to call stored procedure
A native DB2 test case to test your stored procedure
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
- Important Things To Note
- Prerequisites for building a stored procedure
- Stored Procedure Example
- Registering your stored procedure
- DBTools Application to call stored procedure
- A native DB2 test case to test your stored procedure
- 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
- 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.
- You will also need write permissions on the directory $DB2DIR/sqllib/function
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
grant execute on packageThe executable will be the name of your .sqC file (without the extension).to public
create procedure outtest(OUT out_string VARCHAR(700)) EXTERNAL NAME 'drprocs!outtest' FENCED LANGUAGE C PARAMETER STYLE DB2DARI NULL CALLIn this example:
- outtest - This is the name the database will call the stored procedure. It should be the same as the stored procedure function name.
- (OUT out_string VARCHAR(700)) - The parameter list for the stored procedure, including type (in, out, in/out), a variable name, and a native database type.
- EXTERNAL NAME 'drprocs!outtest' - This specifies the path of the stored procedure. The syntax is the name of the stored procedure executable, an exclamation mark and then the name of the stored procedure function. Important note: DB2 stored procedure executables can have multiple stored procedures contained in them. Provided they all have unique function names, you can register them all as separate stored procedures with the database using this syntax.
- FENCED - This indicates if the procedure is fenced or unfenced. This example only generates fenced procedures. A fenced procedure runs in an independent memory block that protects the database from stored procedure crashes. A fenced procedure is slower than an unfenced procedure but safer.
- LANGUAGE C - This indicates to the database the language that the stored procedure was written in. Options are C or JAVA.
- PARAMETER STYLE DB2DARI - This specifies to the database the convention used to pass parameters to the stored procedure. Options are DB2DARI (which is used for C/C++) and DB2GENERAL (which is used for Java)
- NULL CALL - Allows the stored procedure to be called even if the parameters are null.
DBTools Application to call stored procedure
*************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
Call the build script in the following manner:
ksh bldCC <.sqC file without extension>
*************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
Article ID: 904
Last updated: 01 Feb, 2008
Revision: 1
Views: 121931
Posted: 24 Jun, 1999 by
Dean J.
Updated: 01 Feb, 2008 by
Dean J.
Others in this category
Powered by KBPublisher (Knowledge base software)