Rogue Wave Knowledge Base
Rogue Wave Knowledge Base
Knowledgebase | News | Ask a Question | ... Browse by category
Knowledge base / Legacy / .h++ / How to create and run Stored Procedures in DB2

How to create and run Stored Procedures in DB2

Article ID: 904
Last updated: 01 Feb, 2008
Article ID: 904
Last updated: 01 Feb, 2008
Revision: 1
Views: 121655
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
Article ID: 904
Last updated: 01 Feb, 2008
Revision: 1
Views: 121655
Posted: 24 Jun, 1999 by Dean J.
Updated: 01 Feb, 2008 by Dean J.
Prev
Next
« In zHelp, the <A NAME=> tag does not work correctly
How do I delete a row in a cell grid? »

Others in this category

rss
Powered by KBPublisher (Knowledge base software)