SAP Sybase IQ: Index Advisor for a user

There are times when you need individual users the ability to determine what indexes are suggested by the index advisor but you don’t want or are unable to give them full dba access. All you need to do is grant the execute permission on the sp_iqindexadvice stored procedure.

Grant execute on sp_iqindexadvice to user;

create table test (col1 int, col2 varchar(10));
insert into test values (1, 'test1');
insert into test values (2, 'test2');
insert into test values (3, 'test3');
insert into test values (4, 'test4');
commit;

SET OPTION index_advisor = 'ON';
SET OPTION index_advisor_max_rows = 100;
commit;

select * from test where col1 =1 ;
commit;

call sp_iqindexadvice ();
Share Button

SAP Sybase IQ: How to Restore Your Backups to Another system

SAP/Sybase’s documentation isn’t very clear for new IQ dbas and developers. Once such item is sdbply restoring an IQ database on to another system. Unlike ASE, you need to specify the new file locations if they are different than the source server.

Assumptions:

  1. IQ software has been installed
  2. The new dbfile locations are symbolic links to raw partitions OR the path exists but not the files
  3. You have a valid SYSAM license for the new IQ instance.
  4. The new IQ instance name is set (via -n instance)
  5. The old directory for the .db, .log and .mir exists (use a symbolic link if you wish)

Obtain dbspace file names with sp_iqfile:

select DBFileName, Path, DBFileSize from sp_iqfile();
DBFileName	Path	DBFileSize
'IQ_SYSTEM_MAIN'	'/database/old_iq/devices/IQ_MAIN/old_iqmain001.iq'	'32G'
'IQ_USER_MAIN_FILE_01'	'/database/old_iq/devices/IQ_USER_MAIN/old_iqusermain001.iq'	'1024G'
'IQ_SYSTEM_TEMP'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp001.iqtmp'	'32G'
'IQ_SYSTEM_TEMP_002'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp002.iqtmp'	'32G'</code></pre>

Create a restore.sql file renaming the DBFileName to the new locations:
<pre class="prettyprint" ><code class="language-sql">restore database 'new_iq'
FROM '/backups/old_iq.20140423100111.17760.IQfullbkp'
RENAME IQ_SYSTEM_MAIN TO '/database/new_iq/devices/IQ_MAIN/new_iqmain001.iq'
RENAME IQ_SYSTEM_TEMP TO '/database/new_iq/devices/IQ_TEMP/new_iqtemp001.iq'
RENAME IQ_SYSTEM_TEMP_002 TO '/backups/new_iqtemp002.iq'
RENAME IQ_SYSTEM_MSG TO '/database/new_iq/IQLOG/new_iq.iqmsg'
RENAME IQ_USER_MAIN_FILE_01 TO '/database/new_iq/devices/IQ_USER_MAIN/new_iqusermain001.iq';

Stop the destination IQ instance if it is running and start the utility database:

stop_iq
Checking system ...

The following 1 server(s) are owned by 'sybdba'

## Owner          PID   Started  CPU Tdbe  Additional Information
-- ---------  -------  --------  --------  ------------------------------------
1: sybdba       13909     Apr24  00:43:46  SVR:new_iq DB:new_iq PORT:58116
              /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16 @/database/new_iq/sybdb/new_iq.cfg /database/new_iq/sybdb/new_iq.db -gn 65 -o /database/new_iq/sybase/IQ-16_0/logfiles
${SYBASE}/IQ-16_0/bin64/start_iq -n utility_db -gu dba -c 48m -gc 20 -iqgovern 30 \
        -gd all -gl all -gm 10 -gp 4096 -ti 4400 -z -zr all -zo $SYBASE/IQ-16_0/logfiles/utility_db.out \
        -o $SYBASE/IQ-16_0/logfiles/utility_db.srvlog -iqmc 100 -iqtc 100 -x "tcpip{port=9000}"
Starting server utility_db on localhost at port 9000 (04/30 09:37:16)

Run Directory       : /database/new_iq/sybdb
Server Executable   : /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16
Server Output Log   : /database/new_iq/IQLOG/utility_db.srvlog
Server Version      : 16.0.0.653/sp03 16.0.0/Linux 2.6.18-194.el5
Open Client Version : N/A
User Parameters     : '-n' 'utility_db' '-gu' 'dba' '-c' '48m' '-gc' '20' '-iqgovern' '30' '-gd' 'all' '-gl' 'all' '-gm' '10' '-gp' '4096' '-ti' '4400' '-z' '-zr' 'all' '-zo' '/database/new_iq/IQLOG/utility_db.out' '-o' '/database/new_iq/IQLOG/utility_db.srvlog' '-iqmc' '100' '-iqtc' '100' '-x' 'tcpip{port=9000}'
Default Parameters  : -gn 25
….

Remove the db, log and mir files:

rm instance.db instance.log instance.mir

Restore the full backup:

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore.sql"

Restore the incremental backup(s):

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore_incrementals.sql"

Stop the utility database:

stop_iq

Start the IQ server to ensure it comes up then shut it back down.

If the name of the server has changed (e.g. old_iq -> new_iq), then we need to update the log and mir files. First let’s find out where the log and mir files are currently set to in the db file:

dblog new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397583

Set the log file to “new_iq.log”:

dblog -t new_iq.log new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" was using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
"new_iq.db" is now using log file "new_iq.log"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

We need to clear the mir file(s) before we can assign a new one:

dblog -r new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using log mirror file "/database/old_iq/sybdb/db00.mir"
"new_iq.db" is now using no log mirror file
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Set the mir file:

dblog -m new_iq.mir new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using no log mirror file
"new_iq.db" is now using log mirror file "new_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Start your IQ instance.

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

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

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__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

Ed Barlow stored procedures: sp__indexspace updated for large tables on SAP Sybase 15x

Ed Barlow createdSAP Sybase the sp__indexspace stored procedure for reporting on index usage in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures). If you have large tables, you will receive an arithmetic overflow error. I’ve updated the stored procedure to correct the error and to format the name of the tables and indexes.

$ diff indexspace.15.old indexspace.15
25a26,33
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Fix Arithmetic overflow error by using a bigint
> --     |          |                    |    | instead of integers.  Fix formatting to show entire
> --     |          |                    |    | table.index name
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure giving index usage
> -------+----------+--------------------+----+-----------------------------------------------------
28a37,38
> declare @max_name_size varchar(3)
> declare @exec_str varchar(2000)
64a75,77
> select @max_name_size = convert(varchar(3), max(char_length(name))) from #indexspace
> select @max_name_size
>
66,76c79,91
< select
<    convert(char(22),name)                       "Name",
<    convert(char(8),row_cnt)                                                "Rows",
<    convert(char(16),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
<    str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
<    convert(char(12),segname) "Segment"
< from #indexspace
< where indid<=1
< order by name
---
> select @exec_str =
>     'select
>         convert(char(' + @max_name_size + '),name) "Name",
>         convert(char(20),row_cnt) "Rows",
>         convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
>         str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
>         convert(char(12),segname) "Segment"
>     from #indexspace
>     where indid< =1
>     order by name'
> exec (@exec_str)
80,85c95,101
< select
<         convert(char(22),name)           "Name",
<    convert(char(8),row_cnt)                                                "Rows",
<    convert(char(16),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
<    rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
---
> select @exec_str =
>     'select
>         convert(char(' + @max_name_size + '),name) "Name",
>         convert(char(20),row_cnt) "Rows",
>         convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
>         rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
88,90c104,107
< from #indexspace
< where indid>1
< order by name
---
>     from #indexspace
>     where indid>1
>     order by name'
> exec (@exec_str)
/* Procedure copyright(c) 1995 by Edward M Barlow */

/******************************************************************************
**
** Name        : sp__indexspace
**
** Created By  : Ed Barlow
**
******************************************************************************/
:r database
go
:r dumpdb
go

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

CREATE PROC sp__indexspace(
                @objname        varchar(92) = NULL ,
                                        @dont_format char(1) = null
                                                 )
AS
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Fix Arithmetic overflow error by using a bigint
--     |          |                    |    | instead of integers.  Fix formatting to show entire
--     |          |                    |    | table name + index name
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure giving index usage
-------+----------+--------------------+----+-----------------------------------------------------
BEGIN

declare @pagesize int                   /* Bytes Per Page */
declare @max_name_size varchar(3)
declare @exec_str varchar(2000)

set nocount on

select  @pagesize = low
from    master..spt_values
where   number = 1
and     type = "E"

select name = o.name,
       idxname = i.name,
       owner_id = o.uid,
       row_cnt  = row_count(db_id(), i.id),
       reserved = reserved_pages(db_id(), i.id, i.indid),
       data     = data_pages(db_id(), i.id, i.indid),
       index_size = data_pages(db_id(), i.id, i.indid),
       segname = s.name,
       indid
into   #indexspace
from   sysobjects o, sysindexes i, syssegments s
where  i.id = o.id
and    (o.type = "U" or o.name = "syslogs")
and    s.segment = i.segment
and    isnull(@objname,o.name)=o.name

update #indexspace
set    name=user_name(owner_id)+'.'+name
where  owner_id>1

update #indexspace
set    name=name+'.'+idxname
where  indid!=0

update #indexspace
set    row_cnt=-1
where  row_cnt>99999999

select @max_name_size = convert(varchar(3), max(char_length(name))) from #indexspace
select @max_name_size

print "Data Level (Index Type 0 or 1)"
select @exec_str =
    'select
        convert(char(' + @max_name_size + '),name) "Name",
        convert(char(20),row_cnt) "Rows",
        convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
        str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
        convert(char(12),segname) "Segment"
    from #indexspace
    where indid< =1
    order by name'
exec (@exec_str)

print ""
print "Non Clustered Indexes"
select @exec_str =
    'select
        convert(char(' + @max_name_size + '),name) "Name",
        convert(char(20),row_cnt) "Rows",
        convert(char(30),rtrim(convert(char(30),(reserved*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(data*@pagesize)/1024))+"/"+
        rtrim(convert(char(30),(index_size*@pagesize)/1024))) "Used/Data/Idx KB",
        str((row_cnt*1024)/(convert(float,data+index_size)*@pagesize),6,2) "Rows/KB",
        convert(char(12),segname) "Segment"
    from #indexspace
    where indid>1
    order by name'
exec (@exec_str)

drop table #indexspace

return(0)

END

go

GRANT EXECUTE ON sp__indexspace TO public
go

indexspace

Share Button

Ed Barlow’s sp__helprotect updated for SAP Sybase ASE 15.7 Solved

I needed to copy the permissions from one database to another but the sp__helprotect stored procedure in Ed Barlow’s Extended System Stored Procedure Library for Sybase and Sql Server wasn’t producing output with “GRANT” and “REVOKE”. It also had problems with DBCC privileges. Here you go:

sp__helprotect.sql

/* Procedure copyright(c) 1995 by Edward M Barlow */
/* Updated for v15.7 by Jason Froebe */

/******************************************************************************
**
** Name : sp__helpprotect.sql
**
******************************************************************************/
if exists (select * from sysobjects
where name = “sp__helprotect”
and type = “P”)
begin
drop proc sp__helprotect
end
go

create procedure sp__helprotect
@parameter varchar(30) = NULL /* name of object or user to check */,
@do_system_tables char(1) = null, /* if not null will include system tbls */
@dont_format char(1) = null,
@groups_only char(1) = null
as
declare @type char(2), @uid int, @msg varchar(255), @objid int

if @parameter is NULL
select @objid=null
else
select @objid = object_id(@parameter)

/* define our table */
select id,uid,action,protecttype,columns,grantor,
column_name = ” ”
,action_text = ” ”
,protecttype_text = ” ”
,ending = ” ”
into #protects
from sysprotects
where 1=2

/* Either a passed object or all objects */
if @objid is not null or @parameter is null
begin

select uid,gid into #groups from sysusers

if @groups_only is not null
delete #groups
where uid != gid

/* IT IS AN OBJECT */
insert #protects
select id,p.uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p, #groups g
where id=isnull(@objid,id)
and p.uid = g.uid

/* REVOKES ON COLUMNS */
insert #protects
select id,p.uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”,””,””,””
from sysprotects p, master.dbo.spt_values c, #groups g
where p.columns is not null
and convert(tinyint,substring(p.columns,c.low,1)) & c.high=0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and id=isnull(@objid,id)
and p.uid=g.uid

if @do_system_tables is null and @objid is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end
else
begin

/* IS IT A USER */
select @uid = uid from sysusers where name=@parameter
if @@rowcount = 0 or @uid is null
begin
print “No User Or Object Found”
return (1)
end

insert #protects
select distinct id,uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p
where uid=@uid
/* and isnull( p.columns,0x01 ) = 0x01 */

/* REVOKES ON COLUMNS */
insert #protects
select id,uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”, “”,””,””
from sysprotects p, master.dbo.spt_values c
where isnull( p.columns,0x01 ) != 0x01
and convert(tinyint, substring(p.columns, c.low, 1)) & c.high = 0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and uid=@uid

if @do_system_tables is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end

/* References etc */
delete #protects
where action in(151,207,222,233,236)

update #protects
set action_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.action

update #protects
set protecttype_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.protecttype +204

— protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
update #protects
set protecttype_text =
case
when protecttype = 0
then “GRANT”
when protecttype = 1
then “GRANT”
when protecttype = 2
then “REVOKE”
end

update #protects
set ending = ” WITH GRANT OPTION”
where protecttype = 0

declare @max_len int
select @max_len = max(char_length( rtrim(protecttype_text)+” “+rtrim(action_text)+” on “+rtrim(object_name(id))+column_name+” to “+rtrim(user_name(uid))+ending))
from #protects

if @max_len < 60 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,59) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 80 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,79) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 132 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,131) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text return (0) go grant execute on sp__helprotect to public go[/sql]

Share Button