Appendix C: Environment Setup Guide

This appendix walks you through setting up a working DB2 environment so you can follow the examples and exercises in this book. We cover three paths: Db2 Community Edition for local development, z/OS access for mainframe practitioners, and GUI tool configuration for both.

C.1 Db2 Community Edition (LUW)

Db2 Community Edition is free for development and light production use (up to 16 GB of memory and 4 cores). It is the fastest way to get a working DB2 environment.

C.1.1 Docker Installation (Recommended)

Docker is the simplest path. It works on Linux, macOS, and Windows (via Docker Desktop).

Step 1: Pull the image.

docker pull icr.io/db2_community/db2

Step 2: Run the container.

docker run -d \
  --name db2server \
  --privileged=true \
  -p 50000:50000 \
  -e LICENSE=accept \
  -e DB2INST1_PASSWORD=db2pass \
  -e DBNAME=TESTDB \
  -v db2data:/database \
  icr.io/db2_community/db2

Key parameters: - --privileged=true --- Required for DB2's shared memory configuration. - -p 50000:50000 --- Maps the default DB2 port. - -e DBNAME=TESTDB --- Automatically creates a database named TESTDB on first start. - -v db2data:/database --- Persists data across container restarts.

Step 3: Wait for initialization. The first startup takes 5-10 minutes. Monitor progress:

docker logs -f db2server

Look for the message: (*) Setup has completed.

Step 4: Connect from inside the container.

docker exec -it db2server bash -c "su - db2inst1"
db2 connect to TESTDB
db2 "SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1"

Step 5: Connect remotely. Use any DB2 client on localhost:50000, database TESTDB, user db2inst1, password db2pass.

C.1.2 Native Linux Installation

For bare-metal or VM installations on supported Linux distributions (Red Hat 8/9, Ubuntu 20.04/22.04, SUSE 15):

Step 1: Download. Obtain the Db2 Community Edition installer from the IBM website. You will need an IBM ID (free).

Step 2: Extract and install.

tar -xzf v11.5.9_linuxx64_dec.tar.gz
cd server_dec
sudo ./db2_install -b /opt/ibm/db2/V11.5 -p SERVER -f NOTSAMP

Step 3: Create an instance.

sudo /opt/ibm/db2/V11.5/instance/db2icrt -u db2fenc1 db2inst1

Step 4: Start the instance and create a database.

su - db2inst1
db2start
db2 "CREATE DATABASE TESTDB USING CODESET UTF-8 TERRITORY US PAGESIZE 32768"

We recommend PAGESIZE 32768 (32 KB) for modern workloads unless you have a specific reason for smaller pages. Larger pages reduce B+ tree height and improve range scan performance.

C.1.3 Windows Installation

Download the Windows installer from the IBM website. Run the graphical installer, accept defaults, and specify an instance password. After installation:

db2cmd
db2 CREATE DATABASE TESTDB USING CODESET UTF-8 TERRITORY US PAGESIZE 32768
db2 CONNECT TO TESTDB

The db2cmd command opens a DB2-aware command window with the correct environment variables.

C.2 z/OS Access Options

If you are working with DB2 for z/OS, you typically access it through one of the following methods.

C.2.1 ISPF/SPUFI

The traditional method. From a 3270 terminal emulator (such as IBM Personal Communications, Mocha TN3270, or x3270):

1. Log into TSO.
2. Enter ISPF (option 6 for command entry, or navigate the panels).
3. Access SPUFI (SQL Processing Using File Input): - Input data set: a PDS member containing your SQL - Output data set: where results are written - Set defaults: autocommit, isolation level, max rows

SPUFI is batch-oriented. You write SQL in a data set member, submit it, and review output. It is spartan but reliable, and it is how DB2 was used for decades.

C.2.2 Zowe CLI

Zowe is an open-source framework that provides a modern CLI and REST API for z/OS. Install it via npm:

npm install -g @zowe/cli
zowe plugins install @zowe/db2-for-zos-plugin

Create a connection profile:

zowe profiles create db2-profile mydb2 \
  --host mainframe.example.com \
  --port 5040 \
  --database DBPROD \
  --user USERID \
  --password PASS

Execute SQL:

zowe db2 execute sql --query "SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 ROWS ONLY"

Zowe is the preferred path for developers who want to interact with z/OS DB2 from a modern workstation.

C.2.3 IBM Data Server Driver and JDBC

For programmatic access, install the IBM Data Server Driver (available free from IBM Fix Central) and connect via JDBC:

jdbc:db2://mainframe.example.com:5040/DBPROD

This works with DBeaver, IBM Data Studio, and any JDBC-compatible tool.

C.2.4 Db2 Connect

IBM Db2 Connect is a licensed product that provides DRDA connectivity from LUW clients to z/OS. If your organization has Db2 Connect, it enables features like two-phase commit and Sysplex workload balancing from distributed applications.

C.3 DBeaver Setup

DBeaver is a free, open-source database tool that supports DB2 on both platforms. It is the recommended GUI for this book's exercises.

C.3.1 Installation

Download DBeaver Community Edition from https://dbeaver.io. Install for your platform. Java is bundled.

C.3.2 Adding a DB2 Connection

1. Click Database > New Database Connection.
2. Select IBM DB2 from the list.
3. Enter connection parameters: - Host: localhost (for Docker) or your server hostname - Port: 50000 (LUW default) or 5040 (z/OS, varies) - Database: TESTDB - Username: db2inst1 - Password: your password
4. Click Test Connection. DBeaver will prompt to download the DB2 JDBC driver automatically.
5. Click Finish.

C.3.3 DBeaver Tips for DB2

• Schema browser. Expand the connection to see schemas, tables, views, indexes, stored procedures.
• SQL editor. Open with Ctrl+] or from the SQL Editor menu. Execute with Ctrl+Enter (single statement) or Alt+X (entire script).
• Explain plan. Select a query and press Ctrl+Shift+E to see the access plan. DBeaver renders it as a visual tree.
• Data export. Right-click a table > Export Data. Supports CSV, SQL inserts, JSON, and more.
• ER diagrams. Right-click a schema > View Diagram for a quick ER diagram of related tables.

C.4 IBM Data Studio Setup

IBM Data Studio is IBM's free Eclipse-based IDE for DB2. It provides deeper DB2 integration than DBeaver, including stored procedure debugging, query tuning advisors, and change management.

C.4.1 Installation

Download from IBM's website (search "IBM Data Studio"). Requires an IBM ID. Available for Windows, Linux, and macOS.

C.4.2 Key Features

• SQL and XQuery editor with syntax highlighting and code completion.
• Visual Explain --- detailed access plan visualization showing costs, cardinalities, and access methods at each operator node.
• Query tuner --- suggests indexes and rewrites for poorly performing queries.
• Stored procedure debugger --- set breakpoints and step through SQL PL procedures.
• Routine development --- deploy stored procedures and UDFs from the IDE.
• Change management --- compare schemas, generate ALTER scripts.

C.4.3 Connecting to DB2

1. Switch to the Database Development perspective.
2. In the Database Connections view, right-click > New Connection.
3. Select DB2 for Linux, UNIX, and Windows or DB2 for z/OS.
4. Fill in host, port, database, credentials.
5. Test and finish.

C.5 Db2 CLP (Command Line Processor) Reference

The CLP is your primary interface for DB2 administration and ad-hoc SQL on LUW.

C.5.1 Starting the CLP

# Interactive mode
db2

# Command mode (single command)
db2 "SELECT * FROM SYSCAT.TABLES FETCH FIRST 5 ROWS ONLY"

# Batch mode (execute a file)
db2 -tvf myscript.sql

Flags for batch mode: - -t --- Use semicolon as statement terminator - -v --- Echo each statement before execution - -f filename --- Read from file - -z logfile --- Write output to log file

C.5.2 Essential CLP Commands

# Database operations
db2 "CREATE DATABASE mydb USING CODESET UTF-8 TERRITORY US PAGESIZE 32768"
db2 "CONNECT TO mydb"
db2 "CONNECT RESET"
db2 "LIST DATABASE DIRECTORY"
db2 "LIST ACTIVE DATABASES"

# Instance operations
db2start
db2stop [force]
db2 "GET INSTANCE"
db2 "LIST APPLICATIONS"
db2 "FORCE APPLICATION (handle)"

# Configuration
db2 "GET DBM CFG"
db2 "GET DB CFG FOR mydb"
db2 "UPDATE DBM CFG USING INTRA_PARALLEL YES"
db2 "UPDATE DB CFG FOR mydb USING LOGFILSIZ 65536"

# Catalog queries
db2 "LIST TABLES FOR SCHEMA DB2INST1"
db2 "DESCRIBE TABLE EMPLOYEE"
db2 "DESCRIBE INDEXES FOR TABLE EMPLOYEE SHOW DETAIL"

# Export / Import / Load
db2 "EXPORT TO data.csv OF DEL SELECT * FROM EMPLOYEE"
db2 "IMPORT FROM data.csv OF DEL INSERT INTO EMPLOYEE_COPY"
db2 "LOAD FROM data.csv OF DEL INSERT INTO EMPLOYEE_COPY"

C.5.3 CLP Formatting Options

db2 -x "SELECT ..."        # Suppress column headings
db2 +c "SELECT ..."        # Turn off autocommit (use COMMIT/ROLLBACK explicitly)
db2 "EXPORT TO out.csv OF DEL MODIFIED BY NOCHARDEL COLDEL| SELECT ..."

C.6 Environment Variables and Configuration

C.6.1 LUW Environment Variables

Set these in the instance owner's .bash_profile or via db2set:

db2set DB2COMM=TCPIP
db2set DB2_PARALLEL_IO=*
db2set -all    # Show all registry variables

C.6.2 z/OS Configuration

On z/OS, DB2 configuration is managed through:

• ZPARM (DSNZPARM): System-level parameters. Modified via installation panels or ALTER SYSTEM command (v12+).
• Subsystem parameters: Buffer pool sizes, log parameters, thread limits.
• Application defaults: Bind options, plan/package defaults.

Key ZPARM categories: - Thread management: MAX USERS, IDLE THREAD TIMEOUT - Logging: OUTBUFF (log output buffer size), LOGLOAD (checkpoint frequency) - Performance: CACHEDYN (dynamic SQL caching), MAXKEEPD (max cached dynamic statements) - Distributed: DDF THREADS, CMTSTAT (default commit scope)

C.6.3 Verifying Your Setup

Run this quick validation after installation:

-- Verify connectivity
SELECT CURRENT TIMESTAMP, CURRENT SCHEMA, CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

-- Verify catalog access
SELECT COUNT(*) FROM SYSCAT.TABLES;          -- LUW
SELECT COUNT(*) FROM SYSIBM.SYSTABLES;       -- z/OS

-- Create and use a test table
CREATE TABLE TEST_SETUP (ID INT, NAME VARCHAR(50));
INSERT INTO TEST_SETUP VALUES (1, 'Setup Verified');
SELECT * FROM TEST_SETUP;
DROP TABLE TEST_SETUP;

If all four statements succeed, your environment is ready for the exercises in this book.

C.7 Sample Database

Several chapters reference the IBM SAMPLE database. On LUW, create it with:

db2sampl -sql -xml -force

This creates the SAMPLE database with the classic EMPLOYEE, DEPARTMENT, PROJECT, and related tables, plus XML sample data. On z/OS, equivalent sample tables are typically installed during DB2 installation in the DSN8 schema.

If you prefer a custom practice database, Chapters 2 and 3 provide DDL scripts for the example schema used throughout this book.

C.8 Troubleshooting Common Setup Issues

C.8.1 Docker Container Fails to Start

Symptom: Container exits immediately or loops during initialization.

Cause: Insufficient memory or missing --privileged flag.

Fix: - Ensure Docker Desktop has at least 4 GB of memory allocated (Settings > Resources > Memory). - Always include --privileged=true in the docker run command. - Check logs: docker logs db2server for specific error messages. - On macOS/Windows, ensure file sharing is enabled for the volume mount path.

C.8.2 Connection Refused on Port 50000

Symptom: Client tools report "Connection refused" or timeout.

Cause: DB2 instance is not started, or the port is not mapped correctly.

Fix: 1. Verify the instance is running: docker exec -it db2server bash -c "su - db2inst1 -c db2gcf -s" 2. Verify the port mapping: docker port db2server 3. Check firewall rules on the host. 4. On Windows, verify that no other service (such as another DB2 instance or another database) is already using port 50000.

C.8.3 SQLCODE -1024: Database Connection Required

Symptom: Commands fail with "A database connection does not exist."

Fix: You must connect before executing SQL:

db2 "CONNECT TO TESTDB"
db2 "SELECT * FROM SYSCAT.TABLES FETCH FIRST 5 ROWS ONLY"

In scripts, always include the CONNECT statement at the top.

C.8.4 License Acceptance Errors

Symptom: "The license agreement was not accepted" during installation.

Fix: For Docker, ensure -e LICENSE=accept is in the run command. For native installation, run db2licm -a /path/to/license/db2ce.lic after installation. Community Edition requires accepting the license terms during first use.

C.8.5 Unicode / Code Page Mismatches

Symptom: Special characters appear garbled, or you get SQLCODE -332 (character conversion error).

Fix: Ensure the database was created with USING CODESET UTF-8. Verify the client code page:

db2set DB2CODEPAGE=1208    # UTF-8

For Java applications, add charset=UTF-8 or ensure the JVM default encoding matches the database code set.

C.8.6 z/OS SPUFI Output Truncation

Symptom: Query results in SPUFI are cut off or show only partial column data.

Fix: Adjust SPUFI defaults: - Increase the MAX SELECT LINES value. - Set RECORD LENGTH to a larger value in the output data set allocation. - Use VARCHAR column formatting to avoid fixed-width padding.

C.9 Recommended Development Workflow

For readers working through this book, we recommend the following workflow:

1. Setup. Install Db2 Community Edition via Docker (Section C.1.1). This takes about 15 minutes.

2. Primary tool. Use DBeaver (Section C.3) for SQL editing, query execution, and explain plan review. Its visual explain feature is particularly valuable for Chapters 8 and 12.

3. Command line. Use the CLP (Section C.5) for administrative tasks: database creation, configuration changes, backup/restore, RUNSTATS, and REORG.

4. Scripts. Keep SQL scripts in a project directory. Use the CLP batch mode (db2 -tvf script.sql) for repeatable execution.

5. Version control. Store DDL scripts and test SQL in Git. This builds good habits for production work where schema changes must be tracked and reviewed.

6. Practice database. Create the book's example schema (Chapters 2-3) in your local instance. Populate it with the sample data provided. All exercises assume this schema.

For z/OS practitioners without local mainframe access, the Docker-based LUW environment covers 90% of the SQL concepts in this book. Platform-specific chapters (z/OS administration, data sharing, JCL utilities) are best practiced on an actual z/OS system through your organization or the IBM Z Development and Test Environment.

C.10 IDE Keyboard Shortcuts

DBeaver Shortcuts

IBM Data Studio Shortcuts

CLP Tips