Re: Slow Performance When Using DBI, otherwise Not
Why are you setting TWO_TASK in the Perl script?
Try running your script without it.
Jared
On 2/23/06, Rhugga Harper <[EMAIL PROTECTED]> wrote:
>
> Here is the perl code to do the same:
>
> $ENV{ORACLE_HOME} = $_oracle_home;
> $ENV{TWO_TASK} = $_oracle_two_task;
> $ENV{TNS_ADMIN} = $_tns_admin;
> my $ds = "dbi:Oracle:$_dbsid";
> my $dbuser = $_dbuser;
> my $dbpass = $_dbpass;
>
> ...
RE: Calling a PostgreSQL function via DBI
Thanks, Greg. That's very informative. I really ought to dig into DBI/DBD internals in the near future, probably using postgreSQL as an example. I use postgreSQL at home sometimes but not currently at work. -Will -Original Message- From: Greg Sabino Mullane [mailto:[EMAIL PROTECTED] Sent: Thursday 23 February 2006 20:42 To: dbi-users@perl.org Subject: Re: Calling a PostgreSQL function via DBI -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Some minor notes and corrections. First, hopefully the most important thing to learn from this thread is to always use placeholders. It just saves you so much pain and trouble. :) Louis Gonzales asked: > Two, you didn't actually put ( $1, $2, ... ) , Did you? In your code, > that still needs to remain as the question marks. Actually, the $1 form is perfectly acceptable, and even preferable, as it can be easier to read and handles the case of the same placeholder used more than once in a statement in a more efficient manner. You can also ue the ":foo" format which is more flexible still. DBD::Oracle and others handle non-"?" placeholders, although the portability factor is probably not very important when one is dealing with database functions. Christian Stalp asks: > Other question, has anybody experences with PLperl on postgreSQL, thats > looks facinating. And I think my next function will be reallized in PLperl. Yes, Pl/Perl is amazing. All the power of Perl, directly in your database. It is so much easier than fighting with the traditional procedural languages such as pl/sql and pl/pgsql. You can even do things like use DBI from within your PL/Perl functions. Best of all, you can now write your triggers in Pl/Perl. How cool is that? Will Rutherdale said: > In particular, I'm uncomfortable with forms like 1::numeric, etc., where > your code contains assumptions about the data types of your columns. If > you let DBI control it through its knowledge of the schema, then you don't > have to put those assumptions into your code. bind_param() takes care > of that for you, so your code can be more maintainable and portable. Those forms can be very important, as PostgreSQL supports polymorphic functions, so you *must* tell it what data type you are using if you have two or more functions with the same name. If you don't, however, and I am guessing that is the case here, it is usually safe to leave them out. DBI does not "control through its knowledge of the schema" - it is a pure abstraction layer, which has no knowledge of any schema (nor should it). It expects the user to tell it what types through methods such as bind_param. Alternatively, DBD::Pg has the ability (with some caveats) to pass a preparable statement to Postgres and let *it* worry about the quoting when executing. - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
Re: Spreadsheet::WriteExcel
On Fri, 2006-02-24 at 05:56 -0800, Robert wrote: > Hi list, > I have installed Spreadsheet::WriteExcel and Parse::RecDescent without any > issues, compilation and installation went went but they are not working. > > > > bash-2.05# perl -MSpreadsheet::WriteExcel > Spreadsheet/WriteExcel.pm did not return a true value. > > bash-2.05# perl -MParse::RecDescent > Parse/RecDescent.pm did not return a true value. > BEGIN failed--compilation aborted. > > Anyone has same issue? Nope. > > Thanks in advance This really is the right list to post such a question. Look at this page for all kinds of perl lists, http://lists.cpan.org/ ...I would also suggest using www.perlmonks.org , that is a good place to get help. > > > > > > - > Relax. Yahoo! Mail virus scanning helps detect nasty viruses! -- Scott T. Hildreth <[EMAIL PROTECTED]>
Spreadsheet::WriteExcel
Hi list, I have installed Spreadsheet::WriteExcel and Parse::RecDescent without any issues, compilation and installation went went but they are not working. bash-2.05# perl -MSpreadsheet::WriteExcel Spreadsheet/WriteExcel.pm did not return a true value. bash-2.05# perl -MParse::RecDescent Parse/RecDescent.pm did not return a true value. BEGIN failed--compilation aborted. Anyone has same issue? Thanks in advance - Relax. Yahoo! Mail virus scanning helps detect nasty viruses!
Re: Slow Performance When Using DBI, otherwise Not
If I run the same perl script on the database server itself it runs
instantly. (database server is red hat AS3 update 4). I wasn't sure how to
check versions of DBI and DBD (I greped for version on every module under
every DBI and DBD directory but found to many versions strings and didnt
know which was which) but the DBI and DBD::Oracle versions on the database
server were the latest release as of 8 months ago or so.
The shell script and perl script are both using the exact same
database/user/schema/table. The database itself is idle with no other active
sessions.
Thanks
On 2/24/06, John Scoles <[EMAIL PROTECTED]> wrote:
>
> Dose seem a little long even though DBD has to createsome objects ect.
> I will have a look at it today.
>
>
> ""Reidy, Ron"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> Rhugga,
>
> I have never seen this before. What happens if you step through this
> with the debugger? Are there logon triggers firing for the user during
> connect? Is your DB using dedicated connections, or MTS?
>
> Also, I assume your shell script is connecting to the same DB, over the
> same network, using the same username.
> -Original Message-
> From: Rhugga Harper [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 23, 2006 2:09 PM
> To: Reidy, Ron
> Cc: dbi-users@perl.org
> Subject: Re: Slow Performance When Using DBI, otherwise Not
>
>
>
> I added some debug output and have determined that the latency occurs
> here, during the call to connect:
> print "DEBUG 1\n";
> my $dbh = DBI->connect($ds, $dbuser, $dbpass);
>
> if (!defined($dbh)) {
> print "Error: main(): database connection failed:
> $DBI::errstr\n";
> cleanup();
> exit(-1);
> }
>
> my $query1 = "SELECT hostname from ithug.adc_ait_hosts where status
> > 0 order by hostname asc ";
> print "DEBUG 2\n";
>
> When running this script I immediately see "DEBUG 1", then I get a
> latency of anywhere from 3 - 10 seconds, then I see "DEBUG 2" and my
> query output almost immediately.
>
> The shell script completes in under 1/2 second:
> time ./ait_hosts.sh > c.1
>
> real0m0.375s
> user0m0.170s
> sys 0m0.070s
>
> Any ideas why the connect call would be lagging?
>
> -CC
>
> On 2/23/06, Reidy, Ron <[EMAIL PROTECTED]> wrote:
>
> The prepare statement in DBI will prepare and then execute the
> statement. This is, essentially tow PARSE calls against the
> dictionary
> cache.
>
> Look at using ora_check_sql
> (
> http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Prepare_postpone
> >
> d_till_execute) to eliminate this issue.
>
> If the problem persists after this change, let us know where
> your waits
> are occurring by using event 10046 and tkprof.
>
> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -Original Message-
> From: Rhugga Harper [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 23, 2006 12:46 PM
> To: dbi-users@perl.org
> Subject: Slow Performance When Using DBI, otherwise Not
>
>
> I have a Solaris 8 host running perl 5.8 and using DBI version
> 1.50 and
> DBD::Oracle version 1.16. The database is Oracle 10.2.0.1 and
> runs on a
> different host.
>
> If I run this query from a shell script, it completes in under 1
> second,
> however, using a perl script it takes 5-10 seconds.
>
> Here is the shell script:
>
> #!/bin/bash
>
> ORACLE_HOME=/u01/app/oracle/product/10.2
> ORACLE_SID=mysid
> SQLPLUS=/u01/app/oracle/product/10.2/bin/sqlplus
>
> $SQLPLUS -s x/[EMAIL PROTECTED] << EOF
> / as sysdba
> SELECT hostname from x.adc_ait_hosts where status > 0 order
> by
> hostname asc; quit; EOF
>
>
> Here is the perl code to do the same:
>
> $ENV{ORACLE_HOME} = $_oracle_home;
> $ENV{TWO_TASK} = $_oracle_two_task;
> $ENV{TNS_ADMIN} = $_tns_admin;
> my $ds = "dbi:Oracle:$_dbsid";
> my $dbuser = $_dbuser;
> my $dbpass = $_dbpass;
>
> my @tapeservers;
>
> my $dbh = DBI->connect($ds, $dbuser, $dbpass);
>
> if (!defined($dbh)) {
> print "Error: main(): database connection failed:
> $DBI::errstr\n";
> cleanup();
> exit(-1);
> }
>
> my $query1 = "SELECT hostname from x.adc_ait_hosts
> where status
> > 0
> order by hostname asc ";
> my $sth1= $dbh->prepare($query1) || die "Error: Unable
> to
> prepare
> query: $DBI::errstr\n";
> $sth1->execute();
>
> while ( my @row = $sth1->fetchrow_array)
> {
> if ($STRIP) {
> my ($host, $subd, $domain, $sfx) = split(/\./,
> $row[0]);
> print STDOUT "$host\n";
> } else {
> print STDOUT "$row[0]\n";
> }
> }
>
>
> Can anyone point me in the right direction?
>
> Thx,
> CC
>
> This electronic message transmission is a PRIVATE communication
> which contains
> information which may be confidential or privileged. The
> information is intended
> to be for the use of the individual or entity named
Re: Slow Performance When Using DBI, otherwise Not
Dose seem a little long even though DBD has to createsome objects ect.
I will have a look at it today.
""Reidy, Ron"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Rhugga,
I have never seen this before. What happens if you step through this
with the debugger? Are there logon triggers firing for the user during
connect? Is your DB using dedicated connections, or MTS?
Also, I assume your shell script is connecting to the same DB, over the
same network, using the same username.
-Original Message-
From: Rhugga Harper [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 23, 2006 2:09 PM
To: Reidy, Ron
Cc: dbi-users@perl.org
Subject: Re: Slow Performance When Using DBI, otherwise Not
I added some debug output and have determined that the latency occurs
here, during the call to connect:
print "DEBUG 1\n";
my $dbh = DBI->connect($ds, $dbuser, $dbpass);
if (!defined($dbh)) {
print "Error: main(): database connection failed:
$DBI::errstr\n";
cleanup();
exit(-1);
}
my $query1 = "SELECT hostname from ithug.adc_ait_hosts where status
> 0 order by hostname asc ";
print "DEBUG 2\n";
When running this script I immediately see "DEBUG 1", then I get a
latency of anywhere from 3 - 10 seconds, then I see "DEBUG 2" and my
query output almost immediately.
The shell script completes in under 1/2 second:
time ./ait_hosts.sh > c.1
real0m0.375s
user0m0.170s
sys 0m0.070s
Any ideas why the connect call would be lagging?
-CC
On 2/23/06, Reidy, Ron <[EMAIL PROTECTED]> wrote:
The prepare statement in DBI will prepare and then execute the
statement. This is, essentially tow PARSE calls against the
dictionary
cache.
Look at using ora_check_sql
(
http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Prepare_postpone
d_till_execute) to eliminate this issue.
If the problem persists after this change, let us know where
your waits
are occurring by using event 10046 and tkprof.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Rhugga Harper [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 23, 2006 12:46 PM
To: dbi-users@perl.org
Subject: Slow Performance When Using DBI, otherwise Not
I have a Solaris 8 host running perl 5.8 and using DBI version
1.50 and
DBD::Oracle version 1.16. The database is Oracle 10.2.0.1 and
runs on a
different host.
If I run this query from a shell script, it completes in under 1
second,
however, using a perl script it takes 5-10 seconds.
Here is the shell script:
#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/10.2
ORACLE_SID=mysid
SQLPLUS=/u01/app/oracle/product/10.2/bin/sqlplus
$SQLPLUS -s x/[EMAIL PROTECTED] << EOF
/ as sysdba
SELECT hostname from x.adc_ait_hosts where status > 0 order
by
hostname asc; quit; EOF
Here is the perl code to do the same:
$ENV{ORACLE_HOME} = $_oracle_home;
$ENV{TWO_TASK} = $_oracle_two_task;
$ENV{TNS_ADMIN} = $_tns_admin;
my $ds = "dbi:Oracle:$_dbsid";
my $dbuser = $_dbuser;
my $dbpass = $_dbpass;
my @tapeservers;
my $dbh = DBI->connect($ds, $dbuser, $dbpass);
if (!defined($dbh)) {
print "Error: main(): database connection failed:
$DBI::errstr\n";
cleanup();
exit(-1);
}
my $query1 = "SELECT hostname from x.adc_ait_hosts
where status
> 0
order by hostname asc ";
my $sth1= $dbh->prepare($query1) || die "Error: Unable
to
prepare
query: $DBI::errstr\n";
$sth1->execute();
while ( my @row = $sth1->fetchrow_array)
{
if ($STRIP) {
my ($host, $subd, $domain, $sfx) = split(/\./,
$row[0]);
print STDOUT "$host\n";
} else {
print STDOUT "$row[0]\n";
}
}
Can anyone point me in the right direction?
Thx,
CC
This electronic message transmission is a PRIVATE communication
which contains
information which may be confidential or privileged. The
information is intended
to be for the use of the individual or entity named above. If
you are not the
intended recipient, please be aware that any disclosure,
copying, distribution
or use of the contents of this information is prohibited. Please
notify the
sender of the delivery error by replying to this message, or
notify us by
telephone (877-633-2436, ext. 0), and then delete it from your
system.
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
Installation Issues
Hi ,
I am new to SYBPERL and need your help to install DBD::sybase in AIX5
unix system.
I am working with Sybase 12.5.1.0 and installed under
/data/sybase/product/12.5.1.0/
The following are my environment variables.
SYBASE=/data/sybase/product/12.5.1.0/
SYBASE_OCS=OCS-12_5
DBD-Sybase-1.07 files are under
/db/pub/infra/importftp/rbh/perldbi/DBD-Sybase-1.07/
The following steps are using to install the DBD:SYBASE. I didn't
modified any variables in the CONFIG or other files.
1. perl Makefile.PL
THe following is the output.
Sybase OpenClient 12.5.1 ASE Edition found.
By default DBD::Sybase 1.05 and later use the 'CHAINED' mode (where available)
when 'AutoCommit' is turned off. Versions 1.04 and older instead managed
the transactions explicitly with a 'BEGIN TRAN' before the first DML
statement. Using the 'CHAINED' mode is preferable as it is the way that
Sybase implements AutoCommit handling for both its ODBC and JDBC drivers.
Use 'CHAINED' mode by default (Y/N) [Y]: N
Running in threaded mode - looking for _r libraries...
Found -lct_r for -lct
Found -lcs_r for -lcs
Found -ltcl_r for -ltcl
Found -lcomn_r for -lcomn
Found -lintl_r for -lintl
The DBD::Sybase module need access to a Sybase server to run the tests.
To clear an entry please enter 'undef'
Sybase server to use (default: SYBASE): nyc_gedrec_sql_prd
User ID to log in to Sybase (default: sa): regreport_ny
Password (default: undef): regreport_ny13x
Sybase database to use on nyc_gedrec_sql_prd (default: undef): its_ged_daily
* Writing login information, including password, to file PWD.
Checking if your kit is complete...
Looks good
Note (probably harmless): No library found for -lct_r
Note (probably harmless): No library found for -lcs_r
Note (probably harmless): No library found for -ltcl_r
Note (probably harmless): No library found for -lcomn_r
Note (probably harmless): No library found for -lintl_r
Note (probably harmless): No library found for -lblk
Using DBI 1.46 (for perl 5.008 on aix-thread-multi) installed in
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/auto/DBI/
Writing Makefile for DBD::Sybase
(nyhshutl06d1):/db/pub/infra/importftp/rbh/perldbi/DBD-Sybase-1.07> make
cp dbd-sybase.pod blib/lib/DBD/dbd-sybase.pod
cp Sybase.pm blib/lib/DBD/Sybase.pm
/usr/bin/perl -e 'use ExtUtils::Mksymlists; Mksymlists("NAME" =>
"DBD::Sybase", "DL_FUNCS" => { }, "FUNCLIST" => [], "DL_VARS" => []);'
/usr/bin/perl -p -e "s/~DRIVER~/Sybase/g"
/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/auto/DBI//Driver.xst >
Sybase.xsi
/usr/bin/perl /usr/local/lib/perl5/5.8.3/ExtUtils/xsubpp -typemap
/usr/opt/perl5/lib/5.8.0/ExtUtils/typemap Sybase.xs > Sybase.xsc && mv
Sybase.xsc Sybase.c
cc_r -c -I/data/sybase/product/12.5.1.0/OCS-12_5/include
-DNO_CHAINED_TRAN=1
-I/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/auto/DBI -D_ALL_SOURCE
-D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias
-DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES -qlonglong -O
-DVERSION=\"1.07\" -DXS_VERSION=\"1.07\"
"-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" Sybase.c
cc_r -c -I/data/sybase/product/12.5.1.0/OCS-12_5/include
-DNO_CHAINED_TRAN=1
-I/usr/opt/perl5/lib/site_perl/5.8.0/aix-thread-multi/auto/DBI -D_ALL_SOURCE
-D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias
-DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES -qlonglong -O
-DVERSION=\"1.07\" -DXS_VERSION=\"1.07\"
"-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" dbdimp.c
"dbdimp.c", line 4779.48: 1506-280 (W) Function argument assignment between
types "int*" and "unsigned long*" is not allowed.
Running Mkbootstrap for DBD::Sybase ()
chmod 644 Sybase.bs
rm -f blib/arch/auto/DBD/Sybase/Sybase.so
LD_RUN_PATH="" ld -L/data/sybase/product/12.5.1.0/OCS-12_5/lib
-bhalt:4 -bM:SRE -bI:/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/perl.exp
-bE:Sybase.exp -bnoentry -lpthreads -lc_r Sybase.o dbdimp.o -o
blib/arch/auto/DBD/Sybase/Sybase.so
-L/data/sybase/product/12.5.1.0/OCS-12_5/lib -ldl -lm
ld: 0711-317 ERROR: Undefined symbol: .ct_dynamic
ld: 0711-317 ERROR: Undefined symbol: .ct_command
ld: 0711-317 ERROR: Undefined symbol: .ct_param
ld: 0711-317 ERROR: Undefined symbol: .cs_convert
ld: 0711-317 ERROR: Undefined symbol: .ct_cancel
ld: 0711-317 ERROR: Undefined symbol: .ct_close
ld: 0711-317 ERROR: Undefined symbol: .cs_diag
ld: 0711-317 ERROR: Undefined symbol: .blk_bind
ld: 0711-317 ERROR: Undefined symbol: .blk_rowxfer
ld: 0711-317 ERROR: Undefined symbol: .ct_results
ld: 0711-317 ERROR: Undefined symbol: .ct_res_info
ld: 0711-317 ERROR: Undefined symbol: .ct_fetch
ld: 0711-317 ERROR: Undefined symbol: .ct_capability
ld: 0711-317 ERROR: Undefined symbol: .ct_send
ld: 0711-317 ERROR: Undefined symbol: .ct_describe
ld: 0711-317 ERROR: Undefined symbol: .ct_cmd_drop
ld: 0711-317 ERROR: Undefined s
