Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
Message-ID: <databases/sybase-faq/
[email protected]>
Supersedes: <databases/sybase-faq/
[email protected]>
Expires: 2 Aug 2004 13:43:10 GMT
References: <databases/sybase-faq/
[email protected]>
X-Last-Updated: 2003/03/02
From:
[email protected] (David Owen)
Newsgroups: comp.databases.sybase,comp.answers,news.answers
Subject: Sybase FAQ: 16/19 - ASE Section 9 (1 of 3)
Reply-To:
[email protected] (David Owen)
Followup-To: comp.databases.sybase
Distribution: world
Organization: Midsomer Consultants Inc.
Approved:
[email protected]
Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
Originator:
[email protected]
Date: 20 Apr 2004 13:45:14 GMT
Lines: 507
NNTP-Posting-Host: penguin-lust.mit.edu
X-Trace: 1082468714 senator-bedfellow.mit.edu 579 18.181.0.29
Xref: senator-bedfellow.mit.edu comp.databases.sybase:106214 comp.answers:56960 news.answers:270300
Archive-name: databases/sybase-faq/part16
URL:
http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.
Freeware
Sybase Tech Docs Open Client ASE FAQ
The best place to search for Sybase freeware is Ed Barlow (
[email protected])'s
site (
http://www.edbarlow.com). He is likely to spend more time maintaining
his list than I will spend on this. I will do my best!
9.0 Where is all the code and why does Section 9 suddenly load in a
reasonable amount of time?
Stored Procedures
9.1.1 sp_freedevice - lists device, size, used and free.
9.1.2 sp_dos - This procedure graphically displays the scope of a
object
9.1.3 sp_whodo - augments sp_who by including additional columns: cpu,
I/O...
9.1.4 sp__revroles - creates DDL to sp_role a mirror of your SQL
Server
9.1.5 sp__rev_configure - creates DDL to sp_configure a mirror of your
SQL Server
9.1.6 sp_servermap - overview of your SQL Server
9.1.7 sp__create_crosstab - simplify crosstable queries
9.1.8 sp_ddl_create_table - creates DDL for all user tables in the
current database
9.1.9 sp_spaceused_table
9.1.10 SQL to determine the space used for an index.
9.1.11 sp_helpoptions - Shows what options are set for a database.
9.1.12 sp_days - returns days in current month.
9.1.13 sp__optdiag - optdiag from within isql
9.1.14 sp_desc - a simple list of a tables' columns
9.1.15 sp_lockconfig - Displays locking schemes for tables.
Shell Scripts
9.2.1 SQL and sh(1)to dynamically generate a dump/load database
command.
9.2.2 update statistics script
Perl/Sybperl
9.3.1 SybPerl - Perl interface to Sybase.
9.3.2 dbschema.pl - Sybperl script to reverse engineer a database.
9.3.3 ddl_insert.pl - creates insert DDL for a table.
9.3.4 int.pl - converts interfaces file to tli
9.3.5 Sybase::Xfer.pm - Module to transfer data between two servers.
9.3.6 sybmon.pl - realtime process and lock monitor
9.3.7 showserver.pl - shows the servers on a particular machine in a
nice format.
9.3.8 Collection of Perl Scripts
Sybtcl
9.4.1 Sybtcl - TCL interface to Sybase.
9.4.2 sybdump - a Tcl script for dumping a database schema to disk
9.4.3 wisql - graphical sql editor and more
Python
9.5.1 Sybase Module for Python.
Tools, Utilities and Packages
9.6.1 sqsh - a superset of dsql with local variables, redirection,
pipes and all sorts of goodies.
9.6.2 lightweight Sybase Access via Win95/NT
9.6.3 BCPTool - a utility for trasferring data from ASE to another
(inc. native port to Linux).
'Free' Versions of ASE
The next couple of questions will move to the OS section (real) soon.
9.7.1 How to access a SQL Server using Linux see also Q11.4.6
9.7.2 Sybase on Linux Linux Penguin
9.7.3 How to configure shared-memory for Linux
9.7.4 Sybase now available on Free BSD
Other Sites of Interest
9.8.1 Ed Barlow's collection of Stored Procedures.
9.8.2 Examples of Open Client and Open Server programs -- see Q11.4.14
.
9.8.3 xsybmon - an X interface to sp_monitor
Sybase Tech Docs Open Client ASE FAQ
-------------------------------------------------------------------------------
9.0: Where is all the code and why does Section 9 suddenly load in a reasonable
amount of time?
-------------------------------------------------------------------------------
This section was in need of a spring clean, and it has now had it. I have
tested all of the stored procs included here against all versions of Sybase
that I have to hand. (11.0.3.3, 11.9.2 and 12.5 on Linux, 11.9.2 and 12 on
Solaris and 11.9.2 and 12 on NT.) If Pablo or the supplier documented that he
had tested it on other versions, then I have included those comments. Just
remember that I did not test them on anything pre-11.0.3.3. If you are still
using them on a pre-11.0.3.3 release (I know of at least one place that is
still running 4.9.2!) then let me know and I will add a suitable comment.
I have actually taken the code away and built a set of packages. First and
foremost is the stored proc package, then there is a shell script package, a
perl package and finally there is the archive package, which contains any stuff
specific to non-current releases of ASE.
In addition to wrenching out the code I have added some samples of the output
generated by the scripts. It occurred to me that people will be better able to
see if the stored proc does what they want if they can see what it produces.
Finally, part of the reason that this is here is so that people can examine the
code and see how other people write stored procs etc. Each stored proc is in a
file of its own so that you can choose which ones you wish to browse on-line
and then cut and paste them without having to go through the hassle of
un-htmling them.
Back to top
9.1.1: sp_freedevice
-------------------------------------------------------------------------------
This script displays the size of the devices configured for a server, together
with the free and used allocations.
Get it as part of the bundle (zip or tarball) or individually from here.
Output:
[30] BISCAY.master.1> sp_freedevice
[30] BISCAY.master.2>> go
total used free
--------------------- --------------------- ---------------------
950.00 MB 750.00 MB 200.00 MB
(1 row affected)
devname size used free
------------------------------ --------------------- --------------------- ---------------------
db01 100.00 MB 72.00 MB 28.00 MB
db02 100.00 MB 0.00 MB 100.00 MB
log01 100.00 MB 51.00 MB 49.00 MB
master 50.00 MB 27.00 MB 23.00 MB
sysprocsdev 200.00 MB 200.00 MB 0.00 MB
tlg01 200.00 MB 200.00 MB 0.00 MB
tmp01 200.00 MB 200.00 MB 0.00 MB
(7 rows affected, return status = 0)
[31] BISCAY.master.1>
Back to top
-------------------------------------------------------------------------------
9.1.2: sp_dos
-------------------------------------------------------------------------------
sp_dos displays the scope of an object within a database. What tables it
references, what other procedures it calls etc. Very useful for trying to
understand an application that you have just inherited.
Get it as part of the bundle (zip or tarball) or individually from here.
The output looks like this:
1> sp_dos sp_helpkey
2> go
** Utility by David Pledger, Strategic Data Systems, Inc. **
** PO Box 498, Springboro, OH 45066 **
SCOPE OF EFFECT FOR OBJECT: sp_helpkey
+------------------------------------------------------------------+
(P) sp_helpkey
|
+--(S) sysobjects
|
+--(S) syskeys
|
+--(P) sp_getmessage
|
+--(S) sysusermessages
|
+--(P) sp_validlang
(return status = 0)
1>
Back to top
-------------------------------------------------------------------------------
9.1.3: sp_whodo
-------------------------------------------------------------------------------
Sybase System 10.x and above
sp_whodo is an enhanced version of sp_who, with cpu and io usage for each user.
Note that this proc is now a little out of date since Sybase introduced the fid
column, so subordinate threads are unlikely to be grouped with their parent.
Get it as part of the bundle (zip or tarball) or individually from here.
Output:
1> sp_whodo
2> go
spid status loginame hostname blk blk_sec program
dbname cmd cpu io tran_name
------ ------------ ------------ ---------- --- ------- ----------------
------- ---------------- ------ ------- ----------------
2 sleeping NULL 0 0
master NETWORK HANDLER 0 0
4 sleeping NULL 0 0
master DEADLOCK TUNE 0 0
5 sleeping NULL 0 0
master MIRROR HANDLER 0 0
6 sleeping NULL 0 0 <astc>
master ASTC HANDLER 0 0
7 sleeping NULL 0 0
master CHECKPOINT SLEEP 0 128
8 sleeping NULL 0 0
master HOUSEKEEPER 0 33
17 running sa n-utsire.m 0 0 ctisql
master SELECT 0 1
(7 rows affected)
Back to top
-------------------------------------------------------------------------------
9.1.4: sp__revroles
-------------------------------------------------------------------------------
Well, I cannot get this one to do what it is supposed to, I am not sure if it
is just that it was written for a different release of Sybase and 11.9.2 and
above has changed the way that roles are built, or what. Anyway, I may work on
it some more.
Get it as part of the bundle (zip or tarball) or individually from here.
Back to top
-------------------------------------------------------------------------------
9.1.5: sp__rev_configure
-------------------------------------------------------------------------------
This proc reverse engineers the configure settings. It produces a set of calls
to sp_configure for those values that appear in syscurconfigs. I am not sure
how relevant this is with the ability to save and load the config file.
Get it as part of the bundle (zip or tarball) or individually from here.
The output is as follows, however, I have edited away some of the values since
my list was considerably longer than this.
-- sp_configure settings
-------------------------------------------------------------
sp_configure 'recovery interval', 5
go
sp_configure 'allow updates', 0
go
sp_configure 'user connections', 25
go
sp_configure 'memory', 14336
go
sp_configure 'default character set id', 2
go
sp_configure 'stack size', 65536
go
sp_configure 'password expiration interval', 0
go
sp_configure 'audit queue size', 100
go
sp_configure 'additional netmem', 0
go
sp_configure 'default network packet size', 512
go
sp_configure 'maximum network packet size', 512
go
sp_configure 'extent i/o buffers',
go
sp_configure 'identity burning set factor', 5000
go
sp_configure 'size of auto identity', 10
go
sp_configure 'identity grab size', 1
go
sp_configure 'lock promotion threshold', 200
go
(41 rows affected)
(return status = 0)
Back to top
-------------------------------------------------------------------------------
9.1.6: sp_servermap
-------------------------------------------------------------------------------
A one stop shop for a quick peek at everything on the server.
Get it as part of the bundle (zip or tarball) or individually from here.
The output for a brand new 11.0.3.3 ASE on Linux server is as follows:
Current Date/Time
------------------------------ --------------------------
TRAFALGAR Jan 14 2001 1:48PM
Version
-------------------------------------------------------------------------------------------------
SQL Server/11.0.3.3 ESD#6/P-FREE/Linux Intel/Linux 2.2.14 i686/1/OPT/Fri Mar 17 15:45:30 CET 2000
A - DATABASE SEGMENT MAP
************************
db dbid segmap segs device fragment start (pg) size (MB)
--------------- ------ ----------- ---- --------------- ----------- ---------
master 1 7 LDS master 4 3.00
master 1 7 LDS master 3588 2.00
tempdb 2 7 LDS master 2564 2.00
model 3 7 LDS master 1540 2.00
sybsystemprocs 4 7 LDS sysprocsdev 16777216 150.00
sybsecurity 5 15 ULDS sybsecurity 33554432 300.00
Segment Codes:
U=User-defined segment on this device fragment
L=Database Log may be placed on this device fragment
D=Database objects may be placed on this device fragment by DEFAULT
S=SYSTEM objects may be placed on this device fragment
B - DATABASE INFORMATION
************************
db dbid size (MB) db status codes created
dump tran
--------------- ------ --------- ------------------ ---------------
---------------
master 1 5.00 01 Jan 00 00:00
07 Jan 01 04:01
tempdb 2 2.00 A 14 Jan 01 13:46
14 Jan 01 13:47
model 3 2.00 01 Jan 00 00:00
07 Jan 01 03:38
sybsystemprocs 4 150.00 B 07 Jan 01 03:32
14 Jan 01 13:43
sybsecurity 5 300.00 B 07 Jan 01 04:01
07 Jan 01 04:55
Status Code Key
Code Status
---- ----------------------------------
A select into/bulk copy allowed
B truncate log on checkpoint
C no checkpoint on recovery
D db in load-from-dump mode
E db is suspect
F ddl in tran
G db is read-only
H db is for dbo use only
I db in single-user mode
J db name has been changed
K db is in recovery
L db has bypass recovery set
M abort tran on log full
N no free space accounting
O auto identity
P identity in nonunique index
Q db is offline
R db is offline until recovery completes
C - DEVICE ALLOCATION MAP
*************************
device fragment start (pg) size (MB) db lstart segs
--------------- ----------- --------- --------------- ----------- ----
master 4 3.00 master 0 LDS
master 1540 2.00 model 0 LDS
master 2564 2.00 tempdb 0 LDS
master 3588 2.00 master 1536 LDS
sybsecurity 33554432 300.00 sybsecurity 0 ULDS
sysprocsdev 16777216 150.00 sybsystemprocs 0 LDS
Segment Codes:
U=USER-definedsegment on this device fragment
L=Database LOG may be placed on this device fragment
D=Database objects may be placed on this device fragment by DEFAULT
S=SYSTEM objects may be placed on this device fragment
D - DEVICE NUMBER, DEFAULT & SPACE USAGE
****************************************
device vdevno default disk? total (MB) used free
--------------- ------ ------------- ---------- ------- -------
master 0 Y 100.00 9.00 91.00
sysprocsdev 1 N 150.00 150.00 0.00
sybsecurity 2 N 300.00 300.00 0.00
E - DEVICE LOCATION
*******************
device location
--------------- ------------------------------------------------------------
master d_master
sybsecurity /d/TRAFALGAR/3/sybsecur.dat
sysprocsdev /d/TRAFALGAR/2/sybprocs.dat
NO DEVICES ARE MIRRORED
(return status = 0)
Back to top
-------------------------------------------------------------------------------
9.1.7: sp__create_crosstab
-------------------------------------------------------------------------------
Hmmm... not quite sure about this one. Was not 100% sure about how to set it
up. From the description it builds a cross tab query. If someone knows how to
use this, then let me know how to set it up and I will improve the description
here and provide some output.
Get it as part of the bundle (zip or tarball) or individually from here.
Back to top
-------------------------------------------------------------------------------
9.1.8: sp_ddl_create_table
-------------------------------------------------------------------------------
Well, you all know what a create table statement looks like... This produces
the table definitions in their barest form (lacking in constraints etc) and the
resulting DDL is perhaps not as elegant as some other utilities, but far be it
from me to blow dbschema's trumpet :-), but it is worth a look just for the
query. The layout of the carriage returns being embedded within strings is
deliberate!
Get it as part of the bundle (zip or tarball) or individually from here.
Back to top
-------------------------------------------------------------------------------
9.1.9: sp_spaceused_table
-------------------------------------------------------------------------------
Brief
In environment where there are a lot of temporary tables #x being created, how
do you tell who is using how much space ? The answer is sp_spaceused_table,
which basically lists the tables in a database with rowcount and space usage
statistics. I have replaced the original proc with K-shell script for a single
proc. I think that it is easier to compare if it is all in one listing.
However, if you disagree I will add the original code to the archive package,
just let me know.
Get it as part of the bundle (zip or tarball) or individually from here.
The output of the proc is as follows: (I used sqsh, hence the prompt, since it
auto-resizes its width as you resize the xterm.)
[25] N_UTSIRE.tempdb.1> sp_spaceused_table
[25] N_UTSIRE.tempdb.2> go
name rowtotal reserved data index_size unused
--------------------------------------------- ----------- --------------- --------------- --------------- ---------------
#matter______00000010014294376 12039 3920 KB 3910 KB 0 KB 10 KB
#synopsis____00000010014294376 6572 15766 KB 274 KB 15472 KB 20 KB
#hearing_____00000010014294376 5856 572 KB 568 KB 0 KB 4 KB
#hearing2____00000010014294376 5856 574 KB 568 KB 0 KB 6 KB
#hearing3____00000010014294376 5856 574 KB 568 KB 0 KB 6 KB
#synopsis2___00000010014294376 6572 15820 KB 274 KB 15472 KB 74 KB
(return status = 0)
Back to top
-------------------------------------------------------------------------------
