Programming Considerations | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 10. Accessing Distributed Data

Programming Considerations

When working with more than one location, we need to find a way to identify which DB2 holds the data we want to process. With application directed data, 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, then 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 if you were already connected. The previous connect will simply be put in the current state. However, when using SQLRULES(STD), the control is more strict. When you are already connected to a location, the SET CONNECTION should 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 problems as long as you execute the SQL against the proper DB2. The same comment applies to the precompiler the option: SQL(ALL) will make the precompiler flag the statements, but it will not prevent the creation of the DBRM.

Beware that 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-OS/390 target, you may find some differences. This can work to your advantage, as you can exploit the full capabilities of your target database.

Application Design Options

The DRDA protocol allows an application to perform any SQL statement as if the database is local to the application. In Figure 10-2 we see an example of a program connecting to a remote database and issuing several SQL statements. We notice 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 go when the context allows this to happen without creating data consistency problems.

Figure 10-2. Accessing a remote database.

graphics/10fig02.gif

Let's assume that the client program doesn't need any interaction between the start and end. What keeps us from running the entire program on the server, and not just 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 only needs to connect to the database server and start the stored procedure (see Figure 10-3).

Figure 10-3. Stored procedure flow.

graphics/10fig03.gif

Stored Procedures

In the stored procedure programming model, the application logic is moved from the client program to the database server. It is the responsibility of the database server to invoke the stored procedure program and handle the parameters flowing to and from the stored procedure. The actual implementation of the environment where the program runs depends on the platform of deployment. On OS/390 you can either configure a stored procedure address space or you can use address spaces that are controlled by the Workload Manager. The second option is by far the best 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 OS/2 there is no need to define a separate runtime environment.

The DB2 stored procedure builder (see Figure 10-4) provides a crossplatform development environment, allowing you to code programs either in Java or as Persistent Stored Modules (PSM). PSM is the programming language defined in the SQL99 standard and is very similar to Basic. The PSM code is translated by DB2 into a C program, resulting in much better performance and the ability to run static SQL.

Figure 10-4. Stored procedure builder.

graphics/10fig04.gif

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 OS/390, it is better to develop the stored procedure outside the scope of the stored procedure builder and use your existing application development environment (see Figure 10-5). IBM already has delivered integration with the MQ series, thereby enriching the capabilities of the stored procedures programming environment. Have a look at the MQ UDF wizard, which you will find in the stored procedure builder.

Figure 10-5. Non-DB2 resources.

graphics/10fig05.gif


Team-Fly

Top