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
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