IBM DB2 for i: Tips

Qshell
Looking for the ability to run SQL statements and scripts from Qshell (STRQSH), the db2 command line utility provides this capability. The db2 utility uses the SQL CLI (Call Level Interface) and allows you to execute SQL statements directly, interactively, or from a file.

When processing SQL interactively or from a file, the db2 utility treats the backslash character at the end of a line as a continuation character. The backslash and newline character(s) are removed and the remaining text is used as the SQL statement.

Here's the syntax for the DB2 utility:
db2 [General Options] [Delimiter Options] [Connection Options] [SQL Source Options]

General Options:
-v:  Echo the SQL statement to standard output
-S:  Suppress spaces and padding in output, useful for viewing LOB columns containing text data

Delimiter Options:
Only one of the following can be specified.
-T, character:  Specified character is used as termination character
-t:  Use the semicolon as the statement termination character
-d:  Use Exclamation point (!) as the termination character

Connection Options:
-r rdbname:  Connect to specified remote database (must be name in WRKRDBDIRE). If not specified connection is to local database.
-u username:  The user profile name for connecting to remote database, can only be used with -r option
-p username:  The password to use on remote database connection

SQL Source Options:
SQL Statement:  SQL statement text. If statement contains spaces or shell characters, be sure to correctly quote on Qshell command line
-f filename default_lib:  Read & execute SQL statements from the specified file. Default_lib parameter is optional. When specified, it is used as the default library/schema for all statements
-i: Enter SQL statements interactively. Type 'quit' or 'exit' to end interactive SQL session

Some Examples:
    db2 select constraint_name from qsys2.syscst
    db2 -t -f mysqlfile.txt

Contents of mysqlfile.txt:
     select constraint_name from qsys2.syscst;
     create table qgpl.testtable (c1 integer);

Special Character & Command Support:

Perl
Perl is another handy utility that can also benefit from DB2 access. Information about installing Perl on the IBM i can be found at: http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/rzalf/rzalfinstaix.htm

After installing Perl, you can find a DB2 access example in this file. /usr/local/lib/perl5/dbsamples/dbtest.pl

Here's a similar example to get you started:
     use DBI;
     use DBD::DB2::Constants;
     use DBD::DB2;
     # $DBI::dbi_debug = 9;    # Turn on debug

     $dbh = DBI->connect("dbi:DB2:*LOCAL") or die;
# $dbh->trace(3);   # Turn on tracing

    $stmt = 'SELECT constraint_name FROM qsys2.syscst';
    $sth = $dbh->prepare($stmt) or die "prepare got error " .     $dbh->err;
    $sth->execute() or die "execute got error" . $dbh->err;
    $,=",";
    $arr = $sth->fetchall_arrayref;
    foreach $a (@$arr) {
    foreach $ref (@$a) {     print "$ref ";
   }
    print "\n";
}
1;

NOTE: This software is "freeware".
IBM does not provide service and support for the db2 utility or Perl script running on the IBM i