DB2 for z/OS and WebSphere: The Perfect Couple

Chapter 5. DB2 application development in a WebSphere environment 111

5.1.2 Best practices

Although there are some instances when JDBC may be more appropriate, the following topics

provide some insight as to why SQLJ is highly regarded, and recommended, as the DB2-Java

application programming solution, especially in a DB2 for z/OS environment.

Superior performance with customized SQLJ

Customized SQLJ reaps the performance benefits associated with the static SQL model.

Authorization checking against the user ID running the program is the only step performed

before executing the SQL statement. As opposed to JDBC and dynamic SQL, there is no

need for DB2 to parse the SQL statement and determine an access path each time the SQL

statement is executed at run time. With SQLJ, as illustrated in Figure 5-1 on page 118 and

Figure 5-2 on page 119, this is done only once during bind time, thereby avoiding the

performance hit at run time for each execution. In large part, this is the reason why

customizing SQLJ applications, although an optional step, is highly recommended. However,

running uncustomized SQLJ can have its place as well.

Uncustomized SQLJ applications are treated as dynamic SQL at run time. This means that, at

run time, the SQL statement will be parsed and an access path will be calculated each time

the SQL statement is executed. This can be very beneficial during the development phase of

an SQLJ application. It also provides an excellent testing ground for performance tuning if you

wish to run different flavors of SQL statements. After determining optimal SQL statement

performance, you can customize the SQLJ application, which will then run as static SQL. This

is one very nice advantage of using SQLJ.

Improved authorization control

The ability to perform authorization checks at the program level is another benefit of running

SQLJ applications within the static SQL model. When using dynamic SQL, the user ID under

which the program is run must have all the necessary privileges required to execute each

SQL statement in the program. For example, take an application that uses dynamic SQL to

insert rows into a table, update rows from another table, and delete rows from a third table. In

this scenario, each user ID that runs the program requires a minimum INSERT, UPDATE, and

DELETE privilege on the specific DB2 table the SQL statement references.

When running dynamic SQL programs, granting privileges at the user level is a major security

exposure. This is because the user has the authority to utilize those same privileges at any

time. They are not restricted to only running the program. Once the user has been granted the

necessary privileges and a connection has been established to the DB2 subsystem, there is

nothing protecting the tables from being manipulated by the user. Instead of using the Java

application, a user can also use any other tool to access the data.

Another issue related to programs running dynamic SQL is managing the authorized user

IDs. Although this task can be somewhat circumvented by using group IDs, and granting the

necessary privileges to the group IDs, it remains a tedious, and potentially unforgiving task if

done incorrectly.

This security exposure can be addressed when using the static SQL model. With static SQL,

any given user requires only the EXECUTE authority on the application package (or DB2

plan), instead of all the privileges to execute INSERTs, UPDATEs, and DELETEs against the

DB2 tables. The user ID of the owner of the package (or plan) is used for authorization

checking against SQL statements executed by the application. The user ID of the person