The blog post Database Partitioning and Semantic Partitioning for SAP Business Warehouse on IBM i took a detailed look at the options that SAP Business Warehouse (SAP BW) and the database IBM Db2 for i offer in order to keep mass data in smaller, easier to manage parts.
Now, if you have decided to use database table partitioning and you have partitioned some of your InfoProviders, you might want to know how exactly the InfoProvider partitioning settings you have entered in the SAP GUI are reflected in the database.
For this purpose, there is program RSDB4_PARTITIONS_INFO_GET, which has been updated recently for even easier use. It displays the partitioning settings of tables in the database. To make it really easy to use, you don’t have to know the exact table names, you can just specify the names of the InfoProviders you are interested in, and the program collects the partitioning info for all data tables that belong to these InfoProviders.
Select InfoProviders for Analysis
When you start program RSDB4_PARTITIONS_INFO_GET, you get a selection screen that offers several options for specifying which database tables should be analyzed.
Database table partitioning is supported by SAP BW for the following basic InfoProvider types:
For a more detailed description about how these InfoProviders are partitioned in a SAP BW system on Db2 for i, see SAP Note 815186 – IBM i: Table partitioning in BW.
Because you can partition any table in the database using native database tools, the program also offers the option to analyze just any database table (no matter if it belongs to an InfoProvider or not). You only need to specify its name – either the long SQL name or the short system name. The table does not have to be defined in the SAP dictionary, but it must reside in the SAP system’s library (R3<SID>DATA).
SQL table names containing special characters like a slash must be enclosed in double quotes when you use them in an SQL statement. However, they are saved in the database catalog without the double quotes, so when you enter a name in field Table Name, you must omit the double quotes as well (e.g. /BIC/FZAHTESTC). System table names (the object names of tables as they are known in the IBM i library) containing special characters start and end with double quotes. The quotes, as part of the name, are saved in the database catalog. Therefore, for system table names containing special characters, you must include the double quotes in field System Table Name (e.g. “/BI65945”), or the table will not be found.
When selecting the InfoCubes or DSOs that should be analyzed, you may specify their names (or a search pattern for the names) directly. But you can also specify other InfoProviders, which contain InfoCubes or DSOs, namely InfoSets, MultiProviders, and semantically partitioned objects (SPOs). The program analyzes all InfoCubes or DSOs that are part of at least one of these InfoProviders.
For all input fields, there is a value help (F4-help) available. The value help for InfoSets, MultiProviders, and SPOs in the InfoCube block only lists the InfoProviders that contain InfoCubes (and the value help in the DataStore Object block only lists InfoProviders that contain DSOs).
Result
Once you have specified the objects that should be analyzed, select Execute (F8).
The program collects all database tables that fulfil the selection criteria and displays for each the name, the system table name, how many records it contains, and whether it is partitioned or not. If it is partitioned, it lists the partitioning method, the partitioning key, the number of partitions, and for each individual partition, it lists the partition number, partition name, number of records in the partition, and the condition, on which values are included in the partition.
If you select the option Including Database Indexes, the program also lists the database indexes for each analyzed table and whether the index is partitioned or not. Global means that the index spans the entire table and contains the keys for all the partitions. Local means that the index is partitioned into individual index structures, one for each partition in the table, which contains only the keys for the corresponding table partition.
The result is just text, which could be saved in a local file, for instance if you want to provide support with this information. Select System -> List -> Save -> Local File and then specify the type of file and where to save it.
Availability
In order to use database table partitioning on Db2 for i, you must have product IBM Db2 Multisystem (Option 27) installed on your database server.
The newest version of program RSDB4_PARTITIONS_INFO_GET was delivered with SAP Note 2713328 – IBM i: DB-Partitioning Info for DB Tables of BW InfoProviders.
Note: Links to the SAP Support portal (SAP Notes) require authorization.
Okumaya devam et...
Now, if you have decided to use database table partitioning and you have partitioned some of your InfoProviders, you might want to know how exactly the InfoProvider partitioning settings you have entered in the SAP GUI are reflected in the database.
For this purpose, there is program RSDB4_PARTITIONS_INFO_GET, which has been updated recently for even easier use. It displays the partitioning settings of tables in the database. To make it really easy to use, you don’t have to know the exact table names, you can just specify the names of the InfoProviders you are interested in, and the program collects the partitioning info for all data tables that belong to these InfoProviders.
Select InfoProviders for Analysis
When you start program RSDB4_PARTITIONS_INFO_GET, you get a selection screen that offers several options for specifying which database tables should be analyzed.
Database table partitioning is supported by SAP BW for the following basic InfoProvider types:
- InfoCubes
The program analyzes the fact tables of the selected InfoCubes.
If the option: Also Analyze All Aggregates of Selected InfoCubes is checked, the fact tables of all aggregates that belong to these InfoCubes are analyzed as well.
If you want to analyze a specific aggregate, you can also enter its name (e.g. 100042) in the InfoCube Name input field. - DataStore objects (DSO)
The program analyzes the table with active data, the Changelog table, and the Activation queue table of the selected DSOs. - Persistent Storage Area (PSA)
The program analyzes the PSA table.
For a more detailed description about how these InfoProviders are partitioned in a SAP BW system on Db2 for i, see SAP Note 815186 – IBM i: Table partitioning in BW.
Because you can partition any table in the database using native database tools, the program also offers the option to analyze just any database table (no matter if it belongs to an InfoProvider or not). You only need to specify its name – either the long SQL name or the short system name. The table does not have to be defined in the SAP dictionary, but it must reside in the SAP system’s library (R3<SID>DATA).
SQL table names containing special characters like a slash must be enclosed in double quotes when you use them in an SQL statement. However, they are saved in the database catalog without the double quotes, so when you enter a name in field Table Name, you must omit the double quotes as well (e.g. /BIC/FZAHTESTC). System table names (the object names of tables as they are known in the IBM i library) containing special characters start and end with double quotes. The quotes, as part of the name, are saved in the database catalog. Therefore, for system table names containing special characters, you must include the double quotes in field System Table Name (e.g. “/BI65945”), or the table will not be found.
When selecting the InfoCubes or DSOs that should be analyzed, you may specify their names (or a search pattern for the names) directly. But you can also specify other InfoProviders, which contain InfoCubes or DSOs, namely InfoSets, MultiProviders, and semantically partitioned objects (SPOs). The program analyzes all InfoCubes or DSOs that are part of at least one of these InfoProviders.
For all input fields, there is a value help (F4-help) available. The value help for InfoSets, MultiProviders, and SPOs in the InfoCube block only lists the InfoProviders that contain InfoCubes (and the value help in the DataStore Object block only lists InfoProviders that contain DSOs).
Result
Once you have specified the objects that should be analyzed, select Execute (F8).
The program collects all database tables that fulfil the selection criteria and displays for each the name, the system table name, how many records it contains, and whether it is partitioned or not. If it is partitioned, it lists the partitioning method, the partitioning key, the number of partitions, and for each individual partition, it lists the partition number, partition name, number of records in the partition, and the condition, on which values are included in the partition.
If you select the option Including Database Indexes, the program also lists the database indexes for each analyzed table and whether the index is partitioned or not. Global means that the index spans the entire table and contains the keys for all the partitions. Local means that the index is partitioned into individual index structures, one for each partition in the table, which contains only the keys for the corresponding table partition.
The result is just text, which could be saved in a local file, for instance if you want to provide support with this information. Select System -> List -> Save -> Local File and then specify the type of file and where to save it.
Availability
In order to use database table partitioning on Db2 for i, you must have product IBM Db2 Multisystem (Option 27) installed on your database server.
The newest version of program RSDB4_PARTITIONS_INFO_GET was delivered with SAP Note 2713328 – IBM i: DB-Partitioning Info for DB Tables of BW InfoProviders.
Note: Links to the SAP Support portal (SAP Notes) require authorization.
Okumaya devam et...