Finally after a long wait, IBM released DB2 LUW 12.1. In this blog we will cover different editions offered, features available in each editions, DB2 licensing modal, New features of DB2 12.1, Changed, Deprecated and Discontinued functionality in DB2 LUW 12.1.
Highlights of DB2 LUW 12.1
DB2 LUW 12.1 rolls out 200+ features including following:
- AI Powered query optimizer
- Namespace separation with new tenancy model
- Db2 pureScale enhancements
- Performance enhancements for backups with intra-tablespace parallelism
- Apple Silicon driver support
- Security enhancements
- Federation enhancements
- Cloud object storage performance and scalability enhancements
- Columnar tables performance enhancements
System Requirements for DB2 LUW 12.1
Minimum supported OS version is as below:
Linux: RHEL 9.4, SLES 15 SP 6, Ubuntu 22.04 LTS
AIX: AIX 7.3 TL 2
Windows: Windows 11 Enterprise, Windows 11 Pro, Windows Server 2022 Datacenter Edition, Windows Server 2022 Standard Edition
Disk Space: Storage requirement depends on edition and installation method you choose. For Linux and Unix environment you need 2 GB of free space in /tmp directory.
Memory: DB2 requires minimum of 256 MB of RAM. It is recommended to have 1 GB of RAM for better performance.
For detailed system requirement, please check DB2 System requirement.
DB2 Editions for DB2 LUW 12.1
Code base for all DB2 edition is same, different editions just have different resource limit and features. To switch from one edition to another you just have to apply appropriate license key and there is no need to do a fresh installation. DB2 LUW 12.1 offers below editions:
DB2 Community edition is free database solution offered by IBM. It has all the core capabilities offered by DB2 LUW database. It has resource limit of 4 core of CPU and 8 GB of memory. IBM doesn't offer Enterprise support for DB2 Community edition.
DB2 Started Edition is a new edition offered from DB2 LUW 12.1.It also has all the core capabilities offered by DB2 LUW database. It has resource limit of 4 core of CPU and 16 GB of memory. This edition also restricts advance functionality like HADR, PureScale, DPF, Native Encryption Audit logging, Point in time recovery. This edition comes with Enterprise support and IBM offers this edition license on Virtual Processor core (VPC) metric for production and non-production workload.
DB2 Standard Edition offers all features of DB2 LUW 12.1 except DB2 PureScale and DPF. It has resource limit of 16 core of CPU and 128 GB of memory. This editions comes with Enterprise support and IBM offers this edition license on Virtual Processor core (VPC) metric for production and non-production workload. IBM also offer license on Authorized User (AU) metric for non-production workload.
DB2 Advance Edition offers all features of DB2 LUW 12.1 with no resource restrictions. This editions comes with Enterprise support and IBM offers this edition license on Virtual Processor core (VPC) metric for production and non-production workload. IBM also offer license on Authorized User (AU) metric for non-production workload.
New Features of DB2 LUW 12.1
- Highlights of DB2 LUW 12.1
AI Query Optimizer: In DB2 LUW 12.1 for all new database AI query optimizer is enable by default. You can enable/disable AI query optimizer by updating DB CFG parameters AUTO_AI_MAINT, AUTO_AI_OPTIMIZER, AUTO_MODEL_DISCOVERY.
Reorg Recommended Operation: Reorg recommended operation limit increased from 3 to 31.
Namespace Isolation by using DB2 Tenant object: DB2 12.1, you can create independent and unique namespace for user defined database object. You can create objects with same schema and object name in different tenant. E.g. You have 2 tenant called tenant1 and tenant2. You can create schema with same name Schema1 in both tenant. Schema1 in both tenant can have table with name table1. One of the use case for tenant object is when there are multiple developers working on a database and each developer needs different database to not conflict with each other works. In such case we can create different tenant for each developer instead of creating separate database. Privileges defined on objects insides tenants are within tenant limit.
Backup Performance: With DB2 12.1, setting registry variable DB2_BACKUP_ITP to ON will enable use of parallel thread to backup each tablespace. This can be help to improve backup performance for databases where most of the data is concentrated on few larger tablespaces. Change to DB2_BACKUP_ITP registry variable is online and takes effect when next backup gets triggered. It does not require instance restart.
Trusted Connections Monitoring: Now we can monitor metrics related to trusted connections from mon table functions MON_GET_CONNECTION, MON_GET_CONNECTION_DETAILS. You can refer to column TRUSTED_CONN_TYPE, TRUSTED_CTX_NAME, TRUSTED_CTX_ROLE.
- Manageability Enhancements:
ADMIN_MOVE_TABLE: Now ADMIN_MOVE_TABLE can be used to perform online movement of columnar table. In DB2 11.5.9 and earlier only read access was allowed on source table by using ALLOW_READ_ACCESS option. RENAME_INDEXES process has been moved to CLEANUP phase from SWAP phase. This will reduces chances of application facing deadlock when trying to access source table during SWAP phase. ADMIN_MOVE_TABLE now supports range-clustered tables.
- Federation Server Enhancements:
Federation Support: Federation support Databricks and SAP ABAP SQL Service as data source for establishing connection, data type mapping, function push-down and server attribute optimization. DB2 also started to support Oracle 21c and 23ai as data source.
Bulk Insert support for Snowflake: DB2 now support bulk insert operation for Snowflake data source.
Large Objects Operations: DB2 now supports LOB operations on remote table for MariaDB, Microsoft Azure, Microsoft SQL Server, Netezza, Oracle, Oracle MySQL, Greenplum, PostgreSQL, SAP HANA, Snowflake through ODBC wrapper.
- High Availability, Backup and recovery Enhancements
Read only access to HADR Standby during upgrade: New upgrade process allows ready only access to HADR Standby database when Primary is getting upgraded.
DB2_HADR_BLOCK_ON_DISKFULL: This registry variable if set to OFF, will shutdown Standby database if DB2 encounters Active log path disk full issue on standby. If this registry variable is set to ON, it will keep Standby database up in case of Active log path disk full issue on standby. If this issue is encounters STANDBY_LOG_DEVICE_FULL flas will be set in HADR flag field. This will block all transactions on Primary untill disk full issue gets fixed on Standby. Default value for this registry variable is ON. Changes to this registry variable is immediate on Standby.
Pacemaker: Pacemaker has been upgrade from 2.1.6 to 2.1.7. Pacemaker now also supports automatic failover for DPF on Linux env.
History File Enhancements: Now history file will contains additional details like Total_Size, SEQ_SIZE, COMPRESSION_LIBRARY, ENCRYPTED, INCLUDE_LOGS.
Automatic Backup Ehnancements: Automatic backups now supports remote storage targets.
DB2_VALIDATE_LOG_ON_ARCHIVE: If this registry variable is set to ON, DB2 will perform validation of log file before archiving. If there is issue with log file DB2 still log file will be archived which will not stop archival of subsequent log files. You can check for validation failure by looking into notification log, db2 history, db2pd -logs output, MON_GET_TRANSACTION_LOG table function. By default this registry variable is set to ON.
PureScale now supports Pacemaker in Linux env. New PureScale deployment in 12.1 will use Pacemaker as default cluster manager. For cluster getting upgraded to 12.1 as part of upgrade process TSA cluster will be dropperd and Pacemaker cluster will be created automatically. Multiple enhancement has been made to handle dropped member situation like now there is no restrictions of offline backup post dropper member topology change event. Database restore and rollforward is possible when there is different between topology of backup image and target system.
- Workload Management Enhancements:
Adaptive workload manager is enabled for all newly created DPF databases.
Changed Functionality in DB2 LUW 12.1
- Registry Variables: DB2_FAIL_RECOVERY_ON_TABLESPACE_ERROR now supports ROLLFORWARD and CRASHRECOVERY options. For DB2_BCKP_PAGE_VERIFICATION registry variable default value is changed from OFF to ON. For DB2_USE_ALTERNATE_PAGE_CLEANING registry variable default value if changed to ON.
- Security Functionality: SSL Hostname validation is enabled by default. While granting DBADM, DATACCESS and ACCESSCTRL authorities are not granted by default. This can be enabled by setting registry variable DB2_ALTERNATE_AUTHZ_BEHAVIOUR.When creating a new database, CONNECT, CREATETAB, IMPLICIT_SCHEMA, BINDADD on database, CREATEIN on NULLID and SQLJ schemas, and USE on table space USERSPACE1 are no longer granted to PUBLIC by default. This can be enabled by setting registry variable DB2_ALTERNATE_AUTHZ_BEHAVIOUR.
- Catalog View Change: Catalog view having authorization details contains new timestamp column to record first grant time and last grant or revoke time.
- DB2 Setup: If you run db2ckupgrade without specifying database name it will check instance level configuration for discontinued values. Also now there is option to run db2ckupgrade just for instance by using option -instOnly
Deprecated Functionality in DB2 LUW 12.1
- DB2_HADR_NO_IP_CHECK registry variable.
- UNFORMATTED EVENT TABLE targets for event monitors: Use WRITE TO TARGET instead of WRITE TO UNFORMATTED EVENT TABLE while creating event monitors.
- db2convert utility: User ADMIN_MOVE_TABLE to convert row organized table to column organized table.
Discontinued Functionality in DB2 LUW 12.1
- db2top discontinued: db2top is not supported anymore on SUSE 15.6 or later
- Load Client with remote storage alias: Load Client which usages remote storage alias is no more supported.
- Ingest operation with remote storage alias: Ingest operation which usages remote storage alias is no more supported.
- db2pd -ha: db2pd -ha option is discontinued in Linux env.
- db2haicu: db2haicu utility is discontinued in Linux env.
- TSA: TSA is discontinued as cluster managed in Linux env.
- db2mtrk: db2mtrk command is discontinued.
- TLS 1.0/1.1: TLS 1.0/1.1 is discontinued for Client Server communication(SSL) and LDAP authentication plugins. by default TLS 1.2 and 1.3 are enabled.
- Load Restart: Load restart option of load command is discontinued.
- DAS: DAS and its associated functionality is discontinued.
- Table functions: Table functions SNAP_GET_APPL_INFO_V95, SNAP_GET_APPL_V95, SNAP_GET_BP_V95, SNAP_GET_CONTAINER_V91, SNAP_GET_DBM_V95, SNAP_GET_DB_V97, SNAP_GET_DETAILLOG_V91, SNAP_GET_DYN_SQL_V95, SNAP_GET_STORAGE_PATHS_V97, SNAP_GET_TAB_V91, SNAP_GET_TBSP_PART_V97, SNAP_GET_TBSP_V91, WLM_GET_SERVICE_CLASS_AGENTS_V97, WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97, WLM_GET_SERVICE_SUBCLASS_STATS_V97, WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97, WLM_GET_WORKLOAD_STATS_V97, WLM_GET_ACTIVITY_DETAILS are discontinued.