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

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.