Programming Considerations

When working with more than one location, you need to find a way to identify which DB2 holds the data you want to process. With application-directed data distribution, before executing the SQL, you identify to DB2 the location to which the SQL statement should be directed. This can be done either explicitly, by coding a CONNECT statement in the application, or by relying on the CURRENT SERVER register. The value of the CURRENT SERVER register is initially determined by the BIND option CURRENT SERVER. If this was not specified, CURRENT SERVER is set to blanks until you issue a CONNECT TO statement.

When a package is bound with DBPROTOCOL(DRDA) and you use a three-part name, DB2 will issue the CONNECT statement under the covers. The SQLRULES bind option will also determine how DB2 will handle connection management. When using SQLRULES(DB2), you can connect to a database location even though you were already connected. The previous CONNECT will simply be put in the current state. However, with SQLRULES(STD), the control is more strict. When you are already connected to a location, the SET CONNECTION must be used to switch between connections.

It is possible that your bind will get errors if your program contains SQL statements that use more than one DB2. Some of the tables may not be defined at all locations or could have different definitions. It is important to bind your package with SQLERROR(CONTINUE) to allow the bind to ignore these errors. The errors are not causing any problem as long as you execute the SQL against the proper DB2. The same comment applies to the precompiler option: SQL(ALL) will make the precompiler flag the statements, but it will not prevent the creation of the DBRM.

When DRDA is used, the SQL statement will be processed by the remote location and will adhere to the syntax rules of the target database. When using a non-z/OS target, you may find some differences. This can work to your advantage, as you can exploit the full capabilities of your target database.

The DRDA protocol allows an application to perform any SQL statement as if the database were local to the application. Figure 14-1 is an example of a program connecting to a remote database and issuing several SQL statements. We note that the application requester already performs some optimization, as not all fetches seem to access the remote database. The second fetch finds the data on the client without having to access the server. This concept is called block fetch. Block fetch sends a number of rows in one transmission when the context allows this to happen without creating data-consistency problems.

Figure 14-1. Accessing a remote database

Let's assume that the client program doesn't need any interaction between the start and the end. What keeps us from running the entire program on the server and not only the SQL statements? We just invented the concept of a stored procedure. The program logic and SQL statements are ported to the database server, and the client program simply needs to connect to the database server and start the stored procedure (see Figure 14-2).

Figure 14-2. Stored-procedure flow

In the stored-procedure programming model, the application logic is moved from the client program to the database server. The database server has the responsibility of invoking the stored-procedure program and handling the parameters flowing to and from the stored procedure. The implementation of the environment in which the program runs depends on the platform of deployment. On z/OS, you can either configure a stored-procedure address space or use address spaces that are controlled by the workload manager. The second option is by far the better technique, as you have much more control over the resources that are used and not all procedures that are running will share the same address space. On UNIX, Windows, or Linux, you do not need to define a separate runtime environment.

The DB2 Development Center provides a cross-platform development environment, allowing you to code programs either in Java or as SQL Procedure Language. The SQL Procedure Language is defined in the SQL99 standard and is very similar to BASIC. The SQL Procedure code is translated by DB2 into a C program, resulting in much better performance and the ability to run static SQL.

If you don't need the portability of the stored procedure but need to exploit non-DB2 resources, such as DL/I databases, sequential files, CICS transactions, or any other resource on z/OS, it is better to develop the stored procedure outside the scope of the DB2 Development Center and use your existing application-development environment. (See Figure 14-3.) IBM already has delivered integration with the MQSeries, thereby enriching the capabilities of the stored-procedures programming environment. Have a look at the MQ UDF wizard, in the DB2 Development Center.

Figure 14-3. Non-DB2 resources