473,231 Members | 1,775 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Connect to remote server using db2 and Perl

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
4 6405
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: mayamorning123 | last post by:
A comparison among six VSS remote tools including SourceOffSite , SourceAnyWhere, VSS Connect, SourceXT, VSS Remoting, VSS.NET To view the full article, please visit...
5
by: Mike L | last post by:
I'm able to connect to my stored procedure in my local database but not able to connect to my stored procedure in the remote database. I use several different UserID and Password that all should...
1
by: linux | last post by:
Sorry if this is a really dumb question. I am trying to connect to our remote MySQL server (which is running just fine). I have a perl script which worked just fine on my other Fedora 4 box (all...
14
by: DaTurk | last post by:
I am makeing a Multicast server client setup and was wondering what the difference is between Socket.Connect, and Socket.Bind. It may be a stupid question, but I was just curious. Because I...
14
by: Marcus | last post by:
I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that...
0
by: tamayi | last post by:
I have a problem (like most others posting issues on this forum :) ) I have a remote server running Windows XP SP2, with both SQL Server 2005 Express with Advanced Features and SQL 2000...
8
by: BD | last post by:
I am developing C# win form app to work with remote database on SQL Server 2005. Problem scenario is as follows: 1. a form is open that has downloaded dataset to local cache 2. computer is...
2
by: idorjee | last post by:
hi, can anyone tell me what am i doing wrong here. i'm trying to connect remotely to the MySQL database on a server (SunOS multivac 5.9 sparc SUNW,Sun-Fire-V240). i know that the username and the...
7
by: RN1 | last post by:
Is it possible to connect to my local SQL Server 2005 database from a remote web server? If yes, what ConnectionString do I use? Thanks, Ron
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.