LOBs with DB2 for z/OS: Stronger and Faster

146 LOBs with DB2 for z/OS: Stronger and Faster

LOB fields are always created with a length of 1 GB, because this is the maximum length

which can be logged in V8. With DB2 9, this length is increased to the maximum size of a

LOB, 2 GB-1 bytes, and the SAP Data Dictionary is adapted accordingly.

The LOB table spaces are always created with BP40. This buffer pool allocation is again an

SAP specific choice that is part of a broader buffer pool management scheme. It does,

however, demonstrate a broad recommendation to separate LOB objects into distinct

separate buffer pools because of their different attributes. More information about buffer pool

considerations can be found in 8.3, “Buffer pools and group buffer pools” on page 246.

5.6.2 Java stack

The following database objects are created for each LOB column and partition on the JAVA

stack. Auxiliary objects to store the LOB data are created as they are in the ABAP stack with

the following differences:

򐂰 The table name is always used without a namespace prefix.

򐂰 Primary and secondary quantities are not specified. DB2 Space Extend Management is

used (sliding allocation).

򐂰 The column name is used without truncation.

5.6.3 DSNZPARMs for DB2 V8

In this section, we show the recommended SAP-related DSNZPARMs for an SAP system.

Table 5-5 shows the additional values for DB2 V8.

Table 5-5 DSNZPARMs for V8 (c) SAP AG; 2006

5.6.4 ROWID

DB2 V8 has introduced the concept of transparent ROWID. Prior to V8, the ROWID was

visible to INSERT or SELECT statements, which did not specify a field list. It can be

advantageous to normalize SELECT and INSERT statements to statements without a field

list, because this cuts down the number of statements against a table and thus reduces stress

on statement cache. This technique can be only used in SAP Unicode systems because the

non-Unicode tables might have been created on DB2 V7 and then migrated to DB2 V8.

Even with a transparent ROWID, the SAP data dictionary has to hide the ROWID field,

because it is visible with catalog SELECTs. SAP in practice hides the visibility of this column

from the ABAP programmers for the tables containing LOB columns. This is important for

application portability across SAP systems using different underlying DBMSs.

(c) SAP AG; 2006

Parameter Value Remark

LOBVALA 1000000K The size of the user storage for LOB

values (in KB). The recommended value

is 1 GB (1,000,000 KB).

LOBVALS 50000M The size of the system storage for LOB

values (in MB). The recommended value

is 50 GB (50,000 MB).

Chapter 5. SAP usage of LOBs 147

Sample DDL for CREATE TABLE statement

As an example, we create a very simple table with three columns and two LOB columns on

the Java stack. The definition is given in Example 5-8 in XML format, as used by the Java

data dictionary.

Example 5-8 Table definition for test table TSTLOBDDL in XML format (c) SAP AG; 2006

<?xml version=”1.0”?>

<Dbtable name=”TSTLOBDDL” creation-date=””>

<properties>

<author> </author>

<description language=””></description>

</properties>

<predefined-action></predefined-action>

<position-is-relevant></position-is-relevant>

<deployment-status></deployment-status>

<columns>

<column name = “F1”>

<position>1</position>

<dd-type>string</dd-type>

<java-sql-type>VARCHAR</java-sql-type>

<length>10</length>

<decimals>0</decimals>

<is-not-null>true</is-not-null>

<default-value> 1 </default-value>

</column>

<column name = “F2”>

<position>17</position>

<dd-type>binary</dd-type>

<java-sql-type>BLOB</java-sql-type>

<length>0</length>

<decimals>0</decimals>

<is-not-null>false</is-not-null>

<default-value></default-value>

</column>

<column name = “F3”>

<position>14</position>

<dd-type>string</dd-type>

<java-sql-type>CLOB</java-sql-type>

<length>1334</length>

<decimals>0</decimals>

<is-not-null>false</is-not-null>

<default-value></default-value>

</column>

</columns>

<primary-key>

<tabname>TSTLOBDDL</tabname>

<columns>

<column>F1</column>

</columns>

</primary-key>

</Dbtable>

With DB2 V8, this results in the DDL shown in Example 5-9.

(c) SAP AG; 2006