HOWTO: Building Perl Module DBD::Sybase 1.15 for ActiveState Perl (Windows 32bit) 5.16.3 using the MinGW compiler

We no longer are tied to using Microsoft’s SAP SybaseVisual C++ compiler for building Michael Peppler’s Perl Module DBD::Sybase 1.15 for ActiveState Perl (Windows 32bit)!

We need to make a few changes to SAP Sybase’s Openclient though. Don’t worry, the changes are only needed to build the module. I used Sybase Openclient 15.7 ESD 7 but you should be able to use any 15.7 version of Openclient with minimal changes.

%SYBASE%\%SYBASE_OCS%\include\csconfig.h differences:

$ diff csconfig.h.old csconfig.h
58a59,63
> /* Load MinGW specific definitions */
> #if defined(__MINGW32__)
> #include "_mingw.h"
> #endif /* __MINGW32__ */
>
74c79
< #if ((SYB_MSC_VER >= 800)  || defined(__BORLANDC__))
---
> #if ((SYB_MSC_VER >= 800)  || defined(__BORLANDC__)) || defined(__MINGW32__)
84c89
< #else /* ((SYB_MSC_VER >= 800) || defined(__BORLANDC__)) */
---
> #else /* ((SYB_MSC_VER >= 800) || defined(__BORLANDC__)) || defined(__MINGW32__) */
94c99
< #endif /* ((SYB_MSC_VER >= 800) || defined(__BORLANDC__)) */
---
> #endif /* ((SYB_MSC_VER >= 800) || defined(__BORLANDC__)) || defined(__MINGW32__) */

%SYBASE%\%SYBASE_OCS%\include\sybfront.h differences:

$ diff sybfront.h.old sybfront.h
162c162
< #if !defined(_MSC_VER) && !defined(__BORLANDC__)
---
> #if !defined(_MSC_VER) && !defined(__BORLANDC__) && !defined(__MINGW32__)
180c180
< #if !defined(_MSC_VER) && !defined(__BORLANDC__)
---
> #if !defined(_MSC_VER) && !defined(__BORLANDC__) && !defined(__MINGW32__)

csconfig
sybfront

We now need to copy the *.lib files in %SYBASE%\%SYBASE_OCS%\lib appending “.a” in place of the “.lib” suffix:
libs

Don’t you perlthink SAP Sybase should add the changes above to OpenClient? I think so. Let your SAP Representative know!

Now we’re ready to build the module!

U:\build\DBD-Sybase-1.15>perl Makefile.PL
Set up gcc environment - 3.4.5 (mingw-vista special r3)
Sybase OpenClient 15.7 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]:

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): test_svr
User ID to log in to Sybase (default: sa):
Password (default: undef):
Sybase database to use on sd02 (default: undef): tempdb

* Writing login information, including password, to file PWD.

Checking if your kit is complete...
Looks good
Warning (mostly harmless): No library found for -llibsybtcl.lib
Warning (mostly harmless): No library found for -llibsybcomn.lib
Warning (mostly harmless): No library found for -llibsybintl.lib
Multiple copies of Driver.xst found in: C:/Perl/site/lib/auto/DBI/ C:/Perl/lib/auto/DBI/ at Makefile.PL line 80.
Using DBI 1.63 (for perl 5.016003 on MSWin32-x86-multi-thread) installed in C:/Perl/site/lib/auto/DBI/
Generating a dmake-style Makefile
Writing Makefile for DBD::Sybase
Writing MYMETA.yml and MYMETA.json

So far so good, so let’s continue with compiling and building the module:

U:\build\DBD-Sybase-1.15>dmake
dmake.exe:  makefile:  line 454:  Warning: -- Macro `BOOTSTRAP' redefined after use
cp dbd-sybase.pod blib\lib\DBD\dbd-sybase.pod
cp Sybase.pm blib\lib\DBD\Sybase.pm
C:\Perl\bin\perl.exe -p -e "s/~DRIVER~/Sybase/g" C:\Perl\site\lib\auto\DBI\Driver.xst > Sybase.xsi
C:\Perl\bin\perl.exe C:\Perl\site\lib\ExtUtils\xsubpp  -typemap C:\Perl\lib\ExtUtils\typemap  Sybase.xs > Sybase.xsc && C:\Perl\bin\perl.exe -MExtUtil
s::Command -e mv -- Sybase.xsc Sybase.c
C:\Perl\site\bin\gcc.exe -c  -IC:\Sybase/OCS-15_0/include -IC:/Perl/site/lib/auto/DBI   -DNDEBUG -DWIN32 -D_CONSOLE -DNO_STRICT -DPERL_TEXTMODE_SCRIPT
S -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -D_USE_32BIT_TIME_T -DHASATTRIBUTE -fno-strict-aliasing -mms-bitfields
-O2       -DVERSION=\"1.15\"    -DXS_VERSION=\"1.15\"  "-IC:\Perl\lib\CORE"   Sybase.c
C:\Perl\site\bin\gcc.exe -c  -IC:\Sybase/OCS-15_0/include -IC:/Perl/site/lib/auto/DBI   -DNDEBUG -DWIN32 -D_CONSOLE -DNO_STRICT -DPERL_TEXTMODE_SCRIPT
S -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -D_USE_32BIT_TIME_T -DHASATTRIBUTE -fno-strict-aliasing -mms-bitfields
-O2       -DVERSION=\"1.15\"    -DXS_VERSION=\"1.15\"  "-IC:\Perl\lib\CORE"   dbdimp.c
dbdimp.c: In function `syb_st_execute':
dbdimp.c:3906: warning: passing arg 2 of `ct_results' from incompatible pointer type
dbdimp.c:3930: warning: passing arg 5 of `ct_bind' from incompatible pointer type
Running Mkbootstrap for DBD::Sybase ()
C:\Perl\bin\perl.exe -MExtUtils::Command -e chmod -- 644 Sybase.bs
C:\Perl\bin\perl.exe -MExtUtils::Mksymlists \
     -e "Mksymlists('NAME'=>\"DBD::Sybase\", 'DLBASE' => 'Sybase', 'DL_FUNCS' => {  }, 'FUNCLIST' => [], 'IMPORTS' => {  }, 'DL_VARS' => []);"
Set up gcc environment - 3.4.5 (mingw-vista special r3)
C:\Perl\site\bin\dlltool.exe --def Sybase.def --output-exp dll.exp
C:\Perl\site\bin\g++.exe -o blib\arch\auto\DBD\Sybase\Sybase.dll -Wl,--base-file -Wl,dll.base -LC:\Sybase/OCS-15_0/lib -mdll -L"C:\Perl\lib\CORE" Syba
se.o    dbdimp.o   C:\Perl\lib\CORE\perl516.lib C:\Sybase\OCS-15_0\lib\libsybct.lib C:\Sybase\OCS-15_0\lib\libsybcs.lib C:\Sybase\OCS-15_0\lib\libsybb
lk.lib C:\Perl\site\lib\auto\MinGW\lib\libm.a C:\Perl\site\lib\auto\MinGW\lib\libkernel32.a C:\Perl\site\lib\auto\MinGW\lib\libuser32.a C:\Perl\site\l
ib\auto\MinGW\lib\libgdi32.a C:\Perl\site\lib\auto\MinGW\lib\libwinspool.a C:\Perl\site\lib\auto\MinGW\lib\libcomdlg32.a C:\Perl\site\lib\auto\MinGW\l
ib\libadvapi32.a C:\Perl\site\lib\auto\MinGW\lib\libshell32.a C:\Perl\site\lib\auto\MinGW\lib\libole32.a C:\Perl\site\lib\auto\MinGW\lib\liboleaut32.a
 C:\Perl\site\lib\auto\MinGW\lib\libnetapi32.a C:\Perl\site\lib\auto\MinGW\lib\libuuid.a C:\Perl\site\lib\auto\MinGW\lib\libws2_32.a C:\Perl\site\lib\
auto\MinGW\lib\libmpr.a C:\Perl\site\lib\auto\MinGW\lib\libwinmm.a C:\Perl\site\lib\auto\MinGW\lib\libversion.a C:\Perl\site\lib\auto\MinGW\lib\libodb
c32.a C:\Perl\site\lib\auto\MinGW\lib\libodbccp32.a C:\Perl\site\lib\auto\MinGW\lib\libcomctl32.a C:\Perl\site\lib\auto\MinGW\lib\libmsvcrt.a dll.exp
C:\Perl\site\bin\dlltool.exe --def Sybase.def --base-file dll.base --output-exp dll.exp
C:\Perl\site\bin\g++.exe -o blib\arch\auto\DBD\Sybase\Sybase.dll -LC:\Sybase/OCS-15_0/lib -mdll -L"C:\Perl\lib\CORE" Sybase.o   dbdimp.o   C:\Perl\lib
\CORE\perl516.lib C:\Sybase\OCS-15_0\lib\libsybct.lib C:\Sybase\OCS-15_0\lib\libsybcs.lib C:\Sybase\OCS-15_0\lib\libsybblk.lib C:\Perl\site\lib\auto\M
inGW\lib\libm.a C:\Perl\site\lib\auto\MinGW\lib\libkernel32.a C:\Perl\site\lib\auto\MinGW\lib\libuser32.a C:\Perl\site\lib\auto\MinGW\lib\libgdi32.a C
:\Perl\site\lib\auto\MinGW\lib\libwinspool.a C:\Perl\site\lib\auto\MinGW\lib\libcomdlg32.a C:\Perl\site\lib\auto\MinGW\lib\libadvapi32.a C:\Perl\site\
lib\auto\MinGW\lib\libshell32.a C:\Perl\site\lib\auto\MinGW\lib\libole32.a C:\Perl\site\lib\auto\MinGW\lib\liboleaut32.a C:\Perl\site\lib\auto\MinGW\l
ib\libnetapi32.a C:\Perl\site\lib\auto\MinGW\lib\libuuid.a C:\Perl\site\lib\auto\MinGW\lib\libws2_32.a C:\Perl\site\lib\auto\MinGW\lib\libmpr.a C:\Per
l\site\lib\auto\MinGW\lib\libwinmm.a C:\Perl\site\lib\auto\MinGW\lib\libversion.a C:\Perl\site\lib\auto\MinGW\lib\libodbc32.a C:\Perl\site\lib\auto\Mi
nGW\lib\libodbccp32.a C:\Perl\site\lib\auto\MinGW\lib\libcomctl32.a C:\Perl\site\lib\auto\MinGW\lib\libmsvcrt.a dll.exp
C:\Perl\bin\perl.exe -MExtUtils::Command -e chmod -- 755 blib\arch\auto\DBD\Sybase\Sybase.dll

Now, you might be thinking that since it compiled it should be good huh? Nah, we test things here! 😉

U:\build\DBD-Sybase-1.15>dmake test
dmake.exe:  makefile:  line 454:  Warning: -- Macro `BOOTSTRAP' redefined after use
C:\Perl\bin\perl.exe "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib\lib', 'blib\arch')" t/*.t

t/autocommit.t .. ok
t/base.t ........ ok
t/exec.t ........ ok
t/fail.t ........ ok
t/login.t ....... 1/6 DBD::Sybase::db ping failed: ct_cmd_alloc failed at t/login.t line 32.
t/login.t ....... ok
t/main.t ........ ok
t/multi_sth.t ... ok
t/nsql.t ........ ok
t/place.t ....... ok
t/thread.t ...... skipped: this DBD::Sybase not configured to support iThreads
t/utf8.t ........ ok
t/xblk.t ........ ok
t/xblob.t ....... ok
All tests successful.
Files=13, Tests=242, 17 wallclock secs ( 0.16 usr +  0.22 sys =  0.37 CPU)
Result: PASS

No major issues so we’re ready to build ActiveState’s PPD file and get it ready distribute:

U:\build\DBD-Sybase-1.15>dmake ppd
dmake.exe:  makefile:  line 454:  Warning: -- Macro `BOOTSTRAP' redefined after use

Zip the blib directory and name it “DBD-Sybase-1.15.zip”. Create a new directory named “MSWin32-x86-multi-thread-5.16” and copy the zip file into it.

You need to tell PPM where to find the zip file. The PPD file is simply an XML document, so we can just update the codebase:

<softpkg NAME="DBD-Sybase" VERSION="1.15">
    <abstract>DBI driver for Sybase datasources</abstract>
    <author>Michael Peppler (mpeppler@peppler.org)</author>
    <implementation>
        <architecture NAME="MSWin32-x86-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x86-multi-thread-5.16\DBD-Sybase-1.15.zip"></codebase>
    </implementation>
</softpkg>

Let’s test that it actually installs using ActiveState’s PPM:

U:\build\DBD-Sybase-1.15>ppm install DBD-Sybase.ppd
Unpacking DBD-Sybase-1.15...done
Generating HTML for DBD-Sybase-1.15...done
Updating files in site area...done
   5 files installed

U:\build\DBD-Sybase-1.15>

We only need to zip up the PPD and the MSWin32-x86-multi-thread-5.16 directory listed in the PPD. It is now ready to distribute. How you do it is entirely up to you.
DBD-Sybase-1.15 for ActiveState Perl 5.16 Win32

For ActiveState Perl 64bit, you will need to use Microsoft Visual Studio. However, since the support of 64bit Perl on Windows by ActiveState is rather poor, I would recommend using the 32bit version instead.

Share Button

HOWTO: Building Perl module DBD::Sybase 1.14 on Windows (32bit or 64bit) with ActiveState Perl 5.16, Microsoft Visual Studio and Sybase OpenClient 15.7

Compiling the DBD::Sybase Perl module really requires Microsoft Visual C++ 2005 or higher. To get started open the “Visual Studio 2005 Command Prompt”.Visual Studio 2005 Command Prompt

You will need to fix the Makefile.PL file:

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibct.lib -llibcs.lib -llibtcl.lib -llibcomn.lib -llibintl.lib -llibblk.lib $attr{EXTRA_LIBS} -lm";

to

if ( $^O eq 'MSWin32' ) {
  $lib_string = "-L$SYBASE/lib -llibsybct.lib -llibsybcs.lib -llibsybblk.lib $attr{EXTRA_LIBS}";

If you don’t, nmake won’t be able to link against the Sybase libraries. Note that we’re adding “syb” after “lib”.

Warning (mostly harmless): No library found for -llibct.lib
Warning (mostly harmless): No library found for -llibcs.lib
Warning (mostly harmless): No library found for -llibtcl.lib
Warning (mostly harmless): No library found for -llibcomn.lib
Warning (mostly harmless): No library found for -llibintl.lib
Warning (mostly harmless): No library found for -llibblk.lib
Warning (mostly harmless): No library found for -lm

When you run perl Makefile.PL, choose the defaults because the nmake test will NOT work with Visual Studio.
Next we need to change lines 3915 and 3916 in dbdimp.c because C89 requires that declarations of variables must occur at the beginning of a code block. This is part of the C89 specification.

for (i = 0; i < foundOutput; i++) { phs = params[i].phs; CS_DATAFMT datafmt;[/c] to [c num=1 highlight_lines = "2,3"]for (i = 0; i < foundOutput; i++) { CS_DATAFMT datafmt; phs = params[i].phs;[/c] If you don't we will get the following errors: [text]dbdimp.c(3916) : error C2275: 'CS_DATAFMT' : illegal use of this type as an expression C:\Sybase\OCS-15_0\include\cstypes.h(864) : see declaration of 'CS_DATAFMT' dbdimp.c(3916) : error C2146: syntax error : missing ';' before identifier 'datafmt' dbdimp.c(3916) : error C2065: 'datafmt' : undeclared identifier dbdimp.c(3918) : warning C4133: 'function' : incompatible types - from 'int *' to 'CS_DATAFMT *' dbdimp.c(3921) : error C2224: left of '.maxlength' must have struct/union type dbdimp.c(3926) : warning C4018: '< ' : signed/unsigned mismatch dbdimp.c(4146) : warning C4244: 'function' : conversion from 'CS_BIGINT' to 'const NV', possible loss of data dbdimp.c(4151) : warning C4244: 'function' : conversion from 'CS_UBIGINT' to 'const NV', possible loss of data dbdimp.c(5124) : warning C4244: '=' : conversion from 'long' to 'CS_BINARY', possible loss of data NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio 8\VC\BIN\cl.EXE"' : return code '0x2' Stop.[/text] The nmake will now complete with many warnings. I've started on working up a patch for the DBD::Sybase maintainer, Michael Peppler. Tar and gzip the blib directory and call it DBD-Sybase-1.14.tar.gz. Put it in a directory like so: "MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz" [text]nmake ppd[/text] Now, you will have a file called DBD-Sybase.ppd consisting of: [xml num=1]
DBI driver for Sybase datasources
Michael Peppler (mpeppler@peppler.org)




[/xml]

If you want to build multiple architectures, you will need to build the Module on the appropriate platform. e.g. Windows 7 64bit. I haven’t had much luck with cross-compilers with ActiveState Perl. YMMV. Once you have the second tar ball, simply add it to your PPD file:

<softpkg NAME="DBD-Sybase" VERSION="1.14">
    <abstract>DBI driver for Sybase datasources</abstract>
    <author>Michael Peppler (mpeppler@peppler.org)</author>
    <implementation>
        <architecture NAME="MSWin32-x64-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x64-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
    <implementation>
        <architecture NAME="MSWin32-x86-multi-thread-5.16"></architecture>
        <codebase HREF="MSWin32-x86-multi-thread-5.16\DBD-Sybase-1.14.tar.gz"></codebase>
    </implementation>
</softpkg>

I typically zip up the PPD file and the two directories listed in the PPD and distribute that. How you do it is entirely up to you.

Oh, if you want DBD::Sybase on Windows to connect to Microsoft SQL Server, build with FreeTDS.

Share Button

Get it here! Perl DBD::Sybase 1.14.01 for Active State Perl 5.16 Windows XP/Vista/7/8 32bit AND 64bit

Assuming that you installed Sybase SDK OpenClient 15.7.

Sybase Openclient is included in the Sybase Developer’s Kit, and ASE PC Client. If you don’t have a license, you can download the ASE 15.7 Developer Edition for Windows which will include it.

Install ActiveState Perl from http://www.activestate.com (free) and install DBI if it isn’t already installed.  It should be but you never know…

  1. Start -> ActiveState Perl -> Perl Package Manager
  2. install DBI
  3. exit

Now, the easy part.  Install the DBD-Sybase-1.14 1.14 PPM:

  1. download DBD-Sybase-1.14
  2. extract zip file to temporary directory (e.g. c:\test)
  3. Start -> Run -> cmd.exe (as Administrator if Vista or Windows 7)
  4. cd \test
  5. ppm install DBD-Sybase.ppd
  6. exit

That’s it :)

It should automatically install the DBD::Sybase for Perl 5.16 32bit or 64bit depending on which version of Active State Perl you have installed.

UPDATE (March 6th, 2013):

The PPM is fixed now. Please let me know of any issues.

Share Button

35383: DBD::Sybase calls the broken sp_tables stored procedure

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");
---
<div class="message-stanza-depth-1">
<div class="message-stanza-depth-2">> #     my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog,</div>
</div>
$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
>
>               });
>
Share Button

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

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 will often store the SQL text for stored procedures and views
with the carriage return embedded (\r). We need to strip that out else
we will receive “^M” in the output.

diff Sybase_orig.pm Sybase_new.pm
108a109
<div class="message-stanza-depth-1">>
</div>
109a111,112
<div class="message-stanza-depth-1">>       $stuff->{view}->{$_->[0]}->{text} =~ s/\r//g
>               if (defined ($stuff->{view}->{$_->[0]}->{text}));
</div>
155a159,161
>       $stuff->{procedures}->{$_->[0]}->{text} =~ s/\r//g
>               if (defined ($stuff->{procedures}->{$_->[0]}->{text} ));
>
Share Button

Replacing Sybase ASE’s Historical Server: Using OpenSource

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:

  1. Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
  2. SQLite for the local storage (way lower overhead than ASE or SQL Anywhere)
  3. Perl Catalyst / Template::Toolkit web front end with REST web services

All of this goes into a VMWare Appliance running Ubuntu 7.10 Server… a drop-in solution that can even be run on a laptop.  I’m hoping that my employer lets me release it as then I can zip it up and put it on sourceforge so everyone can benefit.

Share Button

TCP Keepalive not working on Linux?

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
Share Button

Using dbcc dbtable() to determine if a database is in LOG SUSPEND

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";
    }
Share Button

Adding TCP Keepalive Support to DBD::Sybase perl module

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.

diff dbdimp.h ../DBD-Sybase-1.07/dbdimp.h
78d77
< char keepalive[16];[/perl] [perl]diff dbdimp.c ../DBD-Sybase-1.07/dbdimp.c 1052d1051 < extractFromDsn("keepalive=", dsn, imp_dbh->keepalive, 10);
1152,1166d1150
< < if (imp_dbh->keepalive[0]) {
< int keepalive = atoi(imp_dbh->keepalive);
< < if (keepalive != 1) { < keepalive = 0; < } < < if(DBIc_DBISTATE(imp_dbh)->debug >= 3)
< PerlIO_printf(DBIc_LOGPIO(imp_dbh), " syb_db_login() -> ct_config(CS_CON_KEEPALIVE,%d)\n”, keepalive);
< < if((retcode = ct_config(context, CS_SET, CS_CON_KEEPALIVE, &keepalive, CS_UNUSED, NULL)) != CS_SUCCEED) < warn("ct_config(CS_SET, CS_CON_KEEPALIVE) failed"); < } <[/perl]

Share Button

How to reconnect a dead DBD::Sybase Connection

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

#!/usr/bin/perl

use strict;
use warnings;

use lib “/home/jfroebe/lib”;

use DBI;
use File::Basename;

our $SYBDBA1_login = ‘login’;
our $SYBDBA1_password = ‘password’;

our $dbh = connect_dbms();

sub report_err {
my $msg = shift;
my $type = shift;

if ($type eq ‘error’) {
print “ERROR: $msgn”;
} else {
print “MSG: $msgn”;
}
}

sub exit_on_error {
my $msg = shift;

report_err($msg, “error”);
return -1;
}

sub connect_dbms {
my $loc_dbh;
my $script = basename($0);

if ($loc_dbh = DBI->connect(“dbi:Sybase:server=SISDBA1;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1”, $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 1 } )) {
return $loc_dbh;
}

report_err(“unable to connect to SISDBA1”, “error”);
return;
}

sub syb_loop {
my $query = “exec sp_helpdb”;

RETRY: for (my $i = 0; $i < 10000; $i++) { my $array_ref; eval { $array_ref = $dbh->selectall_arrayref($query);
};

if ($@) {
if ($dbh->err == 151 || $dbh->err == 60) {
report_err(“Connection to DBMS died”, “error”);
undef($dbh);

for (my $i = 0; $i < 10; $i++) { if( $dbh = connect_dbms() ) { report_err("Reconnected", "msg"); redo RETRY; } sleep 3; } exit_on_error("Unable to reconnect after $i attempts!"); } } } } syb_loop(); [/perl]

Share Button