(SM-2508) DB2/DB6 Storage Setup
DB2 is a database server developed by IBM, running on Linux, Unix, and Windows (LUW). SAP calls this database type DB6.
It supports the relational DB model, also supports object-relational features, and non-relational structures like JSON and XML.
Supported versions
The minimal supported version is 10.05.0004.
Creating DB2/DB6 Storages in Kyano Storage Management
Proceed as follows to create DB2/DB6 storage:
Start transaction /DVD/SM_SETUP.
Click Create to create a new storage.
Enter a Storage ID.
In the Storage type field, enter SM_TRS_DB2.
Click Confirm.
Fill out the desired entries as described below.
Storage ID: Name of the storage.
Storage Type: Type of storage. Use the storage type SM_TRS_DB2.
Description: Short text description.
DB Connection Name: Name of the existing SAP database connection.
Connection mode: Select Open SQL or Native SQL for query execution.
Tablespace: Table space. For more information, see the latest IBM documentation on DB2 table spaces.
COLUMN-ORIENTED: If selected, the tables created using this storage are organized by columns.
Compression: If selected, adaptive compression is used.
Read all clients: Read data from the database with all clients (relevant for Open SQL Connection mode).
Repeats: Number of repeated attempts to connect to the database if the connection fails.
Delay: Number of seconds to wait before the next attempt to connect if the connection fails.
Click Save.
The new storage has been created.
Recommended DB2/DB6 Setups per Use Case
Use case 1: Archiving and reporting – use columnar technology (DB2 Blu)
The customer must have a license for DB2 Blu.
System Sizing and configuration must be specifically adapted to use Blu.
We recommend following the IBM recommendation, e.g., 64 GB RAM, 8 Cores for production systems; for low-use non-production systems, half the size may be sufficient.
The use of multiple tablespaces to spread data. If possible, aim for a maximum of 1 TB per tablespace. Start with 5–10 tablespaces and spread the objects.
Use case 2: Most space-efficient archiving – use BLOBs
The system sizing can normally be independent of the database size. Recommendations:
16 GB RAM
4 CPUs
The use of multiple tablespaces to spread data. If possible, aim for a maximum of 1 TB per tablespace. Start with 5–10 tablespaces and spread the objects.
For maximum performance, create 3 tablespaces per SAP data class: data+index+LOB. You must create the LOB tablespace with file system caching enabled. For each tablespace, we recommend using multiple tablespace containers (8–16) to avoid file-level locking issues. When the tables are created, you must assign the tablespaces to the table correctly (tbspace, index_tbspace, long_tbspace from syscat.tables). Changes are possible using admin_move_table.
The majority of the data will be stored in the LOB tablespace in compressed LOB table columns. Due to the nature of the content, we do not recommend DB2 log file compression. Previous experience shows that log archiving times are at least four times longer with log file compression enabled.
Table and index compression typically do not impact this setup because LOBs are already compressed in SAP. We recommend keeping compression flags disabled.
General remarks for tablespace
Tablespaces in DB2 are used to store data. From a technical perspective, it is useful to distribute data across many tablespaces for various reasons. This is because there are locking/latching mechanisms in place per tablespace – also in lower layers at the file level. In the case of a high workload – but also for HADR, backup, and recovery – having many tablespaces can reduce the chance of conflicts.
Space extensions: To avoid high file-level fragmentation, we recommend using larger increase sizes, for example, 128 MB per container (8 containers = 1024 MB).
SAP recommends very small tablespace extent sizes of two blocks. For tablespaces containing a large amount of data, we recommend using a much larger extent size, for example, 16 or higher.
IBM Documentation for requirements
IBM Db2 Advanced Enterprise Server system requirements:
For BLU Acceleration, a minimum of 8 cores or IFLs and 64 GB RAM is recommended for production use.
IBM Db2 Advanced Enterprise Server hardware requirements:
Minimum of 8 cores with at least 64 GB RAM.
Memory requirements temporarily increase when the column compression dictionary is created.
For optimal load performance, additional cache memory is required to write column-organized data in extent-sized amounts, rather than one page at a time, thereby reducing I/O costs.