Jun 29
My wife says this is so me… the fact that I’m not allowed to touch her macbook says something… *big grin*
Listen to this podcast
Listen to this podcast
Apr 24
35383: DBD::Sybase calls the broken sp_tables stored procedure
ASE, DBI, Databases, Perl, Sybase No Comments »35383: DBD::Sybase calls the broken sp_tables stored procedure
Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux
Perl v5.8.8 built for i386-linux-thread-multi
DBD::Sybase 1.07 but 1.08 also has the problem.
Due to Sybase CR 497173, sp_tables can not be relied upon to retrieve
table information correctly for ASE 15.
(ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs)
We need to query the system tables instead. Obviously, the below code
would work in v12.0 and higher. A temp table could probably be used for
prior to 12.0 for the TABLE_TYPE to be filled correctly.
$ diff ../DBD-Sybase-1.07/Sybase.pm Sybase.pm
218c218
< my $sth = $dbh->prepare(”sp_tables $table, $schema, $catalog,
$type”);
—
$type”);
219a220,238
> my $sth = $dbh->prepare( q{
> select TABLE_QUALIFIER = db_name()
> , TABLE_OWNER = u.name
> , TABLE_NAME = o.name
> , TABLE_TYPE =
> case o.type
> when “U” then “TABLE”
> when “V” then “VIEW”
> when “S” then “SYSTEM TABLE”
> end
> , REMARKS = NULL
> from sysobjects o
> , sysusers u
> where o.type in (’U', ‘V’, ‘S’)
> and id >99
> and o.uid = u.uid
>
> });
>
Listen to this podcast
Apr 24
SQL::Translator::Parser::DBI::Sybase bug (35380) causes script to fail when sp_helpindex doesn’t return a result set
ASE, DBI, Perl, Sybase 1 Comment »35380: SQL::Translator::Parser::DBI::Sybase fails when sp_helpindex is empty
Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux
Perl v5.8.8 built for i386-linux-thread-multi
SQL::Translator::0.09000
Sybase ASE’s sp_helpindex will normally return a result set containing
the column “INDEX_NAME” which parse() requires. When no index exists,
sp_helpindex returns a error string similar to “Object does not have any
indexes.” but no result set.
The following workaround simply wraps the selectall_hash with an eval,
if the eval results in no error, then try to use the hash.
$ diff Sybase_orig.pm Sybase_new.pm
252,259c252,259
< my $h = $dbh->selectall_hashref("sp_helpindex
< $table_info->{TABLE_NAME}", ‘INDEX_NAME’);
< foreach (values %{$h}) {
< my $fields = $_->{‘INDEX_KEYS’};
< $fields =~ s/\s*//g;
< my $i = $table->add_index(
< name =>
< $_->{INDEX_NAME},
—
> my $h;
> eval { $h = $dbh->selectall_hashref("sp_helpindex $table_info->{TABLE_NAME}", ‘INDEX_NAME’) };
> unless ($@) {
> foreach (values %{$h}) {
> my $fields = $_->{‘INDEX_KEYS’};
> $fields =~ s/\s*//g;
> my $i = $table->add_index(
> name => $_->{INDEX_NAME},
262,263c262,263
< if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
< $i->type(‘unique’);
—
> if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
> $i->type(‘unique’);
265,276c265,277
< # we could make this a primary key if there
< # isn’t already one defined and if there
< # aren’t any nullable columns in thisindex.
<
< if (!defined($table->primary_key())) {
< $table->primary_key($fields)
< unless grep {
< $table->get_field($_)->is_nullable()
< } split(/,\s*/, $fields);
< }
< }
< }
—
> # we could make this a primary key if there
> # isn’t already one defined and if there
> # aren’t any nullable columns in thisindex.
>
> if (!defined($table->primary_key())) {
> $table->primary_key($fields)
> unless grep {
> $table->get_field($_)->is_nullable()
> } split(/,\s*/, $fields);
> }
> }
> }
> }
252,259c252,259
< my $h = $dbh->selectall_hashref("sp_helpindex
< $table_info->{TABLE_NAME}", ‘INDEX_NAME’);
< foreach (values %{$h}) {
< my $fields = $_->{‘INDEX_KEYS’};
< $fields =~ s/\s*//g;
< my $i = $table->add_index(
< name =>
< $_->{INDEX_NAME},
—
> my $h;
> eval { $h = $dbh->selectall_hashref("sp_helpindex $table_info->{TABLE_NAME}", ‘INDEX_NAME’) };
> unless ($@) {
> foreach (values %{$h}) {
> my $fields = $_->{‘INDEX_KEYS’};
> $fields =~ s/\s*//g;
> my $i = $table->add_index(
> name => $_->{INDEX_NAME},
262,263c262,263
< if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
< $i->type(‘unique’);
—
> if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
> $i->type(‘unique’);
265,276c265,277
< # we could make this a primary key if there
< # isn’t already one defined and if there
< # aren’t any nullable columns in thisindex.
<
< if (!defined($table->primary_key())) {
< $table->primary_key($fields)
< unless grep {
< $table->get_field($_)->is_nullable()
< } split(/,\s*/, $fields);
< }
< }
< }
—
> # we could make this a primary key if there
> # isn’t already one defined and if there
> # aren’t any nullable columns in thisindex.
>
> if (!defined($table->primary_key())) {
> $table->primary_key($fields)
> unless grep {
> $table->get_field($_)->is_nullable()
> } split(/,\s*/, $fields);
> }
> }
> }
> }
Listen to this podcast
Apr 16
Replacing Sybase ASE’s Historical Server: Using OpenSource
ASE, DBI, Databases, Linux, OS, Perl, SQLite, Sybase No Comments »I’m working on a Historical Server replacement. I need to check with my employer as to whether I can open source it or not but the guts of it consist of:
- Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
- SQLite for the local storage (way lower overhead than ASE or SQL Anywhere
)
- Perl Catalyst / Template::Toolkit web front end with REST web services
All of this goes into a VMWare
Listen to this podcast
Oct 01
In the post Adding TCP Keepalive Support to DBD::Sybase perl module, I describe a patch that I submitted to Michael Peppler for his Perl DBD::Sybase driver. While he implemented it in DBD::Sybase version 1.08, I didn’t mention that for Linux distributions, TCP Keepalive is disabled by default for processes. That means, any application that is to use TCP Keepalive, must be specially built for it or wrapper code must be used.
In order to modify the system settings for TCP Keepalive on Linux, you need to make a change to your /etc/sysctl.conf file and then run /sbin/sysctl -p:
# TCP Keepalive
net.ipv4.tcp_keepalive_probes = 20
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 20
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
Listen to this podcast
Aug 28
Why would you want to use Perl/Java/etc instead of isql?
ASE, DBI, Databases, Perl, SQL Server, Sybase No Comments »I’m not trying to raise any ire in anyone, so please bear with me….
The issue of using isql or some other tool (such as Perl or Java) was brought up in the sybase-l mailing list
A benefit of handling each batch separate entity instead of all the batches as a whole in isql, we have the ability to isolate each batch. That means, the we can place logic around each batch or run them in parallel or a number of other reasons. This doesn’t actually require Perl and you could definitely do this in Java, but you would probably want to shy away from using isql for executing each batch. You may have hundreds or thousands of batches and starting isql for each batch would be very time consuming.
I have no problems with using awk/sed/whatever for maintenance scripts. I do have a problem of relying too heavily on the isql program from Sybase or Microsoft. Why? isql does not handle errors very well and often can fail due to bugs in the isql/openclient or network issues. sqsh is a bit better but I wouldn’t trust either in a production environment.
Yeah, I know lots of people run isql/sqsh in production environments but when isql fails, are you able to identify the problem and workaround it without having your script fail?
For example, if you were using isql to import a SQL file of say 10,000 batches (table ddls, etc), would you really want to restart the whole job if the 998th batch failed because of a deadlock? Wouldn’t you rather just retry the 998th batch?
In many cases, you might not be able to restart the whole job, so you would have to create a new SQL file copy/paste it from the original.
Sure, you could pipe the output of isql/sqsh and parse it but they don’t show all the errors and a general disconnect error message doesn’t really help.
I’m lazy by nature, so doing something manually when it can be scripted to be have more error handling is pretty much a good thing.
Listen to this podcast
Aug 27
How to read batches of SQL from a file in Perl
ASE, DBI, Databases, How To, Perl, Sybase 3 Comments »Update: I fixed the code to actually use the iterator as an iterator 
Scenario: You have a Perl application that performs lots of stuff but you are handed a SQL text file that you need to run on a regular basis from within your application.
Solution #1: Call isql from within your Perl application to run the SQL text file. This makes your code dependent on an external program.
`$<a href="http://www.sybase.com" class="alinks_links" onclick="return alinks_click(this);" title="" style="padding-right: 13px; background: url(http://froebe.net/blog/wp-content/plugins/alinks/images/external.png) center right no-repeat;" rel="external">SYBASE</a>/$SYBASE_OCS/bin/isql -U $login -P $pass -S $DBMS -i $SQL_FILE`;
Solution #2: Read the SQL text file and send each batch to the database using Perl. In this case, we aren’t performing any real parsing of the SQL itself, we are simply retrieving the individual SQL batches. I’m using Rintaro Ishizaki’s Iterator::Simple Perl module so we can very easily get the next SQL batch.
Our Perl module:
package dbS::Sybase::Parse::SQL_File;
use warnings;
use strict;
use Iterator::Simple qw(iterator);
BEGIN {
use Exporter ();
our ($VERSION, @ISA, @EXPORT, @EXPORT_OK, %EXPORT_TAGS);
$VERSION = 1.0.0;
@ISA = qw(Exporter);
@EXPORT_OK = qw(&get_batch);
}
our $FH;
#############################
sub _open_file {
my $file = shift;
my $FH;
open ($FH, "< ", $file)
or warn("unable to open $file file\\n");
return $FH;
}
#############################
sub get_batch {
my ($file, $delimiter) = @_;
# The delimiter is a single or multiple characters
# that are on a line by itself.
$delimiter = ‘go’ unless $delimiter;
if ( my $FH = _open_file($file) ) {
# Why use an iterator? The SQL file may be huge
# so we want to retrieve only a single batch at
# a time.
iterator {
if (fileno $FH) {
# Since this is an iterator, we need to
# verify that the $FH filehandle is valid.
# fileno() will return a file descriptor
# if the $FH file handle is valid else
# it will return undef.
my $query = "";
while (my $line = <$FH>) {
chomp $line;
last if ($line =~ m/^$delimiter\\s*$/i);
$query .= $line . " ";
}
return $query;
}
}
}
}
1;
Obtaining the individual batches are now very easy. Note, that we are making several assumptions:
- SQL batches end with a go (case insensitive)
- SQL code is valid
- security of the SQL text file is handled by the operating system (we’re not going to worry about SQL injection attacks at this level)
use dbS::Sybase::Parse::SQL_File qw(batch);
….
if ( my $batch = dbS::Sybase::Parse::SQL_File::get_batch("SQL/SNAP.sql") ) {
print "-"x40 . "\\n";
print " Performing IGOR\\n";
print "-"x40 . "\\n";
while ( my $sql_batch = $batch->next ) {
dbh_do($local_dbh, $sql_batch);
}
}
Granted, we could have performed this without the iterator, but this is just the first revision. I expect to be adding a lot more to it (e.g. T-SQL verifier) so that I can hide the complexity behind the iterator.
Listen to this podcast
Aug 11
Just installed Sybase’s PowerBuilder 11 and Created a .NET application accessing a Sybase ASE server, a MySQL server and a PostgreSQL server
ASE, DBI, Databases, Events, MySQL, News, OS, Perl, Postgres, Powerbuilder, Sybase, TechWave, Windows 1 Comment »Because the of the excellent sessions and a keynote at Sybase TechWave 2007, I’m picking up PowerBuilder 11. Thought it was dead? We all heard it over and over again from the VB, C++, .NET folk. Think again and check this out!
SPECIAL PLENARY SESSION — Advancing the Boundaries of Development: How IT developers will face the future.
Tuesday, 1-2:15 p.m. PDT. | View the recorded webcast
Using the PowerBuilder Application Server Plugin
Wednesday, 1 - 2 p.m. PDT. Speaker: Evan Ireland | View the recorded webcast
Using .NET Classes in PowerBuilder
Wednesday, 2:15 - 3:45 p.m. Speaker: Xue-song Wu | View the recorded webcast
Best Practices of PowerBuilder 11.0 WinForm/Smart Client Application Development
Wednesday, 4 - 5:30 p.m. Speaker: Harry Zhang | View the recorded webcast
The DataWindow in PowerBuilder WebForm Targets
Thursday, 1 - 2 p.m. Speaker: Frederick Koh | View the recorded webcast
PowerBuilder: Service Creation
Thursday, 2:15 - 3:15 p.m. Speaker: Sheila Wood | View the recorded webcast
Did you know that you can build .NET applications easily with PowerBuilder? I did, but I didn’t realize that it was very very easy! Say good bye to C# and VB.net for any database application, because PowerBuilder is back and BETTER than anything that Microsoft has to offer!
Most importantly, the video of the key note, Future Development Will Rely on Current Core Technologies, should be out in the next few days. I’ll post the link to it…. it ultimately was the final motivator to make me pick up PowerBuilder 11. Thanks to Jason Fentor for dragging me to the keynote!
UPDATE: Future Development Will Rely on Current Core Technologies video is here! When you open the video click down to “PowerBuilder 11: Accelerate your development”
It took me 10 minutes from knowing absolutely no PowerBuilder to building a working client/server .NET application connecting three wildly different databases (Sybase ASE, MySQL and PostgreSQL) and retrieving/updating data to them! TEN MINUTES! I expected an hour or two but ten minutes… I’m just completely floored!
Sorry, I’m not usually this impressed and try to take everything with a grain of salt, but… Sybase, with all your faults, dang! You did good!
For comparison: The same application took me 30 minutes to write in Perl, which I’m pretty good at.
Listen to this podcast
Aug 08
FW: Scanning your iPhone Backup Files
ASE, DBI, Databases, Gadgets, How To, Mac OSX, Microsoft, MySQL, OS, Perl, SQL Server, Sybase No Comments »Erica Sadun wrote an application, written in Perl, to convert your iPhone backup files into a SQLite3 database:
Here’s a nice way to recover notes from your iPhone without having to mail them to yourself–although it’s not for the faint of heart. Read more at The Unofficial Apple Weblog…
It would be a simple matter to have the data imported in to your favorite DBMS if you prefer Sybase ASE, MS SQL Server, dBASE / xBase, etc.
Listen to this podcast
Jun 21
Geek Spotlight: Michael Peppler
ASE, DBI, Databases, Geek Spotlight, Linux, OS, Perl, Sybase No Comments »You are best known for creating the custom build of Perl known as SybPerl back in 1991 when Perl itself was new. What made you decide to create SybPerl?
In 1989 I moved from developing on MS-DOS and Waterloo Port (Waterloo Port was a cousin to QNX) to SunOS 4.x. I had no formal software (or OS) training, so this was a major shift, and required me to learn a lot of new things (shell, awk, sed, etc.). At the same time I first started using Sybase (SQL Server 4.0, on a Sun 386i). In 1990 I went to the Software Development conference in Oakland, and Rob Kolstad (IIRC he was at Sun at the time - a quick google search shows that he is a director of SAGE now) made a presentation on perl - and as a C programmer this made a lot of sense.
I managed to download and build perl (remember - no internet back then so getting the code meant sending an email request via uucp to a mail responder that returned uuencoded chunks…). After building perl it quickly became obvious that writing perl scripts to perform various duties on the Sybase database would be ideal. The first attempt involved running isql in a subshell, via pseudo terminals. While this worked it was rather slow, and error prone.
Then around June 1990 Larry Wall added “user subs” to perl (version 3.something) and that made it comparatively easy to link a few DBLibrary calls into perl. That first version only opened a single connection, and had a number of other limitations. During the next year I expanded it to allow multiple connections, and then in September 1991 I made it public in a post to comp.lang.perl. It was a natural move for me - I had already been active for several years on a few public BBSs (mainly BIX, set up by Byte Magazine), and Usenet, and helping others had allowed me to learn my craft - making this code public was just an extension of that activity.
You wrote the Sybase module for the Perl DBI (DBD::Sybase). What difficulties did you run into when making the module? Were you also involved in the design of the Perl DBI?
The impetus for DBI was started by Buzz Moschetti - he was also a sybperl user (among other things) and was getting annoyed at having different APIs for the various database servers that he needed to access from his perl scripts. Buzz, Kevin Stock, Ted Lemon, Tim Bunce and various others (including myself) were on the original mailing list for the design of the DBI spec, although Tim certainly did most of the actual work. It took a lot of time before I started writing the DBI module (initially as an emulation layer on top of Sybase::CTlib). Then there was mostly the issue of handling multiple result sets which the DBI wasn’t really geared to handle. I think the work-around I chose (using the syb_more_results attribute to check for more results) was reasonable and allowed code written for DBD::Sybase to be compatible with “normal” DBI code. For a long time I was really still a Sybase::CTlib user, and it’s only comparatively recently (in the last couple of years) that I’ve started to use DBD::Sybase extensively for my own work.
Did you receive any help from Sybase, specifically any undocumented features of OpenClient? Has it been difficult supporting both FreeTDS and OpenClient?
I received some help from Sybase in 1995 when they were developing web.sql, which itself used perl and the Sybase::CTlib module. Since then I have received help “privately” from various people at Sybase (via email, or in the newsgroups). However, Sybase doesn’t publicly acknowledge DBD::Sybase (yet ?), which is a little unfortunate as they do acknowledge various other OpenSource interfaces (for PHP and the DBI module for SqlAnywhere).
My support for FreeTDS is minimal at the moment, due to a lack of time. I did some work on a DBD::FreeTDS module a couple of years ago (sponsored by a Wall Street brokerage firm), and I should really find the time to make this module public. The reason to have this module separate from DBD::Sybase is to allow linking with both Sybase OpenClient and FreeTDS from the same perl process, and to remove the functionality that isn’t supported by FreeTDS.
Is there anything in the pipeline from you? Any new features that will be added soon to your modules?
Not really - at the moment I’m focusing on maintenance, bug removal, and following the DBI evolution. As Tim Bunce is now a DBD::Sybase user he has contributed some significant changes (and motivation). After 17 years spent working on what is for large parts the same code it gets harder to sit down after a days work and decide to work on this or that feature.
Can you tell us a little about your professional work life?
I’m a production DBA in a large bank, with primary focus on Sybase ASE, and secondary focus on Oracle. As a production DBA I don’t do much coding, and only minimal performance tuning. Most of the applications we run are third party apps, so our maneuvering range is quite small (add cache, make sure the disks are configure corrrectly, and that’s about it). The things we do code are backup and restore scripts, DBCC checks, and so on. I’m on call one day out of five (meaning I must be able to get on to the company network in less than one hour), but other than that it’s fairly low stress - mainly because we’ve developped procedures and checks to catch problems as early as possible.
What do you do when you’re not coding?
I read a lot (Science Fiction, Crime/detective, general fiction), listen to music (I used to play guitar and bass in a band a long time ago…), I like to ski (I live in Switzerland), play golf, and just hang out with friends…
What other languages do you work with besides Perl?
SQL (well, I’m a DBA :-), and C.
For those of us that use your Perl modules, how can we express our gratitude?
Submit patches with the changes you’d like to see, help answering questions for newbies in the newsgroups and mailing lists.
Listen to this podcast
Jun 05
Update to “Using dbcc dbtable() to determine if a database is in LOG SUSPEND”
ASE, DBI, Databases, Perl, Sybase No Comments »Way back in January, I posted a method using dbcc dbtable() to determine if a database was in log suspend. After a comedy of miscommunication with Sybase, the feature request 470339 was created to handle this issue:
When an 1105 is hit on some segment, the used/free page counters for the fragements the segment is on should be set to 100% used. So we can have more accurate output of log spaces usages, Or a better way for monitoring “tempdb” / “user tempdb” log space.
Kudos to Sybase for recognizing this issue as a real problem.
Listen to this podcast
Apr 15
Making headway on the Sybase::TdsServer and Sybase::RepAgent perl modules
DBI, Databases, News, OS, Perl, Postgres, RepAgents, Replication Server, Sybase, Windows, subversion 2 Comments »Remember my Sybase Replication Server: custom built RepAgents with Perl! post back last year? Well, not only am I the new maintainer, but I finally have it working in a test environment. It isn’t ready for a new release for Sybase::TdsServer and Sybase::RepAgent, yet, but expect one soon.
My main development environment for these two modules is:
- Windows XP SP2
- ActiveState Perl 5.8.8
- Sybase Workspace 1.7 (Eclipse based)
- EPIC Perl plugin
- Subclipse (subversion client)
I’m hoping to get out my first CPAN release to the perl testers within the next month or so. Why the delay? My wife and I are expecting family and friends to stay with us for the next few weeks. That’s a very good thing
Listen to this podcast
Feb 28
First let me explain that I’ve been up for more than 40 hours. I’ve had a few hours of sleep earlier today but my brain is not working yet.
On page 171, chapter 4, section 4.5.5 of Higher Order Perl
} elsif ($action eq ‘nextval’) {
my $oldrow = $row;
$row = $sth->fetchrow_arrayref;
return $oldrow->[0];
}
UPDATE: This simply sets up the next iteration. That way, we will know if the result set has been entirely processed and the next iteration will be the last iteration. Lesson learned: before reading Higher Order Perl, get some SLEEP!
Full subroutine code:
sub dbi_query_iterator {
my ($sth, @params) = @_;
$sth->execute(@params) or return;
my $row = $sth->fetchrow_arrayref()
return Iterator {
my $action = shift() || ‘nextval’;
if ($action eq ‘exhausted?’) {
return ! defined $row;
} elsif ($action eq ‘nextval’) {
my $oldrow = $row;
$row = $sth->fetchrow_arrayref;
return $oldrow->[0];
}
}
Listen to this podcast
Jan 16
Using dbcc dbtable() to determine if a database is in LOG SUSPEND
ASE, DBI, Perl, Sybase No Comments »Usually when a database runs out of log and a process enters LOG_SUSPEND, you can see this in the output of sp_who. What happens if the process doesn’t exist? Sometimes, Sybase ASE doesn’t properly clean up after a process exits (this would be a bug) and leaves resources still tied up.
If that particular process filled up the log, you might be able to detect it if you ran Ed Barlow’s “sp__dbspace” stored procedure. The space structures that sp__dbspace uses may only get updated when the log passes the Last Chance Threshold (LCT). For small databases the LCT might be at 80% or less of the total log or the space usage information could be just plain wrong. If you have a monitoring process that alerts you if the log fills to 90% or higher, you may never get the alert because ASE is still reporting that the log is still 89% full when in fact, it is plum full. No matter how many times you performed dump tran with truncate_only or no_log, the log would never be freed up until you restarted ASE and then performed a dump tran.
If you run dbcc dbtable(< dbname>), you can look for the “dbt_logsuspended” boolean in the output. Note, that dbt_logsuspended will only be set to ‘1′ if there is an active or phantom process that is in LOG SUSPEND.
Obviously, checking if the database is in LOG SUSPEND is just one piece of determining if there is an outage for a particular database. In the coming days, I’ll provide other methods to complete it.
I am including a perl script that shows the use of dbcc dbtable().
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Basename;
our $SYBDBA1_login = ’sa’;
our $SYBDBA1_password = ‘password’;
our %dbTable;
sub _syb_errhandler_dbTable{
my ($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_type) = @_;
if ($msg =~ m/dbt_logsuspended=(\d+)/) {
$dbTable{’log suspended’} = $1;
}
return 0;
}
sub _is_db_in_logsuspend {
my ($dbh, $dbname) = @_;
# dbcc ouput is sent to STDERR, so we redirect it to /dev/null just for this subroutine
local *STDERR;
open (STDERR, ">", "/dev/null");
# set up temporary error handler. dbcc commands
# can’t be caught using $dbh->err*
$dbh->{syb_err_handler} = \&_syb_errhandler_dbTable;
$dbh->do("dbcc traceon(3604)");
$dbh->do("dbcc dbtable($dbname)");
$dbh->{syb_err_handler} = undef;
return $dbTable{’log suspended’};
}
sub _connect_dbms {
my $loc_dbh;
my $script = basename($0);
# we use the new keepalive parameter to enable TCP_KEEPALIVE for this script
if ($loc_dbh = DBI->connect("dbi:Sybase:server=DBADEV1;loginTimeout=10;timeout=30;keepalive=1;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1", $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 0 } )) {
return $loc_dbh;
} else {
report_err("unable to connect to DBADEV1", "error");
}
}
my $dbh = _connect_dbms;
my $dbname = "test";
if (_is_db_in_logsuspend($dbh, $dbname) == 1) {
print "Database $dbname is in LOG SUSPEND.\n";
} else {
print "Database $dbname is okay.\n";
}
Listen to this podcast
Jan 10
I’ve created a patch to enable support for CS_CON_KEEPALIVE (TCP keepalive packet SO_KEEPALIVE) in the DBD::Sybase v1.07 code. This will send an empty TCP keepalive ‘packet’ to the remote server.
"KeepAlive sets the value of the Sybase CT-Lib connection property CS_CON_KEEPALIVE to true or false. The default setting ensures that your connection is alive by sending packets to the database when the connection is idle. Set the value of this property to false for mobile clients that do not maintain constant connections."
I’ve sent the patch to Michael Peppler.
Listen to this podcast
Nov 08
It is important to know that this is just one method of reconnecting a dead connection. Note that we are handling the errors manually for the individual query. It wouldn’t take much to create a db_exec subroutine so we just call the db_exec() subroutine and just worry about the reconnect in one place.
Notice that because of DBD::Sybase bug # 616, the error 151 will be printed to STDERR.
./test_sybase
OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (151)
Message String: ct_cancel(): user api layer: external error: A connection to the server must exist on the connection structure before this routine can be called.
ERROR: Connection to DBMS died
syb_db_disconnect(): ct_close() failed
MSG: ERROR: Reconnected
Listen to this podcast
Nov 08
I received an email from Michael Peppler, the creator/maintainer of DBD::Sybase. In it, he says that this is a limitation of Sybase’s OpenClient CT-Library not DBD::Sybase.
The error handler is called by Sybase’s ct_callback() function. Sybase has restricted what CT-Library calls that can be made. Apparently while ct-connect() can be called, memory allocation of the connection will be disallowed.
I’m opening a number of feature requests with Sybase to make reconnecting, when the connection dies, easier to do.
- Allow reconnect from within callback function
- Allow ct_connect() to reconnect (basically have CT-Library directly handle the reconnect rather than the programmer)
Listen to this podcast
Nov 06
Problem with filtered Mason code & db connection
ASE, DBI, Mason, Perl, Sybase, mod_perl No Comments »Hi,
In chapter 5 (Advanced Features pgs 82,83) of Embedding Perl in HTML with Mason from O’Reilly, the"a simple SQL select expressed in something like a taglib style" example appears to be straight forward. It is but it doesn’t seem to work too well.
The premise is that the ".components/sql/select" will filter the chunk of html code
<&| .components/sql/select, query => ‘SELECT name, type FROM sysobjects’ &>
—-> here
<tr>
<td>%name</td>
<td>%type</td>
</tr>
—–>to here
</&>
The select returns data correctly but the ".components/sql/select" doesn’t appear to be printing the code hmmmm…. see the very bottom for the answer… I didn’t catch it for awhile but later saw the cause and could have kicked myself.
Listen to this podcast
Sep 25
There appears to a bug with DBD::Sybase or perhaps Sybase OpenClient ctlib (threaded) that causes custom signal handlers to segfault. This tripped up a monitoring script that I wrote. 
I’ve asked the perl module maintainer, Michael Peppler, whether this is a DBD::Sybase bug or an Openclient bug.
Update:
looks like this is a DBD::Sybase bug not an OpenClient ctlib as the example $SYBASE/$SYBASE_OCS/sample/ctlibrary/multthrd.c with an added signal handler works fine:
$ diff multthrd.c $SYBASE/$SYBASE_OCS/sample/ctlibrary/multthrd.c
150,151d149
< #include <signal.h>
<
217,223d214
< void leave(int sig);
<
< void leave(int sig) {
< printf("caught SIGINT\n");
< exit(-1);
< }
<
258,259d248
< (void) signal(SIGINT,leave);
<
344,348d332
< for(;;) {
< printf("Ready…\n");
< (void)getchar();
< }
<…..
Thread_2:All done processing rows - total 116.
Ready…
caught SIGINT
Listen to this podcast
Sep 13
When I was looking up the syntax for a parameter on Michael Peppler’s DBD::Sybase perl module, I ran across his explanation of the syb_flush_finish parameter. I just had to explain what was being reported to him
syb_flush_finish (bool)
If $dbh->{syb_flush_finish} is set then $dbh->finish will drain any results remaining for the current command by actually fetching them. The default behaviour is to issue a ct_cancel(CS_CANCEL_ALL), but this appears to cause connections to hang or to fail in certain cases (although I’ve never witnessed this myself.)
What was being reported to Michael Peppler was most likely the result of how cancels were performed in TDS version 4.x. In TDS 4, the cancel was performed using the TCP expedited flag (similar to the OUT OF BAND flag. Which caused ASE to cancel whatever operation it was currently doing and in some cases, this would involve the connection going into an unknown state.
Listen to this podcast
