Excellent. Thanks a bunch.
- Pavan.

On Thu, September 28, 2006 5:49 pm, Davi de Paula Cabral wrote:
> 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
>
>
>
>
> ---------------------------------------------------------------------
> 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