At the Circus we are removing the last vestige of our OpenLDAP implementation and moving it to Active Directory. As a result I’m going to document some of the odd scripts I have written as glue to help keep things running. Some of the scripts are too long to document and will just be kept in my script library, while others like the ones below, are generic enough that they might help someone else.

This is some documentation for how to query an AS400 from Linux. When I did this project I actually talked to one of the developers of the iSeries Access programs for help. He ended up sending me a snapshot rpm to get it all working.

Here are the configuration files.

#cat /etc/odbcinst.ini

[iSeries Access ODBC Driver]
Description		= iSeries Access for Linux ODBC Driver
Driver		    = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup		    = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Threading		= 2
DontDLClose		= 1
UsageCount		= 1

#cat /etc/odbc.ini 

[AS400]
Description		= iSeries Access ODBC Driver
Driver		    = iSeries Access ODBC Driver
System		    = as400.circus.org
UserID		    = UserName
Password		= Password
Naming		    = 0
DefaultLibraries= QGPL
Database		= 
ConnectionType  = 0
CommitMode      = 2
ExtendedDynamic	= 1
DefaultPkgLibrary = QGPL
DefaultPackage   = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView     = 0
AllowUnsupportedChar = 0
ForceTranslation= 0
Trace           = 1
DSN             = AS400

This is a simple script to list the drivers available on the system. This does not look like a script I wrote so I am reticent to take ownership.

#!/usr/bin/perl

use DBI;

my @drivers = DBI->available_drivers();

die "No drivers found. \n" unless @drivers;

# list the data sources
foreach my $driver ( @drivers )
{
	print "Driver: $driver\n";
	my @dataSources = DBI->data_sources( $driver );
	foreach my $dataSource ( @dataSources )
	{
		print "\tData Source: $dataSource\n";
	}
	print "\n";
}

exit

And the resulting output from the script above.

# perl drivers.pl 
Driver: DBM
	Data Source: DBI:DBM:f_dir=PHPDemo
	Data Source: DBI:DBM:f_dir=SQL-LDAP
	Data Source: DBI:DBM:f_dir=.
	Data Source: DBI:DBM:f_dir=DBD-ODBC-1.09
	Data Source: DBI:DBM:f_dir=unixODBC-2.2.8
	Data Source: DBI:DBM:f_dir=DBI-1.42

Driver: ExampleP
	Data Source: dbi:ExampleP:dir=.

Driver: File
	Data Source: DBI:File:f_dir=PHPDemo
	Data Source: DBI:File:f_dir=SQL-LDAP
	Data Source: DBI:File:f_dir=.
	Data Source: DBI:File:f_dir=DBD-ODBC-1.09
	Data Source: DBI:File:f_dir=unixODBC-2.2.8
	Data Source: DBI:File:f_dir=DBI-1.42

Driver: ODBC
	Data Source: DBI:ODBC:AS400

This is a simple script to query an AS400. You will have to get with your AS400 administrator because the the query is actually an odd series of fields, at least it was for our organization. I just wrote this to learn how to query the AS400, it was never used in production.

#!/usr/bin/perl

# 2005-09-12 Jud Bishop
# Released under the New BSD License.

# This script uses the DBI::ODBC driver to interact with an AS 400
# database.  Make sure that you use a system DSN rather than a 
# user defined one or you will have trouble later. 

use strict;
use DBI;

my $UID="UserName";
my $PW="Password";
my $DSN="AS400";


print "connect\n";
my $dbh = DBI->connect ( "dbi:ODBC:$DSN", $UID, $PW, {
#	LongTruncOk => 1
	PrintError => 1
})
	or die "connection failed to database $DBI::errstr\n";

## Set up tracing
print "trace\n";
unlink 'trace.log' if -e 'trace.log';
DBI->trace( 2, 'trace.log' );

## Prepare the SQL statement for execution
print "prepare\n";
my @t = localtime(time);
my $sql_st = $dbh->prepare( " SELECT ALL X FROM Y WHERE I = J ORDER BY Z ");

print "execute\n";
$sql_st->execute()
	or die "Cannot execute SQL statement $DBI::errstr\n";

my @row;
while ( @row = $sql_st->fetchrow_array() )
{
	print "@row\n";
}
warn "Data fetch terminated by error $DBI::errstr\n"
	if $DBI::err;

$dbh->disconnect or warn "disconnection failed $DBI::errstr\n";

exit