home *** CD-ROM | disk | FTP | other *** search
- Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
- Message-ID: <databases/sybase-faq/part17_1082468590@rtfm.mit.edu>
- Supersedes: <databases/sybase-faq/part17_1074677126@rtfm.mit.edu>
- Expires: 2 Aug 2004 13:43:10 GMT
- References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
- X-Last-Updated: 2003/03/02
- From: dowen@midsomer.org (David Owen)
- Newsgroups: comp.databases.sybase,comp.answers,news.answers
- Subject: Sybase FAQ: 17/19 - ASE Section 9 (2 of 3)
- Reply-To: dowen@midsomer.org (David Owen)
- Followup-To: comp.databases.sybase
- Distribution: world
- Organization: Midsomer Consultants Inc.
- Approved: news-answers-request@MIT.EDU
- Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
- Originator: faqserv@penguin-lust.MIT.EDU
- Date: 20 Apr 2004 13:45:15 GMT
- Lines: 1580
- NNTP-Posting-Host: penguin-lust.mit.edu
- X-Trace: 1082468715 senator-bedfellow.mit.edu 580 18.181.0.29
- Xref: senator-bedfellow.mit.edu comp.databases.sybase:106215 comp.answers:56961 news.answers:270301
-
- Archive-name: databases/sybase-faq/part17
- 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.
-
- 9.1.10: SQL to determine space used for an index
-
- -------------------------------------------------------------------------------
-
- This one is not strictly a stored proc, but it has its uses.
-
- Fundamentally, it is sp_spaceused reduced to bare essentials:
-
- set nocount on
- declare @objname varchar(30)
- select @objname = "your table"
-
- select index_name = i.name,
- i.segment,
- rowtotal = rowcnt(i.doampg),
- reserved = reserved_pgs(i.id, i.doampg) +
- reserved_pgs(i.id, i.ioampg),
- data = data_pgs(i.id, i.doampg),
- index_size = data_pgs(i.id, i.ioampg),
- unused = (reserved_pgs(i.id, i.doampg) +
- reserved_pgs(i.id, i.ioampg) -
- (data_pgs(i.id, i.doampg) +
- data_pgs(i.id, i.ioampg)))
- into #space
- from sysindexes i
- where i.id = object_id(@objname)
-
- You can analyse this in a number of ways:
-
- 1. This query should tally with sp_spaceused @objname:
- select 'reserved KB' = sum(reserved) * 2,
- 'Data KB' = sum(data) * 2,
- 'Index KB' = sum(index_size) * 2,
- 'Unused KB' = sum(unused) * 2
- from #space
- 2. This one reports space allocation by segment:
- select 'segment name' = s.name,
- 'reserved KB' = sum(reserved) * 2,
- 'Data KB' = sum(data) * 2,
- 'Index KB' = sum(index_size) * 2,
- 'Unused KB' = sum(unused) * 2
- from #space t,
- syssegments s
- where t.segment = s.segment
- group by s.name
- 3. This one reports allocations by index:
- select t.index_name,
- s.name,
- 'reserved KB' = reserved * 2,
- 'Data KB' = data * 2,
- 'Index KB' = index_size * 2,
- 'Unused KB' = unused * 2
- from #space t,
- syssegments s
- where t.segment = s.segment
-
- If you leave out the where clause in the initial select into, you can analyse
- across the whole database.
-
- Hope this points you in the right direction.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.1.11: sp_helpoptions - Shows what options are set for a database.
-
- -------------------------------------------------------------------------------
-
- Thanks again go to Bret Halford for some more sterling work. The following proc
- will let you know some of options that are set within a database. The release
- included is here has been tested to work on Solaris (11.9.2 and 12.0), but it
- is likely that other platforms use and set @@options differently (endian issues
- etc). As such, it is more of a sort of template for platforms other than
- Solaris. Please feel free to expand it and send the modified proc back to me
- and Bret.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- The output is as follows:
-
- 1> sp_helpoptions
- 2> go
- showplan is off
- ansinull is off
- ansi_permissions is off
- arithabort is on
- arithignore is off
- arithignore arith_overflow off
- close on endtran is off
- nocount is on
- noexec is off
- parseonly is off.
- (return status = 0)
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.1.12: sp_days - returns days in a given month.
-
- -------------------------------------------------------------------------------
-
- Returns the number of days in a month. Modify to fit your needs, either
- returning a result set (of 1 row) or set a variable, or both as this version
- does.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- The output is as follows:
-
- 1> declare @days int
- 2> -- For November 1999
- 3> exec sp_days @days,11,99
- 4> go
-
- ---
- 30
-
- (1 row affected)
- (return status = 0)
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.1.13: sp__optdiag - optdiag from within isql.
-
- -------------------------------------------------------------------------------
-
- Versions of ASE: minimum of 11.5. I cannot test it on 11.5, so I do not know if
- it works on that version. However, the procedure uses a 'case' statement, so
- will certainly not work before 11.5. If anyone still has 11.5 running and can
- let me know that it works, I would be grateful.
-
- It seems little point in showing you what optdiag looks like, since it takes a
- fair amount of space. This proc produces pretty much identical output.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.1.14: sp_desc - a simple list of a tables' columns
-
- -------------------------------------------------------------------------------
-
- Stored proc to return a much simpler picture of a table that sp_help. sp_help
- produces all of the information, too much in fact, and it always takes me a
- couple of minutes to work out the various flags etc. I think that this is a
- little easier to read and understand quickly.
-
- 1> sp_desc spt_values
- 2> go
- spt_values
- No. Column Name Datatype
- ----- ------------------------------ -------------------- --------
- (1) name varchar(28)
- (2) number int NOT NULL
- (3) type char(2) NOT NULL
- (4) low int
- (5) high int
- (6) msgnum int
-
- (6 rows affected)
- (return status = 0)
- 1>
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.1.15: sp_lockconfig - displays locking schemes for tables
-
- -------------------------------------------------------------------------------
- sp_lockconfig [sys_flag]
- will list the server default locking scheme and lock promotion data (HWM, LWM,
- and PCT) in priority order:
-
- 1. All table-specific lock configurations for the current database.
- 2. The database-wide lock configurations, if they exist.
- 3. The server-wide lock configurations.
-
- A list of all tables will then be listed by locking scheme. For data-only
- tables a suffix of "*" indicates that the table was originally created with a
- clustered allpages configuration and was then altered to a data-only
- configuration. (The reverse cannot be detected.) If sys_flag is non-null then
- system tables will be included. Note that many system tables do not have a
- defined locking scheme. (The implicit usage is allpages.)
-
- 1> sp_lockconfig
- 2> go
-
- TYPE OBJECT LEVEL LOCK DATA
- -------- ---------------------------- ----- ---------------------------------
- Server - page PCT = 100, LWM = 200, HWM = 200
- Server - row PCT = 100, LWM = 200, HWM = 200
- Server default lock scheme - allpages
-
- THERE ARE 4 USER TABLES WITH ALLPAGES LOCKING.
-
- TABLE OWNER
- ------------------------------ ------------------------------
- appkey8 dbo
- appkey8_hist dbo
- text_table7 TESTUSER2
- with_types_table12 TESTUSER3
-
- THERE ARE 2 USER TABLES WITH DATAPAGES LOCKING.
-
- TABLE OWNER
- ------------------------------ ------------------------------
- dol_test1 dbo
- dol_test2 dbo
-
- THERE ARE 2 USER TABLES WITH DATAROWS LOCKING.
-
- TABLE OWNER
- ------------------------------ ------------------------------
- dol_test10 dbo
- dol_test11 dbo
-
- (return status = 0)
- 1>
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.2.1: Generating dump/load database command.
-
- -------------------------------------------------------------------------------
-
- This shell script generates dump/load database commands from dump devices. I
- cannot show the output because it seems to be broken, it is certainly a little
- convoluted and is really pertinent to the pre-11 days, possibly even pre-10. It
- is available as part of the archive code package.
-
- What is really needed here is some automatic backup scripts. How is it going
- Barb?
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.2.2: upd_stats.csh
-
- -------------------------------------------------------------------------------
-
- This is a script from Frank Lundy (mailto:flundy@verio.net) and does not
- generate output, but does the updates directly. As such there is no output to
- show you. It requires a program called sqlsa which you will need to modify to
- suit your own server. You probably want to make the file unreadable by regular
- users who have no need for any passwords contained within.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.1: SybPerl FAQ
-
- Sybperl is a fantastic utility for DBAs and system administrators needing to
- put together scripts to monitor and manage their installations as well as the
- main way that web developers can gain access to data held in ASEs.
-
- Sybperl now comes in a number of flavours, including a DBD version that is part
- of the DBI/DBD suite. Michael has also written a package called Sybase::Simple
- that sits on top of Sybperl that makes building such scripts a breeze.
-
- Find out more and grab a copy from Michael Peppler's mpeppler@peppler.org own
- FAQ:
-
- http://www.mbay.net/~mpeppler/Sybperl/sybperl-faq.html
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.2: dbschema.pl
-
- -------------------------------------------------------------------------------
-
- dbschema.pl is a script that will extract the schema (everything from the
- server definition down to table permissions etc) from ASE/SQL Server. It was
- initially developed by Michael Peppler but currently maintained by me (David
- Owen dowen@midsomer.org) The script is written using Sybperl and was
- originally distributed solely as part of that package. The latest copy can be
- got from ftp://ftp.midsomer.org/pub/dbschema.tgz.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.3: ddl_insert.pl
-
- -------------------------------------------------------------------------------
-
- In order to use this script you must have Sybperl installed -- see Q9.3.1 for
- more information.
-
- This utility produces the insert statements to rebuild a table. Note that it
- depends on the environment variable DSQUERY for the server selection. Also be
- warned that the generated script truncates the destination table, which might
- not be what you want. Other than that, it looks like an excellent addition to
- the testing toolkit.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- [dowen@n-utsire code]$ ./ddl_insert.pl alrbprod sa myPassword h%
- -- This script is created by ./ddl_insert.pl.
- -- It would generate INSERT statements for tables whose names match the
- -- following pattern:
- /* ( 1 = 0
- or name like 'h%'
- )
-
- */
-
- set nocount on
- go
-
-
- /*.............. hearing ...............*/
- -- Sat Feb 17 13:24:09 MST 2001
-
- declare @d datetime
- select @d = getdate()
- print ' %1! hearing', @d
- go
-
- truncate table hearing -- Lookout !!!!!!
- go
-
- insert hearing values('Dec 11 1985 12:00:00:000AM', 1, '1030', 2, '0930', 'Calgary, Alberta', NULL, NULL, '3408 Board Room', 3, NULL, '35', NULL)
- ...
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.4: int.pl
-
- -------------------------------------------------------------------------------
-
- Background
-
- Please find included a copy of int.pl, the interfaces file conversion tool. It
- should work with perl 4 and 5, but some perl distributions don't seem to
- support gethostbyname which you need for the solaris, ncr, and vms file format.
-
- You may need to adjust the first line to the path of perl on your system, and
- may need to set the PERLLIB environment variable so that it finds the
- getopts.pl module.
-
- While it may not be 100% complete (e.g. it ignores the timeout field) you're
- free to add any functionality you may need at your site.
-
- int.pl -h will print the usage, typical invocation is
- int.pl -f sun4-interfaces -o sol > interfaces.sol
- Usage: int.pl -f
- -o { sol|ncr|vms|nw386|os2|nt386|win3|dos|ntdoswin3 }
- [-V] [-v] [-h]
- where
- -f input file to process
- -o specify output mode
- (e.g. sol, ncr, vms, nw386, os2, nt386, win3, dos, ntdoswin3)
- -V turn on verbose mode
- -v print version string
- -h print this message
-
- [The following are a couple of output examples, is any other utility ever
- needed? Ed]
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- The following interface file:
-
- N_UTSIRE
- master tcp ether n-utsire 4100
- query tcp ether n-utsire 4100
-
- N_UTSIRE_XP
- master tcp ether n-utsire 4400
- query tcp ether n-utsire 4400
-
- N_UTSIRE_BS
- master tcp ether n-utsire 4010
- query tcp ether n-utsire 4010
-
-
- becomes
-
- [dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o vms
- N_UTSIRE
- master tcp ether 192.168.1.1 4100
- query tcp ether 192.168.1.1 4100
- N_UTSIRE_XP
- master tcp ether 192.168.1.1 4400
- query tcp ether 192.168.1.1 4400
- N_UTSIRE_BS
- master tcp ether 192.168.1.1 4010
- query tcp ether 192.168.1.1 4010
- [dowen@n-utsire code]$
- [dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o sol
- N_UTSIRE
- master tli tcp /dev/tcp \x00021004c0a801010000000000000000
- query tli tcp /dev/tcp \x00021004c0a801010000000000000000
- N_UTSIRE_XP
- master tli tcp /dev/tcp \x00021130c0a801010000000000000000
- query tli tcp /dev/tcp \x00021130c0a801010000000000000000
- N_UTSIRE_BS
- master tli tcp /dev/tcp \x00020faac0a801010000000000000000
- query tli tcp /dev/tcp \x00020faac0a801010000000000000000
- [dowen@n-utsire code]$
- [dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o ncr
- N_UTSIRE
- master tli tcp /dev/tcp \x00021004c0a80101
- query tli tcp /dev/tcp \x00021004c0a80101
- N_UTSIRE_XP
- master tli tcp /dev/tcp \x00021130c0a80101
- query tli tcp /dev/tcp \x00021130c0a80101
- N_UTSIRE_BS
- master tli tcp /dev/tcp \x00020faac0a80101
- query tli tcp /dev/tcp \x00020faac0a80101
- [dowen@n-utsire code]$
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.5: Sybase::Xfer.pm
-
- -------------------------------------------------------------------------------
-
- The following is taken directly from the authors own documentation.
-
- QUICK DESCRIPTION
- Sybase::Xfer transfers data between two Sybase servers with multiple
- options like specifying a where_clause, a smart auto_delete option and
- can pump data from a perl subroutine or take a plain flat file. Has
- option, similiar to default behaviour in Sybase::BCP, to capture failed
- rows in a batch.
-
- Also comes with a command line wrapper, sybxfer.
-
- Also comes with a sister module Sybase::ObjectInfo.pm
-
-
- DEPENDENCIES
- Requires Perl Version 5.005 or beyond
-
- Requires packages:
- Sybase::DBlib
- Getopt::Long
- Tie::IxHash
-
-
- SYNOPSIS
- #from perl
- #!/usr/bin/perl5.005
- use Sybase::Xfer;
- $h = new Sybase::Xfer( %options );
- $h->xfer();
- $h->done();
-
- #from shell
- #!/usr/ksh
- sybxfer <options>
-
-
- DESCRIPTION (a little bit from the pod)
-
- If you're in an environment with multiple servers and you don't want
- to use cross-server joins then this module may be worth a gander. It
- transfers data from one server to another server row-by-row in memory
- w/o using an intermediate file.
-
- To juice things up it can take data from any set of sql commands as
- long as the output of the sql matches the definition of the target
- table. And it can take data from a perl subroutine if you're into
- that.
-
- It also has some smarts to delete rows in the target table before the
- data is transferred by several methods. See the -truncate_flag,
- -delete_flag and -auto_delete switches.
-
- Everything is controlled by switch settings sent has a hash to the
- module. In essence one describes the from source and the to source and
- the module takes it from there.
-
- Error handling:
-
- An attempt was made to build in hooks for robust error reporting via
- perl callbacks. By default, it will print to stderr the data, the
- column names, and their datatypes upon error. This is especially
- useful when sybase reports attempt to load an oversized row warning
- message.
-
-
- Auto delete:
-
- More recently the code has been tweaked to handle the condition
- where data is bcp'ed into a table but the row already exists and the
- desired result to replace the row. Originally, the -delete_flag
- option was meant for this condition. ie. clean out the table via the
- -where_clause before the bcp in was to occur. If this is action is
- too drastic, however, by using the -auto_delete option one can be
- more precise and force only those rows about to be inserted to be
- deleted before the bcp in begins. It will bcp the 'key' information
- to a temp table, run a delete (in a loop so as not to blow any log
- space) via a join between the temp table and target table and then
- begin the bcp in. It's weird but in the right situation it may be
- exactly what you want. Typically used to manually replicate a table.
-
-
- CONTACTS
- my e-mail: stephen.sprague@msdw.com
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.6: Sybmon.pl
-
- -------------------------------------------------------------------------------
-
- Sybmon is a utility for interactive, realtime, monitoring of processes and
- locks. It is a sort of "top" for Sybase. It requires both Sybperl and Perl/Tk
- to be installed, both are available for most platforms, including Linux, NT,
- Solaris.
-
- Grab the tarball from ftp://ftp.midsomer.org/pub/sybmon.tar.gz or a zip'd one
- from ftp://ftp.midsomer.org/pub/sybmon.zip.
-
- There is also an NT binary for those people that are unable or just don't want
- to install Perl. You can get that from ftp://ftp.midsomer.org/pub/
- sybmon-i386.zip.
-
- You can view a screenshot of the main process monitor from here (just to prove
- that it runs on NT fine!!!!). A not very exciting server, doing not a lot!
-
- A note of interest! To get the screenshot I used the latest copy of Activestate
- Perl for NT and their Perl Package Manager (just type PPM from a DOS prompt
- once Perl is installed) and had the 3 required packages (Tk, Sybperl, Sybase::
- Login) installed in under 2 minutes!!!!
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.7: showserver.pl
-
- -------------------------------------------------------------------------------
-
- This small Perl script shows a list of what servers are running on the current
- machine. Does a similar job to the showserver that comes with ASE, but looks
- much nicer.
-
- Get it as part of the bundle (zip or tarball) or individually from here.
-
- bash-2.03$ ./showserver.pl
-
- monserver's
- -----------
- CONCRETE Owner: sybase, Started: 14:25:51
- Engine: 0 (PID: 520)
- PORTLAND Owner: sybase, Started: 14:29:33
- Engine: 0 (PID: 545)
-
- dataserver's
- ------------
- CONCRETE Owner: sybase, Started: 14:10:38
- Engine: 1 (PID: 494)
- Engine: 0 (PID: 493)
- PORTLAND Owner: sybase, Started: 14:26:56
- Engine: 0 (PID: 529)
-
- backupserver's
- --------------
- CONCRETE_back Owner: sybase, Started: 14:25:25
- Engine: 0 (PID: 515)
- PORTLAND_back Owner: sybase, Started: 14:29:07
- Engine: 0 (PID: 538)
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.3.8: Collection of Perl Scripts
-
- -------------------------------------------------------------------------------
-
- David Whitmarsh has put together a collection of scripts to help manage and
- monitor ASEs. They can be grabbed individually or en masse from http://
- sparkle-consultancy.co.uk/sybase/ .
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.4.1: Sybtcl FAQ
-
- This is Tom Poindexter http://www.nyx.net/~tpoindex/ FAQ.
-
- -------------------------------------------------------------------------------
-
- Index of Sections
-
- * Overview
- * The enabling language platform
- * Design and commands
- * Applications
- * Information Sources
- * Download
- * About the Author
-
- -------------------------------------------------------------------------------
-
- Overview
-
- Sybtcl is an extension to Tcl (Tool Command Language) that allows Tcl programs
- to access Sybase databases. Sybtcl adds additional Tcl commands to login to a
- Sybase server, send SQL statements, retrieve result sets, execute stored
- procedures, etc. Sybtcl simplifies Sybase programming by creating a high level
- interface on top of DB-Library. Sybtcl can be used to program a wide variety of
- applications, from system administration procedures to end-user applications.
-
- Sybtcl runs on Unix, Windows NT and 95, and Macintosh platforms.
-
- -------------------------------------------------------------------------------
-
- The enabling language platform
-
- Tool Command Language, often abbreviated "Tcl" and pronounced as "tickle", was
- created by Dr. John Ousterhout at the University of California-Berkeley. Tcl is
- an interpreted script language, similar to Unix shell, Awk, Perl, and others.
- Tcl was designed to be easily extended, where new commands are added to the
- base interpreter to provide additional functionality. Core Tcl commands contain
- all of the usual constructs provided by most programming languages: setting and
- accessing variables, file read/write, if-then-else, do-while, function calls.
- Tcl also contains many productivity enhancing commands: list manipulation,
- associative arrays, and regular expression processing.
-
- Tcl has several features that make it a highly productive language. First, the
- language is interpreted. Interpreters allow execution without a compile and
- link step. Code can be developed with immediate feedback. Second, Tcl has a
- single data type: string. While this might at first glance seem to a
- deficiency, it avoids problems of data conversion and memory management. (This
- feature doesn't preclude Tcl from performing arithmetic operations.) Last, Tcl
- has a consistent and simple syntax, much the same as the Unix shell. Every Tcl
- statement is a command name, followed by arguments.
-
- Dr. Ousterhout also developed a companion Tcl extension, called Tk. Tk provides
- simplified programming of X11 applications with a Motif look and feel. X11
- applications can be programmed with 60%-80% less code than equivalent Xt,
- Motif, or Xview programs using C or C++.
-
- Dr. Ousterhout now leads Tcl/Tk development at Sun Microsystems.
-
- -------------------------------------------------------------------------------
-
- Design and commands
-
- Sybtcl was designed to fill the gap between pure applications development tools
- (e.g. Apt, Powerbuilder, et.al.) and database administration tools, often Unix
- shell scripts consisting of 'isql' and Awk pipelines. Sybtcl extends the Tcl
- language with specialized commands for Sybase access. Sybtcl consists of a set
- of C language functions that interface DB-Library calls to the Tcl language.
-
- Instead of a simple one-to-one interface to DB-Library, Sybtcl provides a
- high-level Sybase programming interface of its own. The following example is a
- complete Sybtcl program that illustrates the simplified interface. It relies on
- the Tcl interpreter, "tclsh", that has been extended with Sybtcl.
-
- #!/usr/local/bin/tclsh
- set hand [sybconnect "mysybid" "mysybpasswd"]
- sybuse $hand pubs2
- sybsql $hand "select au_lname, au_fname from authors order by au_lname"
- sybnext $hand {
- puts [format "%s, %s" @1 @2]
- }
- sybclose $hand
- exit
-
- In this example, a Sybase server connection is established ("sybconnect"), and
- the "pubs" sample database is accessed ("sybuse"). An SQL statement is sent to
- the server ("sybsql"), and all rows returned are fetched and printed
- ("sybnext"). Finally, the connection is closed ("sybclose").
-
- The same program can be made to display its output in an X11 window, with a few
- changes. The Tcl/Tk windowing shell, "wish", also extended with Sybtcl is used.
-
- #!/usr/local/bin/wish
- listbox .sql_output
- button .exit -text exit -command exit
- pack .sql_output .exit
- set hand [sybconnect "mysybid" "mysybpasswd"]
- sybuse $hand pubs2
- sybsql $hand "select au_lname, au_fname from authors order by au_lname"
- sybnext $hand {
- .sql_output insert end [format "%s, %s" @1 @2]
- }
- sybclose $hand
-
- In addition to these commands, Sybtcl includes commands to access return column
- names and datatypes ("sybcols"), return values from stored procedures
- ("sybretval"), reading and writing of "text" or "image" columns ("sybreadtext",
- "sybwritetext"), canceling pending results ("sybcancel"), and polling
- asynchronous SQL execution ("sybpoll").
-
- Full access to Sybase server messages is also provided. Sybtcl maintains a Tcl
- array variable which contains server messages, output from stored procedures
- ("print"), DB-Library and OS error message.
-
- -------------------------------------------------------------------------------
-
- Applications
-
- The Sybtcl distribution includes "Wisqlite", an X11 SQL command processor.
- Wisqlite provides a typical windowing style environment to enter and edit SQL
- statements, list results of the SQL execution in a scrollable listbox, save or
- print output. In addition, menu access to the Sybase data dictionary is
- provided, listing tables in a database, the column names and datatypes of a
- table, text of stored procedures and triggers.
-
- For a snapshot of Wisqlite in action, look here.
-
- Other applications included in the Sybtcl distribution include:
-
- * a simple graphical performance monitor
- * a version of "sp_who", with periodic refresh
-
- Sybtcl users have reported a wide variety of applications written in Sybtcl,
- ranging from end user applications to database administration utilities.
-
- -------------------------------------------------------------------------------
-
- Information Sources
-
- Sybtcl is extensively documented in "Tcl/Tk Tools", edited by Mark Harrison,
- published by O'Reilly and Associates, 1997, ISBN: 1-56592-218-2.
-
- Tcl/Tk is described in detail in "Tcl and the Tk Toolkit" by Dr. John
- Ousterhout, Addison-Wesley Publishing 1994 ISBN: 0-201-63337-X . Another recent
- publication is "Practical Programming in Tcl and Tk" by Brent Welch, Prentice
- Hall 1995 ISBN 0-13-182007-9.
-
- A wealth of information on Tcl/Tk is available via Internet sources:
-
- news:comp.lang.tcl
- http://www.neosoft.com/tcl/
- http://www.sco.com/Technology/tcl/Tcl.html
- ftp://ftp.neosoft.com/pub/tcl/
-
- -------------------------------------------------------------------------------
-
- Download
-
- Download Sybtcl in tar.gz format for Unix.
- Download Sybtcl in zip format for Windows NT and 95.
-
- Tcl/Tk and Sybtcl are both released in source code form under a "BSD" style
- license. Tcl/Tk and Sybtcl may be freely used for any purpose, as long as
- copyright credit is given to the respective owners. Tcl/Tk can be obtained from
- either anonymous FTP site listed above.
-
- Tcl/Tk and Sybtcl can be easily configured under most modern Unix systems
- including SunOS, Solaris, HP-UX, Irix, OSF/1, AIX, SCO, et.al. Sybtcl also runs
- under Windows NT and 95; pre-compiled DLL's are include in the distribution.
- Sybtcl requires Sybase's DB-Library, from Sybase's Open Client bundle.
-
- Current versions are:
-
- * Sybtcl 2.5: released January 8, 1998
- * Tcl 8.0: released August 13, 1997
- * Tk 8.0: released August 13, 1997
-
- The Internet newsgroup comp.lang.tcl is the focal point for support. The group
- is regularly read by developers and users alike. Authors may also be reached
- via email. Sun has committed to keeping Tcl/Tk as freely available software.
-
- -------------------------------------------------------------------------------
-
- About the Author
-
- Tom Poindexter is a consultant with expertise in Unix, relational databases,
- systems and application programming. He holds a B.S. degree from the University
- of Missouri, and an M.B.A. degree from Illinois State University. He can be
- reached at tpoindex@nyx.net.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.4.2: sybdump
-
- -------------------------------------------------------------------------------
-
- Sybdump is a Tcl script written by De Clarke (de@ucolick.org) for extracting a
- database schema. Look in
-
- ftp://ftp.ucolick.org/pub/src/UCODB
-
- for sybdump.tar or sybdump.tar.gz.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.4.3: wisql
-
- -------------------------------------------------------------------------------
-
- Another Sybtcl package maintained by De Clarke (de@ucolick.org) this one is a
- graphical replacement for isql. Correct me if I am wrong, but I think that this
- started life as wisqlite and was included as part of the Sybtcl package and was
- then updated by De and became wisql.
-
- You can grab a copy of wisql from ftp://ftp.ucolick.org:/pub/UCODB/
- wisql5B.tar.gz
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.5.1: Sybase Module for Python
-
- -------------------------------------------------------------------------------
-
- Dave Cole has a module for Python that allows connectivity to Sybase in an
- analagous way to Sybperl or Sybtcl. You can find details from http://
- www.object-craft.com.au/projects/sybase/.
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.6.1: SQSH, SQshelL
-
- SQSH is a direct replacement for isql with a million more bells and whistles.
- In fact, the title gives it away, since SQSH is a pretty seemless marriage of
- sh(1)
- and isql.
-
- There has been a webified copy of the SQSH FAQ based on the 1.4 release
- contained within these pages for a while, but it is considerably behind the
- times. As such, I have moved the 1.4 release to a separate file readable from
- here.
-
- The current SQSH FAQ can be seen on Scott's own site, http://www.voicenet.com/
- ~gray/FAQ.html.
-
- -------------------------------------------------------------------------------
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.6.2: NTQuery.exe
-
- -------------------------------------------------------------------------------
-
- Brief
-
- ntquery.exe is a 32-bit application allowing a lightweight, but robust sybase
- access environment for win95/NT. It has a split window - the top for queries,
- the bottom for results and error/message handler responses, which are processed
- in-line. Think of it as isql for windows - a better (reliable) version of wisql
- (with sensible error handling). Because its simple it can be used against
- rep-server (I've also used it against Navigation Server(R.I.P.))
-
- Requirements: open client/dblib (Tested with 10.x up to 11.1.1)
-
- It picks up the server list from %SYBASE%\ini\sql.ini and you can add
- DSQUERY,SYBUSER and SYBPASS variables in your user variables to set default
- server,username and password values.
-
- Instructions
-
- To connect: SQL->CONNECT (only one connection at a time, but you can run
- multiple ntquery copies) Enter query in top window and hit F3 (or SQL->Execute
- Query if you must use the mouse) Results/Messages/Errors appear in bottom
- window
-
- A script can be loaded into the top window via File->Open Either sql or results
- can be saved with File->Save - it depends which window your focus is on.
-
- Theres a buffer limit of 2mb
-
- Get it here
-
- ntquery.zip [22K]
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.6.3: BCPTool - A utility for Transferring Data from one ASE to Another.
-
- -------------------------------------------------------------------------------
-
- BCPTool is a GUI utility written by Anthony Mandic that moves data from one ASE
- to another. It runs on Solaris and Linux and is very straightforward to use.
-
- Go to http://www.mbay.net/~mpeppler/bcptool to grab a copy, read the
- documentation and see a couple of screen shots.
-
- Hot news! Michael Peppler is porting BCPtool to use the GTK+ libraries, which
- is basically the standard gnome toolkit for Linux. Go to Michael's site for
- more details (http://www.mbay.net/~mpeppler).
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.7.1: How to access a SQL Server using Linux
-
- -------------------------------------------------------------------------------
-
- I am planning to remove/reduce/rewrite this section when the ASE on Linux FAQ
- moves to section 2. Most of it is out of date, and I think that most of its
- links are broken.
-
- Some time back, Sybase released a binary distribution of ctlib for Linux. This
- is just the header and libraries files for ctlib only, not dblib, not isql, not
- bcp, not the dataserver and not the OpenServer. This was done as a skunk works
- internal project at Sybase, for the good of the Linux community, and not
- supported by Sybase in any official capacity. This version of ctlib identifies
- itself as 10.0.3.
-
- At the time, the binary format for Linux libraries was a format called a.out.
- Since then, the format has changed to the newer, ELF format. ELF libraries and
- .o files cannot be linked with a.out libraries and .o files. Fortunately, a.out
- libraries and .o files can easily be converted to ELF via the objdump(1)
- program.
-
- Getting a useable ctlib for Linux isn't that easy, though. Another
- compatibility problem has arisen since these old libraries were compiled. The
- byte-order for the ctype macros has changed. One can link to the
- (converted-to-ELF) ctlib, but running the resulting executable will result in
- an error message having to do with missing localization files. The problem is
- that the ctype macros in the compiled ctlib libraries are accessing a structure
- in the shared C library which has changed its byte order.
-
- I've converted the a.out library, as distributed by Sybase to ELF, and added
- the old tables directly to the library, so that it won't find the wrong ones in
- libc.
-
- Using this library, I can link and run programs on my Linux machines against
- Sybase databases (It also can run some programs against Microsoft SQL server,
- but that's another FAQ). However, you must be running Linux 2.0 or later, or
- else the link phase will core dump.
-
- This library is available for ftp at:
-
- * ftp://mudshark.sunquest.com/pub/ctlib-linux-elf/sybperl.tar.gz
- * ftp://mudshark.sunquest.com/pub/ctlib-linux-elf/ctlib-linux-elf.tgz
-
- is a compiled version of sybperl 2.0, which is built with the above library.
- Obviously, only the ctlib module is in this distribution.
-
- In order to use this code, you will need a Sybase dataserver, a Sybase
- interfaces file (in the non-TLI format -- see Q9.3.4), a user named sybase in
- your /etc/passwd file, whose home directory is the root of the distribution,
- and some application code to link to.
-
- As far as an isql replacement goes, use sqsh - Q9.5.1.
-
- One of the libraries in the usual Sybase distribution is a libtcl.a This
- conflicts with the library on Linux which implements the TCL scripting
- language, so this distribution names that library libsybtcl.a, which might
- cause some porting confusion.
-
- The above conflict problem is addressed by SybPerl - Q9.3.1 and sqsh -
- Q9.5.1
-
- More information
-
- See Q11.4.6 for more information on setting up DBI/DBD:Sybase
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.7.2: Sybase on Linux FAQ
-
- -------------------------------------------------------------------------------
-
- I am planning to move this section out of here next release.
-
- Sybase have released two versions of Sybase on Linux, 11.0.3.3 and 11.9.2, and
- a third, 12.5, is in beta testing at this moment, slated for GA sometime in the
- first half of 2001.
-
- 11.9.2
-
- This is officially supported and sanctioned. The supported version can be
- purchased from Sybase at similar, if not exactly the same, conditions as 11.9.2
- on NT, with one small exception: you can download a developer's version for
- free! There is a 11.9.2.2 EBF, although I am not 100% sure if the current
- developer's release is 11.9.2 or 11.9.2.2. Certainly for a while, you could
- only get the EBF if you had a paid for version.
-
- 11.0.3.3
-
- Please remember that Sybase Inc does not provide any official support for
- SQL Server on Linux (ie the 11.0.3.3 release). The folks on the 'net
- provide the support.
-
- Index
-
- * Minimum Requirements
- * How to report a bug
- * Bug list
-
- Minimum Requirements
-
- * Linux release: 2.0.36 or 2.1.122 or greater.
-
- How to report a bug
-
- I hope you understand that the Sybase employee who did the port is a very busy
- person so it's best not to send him mail regarding trivial issues. If you have
- tried posting to comp.databases.sybase and ase-linux-list@isug.com and have
- checked the bugs list, send him an e-mail note with the following data - you
- will not get an acknowledgement to your e-mail and it will go directly into the
- bug tracking database; true bugs will be fixed in the next release; any message
- without the above Subject will be deleted, unseen, by a filter.
-
- Administrator: I know that the above sounds harsh but Wim ten has been
- launched to world-wide exposure. In order for him to continue to provide
- Sybase ASE outside of his normal workload we all have to support him.
- Thanks!
-
- With the above out of the way, if you find a bug or an issue please report it
- as follows:
-
- To: wtenhave@sybase.com
- Subject: SYBASE ASE LINUX PR
- uname: the result of typing 'uname -a' in a shell
- $SYBASE/scripts/hw_info.sh: As 'sybase' run this shell script and enclose
- its output
- short description: a one to two line description of the problem
- repeatable: yes, you can repeat it, no you cannot
- version of dataserver: the result of: as the 'sybase' user, 'cd $SYBASE/bin
- ' and type './dataserver -v|head -1'
- test case: test case to reproduce the problem
-
- Bug List
- +-----------------------------------------------------------------------------+
- | | | | | | |
- |-------------+--------+--------------+-------------+-------------+-----------|
- | Short | Fixed? | Dataserver | Date | Fix Date | Fix Notes |
- | Description | | Release | Reported | | |
- |-------------+--------+--------------+-------------+-------------+-----------|
- | | | SQL Server/ | | | You must |
- | | | 11.0.3.3/P/ | | | upgrade |
- | Remote | | Linux Intel/ | Pre-release | Pre-release | your OS |
- | connections | Yes | Linux 2.0.36 | of SQL | of SQL | to either |
- | hang | | i586/1/OPT/ | Server | Server | 2.0.36 or |
- | | | Thu Sep 10 | | | 2.1.122 |
- | | | 13:42:44 | | | or |
- | | | CEST 1998 | | | greater |
- +-----------------------------------------------------------------------------+
-
- as of Fri Nov 20 20:16 (08:16:47 PM) MST 1998
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.7.3: Linux Shared Memory for ASE (x86 Processors)
-
- -------------------------------------------------------------------------------
-
- 2.2.x Series Kernels and Above
-
- To set the maximum shared memory to 128M use the following:
-
- # echo 134217728 > /proc/sys/kernel/shmmax
-
- This comes from the following calculation: 128Mb = 128 x 1024 x 1024 bytes =
- 134217728 bytes
-
- 2.0.x and 2.1.x Kernels
-
- To increase the total memory for ASE (SQL Server) beyond 32mb, several kernel
- parameters must be changed.
-
- 1. Determine Memory/System Requirements
- + a: Total Memory < 128mb specific instructions
- + b: Total Memory > 128MB - specific instructions
- 2. Modify the linux/include/asm/shmparam.h to setup shared memory
- 3. Increase the size of the swap
- 4. Recompile your kernel & start using the new kernel
- 5. Verify the changes have taken effect
- 6. Increase the total memory to the desired size
-
- Comments
-
- -------------------------------------------------------------------------------
-
- 1a - Total Memory < 128mb specific instructions
-
- -------------------------------------------------------------------------------
-
- Requirements:
-
- Linux 2.0.36 or higher
-
- Total memory is currently limited to 128mb. A request to the Linux kernel
- developers has been made to enable large swap support which will allow the same
- size as 2.2.x kernels.
-
- -------------------------------------------------------------------------------
-
- 1b - Total Memory > 128mb - specific instructions
-
- -------------------------------------------------------------------------------
-
- Requirements:
-
- * Linux Kernel 2.2.x or higher *
- * util-linux package 2.9 or higher *
- * Swap space atleast as large as the SQL Server
-
-
- * - both are available from ftp://ftp.us.kernel.org
-
- You need to make the following changes in linux/include/asm-i386/page.h:
-
- - #define __PAGE_OFFSET (0xC0000000)
- + #define __PAGE_OFFSET (0x80000000)
-
- This allows accessing up to 2gb of memory. Default is 960mb.
-
- -------------------------------------------------------------------------------
-
- Step 2: Modify the linux/include/asm/shmparam.h to setup shared memory
-
- -------------------------------------------------------------------------------
-
-
- [max seg size]
- - #define SHMMAX 0x2000000 /* defaults to 32 MByte */
- + #define SHMMAX 0x7FFFE000 /* 2048mb - 8k */
-
- [max number of segments]
- - #define _SHM_ID_BITS 7 /* maximum of 128 segments */
- + #define _SHM_ID_BITS 5 /* maximum of 32 segments */
-
- [number of bits to count how many pages in the shm segment]
- - #define _SHM_IDX_BITS 15 /* maximum 32768 pages/segment */
- + #define _SHM_IDX_BITS 19 /* maximum 524288 pages/segment */
-
- Alter _SHM_IDX_BITS only if you like to go beyond the default 128MByte
- where you also need the swap space available.
-
- _SHM_ID_BITS + _SHM_IDX_BITS must be equal to or less then 24.
-
- Linux kernel PAGE size for Intel x86 machines = 4k
-
- -------------------------------------------------------------------------------
-
- Step 3: To increase the size of swap
-
- -------------------------------------------------------------------------------
-
-
- $ mkswap -c <device> [size] <- use for pre 2.2 kernels
- - limited to 128mb - 8k
-
- $ mkswap -c -v1 <device> [size] <- limited to 2gb 8k
-
- $ swapon <device>
-
- * Add the following to your /etc/fstab to enable this swap on boot
-
- <device> swap swap defaults 0 0
-
- -------------------------------------------------------------------------------
-
- Step 4: Recompile your kernel & restart using the new kernel
-
- -------------------------------------------------------------------------------
-
-
- Follow the instructions provided with the Linux Kernel
-
- -------------------------------------------------------------------------------
-
- Step 5: Verify the changes have taken effect
-
- -------------------------------------------------------------------------------
-
-
- $ ipcs -lm
-
- ------ Shared Memory Limits --------
- max number of segments = 32
- max seg size (kbytes) = 2097144
- max total shared memory (kbytes) = 67108864
- min seg size (bytes) = 1
-
- [jfroebe@jfroebe-desktop asm]$
-
- The changes took.
-
- -------------------------------------------------------------------------------
-
- Step 6: Increase the total memory to the desired size
-
- -------------------------------------------------------------------------------
-
-
- Because of current limitations in the GNU C Library (glibc), ASE is limited
- to 893mb. A workaround to increase this to 1400mb has been submitted.
-
- Increase the total memory to desired size. Remember the above limitation as
- well as the 128mb limitation on Linux kernel 2.0.36.
-
- For example, to increase the total memory to 500mb:
-
- 1> sp_configure "total memory", 256000
- 2> go
- 1> shutdown
- 2> go
-
- -------------------------------------------------------------------------------
-
- Comments
-
- * Note that it is possible to increase the total memory far above the physical
- RAM
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.7.4: Sybase now available on Free BSD
-
- -------------------------------------------------------------------------------
-
- Amazing, the Sybase folks have got ASE running on FreeBSD! The following post
- is from Reinoud van Leeuwen (reinoud.v@n.leeuwen.net). His web site is http://
- www.xs4all.nl/~reinoud and contains lots of other useful stuff.
-
- Sybase has made an update of their free 11.0.3.3 SQL server available. This
- updated version includes some bug fixes and *FreeBSD support*.
-
- The 11.0.3.3 version is unsupported, but Free for development *and production*!
-
- The server still runs under the Linux emulation, but there is a native SDK
- (libraries).
-
- download on
-
- http://www.sybase.com/linux/ase/
-
- some extra info on:
-
- http://my.sybase.com/detail?id=1009270
-
- Here are the notes I made to get everything working (still working on things
- like sybperl, dbd::sybase and PHP :-)
-
- notes on getting Sybase to work on FreeBSD 4.0 RELEASE
- ======================================================
-
- (log in as root)
-
- 1: create a user sybase. give it /usr/local/sybase as home directory. I gave
- him bash as shell and put him in the group sybase
-
- 2: put the following files in /usr/local (they contain the path sybase):
-
- * sybase-ase-11.0.3.3-FreeBSD-6.i386.tgz
- * sybase-doc-11.0.3.3-FreeBSD-6.i386.tgz
- * sybase-ocsd-10.0.4-FreeBSD-6.i386.tgz
-
- 3: untar them:
-
- tar xvzf sybase-ase-11.0.3.3-FreeBSD-6.i386.tgz
- tar xvzf sybase-doc-11.0.3.3-FreeBSD-6.i386.tgz
- tar xvzf sybase-ocsd-10.0.4-FreeBSD-6.i386.tgz
- rm sybase*.tgz
-
- 4: change the ownership of the tree to sybase:
-
- chown -R sybase:sybase /usr/local/sybase
-
- 5: install the FreeBSD linux emulation:
-
- * add the following line to /etc/rc.conf
- linux_enable="YES"
- * build the following ports:
- /usr/ports/emulators/linux_base
-
- (TIP: move the nluug site up in the makefile, this speeds up things
- considerably from the Netherlands!)
-
- 6: build a kernel that supports System V shared memory blocks make sure that
- the following lines are in the kernel config file (/sys/i386/conf/YOUR_KERNEL)
-
- # the next 3 are now standard in the kernel
- options SYSVSHM
- options SYSVMSG
- options SYSVSEM
-
- options SHMMAXPGS="8192"
- options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)"
-
- (this might be a good time to also enable your kernel for Multi processor) It
- is also possible to set the last two entries during runtime:
-
- sysctl -w kern.ipc.shmmax=32000000
- sysctl -w kern.ipc.shmall=8192
-
- (log in as sybase or su to it; make sure that the SYBASE environment variable
- is set to /usr/local/sybase ; the .cshrc file should set it.)
-
- 7: brand some executables to make sure FreeBSD knows that they are Linux ones
-
- brandelf -t Linux /usr/local/sybase/install/sybinit
- brandelf -t Linux /usr/local/sybase/install/startserver
- brandelf -t Linux /usr/local/sybase/bin/*
-
- 8: run ./install/sybinit
-
- With this program you should be able to install a sybase server and a backup
- server. (see the included docs or the online manuals on http://
- sybooks.sybase.com)
-
- 9: To make Sybase start during system boot copy the following script to /usr/
- local/etc/rc.d and make it executable by root
-
- #!/bin/sh
- # start all sybase servers on this system
- # assume that sybase is installed in the home dir of user
- # sybase
- export SYBASE=`grep -e "^sybase" /etc/passwd | cut -d: -f 6`
- export PATH="${SYBASE}/bin:${SYBASE}/install:${PATH}"
-
- unset LANG
- unset LC_ALL
-
- cd ${SYBASE}/install
-
- for RUN_SERVER in RUN_*
- do
- su sybase -c "startserver -f ${RUN_SERVER}" > /dev/null 2>&1
- echo -n "${RUN_SERVER} "
- done
- echo
-
- # end of script
-
- Getting 2 CPU's working
- =======================
-
- Two get Sybase running on 2 CPU's involves two steps:
-
- * getting Unix working on 2 CPU's and
- * configuring Sybase to use them.
-
- 1: Getting FreeBSD to work on 2 CPU's.
-
- Build a new kernel that supports 2 CPU's. Run the command mptable (as root).
- note the last few lines of output, they will tell you what you should include
- in your kernel file.
-
- Edit the Kernel file and build it. Note the messages during the next reboot. It
- should say somewhere that it uses the second CPU now.
-
- 2: insert the following line in the sybase.sh startup script in /usr/local/etc/
- rc.d
-
- export SRV_CPUCOUNT=2
-
- Also insert this line in the files where environment variables are set for the
- user sybase. Edit the config file for the sybase server(s) on your system (/usr
- /local/sybase/<SERVERNAME>.cfg). Change the values in the line "max online
- engines" from "Default" to "2". (Another option is to give the SQL command
- sp_configure "max online engines",2) During the next Sybase reboot, the last
- line in the errorlog should say something like:
-
- engine 1, os pid xxx online
-
- there should be two processes with the name dataserver now.
-
-
- Back to top
- -------------------------------------------------------------------------------
-
- 9.8.1: Other Extended Stored Procedures
-
- -------------------------------------------------------------------------------
-
- The following stored procedures were written by Ed Barlow sqltech@tiac.net and
- can be fetched from the following site:
-
- http://www.edbarlow.com
-
- Here's a pseudo-man page of what you get:
-
- Modified Sybase Procedures
- +-------------------------------------------------------------+
- | | |
- |---------------+---------------------------------------------|
- | Command | Description |
- |---------------+---------------------------------------------|
- |---------------+---------------------------------------------|
- |sp__help |Better sp_help |
- |---------------+---------------------------------------------|
- |sp__helpdb |Database Information |
- |---------------+---------------------------------------------|
- |sp__helpdevice |Break down database devices into a nice |
- | |report |
- |---------------+---------------------------------------------|
- |sp__helpgroup |List groups in database by access level |
- |---------------+---------------------------------------------|
- |sp__helpindex |Shows indexes by table |
- |---------------+---------------------------------------------|
- |sp__helpsegment|Segment Information |
- |---------------+---------------------------------------------|
- |sp__helpuser |Lists users in current database by group |
- | |(include aliases) |
- |---------------+---------------------------------------------|
- |sp__lock |Lock information |
- |---------------+---------------------------------------------|
- |sp__who |sp_who that fits on a page |
- +-------------------------------------------------------------+
- Audit Procedures
- +-------------------------------------------------------------+
- | | |
- |-----------------+-------------------------------------------|
- | Command | Description |
- |-----------------+-------------------------------------------|
- |sp__auditsecurity|Security Audit On Server |
- |-----------------+-------------------------------------------|
- |sp__auditdb |Audit Current Database For Potential |
- | |Problems |
- +-------------------------------------------------------------+
- System Administrator Procedures
- +-------------------------------------------------------------+
- | | |
- |--------------+----------------------------------------------|
- | Command | Description |
- |--------------+----------------------------------------------|
- |--------------+----------------------------------------------|
- |sp__block |Blocking processes. |
- |--------------+----------------------------------------------|
- |sp__dbspace |Summary of current database space information.|
- |--------------+----------------------------------------------|
- |sp__dumpdevice|Listing of Dump devices |
- |--------------+----------------------------------------------|
- |sp__helpdbdev |Show how Databases use Devices |
- |--------------+----------------------------------------------|
- |sp__helplogin |Show logins and remote logins to server |
- |--------------+----------------------------------------------|
- |sp__helpmirror|Shows mirror information, discover broken |
- | |mirrors |
- |--------------+----------------------------------------------|
- |sp__segment |Segment Information |
- |--------------+----------------------------------------------|
- |sp__server |Server summary report (very useful) |
- |--------------+----------------------------------------------|
- |sp__vdevno |Who's who in the device world |
- +-------------------------------------------------------------+
- DBA Procedures
- +-------------------------------------------------------------+
- | | |
- |---------------+---------------------------------------------|
- | Command | Description |
- |---------------+---------------------------------------------|
- |---------------+---------------------------------------------|
- |sp__badindex |give information about bad indexes (nulls, |
- | |bad statistics...) |
- |---------------+---------------------------------------------|
- |sp__collist |list all columns in database |
- |---------------+---------------------------------------------|
- |sp__indexspace |Space used by indexes in database |
- |---------------+---------------------------------------------|
- |sp__noindex |list of tables without indexes. |
- |---------------+---------------------------------------------|
- |sp__helpcolumns|show columns for given table |
- |---------------+---------------------------------------------|
- |sp__helpdefault|list defaults (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__helpobject |list objects |
- |---------------+---------------------------------------------|
- |sp__helpproc |list procs (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__helprule |list rules (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__helptable |list tables (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__helptrigger|list triggers (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__helpview |list views (part of objectlist) |
- |---------------+---------------------------------------------|
- |sp__trigger |Useful synopsis report of current database |
- | |trigger schema |
- +-------------------------------------------------------------+
- Reverse Engineering
- +-------------------------------------------------------------+
- | | |
- |-----------------+-------------------------------------------|
- | Command | Description |
- |-----------------+-------------------------------------------|
- |-----------------+-------------------------------------------|
- |sp__revalias |get alias script for current db |
- |-----------------+-------------------------------------------|
- |sp__revdb |get db creation script for server |
- |-----------------+-------------------------------------------|
- |sp__revdevice |get device creation script |
- |-----------------+-------------------------------------------|
- |sp__revgroup |get group script for current db |
- |-----------------+-------------------------------------------|
- |sp__revindex |get indexes script for current db |
- |-----------------+-------------------------------------------|
- |sp__revlogin |get logins script for server |
- |-----------------+-------------------------------------------|
- |sp__revmirror |get mirroring script for server |
- |-----------------+-------------------------------------------|
- |sp__revuser |get user script for current db |
- +-------------------------------------------------------------+
- Other Procedures
- +-------------------------------------------------------------+
- | | |
- |---------------+---------------------------------------------|
- | Command | Description |
- |---------------+---------------------------------------------|
- |---------------+---------------------------------------------|
- |sp__bcp |Create unix script to bcp in/out database |
- |---------------+---------------------------------------------|
- |sp__date |Who can remember all the date styles? |
- |---------------+---------------------------------------------|
- |sp__quickstats |Quick dump of server summary information |
- +-------------------------------------------------------------+
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- 9.8.3: xsybmon
-
- -------------------------------------------------------------------------------
-
- The original site, NSCU, no longer carries these bits. If you feel that it's
- useful to have xsybmon and you know where the new bits are, please drop me an
- e-mail: dowen@midsomer.org
-
- There is an alternative that is include as part of De Clarke's wisql package.
- It is called syperf. I do not have any screen shots, but I will work on it. You
- can grab a copy of wisql from ftp://ftp.ucolick.org:/pub/UCODB/wisql5B.tar.gz
-
- Back to top
-
- -------------------------------------------------------------------------------
-
- Sybase Tech Docs Open Client ASE FAQ
-
-