[Comp.Sci.Dept, Utrecht] Note from archiver<at>cs.uu.nl: This page is part of a big collection of Usenet postings, archived here for your convenience. For matters concerning the content of this page, please contact its author(s); use the source, if all else fails. For matters concerning the archive as a whole, please refer to the archive description or contact the archiver.

Subject: Sybase FAQ: 17/19 - ASE Section 9 (2 of 3)

This article was archived around: 18 Apr 2006 04:29:50 GMT

All FAQs in Directory: databases/sybase-faq
All FAQs posted in: comp.databases.sybase
Source: Usenet Version

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 4100 query tcp ether 4100 N_UTSIRE_XP master tcp ether 4400 query tcp ether 4400 N_UTSIRE_BS master tcp ether 4010 query tcp ether 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, 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 EBF, although I am not 100% sure if the current developer's release is 11.9.2 or Certainly for a while, you could only get the EBF if you had a paid for version. Please remember that Sybase Inc does not provide any official support for SQL Server on Linux (ie the 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 | | | | | | | 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 SQL server available. This updated version includes some bug fixes and *FreeBSD support*. The 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- * sybase-doc- * sybase-ocsd-10.0.4-FreeBSD-6.i386.tgz 3: untar them: tar xvzf sybase-ase- tar xvzf sybase-doc- 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