Knowledge to identify when to use stored procedures

In a basic DB2 client/server environment, each time an SQL statement is executed against a remote database, the statement itself is sent through a network from the client workstation to a database server. The database server then processes the statement, and the results are sent back, again through the network, to the client workstation. This means that two messages must go through the network for every SQL statement executed. Client/server application development focuses on breaking an application into two separate parts, storing those parts on two different platforms (the client and the server), and having them communicate with each other as the application executes. This allows the code that interacts directly with a database to reside on a database server, where computing power and centralized control can be used to provide quick, coordinated data access. At the same time, the application logic can reside on one or more client workstations so that it can make effective use of all the resources that a workstation has to offer without causing a bottleneck at the server.

If you have an application that contains one or more transactions that perform a relatively large amount of database activity with little or no user interaction, those transactions can be stored on the database server as a stored procedure. When a stored procedure is used, all database processing done by the transaction can be performed directly at the database server. And, because a stored procedure is invoked by a single SQL statement, fewer messages have to be transmitted across the network-only the data that is actually needed at the client workstation has to be sent across. It is important to note that a stored procedure can be invoked by a user-defined function, and a user-defined function can be invoked as part of a stored procedure.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz