Chapter 31 Exercises: DB2 in the Cloud

Exercise 31.1: Provision a Db2 on Cloud Instance

Objective: Understand the provisioning process and connection configuration for IBM Db2 on Cloud.

Tasks:

  1. Using the IBM Cloud CLI syntax shown in Section 31.2.2, write the command to provision a Db2 on Cloud Enterprise HA instance with the following specifications: - Instance name: meridian-digital-prod - Region: eu-de (Frankfurt) - CPU: 16 vCPUs - Memory: 64 GB - Storage: 500 GB

  2. Write the JDBC connection string for this instance assuming the hostname is dashdb-txn-flex-yp-fra02-01.services.eu-de.bluemix.net, port 50001, database BLUDB, user meridian_admin, with SSL enabled.

  3. Write a Python connection script using the ibm_db library that: - Connects to the instance using the DSN string. - Executes SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1. - Prints the result. - Closes the connection.

  4. Explain why you would choose the Enterprise HA plan over the Standard plan for Meridian's digital banking workload.

Exercise 31.2: Docker Db2 Deployment

Objective: Deploy a Db2 Community Edition container for local development.

Tasks:

  1. Write a complete docker run command that: - Uses the icr.io/db2_community/db2:latest image. - Creates a database named DEVMERIDIAN. - Maps ports 50000 and 50001 to the host. - Uses a named volume db2-dev-data for persistent storage. - Sets the instance password to a secure value. - Enables archive logging.

  2. Write a docker-compose.yml file that defines the same Db2 service with the addition of: - A health check that verifies the database is accessible. - Memory limits (8 GB max, 4 GB minimum). - Automatic restart on failure.

  3. Write a shell script that waits for the container to be ready and then: - Creates a schema named MERIDIAN. - Creates the CUSTOMER_DIGITAL_PROFILE table with columns: customer_id, preferred_channel, notification_prefs, last_login_ts, mfa_enabled. - Inserts 5 sample rows. - Queries and displays the data.

  4. Explain why the --privileged flag is required for Db2 containers and what security implications it has.

Exercise 31.3: Kubernetes StatefulSet for Db2

Objective: Design a Kubernetes deployment for Db2 in a production-like environment.

Tasks:

  1. Write a Kubernetes Secret manifest that stores the Db2 instance password.

  2. Write a StatefulSet manifest that: - Deploys a single Db2 instance. - Uses the Db2 Community Edition image. - Requests 4 CPUs and 8 GB memory, with limits of 8 CPUs and 16 GB. - Uses a PersistentVolumeClaim template for 200 GB of SSD storage. - Mounts the Db2 data volume at /database. - References the password from the Secret.

  3. Write a ClusterIP Service that exposes both port 50000 (plain) and port 50001 (SSL).

  4. Write a LoadBalancer Service for external access on the SSL port only.

  5. Explain why a StatefulSet is used instead of a Deployment for Db2, and what guarantees a StatefulSet provides that a Deployment does not.

Exercise 31.4: Federation Configuration

Objective: Configure data federation between on-premises DB2 z/OS and Db2 on Cloud.

Tasks:

  1. Write the complete federation configuration script including: - Enabling the federated feature in DBM CFG. - Creating the DRDA wrapper. - Defining a server for the on-premises DB2 z/OS instance. - Defining a server for Db2 on Cloud. - Creating user mappings. - Creating nicknames for:

    • MERIDIAN.ACCOUNT_MASTER on z/OS
    • MERIDIAN.TRANSACTION_HISTORY on z/OS
    • MERIDIAN.CUSTOMER_DIGITAL_PROFILE on Db2 on Cloud

  2. Write a federated query that: - Joins the z/OS account master with the cloud digital profile. - Filters for accounts with a balance greater than $100,000. - Returns account details and the customer's preferred channel and last login.

  3. Explain what "predicate pushdown" means in the context of this federated query and which predicates would be pushed to which server.

  4. Design a materialized query table (MQT) that caches the result of the federated join for frequently accessed data. Explain when the MQT should be refreshed.

Exercise 31.5: Cloud Migration Planning

Objective: Develop a migration plan for moving a Db2 LUW database to Db2 on Cloud.

Scenario: Meridian's digital banking team has a 500 GB Db2 LUW database running on-premises that needs to migrate to Db2 on Cloud. The database contains: - 45 tables - 120 indexes - 15 stored procedures - 8 triggers - 3 MQTs (materialized query tables)

Tasks:

  1. Choose a migration strategy (lift-and-shift, re-platform, or re-architect) and justify your choice.

  2. Create a migration checklist that includes: - Pre-migration compatibility assessment - Schema migration steps - Data migration steps - Application connection updates - Post-migration validation

  3. Calculate the data transfer time for the 500 GB database over: - A 100 Mbps internet connection - A 1 Gbps dedicated link - A 10 Gbps Direct Link

  4. Write the db2move commands for exporting and importing the entire database.

  5. Design a zero-downtime migration plan using CDC replication. List the steps from initial setup through final cutover.

Exercise 31.6: Cloud Security Configuration

Objective: Implement a comprehensive security configuration for Db2 on Cloud.

Tasks:

  1. Write the IBM Cloud CLI command to create a Key Protect root key for database encryption.

  2. Write SQL statements to implement role-based access control: - Create roles: APP_READONLY, APP_READWRITE, ANALYTICS_USER, DBA_ADMIN. - Grant appropriate privileges for each role on the MERIDIAN schema. - Create a user mapping that ties an IAM service ID to the APP_READWRITE role.

  3. Write an audit policy that: - Audits all EXECUTE operations (with data) on the ACCOUNT_MASTER and TRANSACTION_HISTORY tables. - Audits all SYSADMIN and DBADMIN operations.

  4. Write the CLI command to configure a private endpoint for the Db2 instance.

  5. Design a network architecture diagram (described in text) showing: - VPC configuration - Private endpoint for Db2 - VPN or Direct Link to on-premises - IP allowlisting rules

Exercise 31.7: Cost Optimization Analysis

Objective: Analyze and optimize cloud costs for Meridian's database deployment.

Tasks:

  1. Using the illustrative pricing from Section 31.10.1, calculate the monthly cost for: - Db2 on Cloud Enterprise with 16 vCPUs, 64 GB RAM, 500 GB storage, Enterprise HA. - Include backup storage (assume 1 TB of backups retained). - Include 100 GB/month of data transfer out.

  2. Calculate the savings from switching to a 3-year reserved commitment.

  3. Design a tiered storage strategy for Meridian's 7-year transaction history: - Hot tier: Last 6 months on Db2 on Cloud. - Warm tier: 6 months to 3 years on Db2 Warehouse (estimate compression ratio of 8:1). - Cold tier: 3 to 7 years on Cloud Object Storage. - Calculate the total monthly storage cost.

  4. Identify three specific cost optimization opportunities beyond what is already implemented.

  5. Build a 3-year TCO comparison between on-premises Db2 LUW and Db2 on Cloud for the digital banking workload. State your assumptions clearly.