I am just waiting for the AIX administrator here, to solve a question
that was not commented in this list, even though I had sent a question,
nobody said anything about my perl/DB2 trouble with DBI / DBD
libraries...

Nowadays, we are using some home-made shell scripts to view "runstats",
for example. But if you see the script, the error treatment is
incidental, you have to read a string and do some greps, cuts, awks and
so on. And if IBM changes the error message? If it was a perl program,
it would capture the error code and treat it more efficiently.

Another "shell script of ours" problem is: people here do remote
assistance in the weekend or at night. When you run a script, it returns
a lot of things on the screen, not only the errors or warnings. The
remote connection becomes slow and frequently the guy lose it and need
to reconnect. I think that, with some more knowledge about, it is
possible to make it better. But the chief found some perl script
examples using DBI library and decided to use it.

Visit this link:

http://www.db2mag.com/showArticle.jhtml?articleID=59301551

Copying an example for you (do it with shell script):

#!/usr/bin/perl
###########################################
#/usr/opt/db2_08_01/samples/perl/dbauth.pl#
###########################################
select STDERR; $|=1;
select STDOUT; $|=1;

use strict;
use warnings;
use DBI;

# access the module for DB2 Sample Utility functions
use DB2SampUtil;

# check and parse the command line arguments
# call the subroutine CmdLineArgChk() from DB2SampUtil.pm
my ($database, $user, $password) = CmdLineArgChk(@ARGV);

# declare return code, statement handler, database handler and local
variable
my ($rc, $sth, $dbh, $i);

sub DbAuthGrant();
sub DbAuthForAnyUserOrGroupDisplay();
sub DbAuthRevoke();

print "\nTHIS SAMPLE SHOWS ";
print "\nHOW TO GRANT/DISPLAY/REVOKE AUTHORITIES AT DATABASE LEVEL.\n";

# connect to the database
print "\n Connecting to database...\n";
$dbh = DBI->connect($database, $user, $password, {AutoCommit =>0})
|| die "Can't connect to $database: $DBI::errstr";
print "\n Connected to database.\n";


# call the subroutine DbAuthGrant
$rc = DbAuthGrant();
if ($rc != 0)
{
print "\nGranting Database authorities at Database level failed\n";
}

# call the subroutine DbAuthForAnyUserOrGroupDisplay
$rc = DbAuthForAnyUserOrGroupDisplay();

if ($rc != 0)
{
print "\nDisplay of Database authorities for any user at Database\n";
print "level failed\n";
}

# call the subroutine DbAuthRevoke
$rc = DbAuthRevoke();
if ($rc != 0)
{
print "\nRevoke Database authorities from user at Database\n";
print "level failed\n";
}

# disconnect from the database
print "\n Disconnecting from database.\n";
$dbh->disconnect
|| die $DBI::errstr;
print " Disconnected from database.\n";


########################################################################
#
# Description: How to grant authorities at database level
# Input : None
# Output : Returns 0 on success, exits otherwise.
########################################################################
#
sub DbAuthGrant()
{
my $sql;

print "\n-----------------------------------------------------------";
print "\nUSE THE SQL STATEMENTS:\n";
print " GRANT (Database Authorities)\n";
print " COMMIT\n";
print "TO GRANT AUTHORITIES AT DATABASE LEVEL.\n";

# grant user authorities at database level
print "\n GRANT CONNECT, CREATETAB, BINDADD ON DATABASE";
print " TO USER user1\n";

$sql = qq(GRANT CONNECT, CREATETAB, BINDADD ON DATABASE TO USER
user1);

# prepare and execute the SQL statement
# call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
$sth = PrepareExecuteSql($dbh, $sql);

print " COMMIT\n";
# commit the transaction or call TransRollback() from DB2SampUtil.pm
# if it fails
$dbh->commit() ||
TransRollback($dbh);

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

return 0;
} # DbAuthGrant


########################################################################
#
# Description: How to display authorities for any user at database level
# Input : None
# Output : Returns 0 on success, exits otherwise.
########################################################################
#
sub DbAuthForAnyUserOrGroupDisplay()
{
my $sql;

print "\n-----------------------------------------------------------";
print "\nUSE THE SQL STATEMENT:\n";
print " SELECT INTO\n";
print "TO DISPLAY AUTHORITIES FOR ANY USER AT DATABASE LEVEL.\n";

print "\n SELECT granteetype, dbadmauth, createtabauth,
bindaddauth,\n";
print " connectauth, nofenceauth, implschemaauth, loadauth\n";
print " FROM syscat.dbauth\n";
print " WHERE grantee = 'USER1'\n";

$sql = qq(SELECT granteetype, dbadmauth, createtabauth, bindaddauth,
connectauth, nofenceauth, implschemaauth, loadauth
FROM syscat.dbauth
WHERE grantee = 'USER1');

# prepare and execute the SQL statement
# call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
$sth = PrepareExecuteSql($dbh, $sql);

my ($granteetype, $dbadmauth, $createtabauth, $bindaddauth,
$connectauth, $nofenceauth, $implschemaauth, $loadauth) =
$sth->fetchrow_array;

# check for problems which may have terminated the fetch early
die $sth->errstr if $sth->err;

print "\n Grantee Type = ", $granteetype, "\n";
print " DBADM auth. = ", $dbadmauth, "\n";
print " CREATETAB auth. = ", $createtabauth, "\n";
print " BINDADD auth. = ", $bindaddauth, "\n";
print " CONNECT auth. = ", $connectauth, "\n";
print " NO_FENCE auth. = ", $nofenceauth, "\n";
print " IMPL_SCHEMA auth. = ", $implschemaauth, "\n";
print " LOAD auth. = ", $loadauth, "\n";

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

return 0;
} # DbAuthForAnyUserOrGroupDisplay()


########################################################################
#
# Description: How to revoke authorities at database level
# Input : None
# Output : Returns 0 on success, exits otherwise.
########################################################################
#

sub DbAuthRevoke()
{
my $sql;

print "\n-----------------------------------------------------------";
print "\nUSE THE SQL STATEMENTS:\n";
print " REVOKE (Database Authorities)\n";
print " COMMIT\n";
print "TO REVOKE AUTHORITIES AT DATABASE LEVEL.\n";

# revoke user authorities at database level
print "\n REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE ";
print "FROM USER user1\n";

$sql = qq(REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE FROM user1);

# prepare and execute the SQL statement
# call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
$sth = PrepareExecuteSql($dbh, $sql);

print " COMMIT\n";
# commit the transaction or call TransRollback() from DB2SampUtil.pm
# if it fails
$dbh->commit() ||
TransRollback($dbh);

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

return 0;
} # DbAuthRevoke()
-----Mensagem original-----
De: pavan
[mailto:db2udbdba-ezmlmshield-x94617961.[Email address protected]
Enviada em: quarta-feira, 27 de setembro de 2006 20:48
Para: LazyDBA Discussion
Assunto: Perl and db2 -- pls respond.

Hello Gurus,
I have a question regarding perl.
They say perl is very powerful language and it is highly useful to play
around in database development/maintenance. Although I am a db2
developer, i hardly used perl till now.
Under what scenarios do we usually perl ? What is something that perl
can do (may be faster ?) and other languages like awk and tool like sed
cannot do ?
I believe with awk and sed combination, we should be able to pretty much
do lot of shell scripting.
I want somebody to negate me with few specific examples. I highly
appreciate any kind of input that you provide.
Looking forward to know more from the gurus.

Thanks.



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page