By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,587 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,587 IT Pros & Developers. It's quick & easy.

Connect to remote server using db2 and Perl

P: n/a
Hello

I have a problem to resolve: I wrote a Perlscript which caches data from a
server (local on my machine) I would like to have a other connection to a
remote server but I don't know how to define the servername / hostname in
my Perl Progrem..

Here is the code:
#!/usr/bin/perl

################################################## ###########################
# Code to connect to a database (local and get / put somme parameters in
it
#

################################################## ###########################

select STDERR; $ |= 1 ;
select STDOUT; $ |= 1 ;

#!/usr/bin/perl -w

use DBI;
use DBD::DB2;
use DBD::DB2::Constants;

my $dsn = 'DBI:DB2:LOGONDB';

my $uid = 'testusr';
my $pwd = 'test';

# connect to the database
print "----------------------------------------------------------\n";
print "Connect to Database LOGONDB \n";
print "----------------------------------------------------------\n";
print "Connecting to database... \n\n";
my $dbh = DBI->connect( $dsn, $uid, $pwd , {AutoCommit => 0 })
|| die "Can't connect to $database: $DBI::errstr";
print "Connected to database. \n\n\n";
print "Program Version Information \n\n";
print "Operating System = $^O\n";
print "Perl Binary = $^X\n";
print "Perl Version = $]\n";
print "DBI Version = $DBI::VERSION\n";
print "DBD::DB2 Version = $DBD::DB2::VERSION\n\n";

print "Database Connection Information \n\n";
printf( "Server Port : %s\n", $dbh->get_info( SQL_SERVER_PORT ) );
printf( "Server Instance : %s\n", $dbh->get_info( SQL_SERVER_NAME ) );
printf( "Database Server : %s\n", $dbh->get_info( SQL_DBMS_NAME ) );
printf( "Database Version : %s\n", $dbh->get_info( SQL_DBMS_VER ) );
printf( "Database Alias : %s\n", $dbh->get_info( SQL_DATA_SOURCE_NAME
) );
printf( "Database Codepage : %s\n", $dbh->get_info( 2519 ) );
printf( "Application Codepage: %s\n", $dbh->get_info( 2520 ) );
printf( "Authorization Id : %s\n", $dbh->get_info( SQL_USER_NAME ) );
printf( "Max Identifier Len : %s\n", $dbh->get_info(
SQL_MAX_IDENTIFIER_LEN ) );
printf( "Max Table Name Len : %s\n", $dbh->get_info(
SQL_MAX_TABLE_NAME_LEN ) );
printf( "Max Index Size : %s\n", $dbh->get_info( SQL_MAX_INDEX_SIZE )
);
printf( "Max Columns in Table: %s\n", $dbh->get_info(
SQL_MAX_COLUMNS_IN_TABLE ) );
printf( "Max Columns in Index: %s\n", $dbh->get_info(
SQL_MAX_COLUMNS_IN_INDEX ) );

# insert rows into the 'staff' table
BasicInsert();

# perform a query with the 'org' table
BasicQuery();

# update a set of rows in the 'staff' table
BasicUpdate();

# delete a set of rows from the 'staff' table
BasicDelete();

# no more data to be fetched from the statement handle
$sth ->finish;

# rollback the transaction
printf "Rollback the transaction. \n\n";
$dbh ->rollback;

# disconnect from the database
print "Disconnecting from database...\n\n";
$dbh ->disconnect
|| die "Can't disconnect from database: $DBI::errstr";
print "Disconnected from database. \n\n";
################################################## ###########################
# Description: This subroutine demonstrates how to insert rows into a
table.
# Input : None
# Output : Returns 0 on success, exits otherwise.

################################################## ###########################
sub BasicInsert
{
# display contents of the 'staff' table before inserting rows
DisplayStaffTable();

print "---------------------------------------------------------- \n";
print "BasicInsert Staff Table \n";
print "---------------------------------------------------------- \n";

# use the INSERT statement to insert data into the 'staff' table.
print "Execute the Statement: \n";
print " INSERT INTO staff(id, name, dept, job, year, salary) \n";
print " VALUES(380, 'Pearce', 35, 'Clerk', 5 , 13217.50), \n";
print " (390, 'Hachey', 39, 'Mgr', 3 ,21270.00), \n";
print " (400, 'Wagland', 45, 'Clerk', 10 , 14575.00) \n";
my $sql = qq(INSERT INTO staff(id, name, dept, job, year, salary)
VALUES ( 380 , 'Pearce' , 38 , 'Clerk' , 5 , 13217.50 ),
( 390 , 'Hachey' , 38 , 'Mgr' , 3 ,21270.00 ),
( 400 , 'Wagland' , 38 , 'Clerk' , 10 , 14575.00
));

# execute the insert statement
$dbh -> do ( $sql );

# display the content in the 'staff' table after the INSERT.
DisplayStaffTable();

# rollback the transaction
# printf "Rollback the transaction. \n\n";
# $dbh ->rollback;

return 0 ;
} # BasicInsert

################################################## ###########################
# Description: This subroutine demonstrates how to perform a standard
query.
# Input : None
# Output : Returns 0 on success, exits otherwise.

################################################## ###########################
sub BasicQuery
{
print "---------------------------------------------------------- \n" ;
print "BasicQuery Staff Table \n";
print "---------------------------------------------------------- \n" ;

# set up and execute the query
print "Execute the Statement: \n";
print " SELECT * FROM staff WHERE salary > 14000 \n";

my $sql = qq(SELECT * FROM staff WHERE salary > 14000 );

# call PrepareExecuteSql subroutine defined in DB2SampUtil.pm
$sth = PrepareExecuteSql( $dbh , $sql );

# output the results of the query
while (( $deptnumb , $location ) = $sth ->fetchrow_array)
{
printf " %-8d %-14s \n " , $deptnumb , $location ;
}

return 0 ;
} # BasicQuery

################################################## ###########################
# Description: This subroutine demonstrates how to update rows in a
table.
# Input : None
# Output : Returns 0 on success, exits otherwise.

################################################## ###########################
sub BasicUpdate
{
# display contents of the 'staff' table before updating
DisplayStaffTable();

print "---------------------------------------------------------- \n";
print "BasicUpdate Staff Table \n";
print "---------------------------------------------------------- \n";

# update the data of table 'staff' by using a subquery in the SET clause
print "Execute the Statement: \n";
print " UPDATE staff \n";
print " SET salary = (SELECT MIN(salary) \n";
print " FROM staff \n";
print " WHERE id >= 310) \n";
print " WHERE id = 310 \n";

my $sql = qq(UPDATE staff
SET salary = (SELECT MIN(salary)
FROM staff
WHERE id >= 310 )
WHERE id = 310 );

# execute the update statement
$dbh -> do ( $sql );

# display the final content of the 'staff' table
DisplayStaffTable();

# rollback the transaction
# printf "Rollback the transaction. \n\n";
# $dbh ->rollback;

return 0 ;
} # BasicUpdate

################################################## ###########################
# Description: This subroutine demonstrates how to delete rows from a
table.
# Input : None
# Output : Returns 0 on success, exits otherwise.

################################################## ###########################
sub BasicDelete
{
# display contents of the 'staff' table
DisplayStaffTable();

print "---------------------------------------------------------- \n";
print "BasicDelete Staff Table \n";
print "---------------------------------------------------------- \n";

# delete rows from the 'staff' table where id >= 310 and salary > 20000
print "Execute the Statement: \n";
print " DELETE FROM staff WHERE id >= 310 AND salary > 20000 \n";

my $sql = qq(DELETE FROM staff
WHERE id >= 310
AND salary > 20000 );

# execute the delete statement
$dbh -> do ( $sql );

# display the final content of the 'staff' table
DisplayStaffTable();

# rollback the transaction
# printf "Rollback the transaction. \n\n";
# $dbh ->rollback;

return 0 ;
} # BasicDelete

################################################## ###########################
# Description: This subroutine displays the contents from the 'staff'
table.
# Input : None
# Output : Returns 0 on success, exits otherwise.

################################################## ###########################
sub DisplayStaffTable
{
print "---------------------------------------------------------- \n";
print "Display Staff Table \n";
print "---------------------------------------------------------- \n";
print "SELECT * FROM staff WHERE id >= 310 \n\n";
print "ID NAME DEPT JOB YEARS SALARY \n";
print "--- -------- ---- ----- ----- --------\n";

my $sql = qq(SELECT * FROM staff WHERE id >= 310 );

# prepare the sql statement
$sth = $dbh ->prepare( $sql );

# execute the sql statement
$sth ->execute;

while (( $id , $name , $dept , $job , $years , $salary , $comm )
= $sth
->fetchrow_array)
{
printf " %3d %-8.8s %4d" , $id , $name , $dept ;
if ( $job ne " " )
{
printf " %-5.5s" , $job ;
}
else
{
print " -" ;
}

if ( $years != 0 )

{
printf " %5d" , $years ;
}
else
{
print " -" ;
}

printf " %7.2f" , $salary ;
if ( $comm != 0 )
{
printf " %7.2f \n " , $comm ;
}
else
{
print " - \n " ;
}
}

return 0 ;
} # DisplayStaffTable

################################################## ########################
# Description : Checks and parses the command line arguments
# Input : An array containing the command line arguments that was
# passed to the calling function
# Output : Database name, user name and password

################################################## #########################
sub CmdLineArgChk
{
my $arg_c = @_; # number of arguments passed to the function
my @arg_l; # arg_l holds the values to be returned to calling function

if ( $arg_c > 3 || $arg_c == 1 && ( ( $_ [ 0 ] eq "?" ) ||
( $_ [ 0 ] eq "-?" ) ||
( $_ [ 0 ] eq "/?" ) ||
( $_ [ 0 ] eq "-h" ) ||
( $_ [ 0 ] eq "/h" ) ||
( $_ [ 0 ] eq "-help" ) ||
( $_ [ 0 ] eq "/help" ) ) )
{
die "Usage: prog_name [dbAlias] [userId passwd] \n " ;
}

# Use all defaults
if ( $arg_c == 0 )
{
$arg_l [ 0 ] = $database ;
$arg_l [ 1 ] = "" ;
$arg_l [ 2 ] = "" ;
}

# dbAlias specified
if ( $arg_c == 1 )
{
$arg_l [ 0 ] = "dbi:DB2:" . $_ [ 0 ];
$arg_l [ 1 ] = "" ;
$arg_l [ 2 ] = "" ;
}

# userId & passwd specified
if ( $arg_c == 2 )
{
$arg_l [ 0 ] = $database ;
$arg_l [ 1 ] = $_ [ 0 ];
$arg_l [ 2 ] = $_ [ 1 ];
}

# dbAlias, userId & passwd specified
if ( $arg_c == 3 )
{
$arg_l [ 0 ] = "dbi:DB2:" . $_ [ 0 ];
$arg_l [ 1 ] = $_ [ 1 ];
$arg_l [ 2 ] = $_ [ 2 ];
}

return @arg_l;
} # CmdLineArgChk

################################################## ########################
# Description : Prepares and Exectes the SQL statement
# Input : Datbase handler, SQL statement
# Output : Statement Handler.

################################################## ########################
sub PrepareExecuteSql
{
# get the database handler and sql into local variables
my ( $dbh_loc , $sql_loc ) = @_;

# prepare the SQL statement or call TransRollback() if it fails
my $sth = $dbh_loc ->prepare( $sql_loc )
|| &TransRollback( $dbh_loc );

# execute the prepared SQL statement or call TransRollback() if it fails
$rc = $sth ->execute()
|| &TransRollback( $dbh_loc );

return $sth ; # return the statement handler
} # PrepareExecuteSql

################################################## ########################
# Description : Rollback the transaction and reset the database
connection
# Input : Database handler
# Output : None

################################################## ########################
sub TransRollback
{

my ( $dbh_loc ) = @_;

# rollback the transaction
print " \n Rolling back the transaction... \n " ;

my $rv = $dbh_loc ->rollback()
|| die "The transaction couldn't be rolled back: $DBI::errstr" ;

print " \n The transaction was rolled back. \n " ;

# get the number of active statement handles currently used
my $no_handles = $dbh_loc ->{ActiveKids};

# close all the active statement handles
for ( $i = 0 ; $i < $no_handles ; $i ++)
{
if ( $i == 0 )
{
# no more data to be fetched from the first statement handle
$sth ->finish;
}
else
{
my $handle = " \$ sth$i" ; # to get the subsequent statement
handles
eval "$handle->finish" ;
}
}

# reset the connection
print " \n Disconnecting from the database... \n " ;

my $rv = $dbh_loc ->disconnect()
|| die "Disconnecting from the database failed: $DBI::errstr" ;

print " \n Disconnected from the database. \n " ;

die " \n Exiting the sample \n " ;
} # TransRollback
1 ; # to always return true to the calling function
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ian
ba**@glion.ch wrote:
Hello

I have a problem to resolve: I wrote a Perlscript which caches data from a
server (local on my machine) I would like to have a other connection to a
remote server but I don't know how to define the servername / hostname in
my Perl Progrem..


You don't define the connection in your perl script. You have to
catalog the database on your local machine (see DB2 commands 'CATALOG
TCPIP NODE' and 'CATALOG DATABASE').

db2 catalog tcpip node serverb remote 10.20.30.40 server 50000
db2 catalog database otherdb at node serverb;

Then you just connect using DBI->connect() as before, just using the
database name you used in CATALOG DATABASE.

Nov 12 '05 #2

P: n/a
Hello,

My procedure I face on is:

1) Boot from Linux CD
2) connect to Netlogon Drive
3) Perl Script gets computerinfo (based on Macadress) in a DB2 Database
4) Install OS and Application based on Info in DB2

So I have to install DB2 Client on the Linux boot CD and put the right to
get atached to the db2 server?
Or is there a way to get out the Information on a other way?

In mysql it is simple no client nees to be installed and just put the
following string in the perl

my $dsn = 'DBI:DB2:LOGONDB:SERVERNAME';
connect( $dsn, $uid, $pwd);

What you mean with "if you don't define the connection in your perl
Script"
How do I define this in My Perl Script?

When i do as in mysqlconnect dsn I always got the error down here like
"overloaded dsn parameter from dbcl".

__________________Output Window____________________

----------------------------------------------------------
Connect to Database LOGONDB
----------------------------------------------------------
Connecting to database...

DBI connect('LOGONDB:localhost','banz',...) failed: [IBM][CLI Driver]
CLI0124E Invalid argument value. SQLSTATE=HY009 at C:\Documents and
Settings\banz\Desktop\db2query\goodcodesnips\DB_fu nctions.pl line 61
Can't connect to : [IBM][CLI Driver] CLI0124E Invalid argument value.
SQLSTATE=HY009 at C:\Documents and
Settings\banz\Desktop\db2query\goodcodesnips\DB_fu nctions.pl line 61.

____________________End Output_____________________
Roman


ba**@glion.ch wrote:
Hello

I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to define the servername / hostname in my Perl Progrem..


You don't define the connection in your perl script. You have to
catalog the database on your local machine (see DB2 commands 'CATALOG
TCPIP NODE' and 'CATALOG DATABASE').

db2 catalog tcpip node serverb remote 10.20.30.40 server 50000
db2 catalog database otherdb at node serverb;

Then you just connect using DBI->connect() as before, just using the
database name you used in CATALOG DATABASE.
Nov 12 '05 #3

P: n/a
Ian
ba**@glion.ch wrote:
Hello,

My procedure I face on is:

1) Boot from Linux CD
2) connect to Netlogon Drive
3) Perl Script gets computerinfo (based on Macadress) in a DB2 Database
4) Install OS and Application based on Info in DB2

So I have to install DB2 Client on the Linux boot CD and put the right to
get atached to the db2 server?
Yes.
Or is there a way to get out the Information on a other way?


Using perl, and DBD::DB2, no. DBD::DB2 depends on the DB2 client,
so you have to have the runtime client installed (with the connections
to the remote database defined).

You can do this with Java using the Type 3 or Type 4 JDBC drivers (you
would only need the JDBC drivers on your boot CD).

The only thing I can think of -- IIRC, there used to some kind of DBI
Proxy server that you could leverage. This is the perl equivalent of
a JDBC type 3 driver.
Good luck,
Nov 12 '05 #4

P: n/a
ba**@glion.ch wrote:
Hello,

My procedure I face on is:

1) Boot from Linux CD
2) connect to Netlogon Drive
3) Perl Script gets computerinfo (based on Macadress) in a DB2 Database
4) Install OS and Application based on Info in DB2

So I have to install DB2 Client on the Linux boot CD and put the right to
get atached to the db2 server?
Or is there a way to get out the Information on a other way?


If you use JDBC to connect to remote DB2 server *AND* if you use JCC type 4
driver - you do not need anything else (except Java JRE), assuming that you will
know:

hostname or IP address of DB2 server,
database alias name on DB2 server,
port DB2 listener on DB2 server is listening.

I don't know whether you can achieve same in Perl/PHP.

Jan M. Nelken
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.