Adaptive Server Enterprise FAQ 2000, version 3 released 12/08/98
Keyword and Phrase Search
Index of Sections
To get a text version of this FAQ:
To get the HTML for this FAQ:
SQL Server Administration
1.1) How do I start/stop SQL Server when the CPU reboots?
1.2) How do I clear a log suspend'ed connection?
1.3) What's the best value for cschedspins?
1.4) What traceflags are available?
1.5) How do I use traceflags 5101 and 5102?
1.6) What is cmaxpktsz good for? see also Q1.8
1.7) How do I move tempdb off of the master device?
1.8) What do all the parameters of a a buildmaster -d<device> -yall mean?
1.9) How do I correct timeslice -201?
1.10) What is a SQL Server anyway?
1.11) The how's and why's on becoming a Certified Sybase Professional (CSPDBA)?
1.12) RAID and Sybase
1.13) How to swap a db device with another
1.14) Server naming and renaming
1.15) How can I tell the datetime my Server started?
1.16) Raw partitions or regular files?
1.17) Is Sybase Y2K (Y2000) compliant?
User Database Administration
2.1) Changing varchar(m) to varchar(n)
2.2) Frequently asked questions on Table partitioning
2.3) How do I turn off marked suspect on my database?
2.4) How do I manually drop a table?
2.5) Why not create all my columns varchar(255)?
2.6) What's a good example of a transaction?
2.7) What's a natural key?
2.8) Making a Stored Procedure invisible
2.9) Saving space when inserting rows monotonically
2.10) How to compute database fragmentation
2.11) Tasks a DBA should do...
2.12) How to implement database security
2.13) How to shrink a database
3.1) How do I set TS Role in order to run DBCC ...?
3.2) What are some of the hidden/trick DBCC commands?
3.3) The unauthorized DBCC list with doco - see Q11.4.1
3.4) Fixing a Munged Log
3.5) Another site with DBCC commands - see Q11.4.2
Performing any of the above may corrupt your SQL Server. Please do not call Sybase Technical Support after screwing up your SQL Server. Remember, always take a dump of the master database and any other databases that are to be affected.
4.1) How do I hide my password using isql?
4.2) How do I remove row affected and/or dashes when using isql?
4.3) How do I pipe the output of one isql to another?
5.1) How do I bcp null dates?
5.2) Can I use a named pipe to bcp/dump data out or in?
5.3) How do I exclude a column?
6.1) Are there alternatives to row at a time processing?
6.2) When should I execute an sp_recompile?
6.3) What are the different types of locks and what do they mean?
6.4) What's the purpose of using holdlock?
6.5) What's the difference between an update in place versus a deferred update? - see Q8.9
6.6) How do I find the oldest open transaction?
6.7) How do I check if log truncation is blocked?
6.8) The timestamp datatype
6.9) Stored Procedure Recompilation and Reresolution
7.1) How to emulate the Oracle decode function/crosstab
7.2) How to implement if-then-else within a select-clause.
7.3) deleted due to copyright hassles by the publisher
7.4) How to pad with leading zeros an int or smallint.
7.5) Divide by zero and nulls.
7.6) Convert months to financial months.
7.7) Hierarchy traversal - BOMs.
7.8) Is it possible to call a UNIX command from within a stored procedure or a trigger?
7.9) Information on Identities and Rolling your own Sequential Keys
Performance and Tuning
8.1) What are the nitty gritty details on Performance and Tuning?
8.2) What is best way to use temp tables in an OLTP environment?
8.3) What's the difference between clustered and non-clustered indexes?
8.4) Optimistic versus Pessimistic locking?
8.5) How do I force an index to be used?
8.6) Why place tempdb and log on low numbered devices?
8.7) Have I configured enough memory for my SQL Server?
8.8) Why should I use stored procedures?
8.9) I don't understand showplan's output, please explain.
8.10) Poor man's sp_sysmon.
8.11) View MRU-LRU procedure cache chain.
8.12) Improving Text/Image Type Performance
9.1) sp_freedevice - lists device, size, used and free.
9.2) sp_whodo - augments sp_who by including additional columns: cpu, I/O...
9.3) SQL and sh(1)to dynamically generate a dump/load database command.
9.4) SybPerl - Perl interface to Sybase.
9.5) dbschema.pl - SybPerl script to take a logical snap of a database.
9.6) Sybtcl - TCL interface to Sybase.
9.7) Augmented system stored procedures.
9.8) Examples of Open Client and Open Server programs -- see Q11.4.14.
9.9) SQL to determine the space used for an index.
9.10) xsybmon - an X interface to sp_monitor
9.11) sp_dos - This procedure graphically displays the scope of a object
9.12) sqsh - a superset of dsql with local variables, redirection, pipes and all sorts of goodies.
9.13) sp_getdays - returns days in current month.
9.14) ddl_insert.pl - creates insert DDL for a table.
9.15) sp_ddl_create_table - creates DDL for all user tables in the current database
9.16) int.pl - converts interfaces file to tli
9.17) How to access a SQL Server using Linux see also Q11.4.6
9.18) sp__revroles - creates DDL to sp_role a mirror of your SQL Server
9.19) sp__rev_configure - creates DDL to sp_configure a mirror of your SQL Server
9.20) sp_servermap - overview of your SQL Server
9.21) sp__create_crosstab - simplify crosstable queries
9.22) update statistics script
9.23) lightweight Sybase Access via Win95/NT
9.24) Sybase on Linux
Sybase Technical News
10.1.1) Volume 3, Number 2
10.1.2) Volume 3, Number 3
10.1.3) Volume 3, Number 4
10.2.1) Volume 4, Number 1
10.2.2) Volume 4, Number 2
10.2.3) Volume 4, Number 3
10.2.4) Volume 4, Number 4
10.3.1) Volume 5, Number 1
10.3.2) Special Supplement -- Migration to System 11
10.3.3) Volume 5, Number 2
10.3.4) Volume 5, Number 3
10.3.5) Volume 5, Number 4
10.4.1) Volume 6, Number 1
10.4.2) Volume 6, Number 2
10.4.3) Volume 6, Number 3
10.4.4) Volume 6, Number 4
10.4.5) Volume 6, Number 5
10.4.6) Volume 6, Number 6
10.4.7) Volume 6, Number 7
10.4.8) Volume 6, Number 8
10.4.9) Volume 6, Number 9
10.5.1) Volume 7, Number 1
10.5.2) Volume 7, Number 2
10.5.3) Volume 7, Number 3
10.5.4) Volume 7, Number 4
10.5.5) Volume 7, Number 5
10.5.6) Volume 7, Number 6
10.5.7) Volume 7, Number 7
10.5.8) Volume 7, Number 8
10.5.9) Volume 7, Number 9
10.5.10) Volume 7, Number 10
10.5.11) Volume 7, Number 11
10.5.12) Volume 7, Number 12
10.6.1) Volume 8, Number 1
11.1.1) Yale Centre for Medical Informatics http://paella.med.yale.edu/topics/database.html
11.1.2) NC State University http://www.acs.ncsu.edu:80/Sybase
11.1.3) Simon Fraser University http://www.cs.sfu.ca/CourseCentral/Software/Sybase
11.1.4) University of California http://www-act.ucsd.edu/webad/sybase.html
11.1.5) Rutgers http://paul.rutgers.edu/sybase.html
11.2.1) Pacific Rim Network Systems Inc Sybase Resource Links http://www.alaska.net/~pacrim/sybase.html
11.2.2) The Sybase Contractors' Resource Page by Magnum Solutions http://www.mag-sol.com/Sybase
11.2.3) The SQL Workshop http://www.sqlworkshop.com
11.2.4) SQL Server and Rep Server on NT http://www.xs4all.nl/~reinoud/ntsqlrep.html
11.2.5) Sybase Replication Quick Reference guide - see Q11.4.16
Books, Magazines and Articles
11.3.1) Sybase Books http://sybooks.sybase.com
11.3.2) Intro to Sybase Architecture - http://www2.dgsys.com/~dcasug/sybintro/intro.html
11.3.3) Papers from SQL Forum http://www.sqlforum.com/sybart.htm
11.3.4) ASE 11.9 Optimizer Statisitics ftp://sgigate.sgi.com/pub/Sybase_FAQ/mamet.pdf [276K ]
11.4.1) The unauthorized documentation of DBCC by Al Huntley http://user.icx.net/~huntley/dbccinfo.htm
11.4.2) More DBCC's by KaleidaTech Associates, Inc. - http://www.kaleidatech.com/dbcc1.htm
11.4.3) sybinit4ever: Sybase ASE 11.5 ASCII-only server creation tool - see Q11.4.16
11.4.4) Sybase Freeware and Shareware http://www.tiac.net/users/sqltech
11.4.5) Peter Thawley's '97 ISUG Talk ftp://sgigate.sgi.com/pub/Sybase_FAQ/Thawleyhndout.ppt.ps.Z [3670K]
11.4.6) DBI/DBD:Sybase on Linux http://www.brodeur.com/~pjacob/dbdsybase
11.4.7) BusinessObjects FAQ - http://www.upenn.edu/computing/da/bo/busob-faq.html
11.4.8) Sybase Scheme Extensions - http://www.cs.indiana.edu/scheme-repository/ext.html
11.4.9) SQShell SQL shell for Unix by Scott Gray http://www.voicenet.com/~gray/sqsh.html
11.4.10) A login widget for Sybase http://ftp.digital.com/pub/plan/perl/CPAN/CPAN.html#sybase
11.4.11) ISUG's Freeware Collection http://www.isug.com/ISUG2/links.html
11.4.12) Sybase to HTML Converter http://www.algonet.se/~bergkarl/lasse/scripts_eng.html
11.4.13) Tool to access Sybase server with line editing and history recall http://www.mcs.net/~ivank/sybtool.html
11.4.14) Sybase connectivity libraries http://www.sybase.com/products/samples/
11.4.15) A web to Sybase interface http://archive.eso.org/wdb/html/
11.4.16) Nifty Sybase tools as well as sybinit4ever http://www.euronet.nl/~syp_rob/download.html
11.5.1) Indiana Sybase User's Group http://www.cs.bsu.edu/homepages/sam/isug
11.5.2) Ontario Sybase User Group (OSUG) Website - http://www.interlog.com/~osug
11.5.3) SUGBay, Bay Area Sybase User Group - http://www.sugbay.com
11.5.4) DCASUG, DC Area Sybase User Group - http://www2.dgsys.com/~dcasug
11.5.5) International Sybase User Group - http://www.sybase.com/ISUG
The following sites are placed here without any endorsement by the FAQ maintainer.
11.6.1) Ed Barlow's site of sites http://www.tiac.net/users/sqltech/links.htm#commercial_links
The mother ship may be reached at http://www.sybase.com
12.1) What can Sybase IQ do for me?
12.2) Net-review of Sybase books
12.3) email lists
12.4) Finding Information at Sybase
Acknowledgements ... Thanks to all the folks in comp.databases.sybase who have made this FAQ possible. Furry instance, this release has two minor contributions by me (although admining the thing can be a nightmare at times!) and the rest has come from folks on the net and at Sybase. I've degenerated into a brain stem. Add a little water and I'm your chia pet.
Please mail any changes, comments, complaints and/or disagreements and I will respond in due time. Heck I may even fix them.
Please abide by the following and include it if redistributing the Sybase FAQ:
Do not use this information for profit but do share it with anyone.
So who is this guy?
Hall of FameThe following people have made the Sybase FAQ Hall of Fame. Partly because Sybase refuses to acknowledge the good work that these people have done for their product and sales but most importantly because they have done it for us!
- Michael Peppler () - author of SybPerl and provider of awesome support.
- Scott Gray () - author of sqsh and ultra-responsive developer. The best tool this side of the Ozarks.
What's New in this Release?
- A non-exhaustive list of contributors for 11.5 Changes through 11.5.6
- Q2.11 - [email protected] - Added ls -la my_sybase_disk_devices
- Q11.22 - [email protected] - The SQL Workshop link
- Q9.18/.19 - [email protected] - Reverse engineer user roles and sp_configure data
- Q1.2 - [email protected] - Correction on using lct_admin command on System 11
- Q9.5 - [email protected] - Updated version of dbschema.pl by David Whitmarsh: constraints/defaults/etc + primary/foreign keys
- Q6.9 - [email protected] - Corrections to bad grammar and increased coverage from System 10 to anything below and including System 10.
- Q6.9 - anonymous - Culled from the sybase-tuning list see Q12.3
- Q9.5 - [email protected]/[email protected] - Updated link to SybPerl FAQ
- Q9.20 - unknown author, provided by Mark.Meredith - sp_servermap, gives an overview of your SQL Server
- Q10.4.[2-9] - Sybase Inc. - Sybase Technical News - I'm not doing much formatting on these because I want to stay up to date with the FAQ. Feedback from you, gentle reader, has told me that you want the data so that you can take the entire FAQ to a client site on your lap top.
- Q1.4 - [email protected] - Added traceflags 303 and 319
- Q9.16 - [email protected] - Update e-mail address
- Q7.1 - [email protected] - Submits his submission on crosstabs. Implemented way before the supposed copyrighted version.
- Q4.1 - [email protected] - Added warning to script #7
- Q1.13 - [email protected] - Updates his answer.
- Q6.7 - [email protected] - Fixed the title of the answer
- Q2.13 - [email protected] - His answer on how to shrink a database
- Q1.15 - [email protected] - An alternative method to determining when your Server started - fixed!
- Q2.3 - [email protected] - sp_configure "allow" wasn't specific enough for System 10, fixed the FAQ
- Q11.1/Q3.5 - [email protected]/[email protected] - Q11.1 was Web pointer to PNL site... dead! I've replaced it with the DBCC site for Q3.5
- Q2.4 - [email protected] - Moved use master to the right spot
- Q7.8 - me - Updated to include 11.5 information
- Q7.9 - [email protected] - A beautiful writeup on using Identities or your own Sequential Keys
- Q2.1 - [email protected] - Noted that these instructions can be used for increasing not just shrinking
- index - me - removed that darn counter (it kept resetting), Ed Barlow was catching up anyway... or so he thinks! :-)
- Q11.15 - [email protected]/[email protected] - Updated DBCC web link
- Q2.4 - [email protected] - Delete entries in sysdepends
- Q3.2 - [email protected] - rebuild_log entry
- Q9.13 - [email protected]/[email protected] - spruced up sp_getdays
- Q2.8 - [email protected] - Advised that 11.5 doesn't allow one to delete the data in syscomments. Instead use sp's to encrypt.
- I cleaned up the Sybase links that I used to keep. Namely, I removed them! :-) For the books, I simply have a link to the books URL and folks should be able to navigate within The Mother Ship. This makes it less probable of storing stale URL's Thanks!
- Q8.13 - me - nuked! It was a link to the Performance and Tuning books. It was stale... see previous note on my reasoning.
- Q11.6/Q9.17 - [email protected] - DBI/DBD:Sybase on linux how to
- Q1.7 - [email protected]@pangea.ca - An alternative to moving tempdb off of master, fill it! Neat trick, see writeup.
- Q12.4 - [email protected] - Self-help... from Sybase. :-)
- Q9.21 - [email protected] - sp__create_crosstab
Q9.6 - [email protected] - the ever-popular Sybtcl
Changes in 11.5.7 - 2/9/98
- Q1.7 - [email protected] - we needed to fill with 1024 rows
Q10.5.2 - sybase - added Sybase Technical News
Changes in 11.5.8 - 2/19/98
- Q7.7 - [email protected] - provided a fix to the hierarchy traversal
- Q4.1 - [email protected] - yet another way to hide your password
- Q3.2 - [email protected] - contributed dbcc settrunc()
Q9.10 - [email protected] - alerted me that Q9.10's link is dead. Thanks!
Changes in 11.5.9 - 4/13/98
- Q10.5.2/Q10.5.3 - Sybase Technical News
- Q6.4 - [email protected] - asked that I provide an example. So I did. It's more of an example how it works rather than a psuedo real world example.
- Q6.9 - [email protected] - suggested I mention traceflag 299 to Q6.9. So I did. Good suggestion.
- index - [email protected] - corrected the correction. :-)
Q7.9 - [email protected] - gave an update on where to find the Malcolm Colton white paper
Changes in 11.5.10 - 6.8.98
- Q3.2 - [email protected] - dbrepair (dbid, ltmignore)
- Section 11 - [email protected] - various web links
- Q9.22 - [email protected] - intelligent update statistics
Q10.5.5/Q10.5.6 - Sybase Technical News
Changes in 11.5.11 - 7.13.98
- Q10.5.7 - Sybase Technical News
- Section 11 - [email protected] - reorg of this section
Q3.2 - [email protected] - dbcc corrupt
Changes in 11.5.12 - 7.31.98
Q11.2.4 - [email protected] - SQL Server and Rep Server on NT
Changes in 11.5.13 - 8.14.98
Q10.5.8 - Sybase Technical News
Changes in 11.5.14 - 9.2.98
- Q9.23 - [email protected] - NTQuery.exe freeware
- Relabeled Shareware to be Freeware - we aren't charging
Q1.17 - [email protected] - Y2K stuff
Changes in 11.5.15 - 9.23.98
- Q10.5.9 - Sybase Technical News
Q1.4 - [email protected] - contributed traceflag 304
Changes in 1 and 2 - 11.6.98
- Renamed FAQ to be hip 'n cool: ...FAQ 2000 and fixed typos
- Q9.24 - [email protected] - Sybase on Linux, what else is there to say?
- Q10.5.10 - Sybase Technical News
- Q10.5.11 - Sybase Technical News
- Q11.4.16/Q11.2.5 - [email protected] - sybinit4ever and other goodies website
- Q11.6.1 - [email protected] - Added Ed Barlow's list of commercial sites. I've created a new subsection under weblinks named Commercial Links Of course as the FAQ maintainer, I do not endorse any of the listed sites.
- Q9.25 - [email protected] - sp_spaceused_table and ksh script used to determine space usage for temp tables.
Q11.4.11 - [email protected] - update to ISUG's link
Changes in 3 - 12.8.98
- Q10.5.12 - Sybase Technical News
- Q11.3.4 - [email protected] - ASE 11.9 Optimizer Statistics from the ISUG Technical Journal