Exercises -- Chapter 4: Setting Up Your DB2 Environment

These exercises are designed to be completed hands-on with a working Db2 environment. If you have not yet set up your environment, complete Exercises 1-3 first to establish your working database.

Setup and Installation Exercises

Exercise 1: Install Db2 via Docker (Fundamental)

Objective: Get a working Db2 instance running in a Docker container.

Tasks: 1. Install Docker Desktop on your operating system if not already installed. 2. Pull the official Db2 Community Edition image: docker pull ibmcom/db2:11.5.8.0 3. Start a Db2 container with the following requirements: - Container name: db2-meridian - Password for db2inst1: a password of your choosing - Database name: TESTDB - Map port 50000 to your host - Use a named volume for persistent storage 4. Wait for the container to fully initialize (watch the logs with docker logs -f db2-meridian). 5. Verify the instance is running by executing: docker exec -ti db2-meridian bash -c "su - db2inst1 -c 'db2 CONNECT TO TESTDB'"

Deliverable: A screenshot or terminal output showing a successful connection to TESTDB.

Exercise 2: Create the Meridian National Bank Database (Fundamental)

Objective: Create the project database that will be used throughout this book.

Tasks: 1. Connect to your Db2 container: docker exec -ti db2-meridian bash -c "su - db2inst1" 2. If the MERIDIAN database does not yet exist, drop TESTDB and create MERIDIAN using the create-meridian-database.sql script from this chapter's code directory. 3. Alternatively, if you prefer to keep TESTDB, run the creation script to create MERIDIAN as an additional database. 4. Run the sample-data-load.sql script to populate tables with sample data. 5. Run the verify-installation.sql script and confirm all 12 tests pass.

Deliverable: The output of the verification script showing all tests passed.

Exercise 3: Explore Database Configuration (Fundamental)

Objective: Understand the configuration parameters that govern your database.

Tasks: 1. Connect to the MERIDIAN database. 2. Display the complete Database Manager Configuration: db2 GET DBM CFG 3. Display the complete Database Configuration for MERIDIAN: db2 GET DB CFG FOR MERIDIAN 4. Identify and record the current values of these parameters: - LOGFILSIZ - LOGPRIMARY - LOGSECOND - SORTHEAP - LOCKLIST - PCKCACHESZ - BUFFPAGE (note: this is the default buffer pool size) 5. For each parameter, write one sentence explaining what it controls.

Deliverable: A table with the parameter name, current value, and your explanation.

Exercise 4: Native Linux Installation (Intermediate)

Objective: Understand the native installation process on Linux (even if you primarily use Docker).

Tasks: 1. Download the Db2 Community Edition installer from the IBM website (you will need an IBM ID). 2. On a Linux system (a virtual machine is fine), run the pre-installation check: ./db2prereqcheck -v 11.5.8.0 3. Document any prerequisite failures and how you would resolve them. 4. If you proceed with installation, use the db2_install command or the db2setup GUI installer. 5. After installation, create an instance with db2icrt and verify it starts correctly.

Deliverable: A written record of the installation steps, any prerequisite issues encountered, and how they were resolved.

Configuration Exercises

Exercise 5: Buffer Pool Analysis (Intermediate)

Objective: Examine and modify buffer pool configuration.

Tasks: 1. List all buffer pools in the MERIDIAN database: sql SELECT BPNAME, PAGESIZE, NPAGES FROM SYSCAT.BUFFERPOOLS; 2. Determine the total memory allocated to buffer pools (multiply NPAGES by PAGESIZE for each buffer pool and sum the results). 3. Create a new buffer pool for testing purposes: sql CREATE BUFFERPOOL BP_TEST SIZE 500 PAGESIZE 4096; 4. Create a tablespace using your new buffer pool: sql CREATE TABLESPACE TS_TEST PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP_TEST; 5. Create a simple test table in the new tablespace and insert 100 rows. 6. Query the buffer pool hit ratio using: sql SELECT BP_NAME, POOL_DATA_L_READS, POOL_DATA_P_READS, CASE WHEN POOL_DATA_L_READS > 0 THEN DECIMAL(1.0 - (FLOAT(POOL_DATA_P_READS) / FLOAT(POOL_DATA_L_READS)), 5, 4) * 100 ELSE 0 END AS HIT_RATIO_PCT FROM TABLE(MON_GET_BUFFERPOOL(NULL, -2)); 7. Clean up: drop the test table, tablespace, and buffer pool.

Deliverable: The calculated total buffer pool memory and the hit ratio output.

Exercise 6: Log Configuration Experiment (Intermediate)

Objective: Understand the relationship between log configuration parameters and transaction capacity.

Tasks: 1. Record the current log configuration: db2 GET DB CFG FOR MERIDIAN | grep -i log 2. Calculate the total primary log space in megabytes: LOGFILSIZ (in 4 KB pages) x LOGPRIMARY x 4 KB. 3. Calculate the total log space including secondary logs. 4. Create a test table and write a loop that inserts rows until you observe a log-full condition (you can use a stored procedure or a script that inserts thousands of large rows in a single transaction without committing). 5. Observe the error message. What SQLCODE do you receive? 6. COMMIT or ROLLBACK the transaction. 7. Explain why LOGSECOND exists and when secondary log files are allocated.

Deliverable: Your calculations, the SQLCODE received, and your explanation of secondary logs.

Exercise 7: Modify and Verify Configuration Changes (Fundamental)

Objective: Practice changing configuration parameters and understanding when changes take effect.

Tasks: 1. Change the SORTHEAP parameter: db2 UPDATE DB CFG FOR MERIDIAN USING SORTHEAP 4096 2. Check whether the change is immediately active: db2 GET DB CFG FOR MERIDIAN SHOW DETAIL | grep SORTHEAP (The SHOW DETAIL option reveals both the current value and the pending value.) 3. Disconnect all applications and reconnect. Check the value again. 4. Now change a parameter that requires an instance restart: db2 UPDATE DBM CFG USING INTRA_PARALLEL YES 5. Verify the pending state, then restart the instance: db2stop force db2start 6. Confirm the change is now active. 7. Reset both parameters to their original values.

Deliverable: Terminal output showing the before, pending, and after states for each parameter change.

Connectivity Exercises

Exercise 8: Connect with DBeaver (Fundamental)

Objective: Establish a graphical tool connection to your Db2 database.

Tasks: 1. Download and install DBeaver Community Edition. 2. Create a new DB2 connection with these settings: - Host: localhost (or your Docker host IP) - Port: 50000 - Database: MERIDIAN - Username: db2inst1 - Password: (your password) 3. When prompted, allow DBeaver to download the DB2 JDBC driver. 4. Test the connection. 5. Browse the MERIDIAN schema and verify you can see all tables. 6. Open a SQL editor and run: sql SELECT FIRST_NAME, LAST_NAME, CUSTOMER_SINCE FROM MERIDIAN.CUSTOMERS ORDER BY CUSTOMER_SINCE FETCH FIRST 5 ROWS ONLY; 7. Export the result as a CSV file.

Deliverable: A screenshot of DBeaver showing the query results and the exported CSV file.

Exercise 9: JDBC Connection String Construction (Intermediate)

Objective: Understand JDBC connection string formats for DB2.

Tasks: 1. Write the Type 4 JDBC connection URL for the following scenarios: - A Db2 LUW database named MERIDIAN on host db2server.example.com, port 50000 - The same database with SSL encryption enabled - The same database with the currentSchema property set to MERIDIAN - A DB2 for z/OS subsystem on host mainframe.example.com, port 5040, location name DSN1 2. For each URL, identify the JDBC driver class name that should be used. 3. Write a minimal Java program (or Python script using ibm_db) that: - Connects to your local MERIDIAN database - Executes SELECT COUNT(*) FROM MERIDIAN.CUSTOMERS - Prints the result - Closes the connection

Deliverable: The four JDBC URLs and the working program source code with its output.

Exercise 10: Catalog a Remote Database (Intermediate)

Objective: Understand the DB2 cataloging mechanism for remote database connections.

Tasks: 1. On your local Db2 instance (or from a second Docker container), catalog a TCP/IP node: db2 CATALOG TCPIP NODE remnode REMOTE localhost SERVER 50000 2. Catalog the remote database: db2 CATALOG DATABASE MERIDIAN AS REMMERID AT NODE remnode 3. Connect to the remote database using the catalog alias: db2 CONNECT TO REMMERID USER db2inst1 USING <password> 4. Run a query to verify the connection works. 5. List all cataloged databases and nodes: db2 LIST DATABASE DIRECTORY db2 LIST NODE DIRECTORY 6. Uncatalog the database and node when finished: db2 UNCATALOG DATABASE REMMERID db2 UNCATALOG NODE remnode

Deliverable: Terminal output from each step, showing successful catalog, connect, query, and cleanup.

SQL Exercises (Using the Meridian Database)

Exercise 11: Explore the Schema Using the Catalog (Fundamental)

Objective: Use the DB2 system catalog to discover database objects.

Tasks: 1. List all tables in the MERIDIAN schema: sql SELECT TABNAME, TYPE, COLCOUNT, CARD FROM SYSCAT.TABLES WHERE TABSCHEMA = 'MERIDIAN' ORDER BY TABNAME; 2. List all columns of the CUSTOMERS table with their data types: sql SELECT COLNAME, TYPENAME, LENGTH, SCALE, NULLS FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'MERIDIAN' AND TABNAME = 'CUSTOMERS' ORDER BY COLNO; 3. List all foreign key relationships in the schema: sql SELECT CONSTNAME, TABNAME, REFTABNAME, FK_COLNAMES, PK_COLNAMES FROM SYSCAT.REFERENCES WHERE TABSCHEMA = 'MERIDIAN' ORDER BY TABNAME; 4. List all indexes on the TRANSACTIONS table: sql SELECT INDNAME, COLNAMES, UNIQUERULE FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'MERIDIAN' AND TABNAME = 'TRANSACTIONS'; 5. Draw a simple entity-relationship diagram based on the foreign key information.

Deliverable: The query outputs and your hand-drawn or tool-generated ER diagram.

Exercise 12: Basic SELECT Queries (Fundamental)

Objective: Verify your setup by writing fundamental queries against the Meridian data.

Tasks: Write and execute SQL queries to answer each question:

  1. List all active branches with their city and state, ordered by open date.
  2. Find all customers whose last name starts with the letter 'S'.
  3. Show all accounts with a current balance greater than $100,000.
  4. List the five most recent transactions across all accounts.
  5. Find all employees who are branch managers.
  6. Show all active loans with their loan type and current principal, ordered by principal descending.
  7. Count the number of customers at each branch.
  8. Find the total balance across all checking accounts (ACCOUNT_TYPE_CODE = 'CHK').

Deliverable: The SQL for each query and its result set.

Exercise 13: INSERT, UPDATE, and DELETE Practice (Fundamental)

Objective: Practice data modification operations.

Tasks: 1. Insert a new customer (yourself, with fictional data) assigned to branch MNB001. 2. Create a checking account for your new customer. 3. Insert three transactions on the new account: an opening deposit, a withdrawal, and a fee. 4. Update the account's CURRENT_BALANCE and LAST_ACTIVITY_DATE to reflect the transactions. 5. Query the account to verify the balance is correct. 6. Delete the fee transaction (simulating a fee reversal). 7. Update the balance again to reflect the reversal. 8. Verify the final state is consistent.

Deliverable: All SQL statements and verification query results.

z/OS Exercises

Exercise 14: SPUFI Simulation (Intermediate)

Objective: Understand the SPUFI workflow even without mainframe access.

Tasks: 1. Using the Db2 CLP as a stand-in for SPUFI, create a file named spufi-input.sql containing five SQL statements: - A SELECT against MERIDIAN.BRANCHES - A SELECT with a WHERE clause against MERIDIAN.CUSTOMERS - A SELECT with a JOIN between CUSTOMERS and ACCOUNTS - An INSERT into MERIDIAN.AUDIT_LOG - A SELECT to verify the INSERT 2. Execute the file: db2 -tvf spufi-input.sql 3. Redirect the output to a file: db2 -tvf spufi-input.sql > spufi-output.txt 4. Examine the output file. For each statement, note the SQLCODE and the number of rows returned or affected. 5. Write a brief explanation of how this process maps to SPUFI on z/OS (input dataset, output dataset, ISPF panels).

Deliverable: Your input SQL file, the output file, and your SPUFI comparison explanation.

Exercise 15: z/OS System Parameter Research (Intermediate)

Objective: Understand key DSNZPARM parameters.

Tasks: Using IBM documentation (or the material from Section 4.4), research and create a reference table for these DSNZPARM parameters:

Parameter Panel Description Default Typical Production Value
CTHREAD
IDTHTOIN
MAXDBAT
EDMPOOL
CONDBAT
CACHEDYN
ZPARM
MAXKEEPD

For each parameter, write one sentence explaining why a DBA would change it from the default value.

Deliverable: The completed reference table with your explanations.

Troubleshooting Exercises

Exercise 16: Diagnose "DB2 Won't Start" (Intermediate)

Objective: Develop systematic troubleshooting skills.

Tasks: Deliberately cause and diagnose the following problems (in your development environment only):

  1. Port conflict: Start a second Db2 container on the same port (50000). Observe and document the error.
  2. Memory exhaustion: Modify a buffer pool to an unreasonably large size (e.g., 10 million pages), stop and restart Db2. Document the error.
  3. Missing environment: In a new terminal (without the DB2 profile sourced), try to run db2 CONNECT TO MERIDIAN. Document the error and the fix.

For each scenario: - Document the exact error message - Describe the diagnostic steps you would take - Explain the resolution

Deliverable: An error documentation table with error, diagnosis steps, and resolution for each scenario.

Exercise 17: Connection Troubleshooting (Fundamental)

Objective: Systematically diagnose connection failures.

Tasks: Create a troubleshooting checklist by attempting to connect under each of these conditions and documenting the result:

  1. Correct host, correct port, correct database, correct credentials -- should succeed.
  2. Correct host, wrong port (50001) -- document the error.
  3. Correct host, correct port, wrong database name -- document the error.
  4. Correct host, correct port, correct database, wrong password -- document the error.
  5. Wrong host (192.168.1.254) -- document the error (and the timeout behavior).

For each failed scenario, record: - The SQLCODE returned - The SQLSTATE returned - The human-readable error message - How you would explain this error to a junior developer

Deliverable: A troubleshooting reference card with all five scenarios documented.

Advanced Exercises

Exercise 18: Automated Environment Setup Script (Advanced)

Objective: Create a fully automated Db2 setup script.

Tasks: Write a shell script (setup-meridian.sh) that:

  1. Checks if Docker is installed and running
  2. Checks if a db2-meridian container already exists (and offers to remove it)
  3. Pulls the latest Db2 image if not present
  4. Starts the container with appropriate parameters
  5. Waits for DB2 to be ready (poll for the "ready" state)
  6. Copies the SQL scripts into the container
  7. Executes the database creation, data load, and verification scripts
  8. Prints a summary of the setup results
  9. Provides connection information (host, port, database, credentials)

The script should handle errors gracefully and provide helpful messages at each step.

Deliverable: The complete shell script, tested and working.

Exercise 19: Configuration Comparison Report (Advanced)

Objective: Compare your development configuration against recommended production values.

Tasks: 1. Export your current database configuration to a file: db2 GET DB CFG FOR MERIDIAN > dev_config.txt 2. Create a comparison table for these parameters with three columns: Parameter Name, Your Dev Value, Recommended Production Value (from Section 4.6 or IBM documentation). 3. For each parameter where your value differs from the production recommendation, explain: - Why the production value is different (what problem it addresses) - Whether the production value would be appropriate in your dev environment - What would happen if you applied the production value in dev 4. Write a script that would apply production-appropriate values to a Db2 LUW database, parameterized by the available system memory.

Deliverable: The comparison table, your analysis, and the configuration script.

Exercise 20: Multi-Container Environment (Advanced)

Objective: Simulate a realistic multi-database environment.

Tasks: 1. Using Docker Compose, create a configuration that starts two Db2 containers: - Container 1: db2-production (simulating production, 4 GB memory, larger buffer pools) - Container 2: db2-reporting (simulating a reporting replica, 2 GB memory) 2. Create the MERIDIAN database on both containers. 3. Load sample data into both. 4. From Container 2, catalog Container 1's database as a remote database. 5. Write a query on Container 2 that uses a federated nickname (or simply connects to Container 1) to compare data between the two databases. 6. Document the Docker Compose YAML and all setup steps.

Deliverable: The docker-compose.yml, setup scripts, and documentation.

Spaced Review Exercises (Chapters 1-3)

Exercise 21: Architecture Recall (Review -- Chapter 3)

Without referring back to Chapter 3, answer these questions:

  1. Name the four address spaces that make up DB2 for z/OS.
  2. What is the purpose of the buffer pool in DB2's architecture?
  3. Explain the difference between active logs and archive logs.
  4. On Db2 LUW, what is the role of the db2sysc process?

After answering from memory, check your answers against Chapter 3 and note any gaps.

Exercise 22: Relational Model Recall (Review -- Chapter 2)

Without referring back to Chapter 2, answer these questions:

  1. Define the five properties of a relation (table) in the relational model.
  2. What is the difference between a candidate key and a primary key?
  3. Explain Third Normal Form (3NF) in your own words.
  4. In the Meridian Bank schema, why does the ACCOUNTS table have a foreign key to both CUSTOMERS and BRANCHES?

After answering from memory, check your answers against Chapter 2.

Exercise 23: DB2 History and Positioning (Review -- Chapter 1)

Without referring back to Chapter 1:

  1. In what year was DB2 for MVS (z/OS) first released?
  2. Name two competing database systems and one advantage DB2 has over each.
  3. What does "DB2" stand for, and why was the name chosen?
  4. Explain the difference between DB2 for z/OS and Db2 LUW in terms of their primary use cases.

Check your answers against Chapter 1.

Exercise 24: Cross-Chapter Synthesis (Comprehensive)

Write a one-page essay (approximately 500 words) that connects concepts from Chapters 1 through 4:

"How does understanding DB2's history (Chapter 1) and relational foundations (Chapter 2) influence the architectural decisions (Chapter 3) that determine how we configure our environment (Chapter 4)?"

Your essay should reference specific examples, such as how the relational model's requirement for data integrity connects to log configuration, or how DB2's dual-platform heritage influences installation choices.

Deliverable: Your essay.

Exercise 25: Teach-Back Challenge (Comprehensive)

The best way to solidify learning is to teach others. Complete one of these:

  1. Write a blog post (800-1000 words) titled "Setting Up Db2 in Docker: A Complete Beginner's Guide" based on what you learned in this chapter.
  2. Create a cheat sheet (one page, front and back) of the most important Db2 commands and configuration parameters from this chapter.
  3. Record a 5-minute screencast walking through the Docker installation and first query execution.

Deliverable: Your chosen teach-back artifact.