How to generate test data in Db2 LUW

Recently I was preparing a case study for my DBA colleagues about dmctop. My case required some test data to be later modified and monitored, so I wanted to quickly and painlessly generate 100k rows in a simple table. I thought it would take me just a couple of minutes to generate that. It turns out I spent on it almost my entire day of work.

Generating test data was done so many times by so many Db2 LUW DBAs and I thought it would be easy to find quickly a blog post that tells me how to do it. But I couldn’t find it! Maybe my search engine failed, but nevertheless, here is my solution.

Unfortunately, in Db2 there is no quick function like called something like “generaterows()” that would do the job in a snap. Therefore I created a simple (but not stupid) bash script that loads random data into my table. At first I was inserting 1 row in a loop, but it was slow. But better solution was preparing data first and then do the batch load. Below is my script with a quick explanation later.

Please note that my script generates random data for just one varchar(200) column, but you can easily modify it so that it works with your table whatever columns it has.

#!/usr/bin/env bash
set -euo pipefail

DB="${DB:-}"                 # pass -d or set DB env var
ROWS="${ROWS:-100000}"       # pass -n or set ROWS
METHOD="${METHOD:-import}"   # import | load
TRUNCATE_FIRST="${TRUNCATE_FIRST:-no}"  # yes | no

SCHEMA="KACPER"
TABLE="DBA_DMCTOP_DEMO"
FULLNAME="${SCHEMA}.${TABLE}"

usage() {
  cat <<EOF
Usage: $0 -d <DBNAME> [-n <rows>] [--method import|load] [--truncate yes|no]
EOF
  exit 2
}

while [[ $# -gt 0 ]]; do
  case "$1" in
    -d) DB="$2"; shift 2;;
    -n) ROWS="$2"; shift 2;;
    --method) METHOD="$2"; shift 2;;
    --truncate) TRUNCATE_FIRST="$2"; shift 2;;
    -h|--help) usage;;
    *) echo "Unknown arg: $1" >&2; usage;;
  esac
done

[[ -n "$DB" ]] || usage
[[ "$METHOD" == "import" || "$METHOD" == "load" ]] || { echo "METHOD must be import|load" >&2; exit 2; }

# Optional: load DB2 env
[[ -f "$HOME/sqllib/db2profile" ]] && source "$HOME/sqllib/db2profile" >/dev/null 2>&1 || true

echo "Connecting to $DB ..."
db2 connect to "$DB" >/dev/null

if [[ "$TRUNCATE_FIRST" == "yes" ]]; then
  echo "Truncating $FULLNAME ..."
  db2 "TRUNCATE TABLE ${FULLNAME} IMMEDIATE"
fi

TMPDIR="$(mktemp -d)"
DATAFILE="${TMPDIR}/payload.del"

echo "Generating ${ROWS} payload rows into ${DATAFILE} ..."
# One column only: PAYLOAD
# Keep characters safe for DEL format (no delimiter |, no quotes)
awk -v n="$ROWS" '
BEGIN {
  srand();
  chars="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_-.:";
  clen=length(chars);
  for (i = 0; i < n; i++) {
    plen = 40 + int(rand() * 161);  # 40..200
    s="";
    for (j = 0; j < plen; j++) {
      s = s "" substr(chars, 1 + int(rand() * clen), 1);
    }
    print s;
  }
}
' > "$DATAFILE"

echo "Loading into ${FULLNAME} using ${METHOD} (ID will be generated) ..."
if [[ "$METHOD" == "load" ]]; then
  db2 "LOAD FROM ${DATAFILE} OF DEL INSERT INTO ${FULLNAME} (payload)"
else
  db2 "IMPORT FROM ${DATAFILE} OF DEL INSERT INTO ${FULLNAME} (payload)"
fi

echo "Row count now:"
db2 -x "SELECT COUNT(*) FROM ${FULLNAME}"

rm -rf "$TMPDIR"
db2 connect reset >/dev/null
echo "Done."

Let’s analyse it step by step, so that everyon can understand exactly how it works.

set -euo pipefail

This is some bash stuff that makes sure the script stops executing after any error or failure with connecting/loading etc.

DB="${DB:-}"                 # pass -d or set DB env var
ROWS="${ROWS:-100000}"       # pass -n or set ROWS
METHOD="${METHOD:-import}"   # import | load
TRUNCATE_FIRST="${TRUNCATE_FIRST:-no}"  # yes | no

SCHEMA="KACPER"
TABLE="DBA_DMCTOP_DEMO"
FULLNAME="${SCHEMA}.${TABLE}"

Here I configure my parameters. You can later pass their values during execution (it works the same as e.g. dmctop -d DBNAME) or in use the defaults.

Apart from the parameters you define the schema.table-name here.

usage() {
  cat <<EOF
Usage: $0 -d <DBNAME> [-n <rows>] [--method import|load] [--truncate yes|no]
EOF
  exit 2
}

This is a standard “usage” function. The text shows up when you ask for help of pass incorrect parameters.

while [[ $# -gt 0 ]]; do
  case "$1" in
    -d) DB="$2"; shift 2;;
    -n) ROWS="$2"; shift 2;;
    --method) METHOD="$2"; shift 2;;
    --truncate) TRUNCATE_FIRST="$2"; shift 2;;
    -h|--help) usage;;
    *) echo "Unknown arg: $1" >&2; usage;;
  esac
done

A while/case loop that processes command-line parameters one at a time:

• -d <DBNAME> sets the database name,
• -n <rows> overrides the row count,
• --method import|load chooses how data gets loaded,
• --truncate yes|no controls whether the table is cleared first,
• -h|--help shows usage,
• anything else prints “Unknown arg” and exits with usage.

shift 2 consumes both the flag and its value after handling each one.

[[ -n "$DB" ]] || usage
[[ "$METHOD" == "import" || "$METHOD" == "load" ]] || { echo "METHOD must be import|load" >&2; exit 2; }

# Optional: load DB2 env
[[ -f "$HOME/sqllib/db2profile" ]] && source "$HOME/sqllib/db2profile" >/dev/null 2>&1 || true

Fails with usage if no database name was given or with an error if METHOD is anything other than import or load.

Get Kacper Kubica’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Moreover, loading db2profile (it can be omitted in most cases).

echo "Connecting to $DB ..."
db2 connect to "$DB" >/dev/null

if [[ "$TRUNCATE_FIRST" == "yes" ]]; then
  echo "Truncating $FULLNAME ..."
  db2 "TRUNCATE TABLE ${FULLNAME} IMMEDIATE"
fi

TMPDIR="$(mktemp -d)"
DATAFILE="${TMPDIR}/payload.del"Loading db2profile (it can be omitted in most cases).

Connecting to the database, truncating the tables (if the flag is set).

At the end: setting up the temporary file just for storing the generated the data before it appears in the table.

echo "Generating ${ROWS} payload rows into ${DATAFILE} ..."
# One column only: PAYLOAD
# Keep characters safe for DEL format (no delimiter |, no quotes)
awk -v n="$ROWS" '
BEGIN {
  srand();
  chars="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_-.:";
  clen=length(chars);
  for (i = 0; i < n; i++) {
    plen = 40 + int(rand() * 161);  # 40..200
    s="";
    for (j = 0; j < plen; j++) {
      s = s "" substr(chars, 1 + int(rand() * clen), 1);
    }
    print s;
  }
}
' > "$DATAFILE"

And finally the core stuff — genrating data. Remember it just fills the varchar(200) column. You must modify it to generate data for your table (integers will be easier that chars).

It uses the awk program (run in a BEGIN block so it doesn't read input) to generate n random strings, one per line:

• srand() seeds the random number generator from the current time.
• chars defines the allowed alphabet: lowercase, uppercase, digits, and _-.: characters. The comment explains these are kept "safe for DEL format" — specifically avoiding | (the default Db2 DEL column delimiter) and double quotes, which would confuse the loader.
• clen is the length of that alphabet.
• For each of the n rows, it picks a random length between 40 and 200 characters (40 + int(rand() * 161)).
• It builds the string character by character by picking a random position in chars and appending that character.
• print s writes each finished string to the file.

The result is 100,000 (by default) random-ish strings of varying length, written to payload.del.

echo "Loading into ${FULLNAME} using ${METHOD} (ID will be generated) ..."
if [[ "$METHOD" == "load" ]]; then
  db2 "LOAD FROM ${DATAFILE} OF DEL INSERT INTO ${FULLNAME} (payload)"
else
  db2 "IMPORT FROM ${DATAFILE} OF DEL INSERT INTO ${FULLNAME} (payload)"
fi

And the second core part, loading (or importing the data into the table). In my case the table had 2 columns (ID that is automatically generated and PAYLOAD that is a varchar column for which I generated the data).

If you don’t know either you should load or import:

• IMPORT is slower but works on tables with constraints/triggers and supports logging — good for smaller or regular loads.
• LOAD is a bulk, high-speed utility that bypasses logging and is far faster for large volumes, but has more restrictions (e.g. tables need a rebind afterward, it can't fire triggers by default).

echo "Row count now:"
db2 -x "SELECT COUNT(*) FROM ${FULLNAME}"

rm -rf "$TMPDIR"
db2 connect reset >/dev/null
echo "Done."

Number of rows loaded is displayed. The temporary directory and its file are deleted. Connect reset is done.

I hope my script will be useful for you and you won’t have to reinvent the wheel as I did :)

The core part of my script