Are you using range partitioning for tables in your SAP system running on a Db2 for LUW database?
Do you have customized LOB inline settings for special columns on binary column types and want to migrate to the cloud?
Then this blogpost’s for you. Read on to learn how to take advantage of the latest enhancements to the report SMIGR_CREATE_DDL.

Overview

Range Partitioning on Db2

Let’s start with an example of how the DDL of a range-partitioned table can look like:

Example: Range-Partitioned Table DDL

If you want to migrate to the cloud, you usually have to perform a heterogeneous system copy using the R3load export/import method even though your database stays Db2 for LUW.

To preserve the range-partitioning definition of tables during the migration, you need to execute the report SMIGR_CREATE_DDL although you are working with an ERP system (non-BW system). SQL files are then also generated for range-partitioned tables, which contain the complete partitioning definition from the source system to be able to produce a 1:1 mapping on the target system.

Partition-specific tablespaces

You can specify a set of data, index, and long tablespaces separately for each partition. When using the Partitioning Administrator (SAP Note 1686102) to create the range partitioning for the application tables, each partition uses tablespace names derived from the table name.

Partition-specific tablespaces in this example are the following:

Data:   CDHDRLD     Index: CDHDRLI
Data:   CDHDR1D     Index: CDHDR1I
Data:   CDHDR2D     Index: CDHDR2I
Data:   CDHDR3D     Index: CDHDR3I
Data:   CDHDR4D     Index: CDHDR4I
Data:   CDHDRHD     Index: CDHDRHI

Since these tablespaces are usually not present in the target system, SMIGR_CREATE_DDL creates a new output file DB6_PART_TABLESPACES.LST that is used by the Software Provisioning Manager (SWPM) on the target system to automatically also create these partition-specific tablespaces on the target system during the installation.

For the time when the modified SWPM is not yet available, SMIGR_CREATE_DDL also creates a second file DB6_PART_TABLESPACES.DDL which contains Db2 command line statements to create the required tablespaces.

User-Customized LOB Inline Sizes

LOB inline sizes define the number of bytes for binary data types like CLOB, BLOB, and DBCLOB, which is inlined into the table rows in the data tablespace. The SAP system sets the inline length dependent on the data type, length, and SAP release level. You can modify the default inline length and specify your own values.

In the past, changes beyond the SAP default inline length got lost during SAP system copy.
But now, SMIGR_CREATE_DDL covers the following two cases:

• The LOB inline length was increased for columns where SAP already set a LOB inline length.
• The LOB inline length was set for columns where SAP does not set a LOB inline length.

Example

This is an example of a changed LOB inline size where the SAP default was 4096 and the new user-defined setting is 8192:

CREATE TABLE "SAPD01  "."ZTEST"  (
                  "POS" INTEGER NOT NULL WITH DEFAULT 0 ,
                  "IND" INTEGER NOT NULL WITH DEFAULT 0 ,
                  "FELD2" CLOB(101376 OCTETS) INLINE LENGTH 8192 LOGGED COMPACT )
                 COMPRESS YES ADAPTIVE
                 IN "D01#STABD" INDEX IN "D01#STABI"
                 ORGANIZE BY ROW;

Procedure

When performing a migration of an SAP system on Db2, perform the following steps:

1. Implement the correction instruction from SAP Note 3208238.
2. Execute the report SMIGR_CREATE_DDL.

This will create the required SQL files also for tables that use range partitioning or have a user-modified LOB inline length. In addition, it creates the file DB6_PART_TABLESPACES.LST that will be used by SWPM to automatically create all partition-specific tablespaces on the target system.

Generic Workflow of a Heterogeneous System Copy

Conclusion

If you use the enhanced SMIGR_CREATE_DDL on your source system, SQL files will also be created for
range-partitioned tables as well as for tables with a user-defined LOB inline length.
With this enhancement you will be able to these keep database-specific settings when migrating to the cloud.

Related links:

SAP Note 3221644 – DB6: Enhanced DDIC support for hidden columns
SAP Note 3208238 – DB6: Enhancements to SMIGR_CREATE_DDL for range
SAP Note 1686102 – DB6: DB6 Partitioning Administrator

Finally, I’d like to thank everyone who contributed to this blogpost, especially my colleagues from IBM & SAP
Dirk Nakott and Karen Kuck.

Feel free to provide feedback in the comment section.
Also check out our community page for more information on SAP on Db2 for LUW.