Chapter 5. SAP usage of LOBs 157
Table 5-7 dbsl_lib profile parameters for LOB handling (c) SAP AG; 2006
5.12 Performance measurements
Some measurement results are included here from different sources.
5.12.1 Locks and SELECT
The IBM/SAP performance team used the SAP program R3load (a utility often used to export
tables in SAP platform independent format) as a means to measure repeated workloads
against LOBs in DB2 V8 and DB2 9 subsystems. The tool is used to export records from a
table with a LOB column into a file and is executed on a remote network-connected client.
The purpose here is to evaluate the impact of the new locking mechanisms for LOBs in DB2 9
showing improvements in the context of a real workload.
The measurement data shows the improvements with reduced locking on LOB objects in
DB2 9.
(c) SAP AG; 2006
Parameter Recommended value Description
dbs/db2/lob_buf_size
dbs_db2_lob_buf_size
64,000 bytes Size of the local LOB buffer. Minimum value
is 32,000 bytes, maximum 2,000,000 bytes.
dbs/db2/use_eda
dbs_db2_use_eda
1 Possible values are [0,1].
Use 0 to turn off the extended da feature.
dbs/db2/use_drda_lob_handling
dbs_db2_use_drda_lob_handling
0 Possible values are [0,1].
Use 1 to turn on the DB2 Connect CLI LOB
streaming. Not supported with DB2 V8.
dbs/db2/chaining
dbs_db2_chaining
20 Possible values [0,MAX_INT].
Use 0 to turn off chaining. For values
greater than 0, this number gives the
minimum value of rows which have to fit into
the dbsl_lib array buffer before chaining is
used.
dbs/db2/lob_free_buffer
dbs_db2_lob_free_buffer
1,000 Possible values [0,MAX_INT]. Number of
locators to fit into free locator buffer. Set to
0 to turn off the free locator buffer.
dbs/db2/max_lob_free_length
dbs_db2_max_lob_free_length
1 GB Possible values [0,2048]. Threshold for
storage which locators in locator free buffer
can hold before freed.
dbs/db2/sql_trace
dbs_db2_sql_trace
0 Possible values [0,4].
0 corresponds to dbsl trace off.
1 corresponds to dbsl statement trace.
2 is unused.
3 corresponds to dbsl statement and data
trace, data is truncated.
4 corresponds to dbsl statement and data
trace, data is not truncated.
dbs/db2/cli_trace_value
dbs_db2_cli_trace_value
0 Possible values [0,1].
Set to 1 to turn on CLI trace.
dbs/db2/cli_trace_dir
dbs_db2_cli_trace_dir
/usr/sap/<SAPSID>/<INSTANCE>/work
Directory to which CLI traces should be
written. If not set, no CLI traces are written.
158 LOBs with DB2 for z/OS: Stronger and Faster
This improvement shows through in two distinct ways: first, in the CPU resource within DB2
processing, and secondly, the number of round-trips required between the remote client and
DB2 was reduced. Both of these factors lead to reduced run time of the application task, and
these improvements require no change to the application execution. It should be noted that
this example uses an application and underlying table specifically chosen because it contains
data predominantly in LOB columns, but nonetheless, it proves significant gains with DB2 9.
The first measurement shown in Figure 5-13 shows the reduction in elapsed time of the test
job, from 5,193 seconds to 4,459 seconds, a total 14% reduction.
Figure 5-13 SAP R3load test case elapsed time (c) SAP AG; 2006
Figure 5-14 shows the dramatic reduction in lock requests. The reduction is comprised
entirely of the elimination of LOB-lock requests for lock and unlock of the LOB row.
Figure 5-14 R3load test case reduced locks (c) SAP AG; 2006
What remains is the lock on the base table row as expected, so for each row processed, two
of the three lock requests are eliminated. This demonstrates the significant improvement that
this change makes, particularly in a DB2 data sharing environment.
(c) SAP AG; 2006
V8 V9
DB2 Version
0
1
2
3
4
5
6
Seconds Elapsed (K)
R3Load Test case - Time
14% Reduction
67% Reduction
V8 V9
DB2 Version
0
1
2
3
4
5
6
Lock & Unlock requests (million)
Reduction in lock counts

Get LOBs with DB2 for z/OS: Stronger and Faster now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.