Subject: Sybase FAQ: 1/19 - index
From: [email-masked] (David Owen)
Date: 20 Apr 2004 13:44:59 GMT
Newsgroups: comp.databases.sybase,comp.answers,news.answers

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 ( [email-masked]) 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 [email-masked] 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 ([email-masked]) 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 ([email-masked]) and Michael
    Peppler ([email-masked]), 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 ([email-masked]) 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 ([email-masked])   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 ([email-masked])   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<device> -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