You can use a stored procedure, a procedure stored on a database server that executes and accesses the database locally, to return information to client applications. A stored procedure saves the overhead of having a remote application pass multiple SQL commands to a database on a server. With a single statement, a client application can call the stored procedure, which then performs the database access work and returns the results to the client application.
To create a stored procedure, you must write the application in two separate procedures. The calling procedure is contained in a client application and executes on the client. The stored procedure executes at the location of the database on the database server.
You can write stored procedures in any language supported by DB2 on your operating system. You do not have to write client applications in the same language as the stored procedure. DB2 transparently passes the values between client application and stored procedure.
You can use the DB2 Stored Procedure Builder (SPB) to help develop Java stored procedures. SPB can be integrated with popular application development tools, including Microsoft Visual Studio and IBM Visual Age for Java, or you can use it as a standalone utility. To help you create your stored procedures, SPB provides design assistants that guide you through basic design patterns, help you create SQL queries, and estimate the performance cost of invoking a stored procedure.
For more information on the DB2 Stored Procedure Builder, please see Overview of Stored Procedure Builder.