LOBs with DB2 for z/OS: Stronger and Faster

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