Recently starting the IBM i BASH Tools repository has prompted me to think  of some scripts that would be useful. One that I have wanted for quite awhile is a Command Line Interface (CLI) for DB2 on the IBM i.

Calling DB2 from Command Line

Calling DB2 from the command line is done in the same way one would call any system command.

system -i "call QSYS/QZDFMDB2 parm('select * from sysibm.tables')"

Repeatedly Asking for User Input Until Exit

Figuring out how to read user input repeatedly until exit turned out to be a little more complex than I anticipated, but it’s still a simple while loop.

while true
do
  printf "> "
  read input

  if [[ $input != exit* ]]
    then
      # Use input
    else
      echo "Goodbye"
      exit
    fi
done

Allowing for Input History

I wanted to allow the user to use the up and down arrows to cycle through the history of input. In order to do this, the input needs to be read in the while loop condition and history needs to be enabled for the input.

while IFS= read -e -p "> " input
do
    history -s "$input"

  if [[ $input != exit* ]]
    then
      # Use input
    else
      echo "Goodbye"
      exit
    fi
done

Db2.sh

Here’s the finished script:

#!/bin/bash

cmd=`basename "$0"`

print_use() {
  echo "Use: $cmd"
  echo "e.g.: $cmd"
  exit 1
}


if [ $# -eq 0 ]
then
  # Loop over SQL and enable history of SQL (up and down arrows)
  while IFS= read -e -p "> " sql
  do
    history -s "$sql"

    # Process SQL if it doesn't start with exit
    if [[ $sql != exit* ]]
    then
      echo "Running \`$sql\`"
      system -i "call QSYS/QZDFMDB2 parm('$sql')"
    else
      echo "Goodbye"
      exit
    fi
  done
else
  print_use
fi

Here it is in action: