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

Ed Barlow Procedures: sp__montableusage updated for SAP Sybase ASE 15 and higher

Back in 2006, Mich Talebzadeh createdSAP Sybase the sp__montableusage stored procedure for reporting on table and index usage based on the MDA tables in Sybase ASE 12.5x using the MDA tables as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). In v15, Sybase deprecated the rowcnt() and reserved_pgs() functions that was used to report on the estimated number of rows in favor of the row_count() and reserved_pages() functions respectfully. I’ve updated the stored procedure to use the row_count() and reserved_pages() functions.

$ diff sp__montableusage.12 sp__montableusage.15
10,15c10,17
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Stored procedure giving comprehensive report
< --     |        |                    |    | about table and index usage based on the MDA tables
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Replace rowcnt with row_count() and reserved_pgs with
> --     |          |                    |    | reserved_pages()
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Stored procedure giving comprehensive report
> --     |          |                    |    | about table and index usage based on the MDA tables
> -------+----------+--------------------+----+-----------------------------------------------------
27,28c29,30
< rowtotal = rowcnt(i.doampg),
<         reserved = (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg)) * (low / 1024)
---
>         rowtotal = row_count(db_id(), o.id),
>         reserved = reserved_pages(db_id(), o.id, i.indid) * (low / 1024)

use sybsystemprocs
go
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = ‘P’ AND name = ‘sp__montableusage’)
BEGIN
DROP PROCEDURE sp__montableusage
END
go
create procedure sp__montableusage
as
————————————————————————————————–
— Vers| Date | Who | DA | Description
——-+———-+——————–+—-+—————————————————–
— 1.1 |11/18/2013| Jason Froebe | | Replace rowcnt with row_count() and reserved_pgs with
— | | | | reserved_pages()
— 1.0 |07/04/2006| Mich Talebzadeh | | Stored procedure giving comprehensive report
— | | | | about table and index usage based on the MDA tables
——-+———-+——————–+—-+—————————————————–

begin
set nocount on

— First of all work out the table rows and sizes (reserved)

select
“Owner” = user_name(o.uid),
TableName = o.name,
IndexName = i.name,
low = d.low,
rowtotal = row_count(db_id(), o.id),
reserved = reserved_pages(db_id(), o.id, i.indid) * (low / 1024)
into #t1
from sysobjects o, sysindexes i, master.dbo.spt_values d
where
o.type = ‘U’
and i.id = o.id
and d.number = 1
and d.type = “E”

select distinct
Owner,
TableName,
RowTotal = convert(char(11), sum(rowtotal)),
Reserved = sum(reserved)
into #table_size
from #t1
group by Owner, TableName

— Identify tables accessed with a table scan
SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“PagesRead” = m.PagesRead,
“WhenLastUsed” = m.LastUsedDate,
“Used” = m.UsedCount
INTO #tabscan
from
sysobjects o,
master..monOpenObjectActivity m
where
o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and m.UsedCount > 0

print “”
print ‘Tables accessed with Table scans ONLY, no index usage’
print “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“PagesRead” = t.PagesRead,
“Table scanned” = str(t.Used, 8, 0),
“When last table scanned” = t.WhenLastUsed
FROM
#tabscan t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and not exists (select 1 from master..monOpenObjectActivity m
where object_name(m.ObjectID, m.DBID) = t.TableName
and object_name(m.ObjectID, m.DBID) = s.TableName
and m.DBID = db_id()
and m.IndexID > 0
and m.LastUsedDate is not NULL)
ORDER BY
t.Owner,
t.TableName


— Identify tables with no DML

SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“LockRequests” = m.LockRequests,
“Operations” = m.Operations,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate
INTO
#dormant
from
sysobjects o,
master..monOpenObjectActivity m
where
object_name(m.ObjectID, m.DBID) = o.name
and o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0 — Only tables!
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and m.RowsInserted = 0
and m.RowsUpdated = 0
and m.RowsDeleted = 0

PRINT “”
PRINT “Displaying dormant tables with no DML activity, table scan or index usage”
PRINT “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“LockRequests” = t.LockRequests
FROM
#dormant t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and t.WhenLastUsed is NULL — table has never been used by the optimiser
— and no index of this table has been used by the optimiser
and not exists (select 1 from master..monOpenObjectActivity m
where object_name(m.ObjectID, m.DBID) = t.TableName
and object_name(m.ObjectID, m.DBID) = s.TableName
and m.DBID = db_id()
and m.IndexID > 0
and m.LastUsedDate is not NULL)
ORDER BY
t.Owner,
t.TableName


— Identify tables with DML activity

SELECT
“Owner” = user_name(o.uid),
“TableName” = o.name,
“LogicalReads” = m.LogicalReads,
“LockRequests” = m.LockRequests,
“Operations” = m.Operations,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate,
“Inserted” = m.RowsInserted,
“Updated” = m.RowsUpdated,
“Deleted” = m.RowsDeleted
INTO
#temp
from
sysobjects o,
master..monOpenObjectActivity m
where
object_name(m.ObjectID, m.DBID) = o.name
and o.type = ‘U’
and o.id = m.ObjectID
and m.IndexID = 0
and m.DBID = db_id()
and object_name(m.ObjectID, m.DBID) not like ‘sa_%’
and object_name(m.ObjectID, m.DBID) not like ‘%__sa%’
and object_name(m.ObjectID, m.DBID) not like ‘rs_%’
and (m.RowsInserted > 0 or m.RowsUpdated > 0 or m.RowsDeleted > 0)

SELECT
“TableName” = object_name(m.ObjectID, m.DBID),
“IndexName” = i.name,
“Selected” = m.OptSelectCount,
“WhenLastSelected” = m.LastOptSelectDate,
“Used” = m.UsedCount,
“WhenLastUsed” = m.LastUsedDate
into #used
from master..monOpenObjectActivity m,
sysindexes i
where
m.IndexID > 0
and m.IndexID <> 255 — ignore text, image data chain
and m.IndexID = i.indid
and m.ObjectID = i.id
and m.DBID = db_id()
print “”
if exists(select 1 from #used where Selected = 0 and Used = 0)
begin
print “”
print ‘Indexes never selected or used by the optimizer’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected,
u.Used
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected = 0 and u.Used = 0
order by u.TableName,
u.IndexName
end
if exists(select 1 from #used where Selected > 0 and Used = 0)
begin
print “”
print ‘Indexes selected by the optimizer but never used in query’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected,
“When Last selected” = u.WhenLastSelected
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected > 0 and u.Used = 0
order by u.TableName,
u.IndexName
end
if exists(select 1 from #used where Selected = 0 and Used > 0)
begin
print “”
print ‘Indexes Used by the optimizer but never selected’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
u.IndexName,
“IndexSize/KB” = i.reserved,
u.Selected
from #used u,
#t1 i
where u.TableName = i.TableName
and u.IndexName = i.IndexName
and u.Selected = 0 and u.Used > 0
order by u.TableName,
u.IndexName
end
PRINT “”
PRINT “Displaying tables with DML activity”
PRINT “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
t.Inserted,
t.Updated,
t.Deleted,
t.LockRequests,
“SUM DML ACTIVITY/ROWS ” =
CASE
WHEN t.Inserted+t.Updated+t.Deleted > 0 and convert(numeric(10,0),s.RowTotal) > 0
THEN convert(varchar(9),(t.Inserted+t.Updated+t.Deleted)/convert(numeric(10, 0),s.RowTotal))
WHEN t.Inserted+t.Updated+t.Deleted > 0 and convert(numeric(10,0),s.RowTotal) = 0
THEN ” ==> Update stats advisable”
END
FROM
#temp t,
#table_size s
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
ORDER BY
t.Owner,
t.TableName

— work out sum of index usage for tables where index(s) have been used

SELECT TableName,
SumUsed =sum(Used)
into #sumused
from #used
where Used > 0
group by TableName
print “”
select TableName,
IndexName,
Selected,
Used,
WhenLastUsed
into #clean
from #used
where Used > 0
print “”
print ‘Tables accessed with Table scans and index usage as well’
print “”
SELECT
“TableName” = substring(t.Owner+”.”+t.TableName, 1, 30),
“Rows” = convert(numeric(10,0),s.RowTotal),
“Size/KB” = convert(numeric(10,0),s.Reserved),
“LogicalReads” = t.LogicalReads,
“PagesRead” = t.PagesRead,
“Table Scans” = str(t.Used, 8, 0),
“Index Usage” = str(u.SumUsed, 8, 0),
“IndexUsage/TableScan” = str(u.SumUsed*1.0/t.Used*1.0, 9, 2)
FROM
#tabscan t,
#table_size s,
#sumused u
WHERE
t.Owner = s.Owner
and t.TableName = s.TableName
and t.TableName = u.TableName
and s.TableName = u.TableName
and u.SumUsed > 0
ORDER BY
t.Owner,
t.TableName

SELECT TableName,
IndexName,
Selected,
Used,
“Selected_over_sum_selected” = convert(numeric(10,2),Selected*1.0/sum(Selected)*1.0),
“Used_over_sum_used” = convert(numeric(10,2),Used*1.0/sum(Used)*1.0),
“Used_over_selected” = convert(numeric(10,2),Used*1.0/Selected*1.0)
into #results
from #clean
group by TableName
if exists (select 1 from #results)
begin
print “”
print ‘Index usage analysis’
print “”
select
“TableName” = substring(i.Owner+”.”+i.TableName, 1, 30),
r.IndexName,
“IndexSize/KB” = i.reserved,
r.Selected,
r.Used,
–“Selected/SUM(Selected)” = r.Selected_over_sum_selected,
“Used/SUM(Used)” = convert(numeric(10,2),r.Used_over_sum_used)
–“Used/Selected” = r.Used_over_selected
from #results r,
#t1 i
where
r.TableName = i.TableName
and r.IndexName = i.IndexName
–and r.Used_over_sum_used < 1.0 order by r.TableName, r.Used_over_sum_used desc end end go grant exec on sp__montableusage to public go exit[/sql] sp__montableusage

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