Executing DB2 Changes from Command Line: CLP Options Guide

When it comes to data changes (like altering a table or creating an index), it seems to me that most DBAs use GUI tools like TOAD, SQuirreL, DBeaver or Data Studio. These apps are nice, easy to use but sometimes it’s hard to get the output in the text format, they are often slow and generate much overhead for CPU and memory, not to mention that if you change employers you must often learn a new tool.

The older I am the more I appreciate the simplicity, reliability and versatility of pure command line. Here is how to properly execute data changes in the CLI (Command Line Interface).

Picture generated for this article in ChatGPT

DB2 command basics

The db2 command launches the Command Line Processor (CLP) for interactive sessions, single commands, or batch script execution.

Get Kacper Kubica’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This approach excels for structural changes, DML batches, or stored procedures directly from shell.

Core options: -t, -v, -m, -f

• -t: Enables semicolon (;) termination for SQL statements; disables backslash (/) line continuation. Perfect for standard SQL scripts.
• -v: Verbose mode — echoes commands before execution for easy verification and debugging.
• -m: Displays rows affected for INSERT, UPDATE, DELETE, MERGE operations — critical for data changes.
• -f <filename>: Reads input from a file (must be last in the sequence).

The powerhouse combo -tvmf runs: db2 -tvmf changes.sql – terminates on “;”, echoes commands, shows row counts, and outputs to stdout.

Logging options: -l and -z

• -l <filename>: Logs executed commands and their status to a history file (appends; must be last, e.g., db2 -tvl script.sql). Great for audits or session replay.
• -z <filename>: Redirects ALL output (results, errors, messages) to a file (appends). Example: db2 -tvmf script.sql -z output.log – ideal for CI/CD pipelines or reports.

These can combined but watch positioning — -f, -l, -z must trail others.

Practical examples

Example 1: DDL with -tvmf

Assuming you have the following file named add_index.sql:

CREATE INDEX idx_name ON table1 (col1);

Run: db2 connect to MY_DB; db2 -tvmf add_index.sql
Output: Echoes CREATE, shows 0 rows affected (DDL), confirms success.

Example 2: DML batch with logging

In this case you have a file named update_data.sql:

UPDATE table1 SET status='ACTIVE' WHERE id<1000;

Run: db2 -tvmf update_data.sql -l history.log -z full_output.log
Reveals updated rows, logs command, captures everything.

Example 3: Custom Delimiter -td@

For procedures (using @): db2 -td@ -vmf proc.sql – handles internal semicolons.

Piping Tip: db2 -tvmf script.sql | tee results.txt – screen and file output.

Advanced tips and gotchas

• DB2OPTIONS: Set globally via env: export DB2OPTIONS="-tvm" for sessions
• Interactive Tweaks: Use UPDATE COMMAND OPTIONS WITH '-m' inside CLP.
• Overlooked: -r (reports only, no errors), -s (stop on error), -x (quiet CSV headers), -o (no headers).

To sum up, -tvmf plus -l/-z delivers traceability, feedback, and captured output. Pair with cron or tee for automation. Faster than GUI tools for routine DB2 LUW tasks.