HOWTO: Install SAP Sybase Control Center 3.2.7 on Windows 8

SAP/Sybase doesn’t SAP Sybaseofficially support running SCC on Windows 8. This seems to be more of an issue with the InstallShield installer. Seriously, why do people still use InstallShield??

If you try installing SCC 3.2x on Windows 8, you’ll get a nasty error when you run setup.exe or setupconsole.exe:

scc_setup

scc_setup_details

You *can* install and run SCC 3.2.x on Windows 8 if you are willing to run the 32bit version of SCC. You will need to install Microsoft C++ 2005 SP1 x86.

Next, open a cmd.exe as Administrator and run “setup.exe -i gui”

Share Button

SAP Sybase IQ: Free “Getting started with SAP Sybase IQ server” mini course!

SAP Sybase has recently made available a mini course called “Getting started with SAP Sybase IQ server” for you to learn IQ, a major data warehouse product. Do yourself a favor and learn IQ. There aren’t enough IQ DBAs in the world… YET!

SAP SybaseGetting started with SAP Sybase IQ server

This how-to booklet will help you learn the basics and get started with SAP Sybase IQ as quickly as possible. You do not need to go through all of the lessons. After completing the first three sections you will have a running SAP Sybase IQ database (Don’t worry…the course comes with a database schema and data ready to load).

You can then pick and choose from the remaining lessons, based on your interest:

Please download the zip file for all the ” Metadata, Data and Queries for Lessons in the Quick Start Guide ”

To benefit from this book, you should be familiar with the following concepts:

  • Relational database systems
  • Database schemas
  • SQL
Share Button

If you’re an author of technical books, please ensure that your publisher and/or editor typesets your book contents to be readable.

If you’re an author of technical books, please ensure that your publisher and/or editor typesets your book contents to be readable. Especially tables and program outputs. Just because the application can’t format the output worth a damn, doesn’t mean you need to transfer it to a book:

Example from Sybase 15.0 Replication Server Administration
book_output

Share Button

SAP Sybase ASE MDA Tables: Why is the OwnerName and OwnerID omitted in most of the MDA tables?

If you look SAP Sybaseat the MDA poster, you will find the object names, the database names and the like but not the owner_id or owner_name. Let’s pick on monOpenObjectActivity, one of the most heavily used mda tables

Having monOpenObjectActivity is great but what if I have two tables named the same in the same database with a different owner? Such a scenario is not uncommon in a development system when having a separate database for each developer is not practical.

A row in monOpenObjectActivity could be mydb.john.table_a or mydb.jane.table_a. While I do have the object id, there is no owner_name(DBID, OBJID) function, so I need to go into each individual database and query sysobjects. What a hassle! ASE already knows, or should know, which owner id the object belongs to so why need I have to create a dynamic query for each and every row in monOpenObjectActivity? Sure, I could create a look up table but it wouldn’t be able to handle temporary objects (e.g. #table) very well.

The following tables have Owner something in the column names:

1> select convert(varchar(30), object_name(id)) as "Object Name", convert(varchar(30), name) as "Column Name" from syscolumns where lower(name) like "%owner%"
2> go
Object Name                    Column Name
------------------------------ ------------------------------
monProcessObject               OwnerUserID
monCachedProcedures            OwnerUID
monCachedProcedures            OwnerName
monProcessProcedures           OwnerUID
monSpinlockActivity            OwnerPID
monSpinlockActivity            LastOwnerPID
monMemoryUsage                 PoolOwnerKPID
monProcessProcedures           OwnerName
monCachedObject                OwnerUserID
monCachedObject                OwnerName
sp_namecrack                   @owner

I think we need two functions: owner_name(DBID, OBJID) and owner_id(DBID, OBJID)

We also need the OwnerID and preferrably OwnerName added to the appropriate MDA tables.

What do you think?

Share Button

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 Stored Procedures: sp__monobj Updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monobj uses a heuristic algorithm to find which objects are most busy as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output

Differences:
diff sp__monobj.old sp__monobj.15
17a18,26
> ————————————————————————————————–
> — Vers| Date | Who | DA | Description
> ——-+———-+——————–+—-+—————————————————–
> — 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs
> — 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables.
> — | | | | This uses a heuristic algorithm to find what objects
> — | | | | are most busy.
> ——-+———-+——————–+—-+—————————————————–
>
21a31,34
> declare @max_objectname_size varchar(3)
> declare @exec_str varchar(2000)
>
>
34a48,51
> print “”
> print ” score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits”
> print “”
>
36,37c53,55
< select ObjName=convert(varchar(40),db_name(DBID)+"."+object_name(ObjectID,DBID)), < score= LogicalReads/100 + --- >
> select ObjName = db_name(DBID) + “..” + object_name(ObjectID, DBID),
> score = LogicalReads/100 +
45,46c63,65
< from master..monOpenObjectActivity < where IndexID=0 --- > into #busy_report
> from master..monOpenObjectActivity
> where IndexID=0
55c74
< and db_name(DBID)!="tempdb" --- > and DBName !=”tempdb”
57a77
>
58a79,87
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
> from #busy_report
> order by score desc’
>
> exec (@exec_str)
>
72a102,121
> create table #busy_report_iter_obj (
> ObjName varchar(255) not null,
> Op bigint not null,
> LogReads int not null,
> PhysReads int not null,
> PageReads int not null,
> Writes int not null,
> Ins int not null,
> Del int not null,
> Upd int not null,
> Locks int not null,
> LockWt int not null)
>
> create table #busy_report_iter (
> ObjName varchar(255) not null,
> Op int not null,
> Reads int not null,
> Writes int not null,
> NumRows bigint not null)
>
81,86c130,131
< -- select * < -- from master..monOpenObjectActivity < -- where IndexID=0 and db_name(DBID)!='tempdb' < -- and ( @object_name is null or @object_name=object_name(ObjectID, DBID)) < < select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), --- > insert into #busy_report_iter_obj
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
88c133
< LogReads=o.LogicalReads - i.LogicalReads, --- > LogReads=o.LogicalReads – i.LogicalReads,
92,103c137,167
< "Ins"=o.RowsInserted -i.RowsInserted, < "Del"= o.RowsDeleted - i.RowsDeleted, < "Upd"= o.RowsUpdated - i.RowsUpdated , < "Locks"= o.LockRequests - i.LockRequests, < "LockWt"= o.LockWaits - i.LockWaits < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 --- > Ins=o.RowsInserted -i.RowsInserted,
> Del= o.RowsDeleted – i.RowsDeleted,
> Upd= o.RowsUpdated – i.RowsUpdated ,
> Locks= o.LockRequests – i.LockRequests,
> LockWt= o.LockWaits – i.LockWaits
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
>
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> LogReads,
> PhysReads,
> PageReads,
> Writes,
> Ins,
> Del,
> Upd,
> Locks,
> LockWt
> from #busy_report_iter_obj’
>
> exec (@exec_str)
>
> delete #busy_report_iter_obj
107c171
< set rowcount 10 --- > set rowcount 10
109,138c173,202
< select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)), < Op=o.Operations-i.Operations, < Reads=o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads, < Writes=o.PhysicalWrites-i.PhysicalWrites, < "Rows"=o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated < from master..monOpenObjectActivity o,#tmp i < where o.IndexID=i.IndexID < and o.ObjectID=i.ObjectID < and o.DBID=i.DBID < and o.IndexID=i.IndexID < and o.IndexID=0 < and i.IndexID=0 < -- and ( o.LogicalReads>0 or o.LockRequests>0 )
< order by < o.LogicalReads/100 + < o.PhysicalReads/10 + < o.PhysicalWrites + < o.RowsInserted + < o.RowsDeleted + < o.RowsUpdated + < o.LockRequests/100 + < o.LockWaits - < i.LogicalReads/100 - < i.PhysicalReads/10 - < i.PhysicalWrites - < i.RowsInserted - < i.RowsDeleted - < i.RowsUpdated - < i.LockRequests/100 - < i.LockWaits desc --- > insert into #busy_report_iter
> select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
> Op = o.Operations-i.Operations,
> Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
> Writes = o.PhysicalWrites-i.PhysicalWrites,
> NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
> from master..monOpenObjectActivity o,#tmp i
> where o.IndexID=i.IndexID
> and o.ObjectID=i.ObjectID
> and o.DBID=i.DBID
> and o.IndexID=i.IndexID
> and o.IndexID=0
> and i.IndexID=0
> order by
> o.LogicalReads/100 +
> o.PhysicalReads/10 +
> o.PhysicalWrites +
> o.RowsInserted +
> o.RowsDeleted +
> o.RowsUpdated +
> o.LockRequests/100 +
> o.LockWaits –
> i.LogicalReads/100 –
> i.PhysicalReads/10 –
> i.PhysicalWrites –
> i.RowsInserted –
> i.RowsDeleted –
> i.RowsUpdated –
> i.LockRequests/100 –
> i.LockWaits desc
139a204,215
> select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter
>
> select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
> Op,
> Reads,
> Writes,
> NumRows
> from #busy_report_iter’
>
> exec (@exec_str)
>
> delete #busy_report_iter
152d227
< -- select 1,* from #tmp where object_id('sp_aux_getsize')=ObjectID and DBID=db_id('master')[/diff] SQL of sp__monobj: [sql highlight_lines="18,19,20,21,22,23,24,25,31,32,33,34,54,55,63,64,65,74,79,80,81,82,83,84,85,86,102,103,104,105,106,107,108,109,110,111,112,113,115,116,117,118,119,120,130,131,133,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,204,205,206,207,208,209,210,211,212,213,214,215,227"]use sybsystemprocs go /* Procedure library copyright(c) 2004-2006 by Edward M Barlow */ IF EXISTS (SELECT * FROM sysobjects WHERE name = "sp__monobj" AND type = "P") DROP PROC sp__monobj go CREATE PROC sp__monobj( @num_sec_delay int=NULL, @num_iter int=NULL, @dont_format char(1)=NULL, @object_name varchar(30)=NULL) AS -------------------------------------------------------------------------------------------------- -- Vers| Date | Who | DA | Description -------+----------+--------------------+----+----------------------------------------------------- -- 1.1 |11/20/2013| Jason Froebe | | Fix formatting of outputs -- 1.0 | 2006 | Edward Barlow | | Shows Highest Usage Objects Based On Mda tables. -- | | | | This uses a heuristic algorithm to find what objects -- | | | | are most busy. -------+----------+--------------------+----+----------------------------------------------------- set nocount on declare @delay char(8) declare @objid int declare @max_objectname_size varchar(3) declare @exec_str varchar(2000) if @object_name is not null begin select @objid=ObjectID from master..monOpenObjectActivity where object_name(ObjectID,DBID)=@object_name if @objid is null return end if @num_sec_delay is null begin --Busy Object Report print "Top 20 Used Objects" print "" print " score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits" print "" set rowcount 20 select ObjName = db_name(DBID) + ".." + object_name(ObjectID, DBID), score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits into #busy_report from master..monOpenObjectActivity where IndexID=0 and LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + LockWaits >1000
and DBName !=”tempdb”
and ( @object_name is null or @object_name=object_name(ObjectID, DBID))
order by score desc

set rowcount 0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName), score
from #busy_report
order by score desc’

exec (@exec_str)

end
else
begin
select *
into #tmp
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

if @num_sec_delay<10
select @delay=”00:00:0″+convert(char(1),@num_sec_delay)
else
select @delay=”00:00:”+convert(char(2),@num_sec_delay)

create table #busy_report_iter_obj (
ObjName varchar(255) not null,
Op bigint not null,
LogReads int not null,
PhysReads int not null,
PageReads int not null,
Writes int not null,
Ins int not null,
Del int not null,
Upd int not null,
Locks int not null,
LockWt int not null)

create table #busy_report_iter (
ObjName varchar(255) not null,
Op int not null,
Reads int not null,
Writes int not null,
NumRows bigint not null)

if @num_iter is null
select @num_iter=100
while @num_iter>0
begin
waitfor delay @delay

if( @object_name is not null )
begin
insert into #busy_report_iter_obj
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op=o.Operations-i.Operations,
LogReads=o.LogicalReads – i.LogicalReads,
PhysReads=o.PhysicalReads- i.PhysicalReads,
PageReads=o.PagesRead- i.PagesRead,
Writes=o.PhysicalWrites-i.PhysicalWrites,
Ins=o.RowsInserted -i.RowsInserted,
Del= o.RowsDeleted – i.RowsDeleted,
Upd= o.RowsUpdated – i.RowsUpdated ,
Locks= o.LockRequests – i.LockRequests,
LockWt= o.LockWaits – i.LockWaits
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
LogReads,
PhysReads,
PageReads,
Writes,
Ins,
Del,
Upd,
Locks,
LockWt
from #busy_report_iter_obj’

exec (@exec_str)

delete #busy_report_iter_obj
end
else
begin
set rowcount 10

insert into #busy_report_iter
select distinct ObjName = db_name(o.DBID) + “..” + object_name(o.ObjectID,o.DBID),
Op = o.Operations-i.Operations,
Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
Writes = o.PhysicalWrites-i.PhysicalWrites,
NumRows = o.RowsInserted + o.RowsDeleted + o.RowsUpdated-i.RowsInserted – i.RowsDeleted – i.RowsUpdated
from master..monOpenObjectActivity o,#tmp i
where o.IndexID=i.IndexID
and o.ObjectID=i.ObjectID
and o.DBID=i.DBID
and o.IndexID=i.IndexID
and o.IndexID=0
and i.IndexID=0
order by
o.LogicalReads/100 +
o.PhysicalReads/10 +
o.PhysicalWrites +
o.RowsInserted +
o.RowsDeleted +
o.RowsUpdated +
o.LockRequests/100 +
o.LockWaits –
i.LogicalReads/100 –
i.PhysicalReads/10 –
i.PhysicalWrites –
i.RowsInserted –
i.RowsDeleted –
i.RowsUpdated –
i.LockRequests/100 –
i.LockWaits desc

select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter

select @exec_str = ‘select “Object Name” = convert(varchar(‘ + @max_objectname_size + ‘), ObjName),
Op,
Reads,
Writes,
NumRows
from #busy_report_iter’

exec (@exec_str)

delete #busy_report_iter
set rowcount 0

end

delete #tmp

insert #tmp
select *
from master..monOpenObjectActivity
where IndexID=0 and db_name(DBID)!=’tempdb’
and ( @object_name is null or ObjectID=@objid )

select @num_iter = @num_iter – 1
end
end

return

go

GRANT EXECUTE ON sp__monobj TO public
go[/sql]
sp__monwaits

Share Button

Ed Barlow Stored Procedures: sp__monrunning updated with formatting for SAP Sybase ASE 15 and higher

Mich Talebzadeh createdSAP Sybase the sp__monrunning stored procedure for displaying the procedures that are running for more than 100 ms as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output and added the dont_format option:

$ diff sp__monrunning.old sp__monrunning.15
9c9,10
< create procedure sp__monrunning
---
> create procedure sp__monrunning(
>                      @dont_format char(1)=NULL)
11,16c12,18
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Statistics on processes currently being executed
< --     |        |                    |    | with Elapsed time > 100ms
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
> --     |          |                    |    | with Elapsed time > 100ms
> -------+--  ------+--------------------+----+-----------------------------------------------------
18,22c20,24
< declare @time datetime
<       select @time = getdate()
<       --print ""
<       --print "Stats for various procedures at %1!. Server up since %2!", @time, @@boottime
<       --print ""
---
>     declare @max_loginname_size varchar(3)
>     declare @max_procname_size varchar(3)
>     declare @max_dbname_size varchar(3)
>     declare @exec_str varchar(2000)
>
35c37
< "Name" = substring(suser_name(p.suid),1,20),
---
>               "Name" = suser_name(p.suid),
37,39c39,42
< "Procedure" = ProcName,
<               "Database" = DBNAME,
<               "Elapsed Time/ms" = TimeMs
---
>               ProcName,
>               DBNAME,
>               TimeMs
>     into #proc_report
43c46,75
< order by TimeMs asc
---
>
>     select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
>         @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
>         @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
>     from #proc_report
>
>     if @dont_format is null
>     begin
>         select @exec_str = 'select
>             "Name" = convert(varchar(' + @max_loginname_size + '), Name),
>                   SPID,
>                   "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
>                   "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc'
>
>         exec (@exec_str)
>     end
>     else
>     begin
>         select
>             "Name" = Name,
>                   SPID,
>                   "Procedure" = ProcName,
>                   "Database" = DBNAME,
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc
>     end

Full SQL code of sp__monrunning:

use sybsystemprocs
go

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monrunning"
           AND    type = "P")
   DROP PROC sp__monrunning
go
create procedure sp__monrunning(
                     @dont_format char(1)=NULL)
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
-- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
--     |          |                    |    | with Elapsed time > 100ms
-------+--  ------+--------------------+----+-----------------------------------------------------
begin
    declare @max_loginname_size varchar(3)
    declare @max_procname_size varchar(3)
    declare @max_dbname_size varchar(3)
    declare @exec_str varchar(2000)

        select
                SPID,
                ProcName = isnull(object_name(ProcedureID, DBID),"UNKNOWN"),
                DBNAME = isnull(db_name(DBID), "UNKNOWN"),
                TimeMs = datediff(ms, min(StartTime), max(EndTime))
                into #performance
                from master..monSysStatement m
        where db_name(DBID) != 'sybsystemprocs'
        group by SPID, DBID, ProcedureID, BatchID
        having ProcedureID != 0

        select distinct
                "Name" = suser_name(p.suid),
                SPID,
                ProcName,
                DBNAME,
                TimeMs
    into #proc_report
        from #performance t, master..sysprocesses p
        where t.SPID = p.spid
        and TimeMs >= 100

    select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
        @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
        @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
    from #proc_report

    if @dont_format is null
    begin
        select @exec_str = 'select
            "Name" = convert(varchar(' + @max_loginname_size + '), Name),
                    SPID,
                    "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
                    "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc'

        exec (@exec_str)
    end
    else
    begin
        select
            "Name" = Name,
                    SPID,
                    "Procedure" = ProcName,
                    "Database" = DBNAME,
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc
    end
end
go
grant exec on sp__monrunning to public
go
exit

sp__monrunning

Share Button

Ed Barlow Stored Procedures: sp__monwaits updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monwaits stored procedure for displaying the wait times since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The sp__monwaits often truncated the event description making it rather difficult to determine exactly which event occurred. I’ve expanded the size the field to reflect the length of the longest event description dynamically.

 Event                                    WaitTime    Waits
 ---------------------------------------- ----------- -----------
 xact coord: pause during idle loop           4001410       66688
 wait for buffer read to complete              532248    85475068
 wait for buffer write to complete              32235    15923057
 wait for buffer validation to complete          3680      162024
 wait for mass to stop changing                 30267    10315571
 wait for mass to finish changing               10774   502746724
 wait to acquire latch                          55839     7807761
 waiting for disk write to complete            115108    40656343
 waiting for disk write to complete             60530    11697654
 waiting for disk write to complete             27566     9398819
 waiting for disk write to complete             85976    50951106
 checkpoint process idle loop                  951594       16619
 hk: pause for some time                      2713933      530621
 wait for flusher to queue full DFLPIECE       151633      150265
 wait for data from client                       1933        6000
 wait until an engine has been offlined       1000427       33342
 wait for someone else to finish reading       372601    55500541
 waiting for semaphore                         391902    13636318
 waiting for CTLIB event to complete            87265   112396697
 waiting while allocating new client sock      997833       97155
 waiting while no network read or write i    11936037  1037354467
 waiting on run queue after yield              295191    72098512
 waiting on run queue after sleep              746636 -1313156962
 replication agent sleeping in retry slee        3839          64
 replication agent sleeping during flush      1840806    12733523
 waiting for incoming network data          201256524   265587239
 waiting for network send to complete         1175318   747115161
 waiting until last chance threshold is c        1144           3
 waiting for date or time in waitfor comm       68630         210

to

 Event                                              WaitTime    Waits
 -------------------------------------------------- ----------- -----------
 waiting for incoming network data                    221594153    12452930
 waiting for client connection request                  2760845      207231
 hk: pause for some time                                2754002      443443
 xact coord: pause during idle loop                      920462       15341
 wait until an engine has been offlined                  920462       30682
 Wait until heartbeat or check interval expires          920399        1534
 checkpoint process idle loop                            919400       18224
 replication agent sleeping during flush                 777679       91871
 replication agent sleeping in retry sleep               138301        2305
 wait for flusher to queue full DFLPIECE                  31029       31055
 waiting for regular buffer read to complete              11461    37289445
 waiting for last i/o on MASS to complete                  8079     2026180
 waiting on run queue after yield                          4914     4974455
 waiting on run queue after sleep                          3652    71141496
 wait for mass read to finish when getting page            3341     5365397
 wait for i/o to finish after writing last log page        2757     3624230
 waiting for network send to complete                      1404       58634
 waiting for buf write to complete before writing          1334     1452867

Differences:

$ diff sp__monwaits.old sp__monwaits.15
1,13d0
< <
< /*
< select distinct SPID,Id=substring(Login+"("+Application+")",1,30),SecondsWaiting=sum(SecondsWaiting),"Non Network Wait Reason"=c.Description
< from master..monProcess p,master..monWaitClassInfo c, master..monWaitEventInfo i
< where p.WaitEventID = i.WaitEventID
< and   i.WaitClassID = c.WaitClassID
< and   c.Description!="waiting for input from the network"
< group by c.WaitClassID,SPID
< order by SPID
< */
<
<
22d8
<
32a19,21
> declare @max_eventstr_size varchar(4)
> declare @exec_str varchar(2000)
>
34c23,25
< select "Event"=substring(i.Description,1,40),WaitTime,Waits
---
> begin
>       select "Event" = i.Description, WaitTime, Waits
>     into #tmp_nodelay
37a29,42
>
>     if @dont_format is null
>     begin
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
>         select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
>         exec (@exec_str)
>     end
>     else
>     begin
>         select * from #tmp_nodelay order by WaitTime desc
>     end
>
>     delete #tmp_nodelay
> end
40c45
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>       select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
51a57
>
56,63c62
< select "Time"=convert(varchar(8),getdate(),8),
<                       "Event"=i.Event,
<                       WaitTime=s.WaitTime-i.WaitTime,
<                       Waits=s.Waits-i.Waits
<               from #tmp i, master..monSysWaits s
<               where (s.WaitTime>i.WaitTime or s.Waits>i.Waits)
< and s.WaitEventID= i.WaitEventID
<               order by WaitTime desc
---
>         select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp
64a64,73
>               select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
>                          "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
>                          WaitTime = s.WaitTime-i.WaitTime,
>                          Waits = s.Waits-i.Waits
>                  from #tmp i, master..monSysWaits s
>                  where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
>                  and s.WaitEventID= i.WaitEventID
>                  order by WaitTime desc'
>         exec (@exec_str)
>
68c77
< select "Event"=substring(i.Description,1,40),WaitTime,Waits,s.WaitEventID
---
>               select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
70c79
< where s.WaitEventID= i.WaitEventID
---
>               where s.WaitEventID = i.WaitEventID

Full SQL text of sp__monwaits:

use sybsystemprocs
go
/* Procedure library copyright(c) 2004 by Edward M Barlow */

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monwaits"
           AND    type = "P")
   DROP PROC sp__monwaits
go

CREATE PROC sp__monwaits(
                @num_sec_delay int=NULL,
                @num_iter int=NULL,
                @dont_format char(1)=NULL)
AS
set nocount on
declare @delay char(8)

declare @max_eventstr_size varchar(4)
declare @exec_str varchar(2000)

if @num_sec_delay is null
begin
        select "Event" = i.Description, WaitTime, Waits
    into #tmp_nodelay
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID
        and     WaitTime>1000

    if @dont_format is null
    begin
        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp_nodelay
        select @exec_str = 'select "Event" = convert(varchar(' + @max_eventstr_size + '), Event), WaitTime, Waits from #tmp_nodelay order by WaitTime desc'
        exec (@exec_str)
    end
    else
    begin
        select * from #tmp_nodelay order by WaitTime desc
    end

    delete #tmp_nodelay
end
else
begin
        select "Event" = i.Description, WaitTime, Waits, s.WaitEventID
        into #tmp
        from master..monWaitEventInfo i, master..monSysWaits s
        where s.WaitEventID= i.WaitEventID

        if @num_sec_delay&lt;10
                select @delay="00:00:0"+convert(char(1),@num_sec_delay)
        else
                select @delay="00:00:"+convert(char(2),@num_sec_delay)

        if @num_iter is null
                select @num_iter=100

        while @num_iter>0
        begin
                waitfor delay @delay

        select @max_eventstr_size = convert(varchar(3), isnull(max(char_length(Event)), 1)) from #tmp

                select @exec_str = 'select "Time" = convert(varchar(8),getdate(),8),
                           "Event" = convert(varchar(' + @max_eventstr_size + '), rtrim(i.Event)),
                           WaitTime = s.WaitTime-i.WaitTime,
                           Waits = s.Waits-i.Waits
                   from #tmp i, master..monSysWaits s
                   where (s.WaitTime > i.WaitTime or s.Waits > i.Waits)
                   and s.WaitEventID= i.WaitEventID
                   order by WaitTime desc'
        exec (@exec_str)

                delete #tmp

                insert #tmp
                select "Event"=i.Description, WaitTime, Waits, s.WaitEventID
                from master..monWaitEventInfo i, master..monSysWaits s
                where s.WaitEventID = i.WaitEventID

                select @num_iter = @num_iter - 1
        end
end

return

go

GRANT EXECUTE ON sp__monwaits  TO public
go

sp__monwaits

Share Button

Ed Barlow Stored Procedures: sp__monunusedindex updated with formatting, including owner and all indexes for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monunusedindex stored procedure for displaying the indexes that have not been used since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve updated the stored procedure to include the table owner and all the indexes & index keys. I’ve also fixed the formatting of the data.

$ diff sp__monunusedindex.old sp__monunusedindex.15
21a22,27
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Show full index name and full index keys
> -- 1.0 |  2006    |  Edward Barlow     |    | show indexes that have not been used since server start
> -------+----------+--------------------+----+-----------------------------------------------------
23c29,34
< select Dbname=db_name(DBID),Object=object_name(ObjectID,DBID),IndexID,IndexName=i.name, ObjectID
---
>
> declare @max_ownername_size varchar(3)
> declare @max_keylist_size varchar(4)
> declare @exec_str varchar(2000)
>
> select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
27,39c38,45
< and   a.ObjectID=o.id  and DBID=db_id()
< and   o.id = i.id and a.IndexID=i.indid
< and   o.type='U'
< and       (LastOptSelectDate is null and OptSelectCount=0)
< -- and   RowsUpdated=0
< -- and   RowsDeleted=0
< -- and   PhysicalReads=0
< -- and   PhysicalWrites=0
< -- and   PagesRead=0
< and   object_name(ObjectID,DBID) not like 'sys%'
< and   object_name(ObjectID,DBID) not like 'rs_%'
< and    object_name(ObjectID,DBID) not like 'spt_%'
< and   IndexID!=0
---
>    and   a.ObjectID=o.id  and DBID=db_id()
>    and   o.id = i.id and a.IndexID=i.indid
>    and   o.type='U'
>    and       (LastOptSelectDate is null and OptSelectCount=0)
>    and   object_name(ObjectID,DBID) not like 'sys%'
>    and   object_name(ObjectID,DBID) not like 'rs_%'
>    and    object_name(ObjectID,DBID) not like 'spt_%'
>    and        IndexID!=0
42,45c48,51
< owner      char(30) not null,
<    uid        smallint not null,
<    name       char(30) not null,
<    index_name char(30) not null,
---
>    owner      sysname not null,
>    uid        int not null,
>    name       longsysname not null,
>    index_name longsysname not null,
53c59
< keylist    char(127) null,
---
>    keylist    varchar(2000) null,
58,73c64,79
< insert into   #indexlist
<    select owner      = user_name(o.uid),
<           o.uid,
<           name       = o.name,
<           index_name = i.name,
<           id = i.id,
<           indexid    = i.indid,
<           clust      = convert(char(1),null),
<           allow_dup  = convert(char(1),null),
<           ign_dup_key  = convert(char(1),null),
<           uniq       = convert(char(1),null),
<           suspect    = convert(char(1),null),
<           keylist    = convert(char(127),"N.A."),
<           status      = status, status2=i.status2
<    from   sysobjects o, sysindexes i, #tmp t
<    where  i.id   = o.id
---
> insert into   #indexlist
> select owner      = user_name(o.uid),
>    o.uid,
>    name       = o.name,
>    index_name = i.name,
>    id = i.id,
>    indexid    = i.indid,
>    clust      = convert(char(1),null),
>    allow_dup  = convert(char(1),null),
>    ign_dup_key  = convert(char(1),null),
>    uniq       = convert(char(1),null),
>    suspect    = convert(char(1),null),
>    keylist    = "N.A.",
>    status      = status, status2=i.status2
> from   sysobjects o, sysindexes i, #tmp t
> where  i.id   = o.id
75d80
< --and    o.type in ("U",@show_type)
106c111
< or     status2&8192= 8192
---
>    or status2 & 8192= 8192
111c116
< while ( @count < 17 )   /* 16 appears to be the max number of indexes */
---
> while ( @count < 250 )   /* max number of indexes = 250 */
113d117
<
120c124
<       set    keylist=rtrim(keylist)+","+index_col(name,indexid,@count,uid)
---
>       set    keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid))
123c127,128
< if @@rowcount=0   break
---
>    if @@rowcount=0
>       break
132,138c137,143
< if @no_print is null
<         begin
<         print "   INDEX KEY:     c = clustered            u = unique"
<         print "                  a = allow dup row        s = suspect"
<         print "                  i = ignore dup key "
<         print ""
<               end
---
> if @no_print is null
> begin
>    print "   INDEX KEY:     c = clustered            u = unique"
>    print "                  a = allow dup row        s = suspect"
>    print "                  i = ignore dup key "
>    print ""
> end
140,146c145,173
< select "Name" = rtrim(name)+"."+index_name,
<        c   = isnull(clust,""),
<        u   = isnull(uniq,""),
<        i   = isnull(ign_dup_key,""),
<        a   = isnull(allow_dup,""),
<        s   = isnull(suspect,""),
<        "List of Index Keys"    = keylist
---
> if @dont_format is null
> begin
>    select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) )
>       from #indexlist
>
>    select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) )
>       from #indexlist
>
>    select @exec_str =
>       'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name),
>          c   = isnull(clust,""),
>          u   = isnull(uniq,""),
>          i   = isnull(ign_dup_key,""),
>          a   = isnull(allow_dup,""),
>          s   = isnull(suspect,""),
>          "List of Index Keys"    = convert(varchar(' + @max_keylist_size + '), keylist)
>       from #indexlist
>       order by owner,name,indexid'
>    exec (@exec_str)
> end
> else
> begin
>    select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name,
>       c   = isnull(clust,""),
>       u   = isnull(uniq,""),
>       i   = isnull(ign_dup_key,""),
>       a   = isnull(allow_dup,""),
>       s   = isnull(suspect,""),
>       "List of Index Keys"    = keylist
148a176
> end

full code of sp__monunusedindex:

use sybsystemprocs
go
/* Procedure copyright(c) 2006 by Edward M Barlow */

/******************************************************************************
**
** Name : sp__monunusedindex
**
** Created By : Ed Barlow
**
******************************************************************************/

IF EXISTS (SELECT * FROM sysobjects
WHERE name = “sp__monunusedindex”
AND type = “P”)
DROP PROC sp__monunusedindex

go

create proc sp__monunusedindex( @dont_format char(1) = null, @no_print char(1) = null)
as
————————————————————————————————–
— Vers| Date | Who | DA | Description
——-+———-+——————–+—-+—————————————————–
— 1.1 |11/20/2013| Jason Froebe | | Show full index name and full index keys
— 1.0 | 2006 | Edward Barlow | | show indexes that have not been used since server start
——-+———-+——————–+—-+—————————————————–
begin

declare @max_ownername_size varchar(3)
declare @max_keylist_size varchar(4)
declare @exec_str varchar(2000)

select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
into #tmp
from master..monOpenObjectActivity a, sysobjects o, sysindexes i
where RowsInserted=0
and a.ObjectID=o.id and DBID=db_id()
and o.id = i.id and a.IndexID=i.indid
and o.type=’U’
and (LastOptSelectDate is null and OptSelectCount=0)
and object_name(ObjectID,DBID) not like ‘sys%’
and object_name(ObjectID,DBID) not like ‘rs_%’
and object_name(ObjectID,DBID) not like ‘spt_%’
and IndexID!=0

create table #indexlist (
owner sysname not null,
uid int not null,
name longsysname not null,
index_name longsysname not null,
id int not null,
indexid smallint not null,
clust char(1) null,
allow_dup char(1) null,
ign_dup_key char(1) null,
uniq char(1) null,
suspect char(1) null,
keylist varchar(2000) null,
status smallint not null,
status2 smallint not null
)

insert into #indexlist
select owner = user_name(o.uid),
o.uid,
name = o.name,
index_name = i.name,
id = i.id,
indexid = i.indid,
clust = convert(char(1),null),
allow_dup = convert(char(1),null),
ign_dup_key = convert(char(1),null),
uniq = convert(char(1),null),
suspect = convert(char(1),null),
keylist = “N.A.”,
status = status, status2=i.status2
from sysobjects o, sysindexes i, #tmp t
where i.id = o.id
and i.id = ObjectID and i.indid=IndexID
and indid > 0

/* delete multiple rows */
delete #indexlist
from #indexlist a, #indexlist b
where a.indexid = 0
and b.indexid != 0
and a.name = b.name

update #indexlist
set clust=’Y’
where indexid = 1
or status&16=16
or status2&512 = 512

update #indexlist
set uniq = ‘Y’
where status & 2 = 2

update #indexlist
set ign_dup_key = ‘Y’
where status & 1 = 1

update #indexlist
set allow_dup = ‘Y’
where status & 64 = 64

update #indexlist
set suspect = ‘Y’
where status & 32768 = 32768
or status2 & 8192= 8192

declare @count int
select @count=1

while ( @count < 250 ) /* max number of indexes = 250 */ begin if @count=1 update #indexlist set keylist=index_col(name,indexid,1,uid) where index_col(name,indexid,@count,uid) is not null else update #indexlist set keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid)) where index_col(name,indexid,@count,uid) is not null if @@rowcount=0 break select @count=@count+1 end update #indexlist set name=convert(char(30), rtrim(rtrim(substring(owner,1,15)) + "." +name)) where owner!="dbo" if @no_print is null begin print " INDEX KEY: c = clustered u = unique" print " a = allow dup row s = suspect" print " i = ignore dup key " print "" end if @dont_format is null begin select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) ) from #indexlist select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) ) from #indexlist select @exec_str = 'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name), c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = convert(varchar(' + @max_keylist_size + '), keylist) from #indexlist order by owner,name,indexid' exec (@exec_str) end else begin select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name, c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = keylist from #indexlist order by owner,name,indexid end end go[/sql] sp__monunusedindex

Share Button

Ed Barlow Procedures: sp__groupprotect updated to show the group name properly for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__groupprotect stored procedure for reporting group permissions in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The problem is that the group name is truncated often making it difficult to determine exactly which group a particular line applies to:

 type grp             tot    sel    upd    del    ins    rev    exe
 ---- --------------- ------ ------ ------ ------ ------ ------ ------
 D    application_gro 253    0      0      0      0      0      0
 D    dtm_tm_role     253    0      0      0      0      0      0
 D    ha_role         253    0      0      0      0      0      0
 D    webservices_rol 253    0      0      0      0      0      0

I’ve updated the stored procedure to show the entire group name:

 type grp                tot    sel    upd    del    ins    rev    exe
 ---- ------------------ ------ ------ ------ ------ ------ ------ ------
 D    application_group  253    0      0      0      0      0      0
 D    dtm_tm_role        253    0      0      0      0      0      0
 D    ha_role            253    0      0      0      0      0      0
 D    webservices_role   253    0      0      0      0      0      0
$ diff groupprotect.sql groupprotect.15
23c23
< create procedure sp__groupprotect( @dont_format char(1) = NULL )
---
> create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
26a27,36
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
> -------+----------+--------------------+----+-----------------------------------------------------
>
> declare @max_name_size varchar(3)
> declare @exec_str varchar(2000)
>
110,112c120,125
< select type,grp=convert(char(15),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
<         from #objects
<         order by type,group_name
---
>         select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
>         select @exec_str =
>             'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
>             from #objects
>             order by type,group_name'
>         exec (@exec_str)

Here’s the full stored procedure code:

/* Procedure copyright(c) 1995 by Edward M Barlow */

/******************************************************************************
**
** Name        : sp__groupprotect.sql
**
**   permissions by object type / user group vs sel/upd/ins/del
**
******************************************************************************/
:r database
go
:r dumpdb
go

if exists (select * from sysobjects
           where  name = "sp__groupprotect"
           and    type = "P")
begin
   drop proc sp__groupprotect
end
go

create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
as
set nocount on

--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
-------+----------+--------------------+----+-----------------------------------------------------

declare @max_name_size varchar(3)
declare @exec_str varchar(2000)

select distinct type,uid=sysusers.uid,group_name=sysusers.name,total=0,s=0,u=0,d=0,i=0,r=0,e=0
into   #objects
from   sysusers,   sysobjects
where  sysusers.uid=sysusers.gid
and    sysobjects.uid=1
and    ( sysusers.uid>=16390 or sysusers.uid < 16000 )

select distinct action,id,uid,protecttype,type="  "
into  #p
from  sysprotects p

update  #p
set     type=o.type
from    sysobjects o
where   o.id=#p.id
and     o.uid=1

update #objects set total=(select count(*) from sysobjects o
                                where o.type=n.type
                                and    o.uid=1)
from #objects n

update  #objects
set     s=(select count(*)
                from    #p
                where   #p.action=193
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     u=(select count(*)
                from    #p
                where   #p.action=197
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     d=(select count(*)
                from    #p
                where   #p.action=196
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     i=(select count(*)
                from    #p
                where   #p.action=195
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     r=(select count(*)
                from    #p
                where   #p.protecttype=2
                and     #p.uid = #objects.uid
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     e=(select count(*)
                from    #p
                where   #p.action=224
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

if @dont_format is null
begin
        select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
        select @exec_str =
            'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
            from #objects
            order by type,group_name'
        exec (@exec_str)
end
else
begin
        select type,grp=group_name,tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
        from #objects
        order by type,group_name
end

return (0)
go
grant execute on sp__groupprotect to public
go

groupprotect

Share Button