Qshell db2 Utility on IBM i – Run, Automate & Validate SQL Scripts the Right Way

Compact, scriptable, and surprisingly powerful. How to use the Qshell db2 utility properly — with real-world gotchas, practical examples, and a clean workflow.

1. What is the Qshell db2 Utility?

This becomes especially handy when you already keep your SQL scripts in the IFS — whether under /home/youruser/sql, in a Git checkout, a deployment folder, or a test environment.

2. When Is It Actually Useful?

This tool isn’t meant to replace IBM i Access Client Solutions, Navigator for i, or proper deployment processes. But it’s incredibly useful when you want to run simple SQL actions reliably and repeatedly.

3. Don’t Confuse It With: RUNSQLSTM, ACS, and PASE db2util

There are several ways to run SQL on IBM i. That’s great — but it’s also a common source of confusion because similarly named tools don’t necessarily do the same thing.

4. Basics: Interactive, Direct, or From a File

You can start Qshell with the QSH command or call a Qshell command directly from CL. For quick tests, a simple SELECT is all you need.

qsh

# Run a direct SQL statement
db2 "select current date from sysibm.sysdummy1"

If your SQL statement contains spaces or shell special characters, wrap it in quotes. This isn’t some IBM i quirk — it’s just how shells work. For multi-line statements, the utility also treats a backslash at the end of a line as a continuation character.

db2 -i

select current timestamp from sysibm.sysdummy1;
quit

For real repeatability, SQL files are almost always better than typing commands by hand. This is where the utility really shines.

db2 -t -v -f /home/youruser/sql/createTables.sql

5. Key Options: -t, -f, -v, -S and Default Library

The most important options are straightforward, but they’re what separate “it worked by accident” from “it runs reliably every time.”

# The second parameter after the filename sets the default library.
# This helps when your SQL script uses unqualified table names.
db2 -t -v -f /home/youruser/sql/createTables.sql TTDEMO

6. Example Repository: SQL Scripts You Can Test Right Now

Alongside this article you’ll find matching example scripts in the Zeus-Commons-APIs GitHub repository under examples/sqlScriptsSampleData/db2_400. The folder contains several SQL files, including createTables.sql, insertData.sql, and dropTables.sql. The structure is intentionally simple: create tables, load sample data, verify results, then clean everything up.

The scripts are deliberately straightforward:

• createTables.sql creates sample tables such as AGENTS, CUSTOMERS, ORDERS, AGENT_REVENUE, plus small EAV demo tables.
• insertData.sql loads compact sample data in an order that respects foreign key relationships.
• dropTables.sql cleans up the tables while respecting dependencies (child tables are dropped before parent tables).

Example: Proper Drop Order

When dropping tables, the actual constraint dependencies matter. In this example, ORDERS references both CUSTOMERS and AGENTS, and CUSTOMERS references AGENTS. Therefore we drop ORDERS first, then CUSTOMERS, then AGENTS. The summary table AGENT_REVENUE and the EAV demo tables have no dependencies in this setup.

SET SCHEMA YOUR_LIB;

DROP TABLE ORDERS;
DROP TABLE CUSTOMERS;
DROP TABLE AGENTS;
DROP TABLE AGENT_REVENUE;
DROP TABLE EAV_DATA;
DROP TABLE EAV_METADATA;

If tables don’t exist yet, a simple drop script will produce expected errors or messages. For a learning example that’s perfectly fine — as long as you’re working in a test library. For anything closer to production you would add existence checks, rollback logic, proper logging, and deployment rules.

7. A Clean, Practical Workflow with SQL Scripts

For a small test setup I like to think in three clear phases: prepare, replace schema placeholders, execute and verify.

Phase 1: Prepare the library

CRTLIB LIB(TTDEMO) TEXT('Demo library for Qshell db2 examples')

Phase 2: Replace schema placeholders in the scripts

The example scripts start with SET SCHEMA YOUR_LIB;. Replace it with your test library before running anything. Don’t experiment in QGPL just because it’s convenient right now. QGPL is patient — that’s exactly the problem.

cd /home/youruser/sql/db2_400

# Create library-specific copies for TTDEMO
sed 's/SET SCHEMA YOUR_LIB/SET SCHEMA TTDEMO/g' createTables.sql > createTables_TTDEMO.sql
sed 's/SET SCHEMA YOUR_LIB/SET SCHEMA TTDEMO/g' insertData.sql   > insertData_TTDEMO.sql
sed 's/SET SCHEMA YOUR_LIB/SET SCHEMA TTDEMO/g' dropTables.sql   > dropTables_TTDEMO.sql

Phase 3: Execute and verify

db2 -t -v -f /home/youruser/sql/db2_400/createTables_TTDEMO.sql

db2 -t -v -f /home/youruser/sql/db2_400/insertData_TTDEMO.sql

db2 -t -v "select count(*) as order_count from TTDEMO.ORDERS;"

8. Automating from CL, Qshell & Job Scheduler

You can call the utility directly from CL. This is handy for small technical jobs or when you version your SQL scripts in the IFS.

QSH CMD('db2 -t -v -f /home/youruser/sql/db2_400/createTables_TTDEMO.sql')

For repeatable flows, a small shell script is often more comfortable. You can keep paths, library, and log file in one place.

#!/bin/sh
set -e

LIB="TTDEMO"
BASE="/home/youruser/sql/db2_400"
LOG="/home/youruser/sql/db2_400/run_${LIB}.log"

{
  echo "Start: $(date)"
  db2 -t -v -f "${BASE}/createTables_${LIB}.sql"
  db2 -t -v -f "${BASE}/insertData_${LIB}.sql"
  db2 -t -v "select count(*) as order_count from ${LIB}.ORDERS;"
  echo "End: $(date)"
} > "${LOG}" 2>&1

9. Output, Logging & Troubleshooting

The Qshell utility is deliberately simple. That’s both its strength and its weakness. There’s no pretty UI, no magic diagnostics, and no friendly dialog that saves you from your own SQL file.

db2 -t -v -f /home/youruser/sql/check.sql \
  > /home/youruser/sql/check.log 2>&1

If you want to process query results programmatically, -S can help by reducing spaces and padding. For real interfaces I usually prefer generating CSV or JSON explicitly, using views, or writing a small export program. The Qshell db2 utility is great for execution and simple output — it’s not a full ETL framework.

db2 -S "select agent_code, cumulative_revenue from TTDEMO.AGENT_REVENUE"

10. Security: Libraries, Authorities & Passwords

With SQL scripts, security isn’t just about passwords. The most dangerous mistake is often using the wrong library.

• •Work with test libraries: Never use production libraries for examples or blog code.
• •Qualify objects deliberately: Either use explicit MYLIB.TABLE or consciously work unqualified with a default library.
• •Double-check drop scripts: Especially with generic names like ORDERS, CUSTOMERS, or TEST.
• •Never store passwords in scripts: The -u and -p options belong in remote connection context with -r. Clear-text passwords in scripts are a bad idea.
• •Keep authorities minimal: A technical user for DDL needs different rights than a read-only check user.

11. Best Practices

12. Final Thoughts

Glossary

Key terms from this article, explained simply.

ACS

IBM i Access Client Solutions — the main client tool for SQL, administration, 5250 sessions, and many other IBM i functions.

AS/400

Historical platform name. Today we correctly refer to it as IBM i on IBM Power.

CL

Control Language on IBM i — commonly used for job control, system commands, and automation.

CLI

Call Level Interface — the interface used by the Qshell db2 utility to access SQL.

Db2 for i

The integrated relational database that comes with the IBM i platform.

Default Library

The library (schema) used to resolve unqualified SQL object names.

IFS

Integrated File System on IBM i. This is where stream files, shell scripts, SQL files, and Git checkouts live.

Qshell

The Unix-like shell and scripting environment on IBM i, started for example with QSH.

RUNSQLSTM

IBM i command to execute SQL statements from a source — frequently used from CL jobs.

Terminator

The character that marks the end of an SQL statement in a file (usually the semicolon).

WRKRDBDIRE

IBM i command to manage relational database directory entries — relevant for remote database connections.

Sources & Further Reading