IBM DB2 for i: Tips

Qshell, Perl, and DB2 for i

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:

Lines starting with two dashes ( -- ) are comments
Lines starting with exclamation point are qshell commands
Lines startng with 'at' symbol (@) are CL commands
Connect command is ignored, utility uses local connection unless -r option is specified.
Echo command is a command built-in to the db2 utility and echoes the text
Exit or quit commands will end the db2 SQL session
Help and ? commands will list basic help
Terminate command is ignored
 

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.

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information