Sybase Frequently Asked Questions supported by Silicon Graphics

Adaptive Server Enterprise FAQ 2000, version 3 released 12/08/98

Keyword and Phrase Search

Enter search words/phrases: Ignore case?
Help!

Index of Sections

To get a text version of this FAQ:
ftp://sgigate.sgi.com/pub/Sybase_FAQ/FAQ_txt.Z
To get the HTML for this FAQ:
ftp://sgigate.sgi.com/pub/Sybase_FAQ/FAQ_html.tar.Z

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

DBCC's
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.

isql
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?

bcp
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?

SQL Fundamentals
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

SQL Advanced
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

Freeware
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 LinuxLinux Penguin
9.25) sp_spaceused_table

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

Web Links
Academia
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

Sybase Resources
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 ]

Shareware
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

User Groups
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

Commercial Links

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

Miscellany
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 pablo@sgi.com 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 Fame

The 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!

What's New in this Release?