SAP Netweaver Business Intelligence BI / BW

Symptom

This note contains additions to the guidelines.
Other terms

Partitioned tables, bitmap indexes, clustered indexes, encoded vector index (EVI), UMG_POOL_TABLE, performance during activation of transfer rule (control indicator L_PSAVER)
Reason and Prerequisites

You want to copy an NetWeaver system homogeneously or heterogeneously using R3load.
A heterogeneous database migration to INFORMIX is not supported.
Solution

Contents
——1. General prerequisites for SAP kernel WEB AS 7.02. Tasks before the export3. Tasks after the export4. Tasks after the import5. Special notes for Database Platform DB26. Special notes for Database Platform DB47. Special notes for Database Platform DB68. Special notes for Database Platform MSSQL9. Special notes for Database Platform MAXDB10. Special notes for the ORACLE Database Platform
1. General prerequisites for SAP kernel WEB AS 7.0
—————————————————-
Prerequisite for the homogeneous or heterogeneous system copy when you do not change the database platform: Ensure that your system meets the following requirements: R3load, R3sychk, BW 7.0, BASIS 7.0 patch level 7. Ensure that you use the new tools (R3load, R3szchk) both in the source system when you export the data and in the target system when you import the data.
Irrespective of these minimal requirements, SAP recommends that you import the current Support Packages since the development is updated constantly.
CAUTION: Since possible program errors have been corrected over time and continue to be corrected, we recommend that you always import the most current Support Package for basis and also for BW.
2. Tasks before the export
———————–
Start program SMIGR_CREATE_DDL before you export data from the source system. You can use this program to copy database objects that do not correspond to SAP standard system. These objects contain partitioned (fragmented) tables and bitmap indexes. Specific files of type .SQL are generated for these objects. These files contain native DLL (create) statements and can be analyzed using R3load. Proceed as follows:Log on to the productive BW client as a user with SAP_ALL rights.Call program SMIGR_CREATE_DDL in transaction SE38.Select the database platform and the database version of your target system.Set the ‘Unicode migration’ indicator if your target system is an SAP Unicode system.Specify a directory for the ‘.SQL’ files to be generated. (If this directory is blank after the program has run, there are no tables in the system that must be treated as BW-specific. This is the case if BW is not operated actively in the system. This note is then no longer relevant and you can proceed in accordance with the standard guidelines.)Execute program ‘SMIGR_CREATE_DDL’. The system now generates the DDL statements and writes them to the specified directory.You must ensure that no further changes (such as, activations, data loads to cubes or field changes) are carried out in the BW system after you call report SMIGR_CREATE_DDL and before you export the data. The best way to do this is to call this report directly before you export the data while the system is locked for the user.

If there are problems with the temporary tables during the test import, you can use the report SAP_DROP_TMPTABLES before the export (see Note 1139396). This report deletes all temporary tables. However, this temporarily affects the performance of the queries in the source system.3. Tasks after the export
————————
Before you import the data, copy the generated DDL files of type to the export directory of the target system. The export directory for pure ABAP systems is /DB/. The export directory for double-stack systems (ABAP and JAVA) is /ABAP/DB/. This is the only option for R3load to evaluate these specific files.4. Tasks after the import
————————
After the import has been carried out successfully, you must run report RS_BW_POST_MIGRATION.
See Notes 1077644 and 948780, before you run report RS_BW_POST_MIGRATION.
Before you run report RS_BW_POST_MIGRATION, connect all source systems to the migrated BW system. This is required to ensure that new versions of all PSA (Persistent Staging Area) tables are migrated. Furthermore, you must ensure that all connected SAP systems are active while program RS_BW_POST_MIGRATION is running. Depending on whether or not you have changed the database platform, you must run report RS_BW_POST_MIGRATION with another variant in the background. The program must run in the background so that the spool log remains available. When you do so, DB2 on zOS, DB2 on AS400 and DB2 on UNIX/NT are treated as three different database platforms.
If you have changed the database platform, use variant &POSTMGRDB. If you have NOT changed the database platform, use variant &POSTMGR. Program RS_BW_POST_MIGRATION carries out all necessary adjustments after the system copy. This includes adjusting the database-specific ABAP Dictionary entries (modify, delete, enhance), invalidating database-specific (generated) programs, deleting temporary objects from specific LRU buffers, migrating more recent versions for the PSA tables and adjusting table DBDIFF. The runtime of the program may be a few hours.
Note 1149665 adds new parameters (described in detail below) to optimize the performance during the “PSA Tables” processing step (control indicator L_PSAVER):L_TI_LT (time stamp format: yy.yym.mdd.hhm.mss
yyyy = year
mm = month
dd = day
hh = hours
mm = minutes
ss = seconds
For example 20.080.325.150.813)
If this parameter is set, only transfer rules that were last activated before the time stamp are taken into account. This parameter is very useful if the L_PSAVER processing step has terminated. When the time stamp L_TI_GT is chosen properly, the processing step can be restarted from the place where it terminated.
Table RSTS can be used to determine the time stamp. It may also be possible to use the user (field TSTPNM) or the last time of activation (field TIMESTMP) to add the value from the field TIMESTMP to the parameter L_TI_LT.
L_TI_GT (time stamp format: yy.yym.mdd.hhm.mss)
If this parameter is set, only transfer rules that were last activated after the time stamp are taken into account. These parameters can be used to exclude transfer rules that are no longer used from processing. To determine a suitable time stamp, you can use the last change date of the Persistent Staging Area template RSTMPL81:
Call transaction SE38 for the program RSTMPL81.
Choose the “Utilities” menu followed by “Versions” then
-> “Version management”. The date and time of the active
version is the required time stamp.L_SRCSYS (Select option for source systems)
This parameter can be used to restrict processing to certain source systems.
5. Special Database Platform DB2
——————————–6. Special database platform DB4
——————————–Also use the “Installation Guide” specific to the platform and product. You can find the Installation Guide on the SAP Service Marketplace.For the migration you can use any installation master DVD for Release 7.00 or 7.10 on the IBM system i.See Note 744735, which provides an overview of special features of the migration with target platform DB2 UDB for system i (DB2/400).
Due to their considerable performance advantages, pay particular attention to the “iSeries InPlace Unicode conversion” for homogeneous Latin-1 migrations.Note 782993 describes how, when you execute a homogenous migration of an SAP product to DB2/400, Basis Release 6.20 and higher, you can change the storage parameters of database objects (such as tables and indexes) without causing any additional downtime.Heterogeneous system copy to target DB2/400 or platform-specific actions after the import:
Secondary indexes of BW fact tables are not created automatically at the time of the heterogeneous system copy with the target platform IBM iSeries (DB4). After you have executed program ‘RS_BW_POST_MIGRATION’ as described above, proceed as follows if you want to migrate a BW system or a system based on BW (SEM, SCM/APO):For performance reasons, when you are later using BW systems, we strongly recommend that you set up the EVI stage 2 support as outlined in Note 501572 However, do not start programs ‘SAP_INFOCUBE_INDEXES_CHG_DB4′ and ‘SAP_INFOCUBE_INDEXES_REPAIR’ that are described in the note.The next step is to set up the entire secondary database indexes of the BW fact tables. This procedure can run in parallel if you use Symmetric Multi Processing (SMP). SMP is an additional product for DB2/400. If you operate SMP you must implement Note 601110 in your system, so that the BW indexes can be set up in parallel.You can now use program ‘SAP_SANITY_CHECK_DB4′ to check the settings for the EVI stage 2 support and to check the usage of SMP. See Note 541508 for more detailed information.If you decide to activate the SMP, you must set up the missing indexes. To do this, start program ‘SAP_INFOCUBE_INDEXES_REPAIR’ in the background.

7. Special Database Platform DB2 for LUW (DB6)
———————————————-Possible performance problem when loading data to MDC tables with the LOAD utility:
Under certain circumstances, loading data to tables that use MDC may take longer than a “LOAD” to non-MDC tables.
This problem occurs under the following conditions:You have defined MDC on the target table.You have chosen “LOAD” as the method for loading data to the MDC table.The input data for the table is not sorted according to MDC columns, for example, if the data is sorted according to the primary key but the MDC columns are not the first columns in the primary key.
Solution: To improve the LOAD performance of unsorted data in MDC tables, increase the value of the database configuration parameter “UTIL_HEAP_SZ” to approximately 250,000 4 KB pages.
Also set the value of the “DATA BUFFER” parameter for the “LOAD Utility” to at least 200,000 4 KB pages. Note that you cannot start multiple R3load processes with these settings because the available utility heap is already completely used for the “DATA BUFFER” of the first process.
If you use the R3load utility (which calls a CLI version of the “LOAD”), you can set the shell environment variable DB6LOAD_DATA_BUFFER_SIZE to the required “DATA BUFFER” value (approx. 200,000 pages). R3load will then transfer the value from the environment variable to the “LOAD Utility”. See Note 454173 for more information about the environment variable DB6LOAD_DATA_BUFFER_SIZE.
The performance problem mentioned above doesnotoccur if the input data for an MDC table is sorted according to the MDC columns or the target table for loading data does not have MDC.Important information about multidimensional clustering (MDC) tables:If you use DB2 Version 9.1 up to and including FixPak 4, you cannot use DB2 LOAD to load MDC tables while using the row compression feature. In this case, data may be corrupted as described in DB2 APAR IZ21943. FixPak 5 solves the problem.We recommend that you use INSERT instead of LOAD.If you migrate to a DB6 database with several DPF partitions and you want to compress tables directly while loading the data, you must refer to Note 1156514 (DB6: Possible data loss during system copy of SAP BI).Important corrections:Implement Note 919530 if you are using a Support Package level lower than SAP BASIS 7.00 Support Package 08. Otherwise, errors may occur when you activate BW objects in the target system. Implement this note in the source system.Implement Note 1060114 if you are using a Support Package level lower than SAP BASIS 7.00 Support Package 13. Otherwise, the Data Definition Language (DDL) for write-optimized DataStore object tables is generated incorrectly. Implement this note in the source system.Implement Note 1010353 if you are using a Support Package level lower than SAP BW 7.00 Support Package 13 and DB2 V8.1. The DDL generated with SMIGR_CREATE_DDL contains ‘COMPRESS NO’, which leads to a syntax error in DB2 V8.1. This problem does not occur for DB2 V9. Implement this note in the source system.Implement Note 1049456 if you are using a Support Package level lower than SAP BW 7.00 Support Package 14. P indexes on F fact tables are optional in SAP BW 7.00. DBDIFF entries are generated for the P indexes in report RS_BW_POST_MIGRATION even if they do not exist. Due to the entries in DBDIFF, missing P indexes on F fact tables are displayed in transaction DB02. The corrections contained in this note ensure that a DBDIFF entry is made only for existing indexes.By default, range partitioned InfoCubes in the source system are not migrated to MDC in the DB6 target system. If you have range partitioned InfoCubes in the source system, you must implement Note 1062348 in the target system. If you do not implement this note, errors occur when you activate these InfoCubes and inconsistencies occur in the BW metadata with reference to partitioning. Note 1023410 (SAP BW 7.00 Support Package 12 and SAP BASIS 7.00 Support Package 13) allows you to convert range partitioned InfoCubes to MDC. This activation problem does not occur then.Note 1167138 corrects the error where the program SMIGR_CREATE_DDL does not add COMPRESS YES to CREATE TABLE statements, even if the RSADMIN parameter DB6_ROW_COMPRESSION is set to YES. This correction is provided in Support Package 17 for SAP BASIS 7.00.Use of multidimensional clustering (MDC)
SAP BW 7.00 supports DB2 multidimensional clustering for InfoCubes, DataStore objects and Persistent Staging Area (PSA) tables. Take the following points into account when migrating to DB6:Note 1023410 allows you to convert range partitioned InfoCubes in the source system to MDC on the partitioning time characteristic in the DB6 target system. The package dimension is also selected as an MDC dimension for the F fact table. Proceed as described in the note when you call SMIGR_CREATE_DDL to generate the DDL with MDC.If you want to convert range partitioned InfoCubes to MDC, you must implement Note 1150148 as well as Note 1023410. If you do not implement this note, the BW metadata of the InfoCubes does not display that the InfoCube uses MDC after the migration.SAP BW 7.00 Support Package 10 changes the default for the use of MDC for PSA and the DataStore activation queue and change log tables from YES to NO (see Note 971135 for more information). Report SMIGR_CREATE_DDL generates the DDL for DataStore and PSA tables in accordance with this default. That is, up to and including SAP BW 7.00 Support Package 09, DDL for PSA and the DataStore activation queue and change log tables are generated by default on the REQUEST or SID columns. As of SAP BW 7.00 Support Package 10, MDC is no longer generated by default. MDC has potential performance advantages when loading and deleting data. However, it is also limited in that no online reorganization for MDC tables is possible. You can use the RSADMIN parameter DB6_MDC_FOR_PSA to control MDC for PSA and the DataStore activation queue and change log tables directly. If you set the parameter in the system to YES before calling SMIGR_CREATE_DDL, DDL will always be generated with MDC. If you set the parameter to NO, DDL will always be generated without MDC.
For more information about MDC, refer to the manual “SAP NetWeaver 7.0 and higher Business Intelligence – Administration Tasks: IBM DB2 Universal Database for Linux, UNIX, and Windows” on SAP Service Marketplace under:
http://service.sap.com/instguidesnw70 -> Operations ->?Database-Specific Guides

8. Special Database Platform MSSQL
———————————-If you migrate to SQL Server 2005, you must manually activate partitioning before you use your BW system. You do not require to activate it for migrations of systems external to BW.
You can perform the partitioning using RSADMIN parameter MSS_FORCE_PARTITIONING_ON. Note 869407 describes how to activate this parameter.
In addition, you must implement Notes962124, 996263 and 1010854if you migrate to SQL 2005 (from any database platform or from SQL Server 2000).If you migrate from the ORACLE database platform (on which the partitioning may have been used), you must ensure that there are no more than 999 partitions in all partitioned ORACLE tables. If F fact tables have more than 999 partitions, you must first condense your cube so that the F fact tables do not contain more than 999 partitions.
You can use the following query on your ORACLE database to check in sqlplus if tables with more than 999 partitions exist:
select table_name from user_part_tables
where partition_count >= 1000 and table_name like ‘/%’As of Basis Support Package 06 (SAPKB70006), this restriction is obsolete. Theoretically, you can now process 1.000 or more partitions for each database object during the migration.If you migrate from ORACLE, DB2, AS400 or INFORMIX to SQL Server (SQL 2000 or SQL 2005), implement Notes962124, 965695and996263if they are required for your release (lower than Basis Support Package 11 for NetWeaver 04). These notes are relevant for a correct partitioning layout and correct P indexes on the E fact table and the F fact table.If you migrate from SQL Server 2005 to an SQL Server 2005 release, we recommend that you use system copy function sp_attach_db/spdetach_db (see Note 151603).
If you must perform a migration using our migration process (for example, Unicode migration), you must at least import Basis Support Package 9 for NetWeaver 04s (Support Package 18 for NetWeaver 04).
Otherwise, the system does not partition your table in your target system.If you migrate from SQL Server 2000 to SQL Server 2000 or SQL Server 2005, implement Notes961591, 962124and996263. This way, you avoid creating a duplicate non-cluster primary index during the import. These notes describe how to avoid such duplicate index errors.If you do not choose a target release for SQL Server in report SMIGR_CREATE_DDL, the system uses Server 2000 as the default.
If you want to migrate to SQL Server 2005, you must choose SQL Server 2005 from the input help.9. Special Database Platform MAXDB
———————————-If you perform a system copy or migration using the Unicode conversion on MaxDB-based systems, implement Note 1002250 during the export preparation.If problems occur during the import while report UMG_POOL_TABLE is running, see Note 1002250.A correction in Basis Support Package 6 (SAPKB70006) prevents that incorrect SQL commands for creating indexes are generated in SMIGR_CREATE_DDL. See Note 896023.To support MaxDB clustering, implement Notes 1020006 and 1095135 in the source system before SMIGR_CREATE_DDL is executed.Note 1014782 comprises general questions and answers about MaxDB/liveCache system copy.10. Special ORACLE Database Platform
———————————–
Implement Note 925309.
Program SMIGR_CREATE_DDL
You do not have to specify a database version.
When you make a homogeneous or heterogeneous system copy without changing the database platform, the necessary information about generating DDL statements is read directly from the system catalog. To do this, the system checks all database objects to see if the memory parameters differ from the parameters in the standard system. This applies, for example, to partitioned tables and bitmap indexes. Note 519407 describes how you can accelerate processing during the DDL generation. Ensure that the cubes are correctly condensed (see Notes 407260 and 5900370) so that your system performance is optimal.

See Note 813562 if you use ASSM on your Oracle database.