As you might have noticed, our SAP partner IBM has introduced indexes for column-organized tables (Db2 BLU Acceleration) with IBM Db2 11.1 M3 FP3. With IBM Db2 11.1 M3 FP3 iFix001SAP, you can now use indexes in SAP Business Warehouse with Db2 BLU Acceleration in controlled availability mode. Since this is a relatively new feature, I’d like to share some technical background and best practices from our development and support team about this topic.
What has changed?
Before IBM Db2 11.1 M3FP3, Db2 systems with BLU Acceleration used column scans to retrieve data. The exception to this were SQL statements where only a single record was expected as response: in this case, unique constraints on column-organized tables were used. When you converted SAP BW objects from row-organized to column-organized, unique primary indexes were converted to primary key constraints, other unique indexes were converted to unique constraints, and non-unique indexes were dropped. Therefore, it was not possible to convert row-organized DataStore objects with non-unique indexes to BLU Acceleration.
With the introduction of indexes, enhanced access options are now available for column-organized tables, which make sense when relatively few records need to be accessed efficiently. The Db2 optimizer decides whether to use an index scan or table scan. What’s more, row-organized DataStore objects with non-unique indexes can now be converted to BLU Acceleration.
The use of indexes means a more efficient access to BW objects when a small amount of data is read, such as DSO queries reading a small data volume and lookups on DSO or master data during ETL processing.
Let me illustrate this with two examples. Here’s an example of a non-unique index on a DataStore object. Even though it’s column-organized, a non-unique index is now possible:
And here’s an example of a transformation that contains DSO lookups on non-key fields or partial keys:
For which SAP BW objects can indexes be used?
Our SAP on Db2 development team invested quite some time to make indexes possible for column-organized SAP BW objects. You might be happy to hear that now for all SAP BW objects, the previous unique constraints can be converted into unique indexes and thus allow enhanced access plans. In addition, the following non-unique indexes are available for SAP BW objects with column-organized tables:
While these technical indexes mentioned cannot be changed by users, you can create user-defined indexes for column-organized active tables of standard DataStore objects and write-optimized DataStore objects. What’s more, DataStore objects with non-unique indexes can now also be converted to BLU Acceleration.
Once indexes are generally available in SAP systems running with Db2 BLU Acceleration, the system-generated indexes will be automatically generated when a new SAP BW object is created. Note, however, that during the phase of controlled availability of indexes, you will have to generate the indexes for BW objects using a program that we provide.
As before, use program DB6CONV to convert your SAP BW objects to BLU Acceleration. Non-unique indexes for DataStore objects will now be retained during conversion, and DB6CONV will also generate the technical (system-generated) indexes for write-optimized DataStore objects, InfoObjects and PSAs mentioned above.
What happens when you create indexes? – More technical background
When you have a closer look at your SAP BW objects, you might come across an additional index type, called modification state index. A modification state index is a system-generated index that is used for index scans with isolation CS on column-organized tables. This additional index was introduced together with indexes for column-organized tables. You might detect modification state indexes (type MDST) when you check the syscat.indexes entries on database level for your SAP BW objects. Note that they are only visible on database level. You cannot and need not change them.
Here’s an example: this is what happens when you create a column-organized BW object before the advent of modification state indexes:
CREATE TABLE "SAPD01 "."/BIC/SZMAT_CR2" (
"/BIC/ZMAT_CR2" VARCHAR(54 OCTETS) NOT NULL WITH DEFAULT ' ' ,
"SID" INTEGER NOT NULL WITH DEFAULT 0 )
ORGANIZE BY COLUMN;
ALTER TABLE "SAPD01 "."/BIC/SZMAT_SECI"
ADD CONSTRAINT "/BIC/SZMAT_SECI~0" PRIMARY KEY
("/BIC/ZMAT_SECI");
TABLE_NAME INDEX_SCHEMA INDEX_NAME INDEXTYPE
------------------ --------------- ------------------------- ---------
/BIC/SZMAT_SECI SYSIBM SQL171201144130981721 CPMA
/BIC/SZMAT_SECI SAPD01 /BIC/SZMAT_SECI~0 REG
After the introduction of indexes, you can see that it’s now possible to define a unique index, and there’s one additional index type MDST:
CREATE TABLE "SAPD01 "."/BIC/SZMAT_CR2" (
"/BIC/ZMAT_CR2" VARCHAR(54 OCTETS) NOT NULL WITH DEFAULT ' ' ,
"SID" INTEGER NOT NULL WITH DEFAULT 0 )
ORGANIZE BY COLUMN;
CREATE UNIQUE INDEX "SAPD01 "."/BIC/SZMAT_SECI~0" ON "SAPD01 "."/BIC/SZMAT_SECI"
("/BIC/ZMAT_SECI" ASC)
PCTFREE 0
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
ALTER TABLE "SAPD01 "."/BIC/SZMAT_SECI"
ADD CONSTRAINT "/BIC/SZMAT_SECI~0" PRIMARY KEY
("/BIC/ZMAT_SECI");
TABLE_NAME INDEX_SCHEMA INDEX_NAME INDEXTYPE
------------------ --------------- ------------------------- ---------
/BIC/SZMAT_SECI SYSIBM SQL171201144130981721 CPMA
/BIC/SZMAT_SECI SAPD01 /BIC/SZMAT_SECI~0 REG
/BIC/SZMAT_SECI SYSIBM SQL171201144131163702 MDST
Modification state indexes are generated for all column-organized tables of SAP BW objects if they have at least one index. (For more information about modification state indexes, see the IBM Knowledge Center.)
More Efficient Access Plans
With the availability of indexes for SAP BW objects, access plans are now more efficient. Let’s have a look at an example: With this index scan, you want to request the following:
SELECT NAME, ADDRESS
FROM T1
WHERE C1 = 67
Note there is a unique index PK(C1).
This is the access plan before 11.1 M3FP3:
This is the access plan after 11.1 M3FP3:
As you can see, CTQ(2), RCTQ(4), NLJOIN(3) and TBSCAN(6) have been replaced by FETCH(2).
What are the prerequisites for using indexes with BLU Acceleration?
Initially, we offer indexes as part of a controlled availability.
With the controlled availability, you will get information about how you can switch on and off indexes for BLU Acceleration. In addition, you can use an enhanced version of program DB6CONV to add indexes to column-organized BW objects – this allows you to carefully control for which SAP BW objects you would like to use indexes.
If you are interested in joining the controlled availability of indexes with BLU Acceleration, you are welcome to register for it: Simply open a ticket on component BW-SYS-DB-DB6.
Okumaya devam et...
What has changed?
Before IBM Db2 11.1 M3FP3, Db2 systems with BLU Acceleration used column scans to retrieve data. The exception to this were SQL statements where only a single record was expected as response: in this case, unique constraints on column-organized tables were used. When you converted SAP BW objects from row-organized to column-organized, unique primary indexes were converted to primary key constraints, other unique indexes were converted to unique constraints, and non-unique indexes were dropped. Therefore, it was not possible to convert row-organized DataStore objects with non-unique indexes to BLU Acceleration.
With the introduction of indexes, enhanced access options are now available for column-organized tables, which make sense when relatively few records need to be accessed efficiently. The Db2 optimizer decides whether to use an index scan or table scan. What’s more, row-organized DataStore objects with non-unique indexes can now be converted to BLU Acceleration.
The use of indexes means a more efficient access to BW objects when a small amount of data is read, such as DSO queries reading a small data volume and lookups on DSO or master data during ETL processing.
Let me illustrate this with two examples. Here’s an example of a non-unique index on a DataStore object. Even though it’s column-organized, a non-unique index is now possible:
And here’s an example of a transformation that contains DSO lookups on non-key fields or partial keys:
For which SAP BW objects can indexes be used?
Our SAP on Db2 development team invested quite some time to make indexes possible for column-organized SAP BW objects. You might be happy to hear that now for all SAP BW objects, the previous unique constraints can be converted into unique indexes and thus allow enhanced access plans. In addition, the following non-unique indexes are available for SAP BW objects with column-organized tables:
- Write-optimized DataStore objects (active tables): System-generated technical index ~RDR on the REQUEST, DATAPAKID, and RECORD fields
- Info objects (X tables): System-generated index on the fields OBJVERS and VARCHAR
- Info objects (X tables): System-generated index on the fields OBJVERS, DATETO, DATEFROM, and VARCHAR
- Persistent storage area (PSA): System-generated, semantic non-unique key (KEY)
While these technical indexes mentioned cannot be changed by users, you can create user-defined indexes for column-organized active tables of standard DataStore objects and write-optimized DataStore objects. What’s more, DataStore objects with non-unique indexes can now also be converted to BLU Acceleration.
Once indexes are generally available in SAP systems running with Db2 BLU Acceleration, the system-generated indexes will be automatically generated when a new SAP BW object is created. Note, however, that during the phase of controlled availability of indexes, you will have to generate the indexes for BW objects using a program that we provide.
As before, use program DB6CONV to convert your SAP BW objects to BLU Acceleration. Non-unique indexes for DataStore objects will now be retained during conversion, and DB6CONV will also generate the technical (system-generated) indexes for write-optimized DataStore objects, InfoObjects and PSAs mentioned above.
What happens when you create indexes? – More technical background
When you have a closer look at your SAP BW objects, you might come across an additional index type, called modification state index. A modification state index is a system-generated index that is used for index scans with isolation CS on column-organized tables. This additional index was introduced together with indexes for column-organized tables. You might detect modification state indexes (type MDST) when you check the syscat.indexes entries on database level for your SAP BW objects. Note that they are only visible on database level. You cannot and need not change them.
Here’s an example: this is what happens when you create a column-organized BW object before the advent of modification state indexes:
CREATE TABLE "SAPD01 "."/BIC/SZMAT_CR2" (
"/BIC/ZMAT_CR2" VARCHAR(54 OCTETS) NOT NULL WITH DEFAULT ' ' ,
"SID" INTEGER NOT NULL WITH DEFAULT 0 )
ORGANIZE BY COLUMN;
ALTER TABLE "SAPD01 "."/BIC/SZMAT_SECI"
ADD CONSTRAINT "/BIC/SZMAT_SECI~0" PRIMARY KEY
("/BIC/ZMAT_SECI");
TABLE_NAME INDEX_SCHEMA INDEX_NAME INDEXTYPE
------------------ --------------- ------------------------- ---------
/BIC/SZMAT_SECI SYSIBM SQL171201144130981721 CPMA
/BIC/SZMAT_SECI SAPD01 /BIC/SZMAT_SECI~0 REG
After the introduction of indexes, you can see that it’s now possible to define a unique index, and there’s one additional index type MDST:
CREATE TABLE "SAPD01 "."/BIC/SZMAT_CR2" (
"/BIC/ZMAT_CR2" VARCHAR(54 OCTETS) NOT NULL WITH DEFAULT ' ' ,
"SID" INTEGER NOT NULL WITH DEFAULT 0 )
ORGANIZE BY COLUMN;
CREATE UNIQUE INDEX "SAPD01 "."/BIC/SZMAT_SECI~0" ON "SAPD01 "."/BIC/SZMAT_SECI"
("/BIC/ZMAT_SECI" ASC)
PCTFREE 0
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
ALTER TABLE "SAPD01 "."/BIC/SZMAT_SECI"
ADD CONSTRAINT "/BIC/SZMAT_SECI~0" PRIMARY KEY
("/BIC/ZMAT_SECI");
TABLE_NAME INDEX_SCHEMA INDEX_NAME INDEXTYPE
------------------ --------------- ------------------------- ---------
/BIC/SZMAT_SECI SYSIBM SQL171201144130981721 CPMA
/BIC/SZMAT_SECI SAPD01 /BIC/SZMAT_SECI~0 REG
/BIC/SZMAT_SECI SYSIBM SQL171201144131163702 MDST
Modification state indexes are generated for all column-organized tables of SAP BW objects if they have at least one index. (For more information about modification state indexes, see the IBM Knowledge Center.)
More Efficient Access Plans
With the availability of indexes for SAP BW objects, access plans are now more efficient. Let’s have a look at an example: With this index scan, you want to request the following:
SELECT NAME, ADDRESS
FROM T1
WHERE C1 = 67
Note there is a unique index PK(C1).
This is the access plan before 11.1 M3FP3:
This is the access plan after 11.1 M3FP3:
As you can see, CTQ(2), RCTQ(4), NLJOIN(3) and TBSCAN(6) have been replaced by FETCH(2).
What are the prerequisites for using indexes with BLU Acceleration?
Initially, we offer indexes as part of a controlled availability.
With the controlled availability, you will get information about how you can switch on and off indexes for BLU Acceleration. In addition, you can use an enhanced version of program DB6CONV to add indexes to column-organized BW objects – this allows you to carefully control for which SAP BW objects you would like to use indexes.
If you are interested in joining the controlled availability of indexes with BLU Acceleration, you are welcome to register for it: Simply open a ticket on component BW-SYS-DB-DB6.
Okumaya devam et...