Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail Message-ID: Supersedes: Expires: 2 Aug 2004 13:43:10 GMT X-Last-Updated: 2003/03/02 From: dowen@midsomer.org (David Owen) Newsgroups: comp.databases.sybase,comp.answers,news.answers Subject: Sybase FAQ: 1/19 - index 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:44:59 GMT Lines: 500 NNTP-Posting-Host: penguin-lust.mit.edu X-Trace: 1082468699 senator-bedfellow.mit.edu 559 18.181.0.29 Xref: senator-bedfellow.mit.edu comp.databases.sybase:106199 comp.answers:56945 news.answers:270285 Archive-name: databases/sybase-faq/part1 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. Sybase Frequently Asked Questions Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ Replication Server FAQSearch the FAQ Sybase FAQ Main Page * Where can I get the latest release of this FAQ? * What's new in this release? * How can I help with the FAQ? * Who do I tell about problems in the FAQ? * Acknowledgements and Thanks * Hall of Fame * Copyright and Disclaimer * General Index Main | ASE | ASA | REP | Search ------------------------------------------------------------------------------- Where can I get the latest release of this FAQ? International Sybase User Group The main page for this site is http://www.isug.com/Sybase_FAQ. It is hosted there by kind permission of the International Sybase User Group (http:// www.isug.com) as a service to the Sybase community. To get a text version of this FAQ: ftp://ftp.midsomer.org/pub/FAQ_txt_tar.Z or ftp://ftp.midsomer.org/pub/FAQ_txt.zip If you want uncompressed versions of the various sections, they can be got from ASE, ASA & REP. To get the HTML for this FAQ: ftp://ftp.midsomer.org/pub/FAQ_html_tar.Z or ftp://ftp.midsomer.org/pub/FAQ_html.zip Last major update: 21st February 2003. Back to Top ------------------------------------------------------------------------------- What's new in this release? Release 1.9 * Running multiple servers on a single server (UNIX and NT). Back to Top ------------------------------------------------------------------------------- What's happening with the FAQ? I have not had a lot of time to spend on the FAQ this year. Mainly, this is down to work, or the lack of it. I know, we are all in the same boat. Well, it has meant that I have had a lot less free time than I used to and as a result the FAQ has not been kept as up to date as I would like. Sadly, the work I have been doing is with those other database vendors, but we won't name them here. Anyway, that is the sob story over and done with. If anyone thinks that they would like to see more effort applied, I would be happy to hand the mantle over. Since the amount of help that I have actually seen amounts to about practically none, then I am sure I will not be over-run with offers! I will definitely have more time come January and plan some serious work on it then. Back to Top ------------------------------------------------------------------------------- How can I help with the FAQ? I have had offers from a couple of people to write sections, but if you feel that you are in a position to add support for a section, or if you have some FAQs to add, please let me know. This is a resource that we should all support, so send me the stuff and I will include it. Typos and specific corrections are always very useful. Less useful is the general I don't think that section x.y.z is very understandable. Sorry to sound harsh, but what I need is actual text that is more readable. Better still is actual HTML that makes it stand out and sing (if necessary)! Currently I am looking for maintainers of the following sections Replication, Adaptive Server Anywhere, IQ server, MPP Server and Open Server. I am not sure whether to add a section for Omni Server. I sort of feel that since Omni has been subsumed into ASE as CIS that any FAQs should really be incorporated there. However, if you know of some good Omni gotchas or tips, whether they are still there in CIS or not, please send them in. I certainly plan to have a subsection of ASE dealing with CIS even if Omni does not get its own major section. I also think that we need sections on some of the really new stuff. Jaguar and the new engines also deserve a spot. Another very useful way that you can help is in getting people to update their links. I have seen lots of links recently, some still pointing to Pablo's original, some pointing to Tom's site but referring to it as coming from the SGI site. Back to Top ------------------------------------------------------------------------------- Who do I tell about problems in the FAQ? The current maintainer is David Owen ( dowen@midsomer.org) and you can send errors in the FAQ directly to me. If you have an FAQ item (both the question and the answer) send it to sybfaq@midsomer.org and I will include it. Do not send email to any of the officials at ISUG, they are simply hosting the FAQ and are not responsible for its contents. Also, do not send email to Sybase, they are not responsible for the contents either. See the Disclaimer. Back to Top ------------------------------------------------------------------------------- Acknowledgements and Thanks Special thanks must go to the following people for their help in getting this FAQ to where it is today. * Pablo Sanchez for getting the FAQ off the ground in the first place and for many years of dedicated work in maintaining it. * Anthony Mandic (am@peppler.org) for a million things. Patiently answering questions in all of the Sybase news groups, without which most beginners would be lost. For supporting and encouraging me in getting this FAQ together and for providing some pretty neat graphics. * The ISUG, especially Luc Van der Veurst (lucv@az.vub.ac.be) and Michael Peppler (mpeppler@peppler.org), for hosting this FAQ and providing support in setting up the website. * The members of the various news groups and mailing lists who, like Anthony, provide unstinting support. The list is fairly long, but I think that Bret Halford (bret@sybase.com) deserves a mention. If you go to Google News and do a search, he submits almost as many replies as Anthony. Back to Top ------------------------------------------------------------------------------- Hall of Fame I am not sure how Pablo chose his select list, there is certainly no question as to their inclusion. I know that there are a couple of awards that the ISUG give out each year for the people that the ISUG members believe have contributed most to the Sybase community that year. I think that this section should honour those people that deserve an award each and every year. If you know of a candidate, let me know and I will consider his or her inclusion. Self nominations are not acceptable :-) The following people have made it to the Sybase FAQ Hall of Fame: * Michael Peppler (mpeppler@peppler.org) For Sybperl and all of the other tools of which he is author or instigator plus the ceaseless support that he provides through countless mailing lists, newsgroups and directly via email. * Scott Gray (gray@voicenet.com) Father of sqsh, much more than simply a replacement for isql. How anyone developing or administering Sybase can survive without it, I will never know. * Pablo Sanchez ( www.hpdbe.com) Pablo got the first web based FAQ off the ground, wrote most (all?) of the first edition and then maintained it for a number of years. He did a fantastic job, building a resource that is worth its weight in gold. Back to Top ------------------------------------------------------------------------------- Copyright and Disclaimer Distribution You are free to copy or distribute this FAQ in whole or in part, on any medium you choose provided that you: * include this Copyright and Disclaimer notice; * do NOT distribute or copy, in any fashion, with the intention of making a profit from its use; * give FULL attribution to the original authors. Disclaimer This FAQ is provided as is without any express or implied warranties. Whilst every endeavour has been taken to ensure the accuracy of the information contained within the articles, the author, nor any of the contributors, assume responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. If you are not happy about performing any of the suggestions contained within this FAQ, you are probably better off calling Sybase Technical Support. Copyright This site and all its contents belongs to the Sybase FAQ (http://www.isug.com/ Sybase_FAQ). Unless explicitly stated in an article, all material within this FAQ is copyrighted. The primary copyright holders are David Owen and Pablo Sanchez. However, all contributed material is, and will remain, the property of the respective authors and contributors. Back to Top ------------------------------------------------------------------------------- ASE 1.1: Basic ASE Administration 1.1.1 What is SQL Server and ASE anyway? 1.1.2 How do I start/stop ASE when the CPU reboots? 1.1.3 How do I move tempdb off of the master device? 1.1.4 How do I correct timeslice -201? 1.1.5 The how's and why's on becoming Certified. 1.1.6 RAID and Sybase 1.1.7 How to swap a db device with another 1.1.8 Server naming and renaming 1.1.9 How do I interpret the tli strings in the interface file? 1.1.10 How can I tell the datetime my Server started? 1.1.11 Raw partitions or regular files? 1.1.12 Is Sybase Y2K (Y2000) compliant? 1.1.13 How can I run the ASE upgrade manually? 1.1.14 We have lost the sa password, what can we do? 1.1.15 How do I set a password to be null? 1.1.16 Does Sybase support Row Level Locking? 1.1.17 What platforms does ASE run on? 1.1.18 How do I backup databases > 64G on ASE prior to 12.x? 1.2: User Database Administration 1.2.1 Changing varchar(m) to varchar(n) 1.2.2 Frequently asked questions on Table partitioning 1.2.3 How do I manually drop a table? 1.2.4 Why not create all my columns varchar(255)? 1.2.5 What's a good example of a transaction? 1.2.6 What's a natural key? 1.2.7 Making a Stored Procedure invisible 1.2.8 Saving space when inserting rows monotonically 1.2.9 How to compute database fragmentation 1.2.10 Tasks a DBA should do... 1.2.11 How to implement database security 1.2.12 How to shrink a database 1.2.13 How do I turn on auditing of all SQL text sent to the server 1.2.14 sp_helpdb/sp_helpsegment is returning negative numbers 1.3: Advanced ASE Administration 1.3.1 How do I clear a log suspend'd connection? 1.3.2 What's the best value for cschedspins? 1.3.3 What traceflags are available? 1.3.4 How do I use traceflags 5101 and 5102? 1.3.5 What is cmaxpktsz good for? 1.3.6 What do all the parameters of a buildmaster -d -yall mean? 1.3.7 What is CIS and how do I use it? 1.3.8 If the master device is full how do I make the master database bigger? 1.3.9 How do I run multiple versions of Sybase on the same server? 1.3.10 How do I capture a process's SQL? 1.4: General Troubleshooting 1. How do I turn off marked suspect on my database? 2. On startup, the transaction log of a database has filled and recovery has suspended, what can I do? 3. Why do my page locks not get escalated to a table lock after 200 locks? 1.5: Performance and Tuning 1.5.1 What are the nitty gritty details on Performance and Tuning? 1.5.2 What is best way to use temp tables in an OLTP environment? 1.5.3 What's the difference between clustered and non-clustered indexes? 1.5.4 Optimistic versus pessimistic locking? 1.5.5 How do I force an index to be used? 1.5.6 Why place tempdb and log on low numbered devices? 1.5.7 Have I configured enough memory for ASE? 1.5.8 Why should I use stored procedures? 1.5.9 I don't understand showplan's output, please explain. 1.5.10 Poor man's sp_sysmon. 1.5.11 View MRU-LRU procedure cache chain. 1.5.12 Improving Text/Image Type Performance 1.6: Server Monitoring 1.6.1 What is Monitor Server and how do I configure it? 1.6.2 OK, that was easy, how do I configure a client? 2.1: Platform Specific Issues - Solaris 2.1.1 Should I run 32 or 64 bit ASE with Solaris? 2.1.2 What is Intimate Shared Memory or ISM? 2.2: Platform Specific Issues - NT/2000 2.2.1 How to Start ASE on Remote NT Servers 2.2.2 How to Configure More than 2G bytes of Memory for ASE on NT 2.2.3 Installation Issues 2.3: Platform Specific Issues - Linux 2.3.1 ASE on Linux FAQ 3: DBCC's 3.1 How do I set TS Role in order to run certain DBCCs...? 3.2 What are some of the hidden/trick DBCC commands? 3.3 Other sites with DBCC information. 3.4 Fixing a Munged Log Performing any of the above may corrupt your ASE installation. Please do not call Sybase Technical Support after screwing up ASE. Remember, always take a dump of the master database and any other databases that are to be affected. 4: 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? 4.4 What alternatives to isql exist? 4.5 How can I make isql secure? 5: 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? 6.1: SQL Fundamentals 6.1.1 Are there alternatives to row at a time processing? 6.1.2 When should I execute an sp_recompile? 6.1.3 What are the different types of locks and what do they mean? 6.1.4 What's the purpose of using holdlock? 6.1.5 What's the difference between an update in place versus a deferred update? - see Q1.5.9 6.1.6 How do I find the oldest open transaction? 6.1.7 How do I check if log truncation is blocked? 6.1.8 The timestamp datatype 6.1.9 Stored Procedure Recompilation and Reresolution 6.1.10 How do I manipulate binary columns? 6.1.11 How do I remove duplicate rows from a table? 6.2: SQL Advanced 6.2.1 How to emulate the Oracle decode function/crosstab 6.2.2 How to implement if-then-else within a select-clause. 6.2.3 deleted due to copyright hassles with the publisher 6.2.4 How to pad with leading zeros an int or smallint. 6.2.5 Divide by zero and nulls. 6.2.6 Convert months to financial months. 6.2.7 Hierarchy traversal - BOMs. 6.2.8 Is it possible to call a UNIX command from within a stored procedure or a trigger? 6.2.9 Information on Identities and Rolling your own Sequential Keys 6.2.10 How can I execute dynamic SQL with ASE 6.2.11 Is it possible to concatenate all the values from a column and return a single row? 6.2.12 Selecting rows N to M without Oracle's rownum? 6.2.13 How can I return number of rows that are returned from a grouped query without using a temporary table? 6.3: Useful SQL Tricks 6.3.1 How to feed the result set of one stored procedure into another. 6.3.2 Is it possible to do dynamic SQL before ASE 12? 7: Open Client 7.1 What is Open Client? 7.2 What is the difference between DB-lib and CT-lib? 7.3 What is this TDS protocol? 7.4 I have upgraded to MS SQL Server 7.0 and can no longer connect from Sybase's isql. 7.5 The Basics of Connecting to Sybase 7.6 Connecting to ASE using ODBC 7.7 Which version of Open Client works with which ASE? 7.8 How do I tell the version of Open Client I am running? 9: Freeware 9.0 Where is all the code and why does Section 9 suddenly load in a reasonable amount of time? Stored Procedures 9.1.1 sp_freedevice - lists device, size, used and free. 9.1.2 sp_dos - This procedure graphically displays the scope of a object 9.1.3 sp_whodo - augments sp_who by including additional columns: cpu, I/O... 9.1.4 sp__revroles - creates DDL to sp_role a mirror of your SQL Server 9.1.5 sp__rev_configure - creates DDL to sp_configure a mirror of your SQL Server 9.1.6 sp_servermap - overview of your SQL Server 9.1.7 sp__create_crosstab - simplify crosstable queries 9.1.8 sp_ddl_create_table - creates DDL for all user tables in the current database 9.1.9 sp_spaceused_table 9.1.10 SQL to determine the space used for an index. 9.1.11 sp_helpoptions - Shows what options are set for a database. 9.1.12 sp_days - returns days in current month. 9.1.13 sp__optdiag - optdiag from within isql 9.1.14 sp_desc - a simple list of a tables' columns 9.1.15 sp_lockconfig - Displays locking schemes for tables. Shell Scripts 9.2.1 SQL and sh(1)to dynamically generate a dump/load database command. 9.2.2 update statistics script Perl/Sybperl 9.3.1 SybPerl - Perl interface to Sybase. 9.3.2 dbschema.pl - Sybperl script to reverse engineer a database. 9.3.3 ddl_insert.pl - creates insert DDL for a table. 9.3.4 int.pl - converts 12: 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 ASA Adaptive Server Anywhere 0.0 Preamble 0.1 What is ASA? 0.2 On what platforms is ASA supported? 0.3 What applications is ASA good for? 0.4 When would I choose ASA over ASE? 0.5 Does ASA Support Replication? 0.6 What is ASA UltraLite? 0.7 Links for further information REP Introduction to Replication Server 1.1 Introduction 1.2 Replication Server Components 1.3 What is the Difference Between SQL Remote and Replication Server? Replication Server Administration 2.1 How can I improve throughput? 2.2 Where should I install replication server? 2.3 Using large raw partitions with Replication Server on Unix. 2.4 How to replicate col = col + 1 2.5 What is the difference between an LTMs an a RepAgent? 2.6 Which Should I choose, RepAgent or LTM? Replication Server Trouble Shooting 3.1 Why am I running out of locks on the replicate side? 3.2 Someone was playing with replication and now the transaction log on OLTP is filling. Additional Information/Links 4.1 Links 4.2 Newsgroups