Close Menu

    Multiple database support on Amazon RDS for Db2 DB instance

    Many organizations run IBM Db2 databases across multiple physical servers or virtual machines. This approach leads to resource investments in infrastructure, management, and licensing. Additionally, advancements in hardware technology, increased CPU capacities, and database engine enhancements result in underutilized servers if not rightsized at the outset.

    To optimize resource utilization, organizations can explore the following strategies:

    • Running multiple databases per physical server or virtual machine
    • Using multiple schemas within a single database to serve different applications

    IBM Db2’s multiple database feature offers consolidation and manageability benefits while maintaining strong isolation between applications.

    Since its release, Amazon Relational Database Service (Amazon RDS) for Db2 has offered a model that supported only a single database per instance. With this new feature, you can set up multiple databases within a single RDS instance and then create multiple schemas within each database. This layered approach allows for resource optimization and cost reduction at various levels. It provides the flexibility to tailor the level of isolation to your specific needs, from instance-level separation down to schema-level segregation within individual databases.

    In this post, we explore Db2’s multiple database architecture in Amazon RDS for Db2, how it supports the creation of multiple databases, and its overall benefits.

    Solution overview

    Db2’s architecture is designed to seamlessly integrate instance-level and database-specific components, promoting efficient management and isolation. Instance-level resources such as CPU and memory serve as the foundation. Each database operates independently with its own buffer pools, transaction logs, catalog tables, and temporary tablespaces. This approach allows multiple databases to coexist within a single instance, balancing shared resource utilization with database-specific configurations for performance and isolation.

    The following are key components in Db2’s multiple database architecture:

    • Logs – Each database has its own set of transaction logs (active and archive) to provide durability and recovery isolation.
    • Catalog tables – Each database maintains its own catalog, a metadata repository storing schema definitions, tables, and object details. This makes sure that databases remain independent, with no shared metadata across databases.
    • Tablespace – Tablespaces are logical storage structures used to manage and organize data storage efficiently. They’re categorized into user tablespaces (for user data and indexes), catalog tablespaces (for storing metadata), and temporary tablespaces (for handling sorting, joins, and temporary operations). This structure promotes isolation, scalability, and optimized performance.
    • Buffer pools – Each database has its own dedicated buffer pool. Buffer pool configurations can be tuned depending on workload requirements
    • Instance-level resources – Resources such as CPU and memory are shared among databases within the instance, requiring careful monitoring and workload management to help prevent resource contention. Instance-wide logs (such as diagnostic logs) capture events for all databases in the instance.
    • Isolation and security – Each database has independent user management, roles, and permissions. Db2 enforces strict isolation, so data and transactions in one database don’t affect others.

    A maximum of 50 databases can be created per RDS for Db2 instance. The following diagram illustrates the architecture of multiple databases on a Db2 instance.

    The following are key benefits of the Amazon RDS for Db2 multiple database feature:

    • Resource optimization, cost savings, and ease of management – You can consolidate multiple Db2 databases on individual RDS for Db2 instances to reduce compute costs, enhance efficiency, and optimize resource utilization. Additionally, you benefit from simplified management and operations with fewer instances to monitor and maintain:
    • Flexible database control – You can manage your Amazon RDS for Db2 DB instances using multiple methods, including Amazon RDS database APIs, AWS Command Line Interface (AWS CLI) and the AWS Management Console. The feature offers support for infrastructure as code (IaC) deployments using AWS CloudFormation and HashiCorp Terraform. Additionally, you can manage your Db2 database instance by calling built-in stored procedures.
    • Logical isolation and security – Each database functions as a separate entity and is logically separate. Each database has unique users, schemas, and tablespaces, allowing for granular access control and isolation. Additionally, the feature offers full support for AWS Key Management Service (AWS KMS) encryption at rest and SSL and TLS encryption in transit.
    • Scalability – You can adjust your database environment by adding or removing databases as needed, without changing your underlying infrastructure. You can also scale your instances up and down to optimize performance and costs.
    • Seamless integration of the new feature with existing instances – Existing RDS for Db2 instances will be automatically upgraded to include multiple database support. You can immediately start adding additional databases to your existing instances.
    • No additional charges for enabling this feature – Your licensing costs will be determined based on your licensing model, either Bring Your Own License (BYOL) or through AWS Marketplace.

    In the following sections, we demonstrate how to create multiple databases using Amazon RDS for Db2 and optionally create a buffer pool and tablespace for a database.

    Prerequisites

    To start using this feature, complete the following prerequisites:

    • Review the prerequisites for creating an RDS for Db2 DB instance.
    • Create the database using the Amazon RDS console or AWS CLI.
    • If you already have an RDS for Db2 instance provisioned, make sure that you reboot the instance to enable the multiple database feature. It’s recommended to perform the reboot during a maintenance window or an agreed downtime, because the database will be unavailable during the reboot activity.

    Catalog your existing database or RDS for Db2 instance

    Catalog and connect to the database using the following code:

    For more details refer to Connecting to your Amazon RDS for Db2 DB instance 

    db2 catalog TCPIP node <node_name> remote <dns_name> server <port>
db2 catalog database rdsadmin as rdsadmin at node <node_name> 
db2 connect to rdsadmin user <master_username> using <master_password>

    List the databases using the following code:

    db2 "select * from table(rdsadmin.list_databases())"

    Create a new database

    Create a new database using the rdsadmin.create_database command:

    db2 "call rdsadmin.create_database('TEST')"

    View the running status of the command:

    db2 "select task_id, task_type, database_name, lifecycle, varchar(bson_to_json(task_input_params), 500) as task_params, cast(task_output as varchar(500)) as task_output from table(rdsadmin.get_task_status(null,null,null))"

    Validate whether the newly created database is present or not:

    db2 "select * from table(rdsadmin.list_databases())"

    Activate or deactivate the database

    By default, a newly created database is active and available for connections and application use. If the database is in a deactivated state, it can be activated by calling activate_database procedure:

    db2 "call rdsadmin.activate_database(?,'TEST')"

    The deactivate_database procedure deactivates the specified database and halts associated services:

    db2 "call rdsadmin. deactivate_database (?,'TEST')"

    The activate and deactivate procedures return a status code indicating the outcome of the operation. A return status of 0 indicates that the procedure ran successfully. If the procedure encounters an issue, a detailed error message is returned in the ERR_MESSAGE parameter.

    For more details about both procedures, refer to Common tasks for databases.

    Create a buffer pool and tablespace for the newly created database

    A buffer pool in Db2 manages memory for caching data pages, reducing disk I/O, and improving query performance, whereas a tablespace organizes the physical storage of database objects like tables and indexes on disk. Together, they optimize data access and storage efficiency.

    After you create the database, you can optionally create a buffer pool and a tablespace.

    Connect to newly created database

    To catalog the new database and verify the connection, use the following code:

    db2 catalog database test as testdb at node <node_name>
db2 connect to testdb user <master_username> using <master_password>

    Check connectivity to the newly created database using the following code:

    db2 "values current server"

    Best practices

    When you create multiple databases on an IBM Db2 instance, the overall resource requirements increase proportionally with the number of databases. Each additional database consumes CPU, memory, and I/O resources, which collectively impact the instance’s performance and capacity.

    Consider the following best practices:

    • Managing active databases – By default, Amazon RDS activates databases when you create them. To optimize memory resources, you can Deactivate a database that you use infrequently and then activate it later when needed. For more information, refer to Multiple databases on an Amazon RDS for Db2 DB instance.
    • Memory usage – Each database requires memory for its processes, caching, and handling queries. More databases mean more memory is needed to maintain performance and help prevent resource contention. IBM recommends a minimum of 1 GB of memory for each active database. For more information, check the disk and memory requirements in the IBM documentation.
    • CPU utilization – The processing power required for multiple databases increases as each one runs its tasks and manages queries. This can lead to increased CPU usage, impacting overall performance if the instance isn’t rightsized.
    • I/O throughput – More databases will lead to more read and write operations, placing additional demands on disk storage and I/O throughput. Providing sufficient input/output operations per second (IOPS) and fast storage is crucial to support multiple databases efficiently.
    • Connection management – Each database supports its own set of connections, increasing the total number of concurrent sessions the instance needs to manage. To control this, the maximum number of connections can be configured at both the instance level (affecting all databases within the instance) and the database level (limiting connections to a specific database). This promotes efficient resource allocation and system stability.
    • Storage space – Make sure you have adequate storage available because each database will require additional disk space. Allocate sufficient storage during the instance creation process or increase the allocated storage to meet this requirement.
    • Tablespaces and buffer pools – For optimal performance and efficient management, it’s recommended to use separate tablespaces and buffer pools for critical databases or those with distinct workloads. The choice must align with your performance goals, workload characteristics, and resource availability.
    • Monitor resources – Make sure you have enough compute and storage resources to support the needs of all the databases hosted on an RDS for Db2 instance. Regularly review Amazon CloudWatch metrics to determine if you need to increase instance compute size or storage IOPS and throughput capacity.
    • Database configuration – Use the Db2 database configuration command (DB CFG) to granularly manage database configuration for individual databases.
    • Permissions – Use granular permissions on each database with unique users using the principle of least privilege. Implement strong security measures and regular audits.
    • Documentation– Maintain clear documentation of database configurations and service level agreements.

    Cleanup

    If you’ve followed this walkthrough to test and evaluate the multiple database feature, it’s good practice to clean up the environment to avoid unnecessary resource usage. Follow these steps to remove the created database, catalog entries, and node configuration:

    1. If you created a test database, you can drop it using drop_database command
      It might take a few minutes for the database to be dropped from the instance.
    2. If you cataloged a database and node, remove it from the catalog using the UNCATALOG DATABASE and UNCATALOG NODE

    Conclusion

    The Amazon RDS for Db2 multiple database feature provides a scalable and cost-effective solution for consolidating multiple databases within a single instance. With this capability, organizations can streamline database management, optimize resource utilization, and reduce costs. To learn more about this feature, refer to the documentation.

    If you have comments or questions, leave them in the comments section.

    About the Authors

    Tushar Ghotikar is a Database Specialist Solutions Architect at Amazon Web Services (AWS), specializing in databases and data modernization. He helps customers design, deploy, and optimize scalable, high-performing database architectures on AWS while integrating AI-driven capabilities to unlock deeper insights.

    Chris Hutchings is a Senior Database Specialist Solutions Architect at Amazon Web Services (AWS). He has over 20 years of experience in relational database services. His expertise is in designing, building, and implementing database platforms using cloud-based services for customers as part of their journey on AWS.

    Brenesh Stanslas Flower Mary is a Sr. Migration and modernization Architect at AWS. His areas of specialization include migrating and modernizing applications, databases, infrastructure, and middleware with a focus on developing reusable architecture patterns that accelerate cloud journeys

    Prasad Matkar is Database Specialist Solutions Architect at Amazon Web Services (AWS) based in the EMEA region. With a focus on relational database engines, he provides technical assistance to customers migrating and modernizing their database workloads to AWS.

    Javeed Mohammed is a Sr. Database Specialist Solutions Architect with Amazon Web Services (AWS). He works with the Amazon RDS team, focusing on commercial database engines like Oracle and Db2. He enjoys working with customers to help design, deploy, and optimize relational database workloads in the AWS Cloud.

    Source: Read More

    Related Posts

    Leave A Reply