Search the FAQ Archives

3 - A - B - C - D - E - F - G - H - I - J - K - L - M
N - O - P - Q - R - S - T - U - V - W - X - Y - Z
faqs.org - Internet FAQ Archives

Sybase FAQ: 16/19 - ASE Section 9 (1 of 3)

( Part1 - Part2 - Part3 - Part4 - Part5 - Part6 - Part7 - Part8 - Part9 - Part10 - Part11 - Part12 - Part13 - Part14 - Part15 - Part16 - Part17 - Part18 - Part19 )
[ Usenet FAQs | Web FAQs | Documents | RFC Index | Zip codes ]
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.

See reader questions & answers on this topic! - Help others by sharing your knowledge
                                   Freeware                                    
                                                                               
 

Sybase Tech Docs Open Client ASE FAQ

 

The best place to search for Sybase freeware is Ed Barlow (sqltech@tiac.net)'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

-------------------------------------------------------------------------------

User Contributions:

Comment about this article, ask questions, or add new information about this topic:

CAPTCHA




Part1 - Part2 - Part3 - Part4 - Part5 - Part6 - Part7 - Part8 - Part9 - Part10 - Part11 - Part12 - Part13 - Part14 - Part15 - Part16 - Part17 - Part18 - Part19

[ Usenet FAQs | Web FAQs | Documents | RFC Index ]

Send corrections/additions to the FAQ Maintainer:
dowen@midsomer.org (David Owen)





Last Update March 27 2014 @ 02:11 PM