db2 blu shadow tables with sap v1.pdf

IBM Americas Advanced Technical Support

© Copyright IBM Corp, 2015 All rights reserved

IIBBMM TTeecchhnniiccaall BBrriieeff

CCoonnffiigguurriinngg DDBB22®® SShhaaddooww TTaabblleess wwiitthh SSAAPP®®

MMaarrkk GGoorrddoonn

IIBBMM SSoolluuttiioonnss TTeecchhnniiccaall SSaalleess SSuuppppoorrtt

VVeerrssiioonn:: 11..00

DDaattee:: JJaannuuaarryy 2222,, 22001155



1. Disclaimers..................................................................................................................................................... 3

2. Trademarks ................................................................................................................................................... 3

3. Version Changes............................................................................................................................................ 3

4. Feedback ........................................................................................................................................................ 3

5. Introduction ................................................................................................................................................... 4

6. Overview of Configuration Steps ................................................................................................................ 4

6.1. Check and Install Software prerequisites ............................................................................................... 5

6.2. Set configuration parameters................................................................................................................... 5

6.3. Create bufferpool and tablespace for shadow tables ............................................................................. 7

6.4. Create Shadow Tables ............................................................................................................................ 10

6.5. Configure CDC........................................................................................................................................ 11

6.6. Execute queries and verify shadow table access .................................................................................. 24

6.7. Starting and stopping CDC .................................................................................................................... 26

7. Summary ...................................................................................................................................................... 26



1. Disclaimers Using DB2 with BLU Acceleration Shadow tables is not currently supported by SAP.

This paper documents configuring SAP ERP with shadow tables in DB2 with BLU Acceleration, to support ad-

hoc queries on line-item data. It is a demonstration of feasibility, not a best practices guide.

2. Trademarks DB2 ® is a registered trademarks of IBM Corporation.

SAP ® and SAP NetWeaver ® are registered trademarks of SAP SE.

3. Version Changes Version 1.0: January 22, 2015 – initial version

4. Feedback Please send comments or suggestions for changes to [email protected].

mailto:[email protected]



5. Introduction This paper supplements the IBM document "Improve performance of mixed OLTAP workloads with DB2

shadow tables", which is available on IBM Developerworks at

http://www.ibm.com/developerworks/data/library/techarticle/dm-1409oltap-db2-shadow-tables/index.html.

That document describes the architecture of shadow tables in DB2. Please review it.

DB2 BLU columnar shadow tables can be used to optimize ad-hoc query on large tables, such as performing

ad-hoc queries on line-item data in a transactional ERP system. With BLU columnar tables, no ETL process is

needed to move the line-item data to an external system optimized for query -- queries can be executed directly

in the system where the line-item data resides.

We add information on the steps necessary to prepare DB2 using DBACOCKPIT and the SAP and DB2

configuration necessary to enable using shadow tables on an SAP system. We also demonstrate configuration

of CDC using the Management Console, rather than the command line configuration shown in the

Developerworks document.

Using DB2 shadow tables does not require any SAP customization. DB2 chooses whether to execute each SQL

statement on the source row table or its columnar shadow table depending on which should perform better.

Since no application changes are needed, one can test BLU shadow tables to optimize any program. BLU

shadow tables are most likely to help programs with varied input parameters that access many rows from tables,

such that indexes cannot be created to optimize all program variants. When SQL joins two or more tables, all

tables must have columnar shadow tables, in order for that SQL to be executed on the shadow tables.

The shadow tables are replicated via CDC reading the log, so changes in the shadow tables are asynchronous

from the application changes to the row tables. As with architectures that use trigger based replication to

columnar tables, the columnar shadow tables may be a few seconds or minutes behind the state of the row

tables.

6. Overview of Configuration Steps We follow the steps described in "Improve performance of mixed OLTAP workloads with DB2 shadow tables",

making changes as required for SAP. Rather than command line configuration, for the most part we use

SAPGUI or InfoSphere Change Data Capture Management Console.

In this paper, we will:

Check and Install Software prerequisites - section 6.1

Set configuration parameters - section 6.2

Create bufferpool and tablespace for shadow tables - section 6.3.

Create Shadow Tables - section 6.4.

Configure CDC - section 6.5.

Execute queries and verify shadow table access - section 6.6.



6.1. Check and Install Software prerequisites As described in "Improve performance of mixed OLTAP workloads with DB2 shadow tables", DB2 10.5

FP4 is required for shadow tables. SAP has a special build, "DB2 10.5 FP4SAP", that should be

downloaded from SAP SMP for DB2 update or install.

See SAP note 2047006 regarding SAP kernel levels required for using INTRA_PARALLEL=YES with

OLTP systems. INTRA_PARALLEL=YES is used for columnar table access plans. If an older kernel is

used, then DB2 parallelism will not be used for SQL on the columnar shadow tables. Our test environment

used 7.41 SPl (patch number) 100.

InfoSphere CDC Access Server Interim Fixes are cumulative. We installed InfoSphere CDC Access Server

Interim Fix 8 on the SAP DB server.

InfoSphere CDC Interim Fixes are not cumulative. We installed the base product InfoSphere for DB2 for

LUW 10.2.1 on the SAP DB server, and then installed Interim Fix 15 to update it.

InfoSphere CDC Management Console Interim Fix 8 was installed on a Windows PC.

Depending on the release of SAP, either SYSTOOLS or SYSTOOLSPACE may be used for the TOOLS

tablespace. Check which it is. This will be needed later. It was SYSTOOLSPACE on our test system.

Figure 1: Check TOOLS tablespace

6.2. Set configuration parameters The DB2 test system has 64GB memory. Using DB2 command or DBA cockpit, we set the following DBM

and DB parameters for our test system BED. "Improve performance of mixed OLTAP workloads with

DB2 shadow tables" describes the recommended relationship between them.

update dbm cfg using INSTANCE_MEMORY 14000000

update dbm cfg using DBM INTRA_PARALLEL YES

update db cfg for BED using SHEAPTHRES_SHR 7000000

update db cfg for BED using SORTHEAP 700000

update db cfg for BED using UTIL_HEAP_SZ 500000



"Improve performance of mixed OLTAP workloads with DB2 shadow tables" recommends setting

DB2_EXTENDED_OPTIMIZATION="OPT_SORTHEAP_EXCEPT_COL 10000", but this parameter is

already set for DB2WORKLOAD for SAP.

Figure 2: DB2_EXTENDED_OTIMIZATION before

We will update this registry variable setting with db2set

DB2_EXTENDED_OPTIMIZATION="OPT_SORTHEAP_EXCEPT_COL

10000,NLJOIN_KEYCARD,GY_DELAY_EXPAND 1000".

Display the new setting in DBACOCKPIT.

Figure 3: DB2_EXTENDED_OPTIMIZATION after

"Improve performance of mixed OLTAP workloads with DB2 shadow tables" has configuration instructions

for special register settings in the section" Enabling query routing to shadow tables". Since SAP issues the

SQL from the app server, we cannot use these special register settings for shadow table configuration

with SAP. Instead, we updated the DB parameters for our test DB "BED" with the following commands:

update db cfg for BED using DFT_DEGREE ANY

update db cfg for BED using DFT_MTTB_TYPES REPLICATION

update db cfg for BED using DFT_REFRESH_AGE 120

Since the default optimization level for SAP is 5, which allows optimization with shadow tables, the

optimization level does not need to be changed.



6.3. Create bufferpool and tablespace for shadow tables DB2 recommends using 32K page size tablespace with extent size of 4. Since our system uses the default

16K page size, we create a new bufferpool and tablespace with DBACOCKPIT.

Figure 4: Adding Buffer Pool

In Figure 4 Press “Add”.

Figure 5: Buffer Pool

In Figure 5, press "Add" to create the new Buffer Pool.



Now, when we display buffer pools in DBACOCKPIT, we have two.

Figure 6: Buffer Pools display

Now create a 32K page size Tablespace.

Figure 7: Add Tablespace

In Figure 7, press "Add".



Figure 8: Table space Technical Settings

In Figure 8, specify a Table space Name, select Page Size 32 KB, set extent size to 4, and for Buffer Pool,

specify the 32K buffer pool created above.



Figure 9: Table space Storage Parameters

In Figure 9, specify the size. Then refresh (to generate the SQL command) and press "Add ...".

As DB2 user (db2bed in our system), create catalog objects for replication, using TOOLS tablespace

determined in Figure 1.

Figure 10: SYSINSTALLOBJECTS

Check table is there.

Figure 11: REPL_MQT_LATENCY

6.4. Create Shadow Tables For each table that will be optimized with columnar shadow tables, do the following.



First, determine the unique key of the source table. A unique key constraint will be created on the shadow

table. In this example, we use SE11 to check the unique key on BKPF.

Figure 12: BKPF Key

The key columns are (MANDT, BUKRS, BELNR, GJAHR).

Create the shadow table and its primary key. In our sample system, SAPBED is the schema.

BED#COLUMNN is the tablespace for the shadow tables.

create table SAPBED.BKPF_SHADOW as (select * from SAPBED.BKPF) data initially deferred

refresh deferred enable query optimization maintained by replication organize by column in

"BED#COLUMN" index in "BED#COLUMN"

set integrity for SAPBED.BKPF_SHADOW all immediate unchecked

alter table SAPBED.BKPF_SHADOW add constraint BKPF_SHADOW_PK primary key

(MANDT, BUKRS, BELNR, GJAHR)

In our tests, we created columnar shadow tables for BKPF, FAGLFLEXA, MKPF, MSEG, and BSIS.

6.5. Configure CDC Create the directory for CDC replication for our database BED.

Figure 13: CDC refresh directory

Start CDC instance configuration with dmconfigurets.

Figure 14: dmconfigurets start



Figure 15: dmconfigurets intance configuration

In Figure 15, specify Instance Name, and quota. Database username/password is the unix DB2 userid

created by the SAP DB install. Choose a Metadata Schema. Refresh loader path is the directory created in

Figure 13. Press OK.

Figure 16: create cdc instance popup

Figure 17: create cdc instance popup 2

Press "Yes".

Figure 18: create cdc instance popup 3

Press "No".



Set CDC instance parameters.

Figure 19: dmset parameters

Start the CDC instance.

Figure 20: Start CDC with dmts64

Start access server.

Figure 21: Access server startup

From directory in Figure 21, create administrative user, datastore, and link admin user, datastore and DB2

instance.

./dmcreateuser cdc-admin "N/A" "Administrator" cdc-admin-password sysadmin true false false

./dmcreatedatastore dscdcbed "db2bed OLTPDB Shadow Tables" atssg148 10901

./dmaddconnection cdc-admin dscdcbed BED db2bed db2bed-password false true false true

Login to CDC with Management Console

Figure 22: CDC Management Console



Login using the userid created with dmcreateuser above.

Figure 23: CDC login

Figure 24: Management Console first startup

At this point, there are no subscriptions in Figure 24. We will create one. Press "Configuration", then right

click for the menu of activities. Choose "New Subscription".



Figure 25: New subscription

Figure 26: New subscription

In Figure 26, enter a subscription name and choose the target, which in our configuration is the same as the

source. Press "Advanced Settings".



Figure 27: Advanced Settings

In Figure 27, select "Mark subscription as persistent". Press OK, then OK again.

Figure 28: Subscription created

Press "Yes".



Next, we will show mapping one row table to a shadow table.

Figure 29: Mapping type

Press "Next".

Figure 30: Select source schema

Expand the SAP schema (in our case SAPBED).



In Figure 31, filter for the source table. In this example we will create a columnar shadow table for BSIS.

Figure 31: Filter tables

Figure 32: Select table

In Figure 32, select "Replicate" for BSIS and press "Next".



Figure 33: Select existing or create new

Since we created the columnar shadow tables earlier, in Figure 33, select "Map to existing target tables",

and press "Next".

Figure 34: Select schema for new target tables

In Figure 34, expand the list of tables for SAPBED.



Figure 35: List target tables

In Figure 35, enter search string. Press "OK".

Figure 36: Select target column table

In Figure 36, select the column table created earlier, and press "Next".



Figure 37: Complete mappings

In Figure 37, check the mapping is correct, and press "Next".

Figure 38: Set replication method

Choose "Mirror", and press "Next".



Figure 39: review mappings

Press "Finish".

In Figure 40, we now see that BSIS is mapped to BSIS_SHADOW.

Figure 40: Table mapping BSIS

BSIS mapped, but replication is not active yet - the Status is "Refresh". After we map other tables, we will

start replication. In addition to BSIS, we mapped FAGLFLEXA, BKPF, MKPF, MSEG.



In Figure 41, right click on the subscription and select "Start mirroring".

Figure 41: Start mirroring

Figure 42: Start mirroring popup

Press "OK".

Figure 43: Check status

As each table is mirrored, its status changes from "Refresh" to "Active".



When all are in synch, the subscription's replication state will be "Mirror Continuous".

Figure 44: Subscription mirrored

6.6. Execute queries and verify shadow table access We used SQVI to create sample SQL to demonstrate shadow table usage. With a columnar copy of the

data in addition to the original row organized tables, the DB2 optimizer can choose whether to use the row

or column tables, based on the characteristics of the SQL.

The first three SQL examples are MKPF MSEG joins, such as are done in MB51 (material document list).

In this example, there is a filtering local predicate (MANDT=) on an indexed column of the row table

MSEG. DB2 chooses the row tables and index access.

Figure 45: DB2 chooses indexed row tables for MATNR =



But when we modify this SQL statement to specify MATNR LIKE, then DB2 chooses the columnar shadow

tables. (Optimizer choice of row or column tables depends on many factors.)

Figure 46: Shadow table access

In Figure 47 neither AEDAT nor KUNNR is indexed, and DB2 chooses the column table join.

Figure 47: MKPF MSEG join



Similarly, this query on FAGLFLEXA in Figure 48 has local predicates on columns that are not indexed,

and DB2 chooses to read the columnar shadow table.

Figure 48: FAGLFLEXA SQL

6.7. Starting and stopping CDC Since CDC starts DB2 applications, you need to shut CDC and Access Server down before you can stop

DB2. For startup, start DB2, then CDC, then access server. For shutdown, shutdown access server, then

CDC, then DB2.

CDC startup: dmts64 -I cdc_instance_name

CDC shutdown: dmshutdown -I cdc_instance_name

Access server startup: dmaccessserver

Access server shutdown: dmshutdownserver

7. Summary CDC shadow tables are not currently supported with SAP. But, they can be configured and tested with DB2

and SAP as shown above.

No customization changes are needed in SAP, so any SAP transparent table can be tested as a columnar shadow

table. The DB2 optimizer will choose whether to execute a query on row or columnar table based the

optimizers evaluation of where the SQL will perform best.



Figure 1: Check TOOLS tablespace ....................................................................................................................... 5 Figure 2: DB2_EXTENDED_OTIMIZATION before .......................................................................................... 6 Figure 3: DB2_EXTENDED_OPTIMIZATION after ........................................................................................... 6 Figure 4: Adding Buffer Pool ................................................................................................................................. 7

Figure 5: Buffer Pool .............................................................................................................................................. 7 Figure 6: Buffer Pools display ................................................................................................................................ 8 Figure 7: Add Tablespace ....................................................................................................................................... 8 Figure 8: Table space Technical Settings ............................................................................................................... 9 Figure 9: Table space Storage Parameters ............................................................................................................ 10

Figure 10: SYSINSTALLOBJECTS .................................................................................................................... 10 Figure 11: REPL_MQT_LATENCY .................................................................................................................... 10

Figure 12: BKPF Key ........................................................................................................................................... 11 Figure 13: CDC refresh directory ......................................................................................................................... 11 Figure 14: dmconfigurets start .............................................................................................................................. 11 Figure 15: dmconfigurets intance configuration ................................................................................................... 12

Figure 16: create cdc instance popup .................................................................................................................... 12 Figure 17: create cdc instance popup 2 ................................................................................................................. 12

Figure 18: create cdc instance popup 3 ................................................................................................................. 12 Figure 19: dmset parameters ................................................................................................................................. 13 Figure 20: Start CDC with dmts64 ....................................................................................................................... 13

Figure 21: Access server startup ........................................................................................................................... 13 Figure 22: CDC Management Console ................................................................................................................. 13

Figure 23: CDC login............................................................................................................................................ 14 Figure 24: Management Console first startup ....................................................................................................... 14

Figure 25: New subscription ................................................................................................................................. 15 Figure 26: New subscription ................................................................................................................................. 15

Figure 27: Advanced Settings ............................................................................................................................... 16 Figure 28: Subscription created ............................................................................................................................ 16 Figure 29: Mapping type ....................................................................................................................................... 17

Figure 30: Select source schema ........................................................................................................................... 17 Figure 31: Filter tables .......................................................................................................................................... 18

Figure 32: Select table .......................................................................................................................................... 18 Figure 33: Select existing or create new ............................................................................................................... 19

Figure 34: Select schema for new target tables .................................................................................................... 19 Figure 35: List target tables .................................................................................................................................. 20

Figure 36: Select target column table ................................................................................................................... 20 Figure 37: Complete mappings ............................................................................................................................. 21 Figure 38: Set replication method ......................................................................................................................... 21 Figure 39: review mappings.................................................................................................................................. 22 Figure 40: Table mapping BSIS ........................................................................................................................... 22

Figure 41: Start mirroring ..................................................................................................................................... 23 Figure 42: Start mirroring popup .......................................................................................................................... 23 Figure 43: Check status ......................................................................................................................................... 23 Figure 44: Subscription mirrored .......................................................................................................................... 24

Figure 45: DB2 chooses indexed row tables for MATNR = ................................................................................ 24 Figure 46: Shadow table access ............................................................................................................................ 25



Figure 47: MKPF MSEG join ............................................................................................................................... 25 Figure 48: FAGLFLEXA SQL ............................................................................................................................. 26

db2 blu shadow tables with sap v1.pdf

Documents

registered trademarks

trademarks db2

blu acceleration shadow

version changes version

sap erp

sap netweaver

rights reserved page

initial version