My own personal Sybase FAQ:
(note: LOTS of this also works for Microsoft SQL Server)

Topics:
SQL Theory
T-SQL Coding Specific
isql specific
sybperl specific
Administrative/Operations
bcp specific
sqsh specific
Performance/Tuning
Replication Server 

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQL Theory

---
Q: What is the "Halloween problem" in Sybase?
A: An anomaly associated with cursor updates, whereby a row seems to appear
twice in the result set. This happens when the index key is updated by the
client and the updated index row moves farther down in the result set. 
Is this still an issue w/ upper versions?

---
Q: Which is faster, updating records or deleting and re-inserting?
A: updating will be faster; only takes half the transactions.  However,
be aware that doing this over and over on a small table will cause bad
contention (unless RLL/max_rows_per_page is turned on).

---
Q: Can you use Unions in Views?  
A: Unions in Views is a feature added in Sybase 12.5.

---
Q: What are non-logged operations?
A: truncate table, fast bcp, use of bcp library routines, writetext to text
fields, select into, and parallel sorts.  You must have "select into/bulkcopy"
dboption set to true to do any of these.  These operations eliminate the
use of dump transaction in the particular database until a dump database
is issued.

---
Q: How do I strip the "^M" that MS tends to tack on the end of lines of
text, if it gets into a string in my database?
A: 
- Run any one of a number of solutions at the Unix level before dealing
with the data in Sybase.   See http://www.bossconsulting.com/sysadmin/sysadmin.faq for several possibilities.

- use stuff command to strip the final character

---
Q: Can you append a string to an existing text field?
A: no; you must bring the text field out of the database, do
your string manipulation in an external client and re-insert.  You cannot
read text fields into variables nor pass them in as parameters of a stored
procedure.  You cannot simply append them like a varchar() field.

---
Q: Can i populate a variable with a where clause and call it dynamically
like "select * from table where @where_clause?
A: No, not in straight T-SQL.  You can use a case statement or an external
program wrapper to get around this.

---
Q: How do i do cross-server queries?
A: You must install CIS and create proxy tables locally to mirror remote
tables that you may want to query (similarly to the way one can query
Sybase IQ tables conventionally in ASE servers).

---
Q: What is the advantage of defining a limit on a varchar(n) field?  Why
not just define every column as a varchar(255)?
A: Several reasons (This is Sybase FAQ #1.2.4)
- (from faq): the sum of the fields in an index can't be > 256, so 
you limit y our index creation flexibility
- (from faq): data structures should match business requirements, since
they end up becoming a Data Dictionary.
- 3rd party tools depend on the defined sizes for report generation.
- v12.0 and below have row-size limitations at 1962 bytes per row.  Defining
many varchar(255) fields on a table can allow a breach of this limit.

---
Q: Should I use declarative constraints or triggers to enforce Referential
Integrity (mostly Foreign keys) in my database?
A: Not a simple question.  Pros and cons to both sides (some of this taken
from a discussion on Sybase-L 2/5/02)

Declarative constraints (foreign key constraints)
Pro
- No SQL coding required, just a simple alter table command.  No SQL to
program and depend on Developers.
- Constraints are read/interpreted by 3rd Party ERD tools, allowing for proper
modelling
- RI constraints appear in system tables; thus creating documentation as
to what RI relationships exist
- ANSI Standard, whereas triggers are not.
- Better for performance?  Arguable: i've heard both are better performing.



Con
- inflexibility doing table modifications.  If you're doing a lot of
table mods (adding and dropping columns) FK constraints and their existing
relationships make for major headaches.  I've always preferred to add/drop
tables instead of using alter commands.  If you've got any sort of
cascading constraint relationship (table A has a foreign key reference in
table b, which has a FK reference in table C) you've got to drop the
FK constraints manually in table C, then table B just to do your table
drop.  It can get tedious.  This would be easier if there was a "cascade
constraint" option similar to Oracle's
- Do not create indexes on FK fields; needs additional step (con in both)
- Older versions of Sybase (allegedly) have had FK constraint bugs (though
I've never seen it in practice).  
- Can't do cascading deletes.
- Declarative RI have inflexible error messages


Triggers 
Pro
- Better for performance?  Arguable: i've heard both are better performing.
- easily disabled w/ "alter table disable trigger" to turn off RI temporarily
- More flexible than constraints; can do more error checking.  
- Can program in before/after behavior, even though all triggers in Sybase
are "after" triggers
- Can program in cascading update and delete behavior
- Can have more flexible error messages than Constraint RI violations.
(Example: a FK relationship to a PK table needs RI for two purposes: to ensure
a child row has a parent, and to ensure a parent row can't be deleted if
there's an existant child row.  The error messages really should distinguish
between these two cases).

Con
- Are NOT read by 3rd party ERD tools, requiring additional maitenance to keep RI
documentation updated through sp_primarykey and sp_foreignkey statements
- Do not create indexes on FK fields; needs additional step (con in both)


---


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
T-SQL Coding Specific

---
Q: What is the skeletal code required for a cursor declaration and use
in Sybase?
A:
declare cursor_name cursor for
  select field1, field2 from table where condition1, condition2
declare @variable1, @variable2 (which must match the fields you select)

open cursor_name
fetch cursor_name into @variable1, @variable2

while (@@sqlstatus = 0)
begin
   perform actions
   ...

   fetch cursor_name into @variable1, @variable2

end

(cleanup)
close cursor_name
deallocate cursor cursor_name


---
Q: Can "set rowcount" take a parameter?
A: No, it can take only a constant.  However if you poplulate a variable then 
set rowcount  it would work.  Setting rowcount=0 returns default to 
all rows.

---
Q: What happens if the parameter data type of a stored procedure doesn't
match the column type in the table?
A: table scan!  If updating, automatic table lock.  This is especially
touchy when converting to 11.9.2, where the previously unforgiving optimizer
really depends on exact data matching. 

---
Q: I've got duplicate rows in my table.  How do I get rid of them?
A: Several methods:

- Create an index w/ ignore_dup_row option
1> create clustered index temp_index 
2> on tablename (col1, col2, col3) with ignore_dup_row
3> go
1> drop index temp_index
2> go

- Drop all indexes. create a non-unique clusetered index on any column
with ignore_dup_row, then drop that index, and recreate the originals.  
(not sure if you have to drop other indexes)

- Create a duplicate table skeletal structure of your target table,
with a unique constraint on the columns in question.  Select/insert into
the new table, letting individual rows error out.  Your cloned table
should now contain only unique records.

- Insert all duplicate rows to temporary table 1.  Then take a distinct 
on those duplicate rows and insert into temp table 2.  Delete from target
table where rows match temp table 2.  Then re-insert temp table 2's data
into target.  This leaves just one copy of each previously duplicate row
in the target table.  

- Manually; set rowcount 1 and issue multiple delete commands.  Typing 
intensive but effective if you cannot create indexes or if your tables
are very large.

---
Q: how do i find all the duplicate instances of particular columns?
A: 
select col1,col2,col3,col4,count(1)
from table
group by col1,col2,col3,col4
having count(1) > 1

---
Q: how do i get distinct instances of a group of columns, since you cannot
pass more than one column to the distinct() function?
A: ???

---
Q: How do I lock an entire table?
A: 
- 11.9.2 and greater: lock table .  
- Previous versions, you'd have to do a hack; 
1. begin tran update table set col=col go.  
2. You can also do a hack by doing something like this:

exec  sp_setpglockpromote "table", my_table, 2, 3, 1
go
set transaction isolation level 3
go
begin tran
go
select something
from my_table
where at least 2 pages will be read, triggering promotion to a table lock
go
commit tran
go
exec sp_dropglockpromote "table", my_table
go

3. Or, try this:
begin tran
delete table where 1=2
go


---
Q: how get the rownumber returned from a select statement?
i.e., print out a row number per row? 
A: No easy way; the best way would be to use a cursor and a counter var:

declare test cursor for
select field from table
declare @counter int
declare @result char(10)
select @counter=1

open test
fetch test into @result

while (@@sqlstatus = 0)
begin
   select @counter,@result
   select @counter=@counter+1

   fetch test into @result
end

close test
deallocate cursor test

- The only problem w/ this method is the multiple select statements
return lots of "1 row(s) affected." output messages.


---
Q: Is there a Sybase equivalent to Oracle's rownum?
A: Not in 11.9.2 and previous.  v12.0 and above (being configured for RLL)
do have an internal rownum construct but it isn't dependable like Oracle's.
In most cases though, you can use combinations of set rowcount X and
using temporary tables w/ identity values to get certain "numbers" of rows
(i.e., the first 20 rows, or rows number 100-150).  See q6.2.12 in the
Sybase FAQ for details.

---
Q: how can you capture the SQL being sent to a Server?
A: 3rd party product or
dbcc traceon(3604)
go
dbcc pss(suid,spid,1) 
go
(the extra "1" spits out a showplan; its optional)

Syntax: dbcc pss( suid, spid, printopt = { 0 | 1 | 2 | 3 | 4 | 5 } )

OR

apparently there's an undocumented dbcc feature called sqltext
dbcc traceon(3604)
go
dbcc sqltext(spid)
go

output is limited to the first 400 bytes of text

---
Q: How do i count the number of characters in a string?
A: select char_length(rtrim(COLUMN NAME)), COLUMN NAME from TABLE NAME 
(you need the rtrim if the column is defined as not null).  Or try
datalength(rtrim(column))

---
Q: How do you count the number of characters in a text field?
A: You can't, within Sybase anyway.  My favorite way is to read the
text variable into perl and use the length() function to get a character
count.

---
Q: how do i emulate Oracle's replace(string,oldstring,newstring) function?
How do i do regular expressions or do string replacements in Tsql?
A: nest a stuff function within a charindex or patindex function.  E.g.:
update xyz 
set col_1 = stuff(col_1,charindex('~~',col_1),2,char(10)) 
where col_1 like "%~~%" 

---
Q: How do i search more than the first 255 characters of a text field?
A: set textsize 

---
Q: What is MS-SQL equivalent to syntax "select top" syntax?
A: set rowcount (or you could create a cursor and limit the rows 
returned through set cursor rows X for cursor)

---
Q: How do i compare times in Sybase
A: use style "8" to convert datetime variables to just hh:mm:ss
select convert(varchar(8),getdate(),8)

---
Q: How do i get the current date (with a default time)?
A: select convert(datetime,(convert(varchar(20),getdate(),101)))
This returns 2002-01-01 00:00:00.000

(this converts getdate() with style 101, which returns mm/dd/yyyy,
then converts that back to a datetime giving current date with default
12:00:00am time, the same as if you inserted just the date).

---
Q: How do I get JUST the date?
A: select select (convert(varchar(20),getdate(),1)).  Its in string
format at this point, but has no extra time fields, just "01/01/02"


---
Q: How do you split up a comma delimited field into two fields (ala last,first)?
A: 
declare @my_name varchar(40)
select @my_name = "last_name,first_name"

select substring(@my_name,1,charindex(',',@my_name)-1),
substring(@my_name,charindex(",",@my_name)+1,char_length(@my_name))

---
Q: How can I take a comma-delimited field and populate a quick temp table?
A: (from Kenny Lucas) (note; your @CommaDelimitedList must have a trailing
comma to work correctly

select @List = '99,141,150,161,'
set @x = patindex('%,%',@List)
while @x > 0
begin

   set @Number = substring(@List,1,@x-1)
   set @List = substring(@List, @x+1,len(@List)-@x)

   insert into #Temp (Field2) values (@Number )
   set @x = patindex('%,%',@List)
end



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
isql specific

---
Q: How do I get isql to use a different editor besides vi?
A: isql -E  will let you use the editor of choice.  Log in using
-E method, then in the 1> cmd line type in  instead of vi.

---
Q: how do i suppress column headings in isql without using sed or some
sort of filter?
A: -b flag on isql.

---
Q: my output is getting split to two lines.  How do I force isql to show
all data on one line?
A: -w flag of isql: isql -Uuser -Ppwd -w9999

---



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
sybperl

Q: What is the error mean: "DB-Library error: SYBERPND: Attempt to initiate
a new SQL Server operation with  results pending."
A: You've still got rows pending from your previous dbsqlexec attempt.  You've
either attempted to issue a new command mid-result stream or you have exited
the previous result-processing loop before you had fetched all the rows.




=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Administrative/Operations

---
Q: What causes "Infected with 11 error?"
A: a bug in Sybase 10 with killing server processes.  Unknown in Sybase 11.x
Error message still seen in 11.9.2.  Suggest searching solved cases in the
Sybase tech support site.  Believed to be a generic error message used
to indicate many root causes.

---
Q: Is there a way to get around the 2gb device size limit in older versions?
A: Yes: Device size limits in Sybase10 is 2gb.  Not an issue with Sybase11.
If you have a 4gb disk partition and can't change it, create 2 sybase 
devices (or several) but specify the vstart=3584000 (# might not be right).

---
Q: What are specific issues to know when working w/ tempdb/temporary tables?
A: 
- Tables created like "#name" are viewable only by the user who created
them (if you look at the actual name of the object in tempdb, the #name
is appended by a session id).  These tables are dropped out of tempdb when the
creating user logs out (if created from isql) or when the transaction is
completed (if called from a stored procedure, e.g.).  Even if you are logged
in as the same user from a different window, you still cannot view the data
in the temporary table nor drop it.
- tables created as "tempdb..name" are viewable by anyone (given proper
grant executes) but still get dropped when the creating user logs out/
transaction finishes.  
- tempdb is recreated from the model database upon every reboot, so if
you want bcp/trunc log on chkpt on your temp db you have to enable it
on model.
- tempdb is read synchronously by the Engine, thus benefits from having its
devices placed on file systems.  Since it is recreated each boot, there
are no recovery issues either.

---
Q: Whats a good way to do index maintenance?
A: Create stored procedures in your database starting with di_ and ci_
that contain the DDL to drop and recreate...then you don't have to
search for it when you need to perform the action.

---
Q: What is a good way to estimate the size of a database dump?
A: sp_spaceused, and look for the "data field."

---
Q: how do i tell what database objects may be affected by dropping a table?
A: sp_depends 

---
Q: How can i insert an image into a table?
A: Two methods (one feasable, one not)
- straight SQL: insert table(image_col) values (0xHEX_STRING) 
(where your image has been hex-encoded)
- Using Open Client calls in C/C++/perl/etc: ct_send_data() and ct_data_info()
functions handle image insertion.  see $sybase/sample/ctlibrary/getsend.c
for code examples.  The documentation goes into very severe detail.

---
Q: How do I encrypt sensitive data stored inside my Sybase database?
A: 
- dbcc hidetext: undocumented feature of dbcc?
- sp_hidetext; deliberately hides the code of stored procs (so that 
commercial vendors can install code w/o having it stolen)
- home grown encryption modules (crypt() function in perl and C, eg)

---
Q: I lost my sa password; how do I reset it?
A:
- su - sybase
- edit the RUN_server file add "-p" at the end of the dataserver line
- restart the server.  
This causes the system to reset the sa password and print it
out to the errorlog as the server comes back up.  Log in as sa with new password.

---
Q: How do I reset the sa password back to NULL once i've set it to something?
A: 
One known step:
sp_configure 'upgrade version' (take note of your current setting)
sp_configure 'upgrade version', 492
sp_password callers_pwd, NULL, sa
sp_configure 'upgrade version', orig_number

in 12.0: to set any account's password to Null;
sp_configure "minimum password length",0
sp_password ,NULL

---
Q: How do i generate a resource file for automating the creation of a Server
through srvbuildres (say, for the purposes of creating an identical server
on another machine?)
A: See $SYBASE/init/logs for the resource file created when you created
(if successful) your last Sybase server (backup, data, etc).

---
Q: What are issues to be aware of when installing Sybase on Linux?
A: 
- Some versions of Linux have a limit of 1024 file descriptors
- Some versions of Linux make creating Shared memory a kernel modification
and recompilation.
- No async i/o and now raw devices, making large scale SMP systems *very*
difficult to operate.
- Make sure Sybase user can write to $TMPDIR location if you have this
defined.  (su - sybase, cd $TMPDIR, touch test.file)

---
Q: How do I dump a database that's larger than 2gb (on systems which cannot
handle larger files?
A: 
dump database DB to "file1" at BACKUP_SVR
stripe on "file2" at BACKUP_SVR
stripe on "file3" at BACKUP_SVR
...

---
Q: Is there a way to determine the last time update statistics was run on a 
particular table?
A: 
- You can get creation date of Statistics by running optdiag.
- You can get the creation date of statistics per column, per table by
running select object_name(id), moddate from sysstatistics order by id

---
Q: Is there a way to determine the create date of an index?
A: 12.0 and below: No, there really isn't... 

- You can tell the creation date of any RI-causal indexes (constraints)
but not the last time they were re-issued.  Any clustered indexes or
non-clustered indexes: no way to tell.  
- You can tell the last time statistics were modified in 11.9.2 and higher
by using optdiag.  This is as close as you can get.  You can look at the
moddate in sysstatistics.

- 12.5 and above: Salvation!  indcrdate column added to sysindexes which
contains the date the index was last created or rebuilt!

---
Q: How do I find out the packet size a Server connection is using?
A: network_pktsz in sysprocesses

---
Q: Which versions of ASE for Linux support Raw devices?
A:
- 11.0.3.3#6 release supports raw, w/ kernel 2.2.14 or higher
- 11.9.2 release does not
- 12.5 will (a future release in Beta now)

---
Q: When is a "bug" not really a "bug"
A: (as paraphrased from an Eric Miner post 1/10/01 to Sybase-L) When an
optimizer inconsistency is reported to Sybase, but its known behavior
with workarounds.  There's not a bug per se, but behavior that could/should
be improved.  Always insist on 302/310 trace output.

---
Q: What do the segman values mean in Sysusages?
A: involves bitwise arithmetic
- 0:
- 3: data only
- 4: log only
- 7: data and log
- 11: ?? (used by sybsecurity)
- 27: ?? (used by dbccdb)

---
Q: How do you get a Stored Procedure to execute immediately after a 
server boots?  A Sybase "rc" stored proc?
A: ?? No known function within Sybase...perhaps an external program
called from the Start RC script?  Still not reliable..what if Sybase
server doesn't come up all the way?

---
Q: Whats a good script to use to age-off old log files?
A: This shell code snippet works w/o bouncing the Sybase server.  Code from
Raoul Bantuas , posted to Sybase-L 2/4/1998

errlog=$1 # name of error log, as parameter no. 1
cat $errlog > $errlog.`date +%b%d_%H:%M` # to keep an old copy
cat /dev/null > $errlog
find $errlog.* -mtime +7 -exec rm {} \;

---
Q: How do you "offline" a database?
A: pre 12.0, 
update sysdatabases
set status2 = 8
where dbname = "your database name here"

(to bring online again, you could try online database or the following query:

update sysdatabases
set status2 = status2 - 8
where dbname = "your database name here"
and status2 & 8 = 8

as of 12.0: offline database  

---
Q: Can you create functions in TSQL?  Ie. 
select a, myfunction(b,c) from tablename
A: no.  Not as of 12.0

---
Q: What does a "Stack Overflow error" mean?
A: Just as it sounds; an internal memory stack used by Sybase has been
corrupted somehow and has failed, causing the SQL execution to fail.

---
Q: How do i get online help for DBCC commands?
A: grant the sybase_ts_role to a useraccount, then run
dbcc traceon (3604)
go
dbcc help (cmd)
go

---
Q: Is it better to store images within Sybase image types or just store
pointers to the files?
A: I prefer pointers to files; it elminates the difficulty of putting
images into and out of Sybase, keeps the databas size down, and the O/S
is far more efficient at storing image files than Sybase.

---
Q: How do i decode the hex string of characters in the interfaces file?
A:
ex: \x00021d4cc0024a8a0000000000000000
\x0002: never varies
1d4c: port #
c0 02 4a 8a: IP addr
0000000000000000: trailing zeros

then run hextoint or some sort of hex-decode to figure out what each
number is.  Or just type in the hex characters to a calculator (in hex mode)
and then convert to decimal.

---
Q: What are all the steps i need to do to change a Server name?
A: 
1. Change the server name online (@@servername global variable)
sp_dropserver oldservername
go
sp_addserver newservername,local
go
this won't take effect til you reboot server
2. review all remote servers that may reference this server
3. Change interfaces file; search for existence of old server name and
replace.  Be careful if you edit it w/ vi on unix; the fields must be
tab-delimited
4. Change RUN_server file to start w/ correct name (-s flag)
5. Change SERVER.cfg file to NEWSERVERNAME.cfg

---
Q: Does a table scan give you a "free" update statistics on a table?
A: No.  Urban myth.  Proof; run optdiag on a table and note statistics
creation date.  Then run a table scanning query, re-run Optdiag and
compare dates.

---
Q: What is the maximum number of indexes you can create on a table?
A: 250: one clustered index max, 249 non-clustered indexes max.

---
Q: What is a good rule of thumb for your log size?
A: Of course this answer is "it depends."  It depends on the nature of your
activity, the type of transactions (large table wide updates will require
more log space than normal).  But, 20% of your dataspace is a good rule of 
thumb.  You can modify existing queries to use a rowcount and periodically
commit to clean out transaction logs during operations, and sp_thresholdaction
can assist as well.

---
Q: how do you tell what user has what permissions (grants) on a particular
object?
A: sp_helprotect and pass it an object or user.  This functionality is
much better done in GUI DBA tools.

---
Q: If a user has permissions on a View, do they need permissions on
the Underlying tables as well?
A: No.  This is widely considered a security hole...or
a feature, depending on who you talk to.

---
Q: Can you rename a column?  
A: sure: sp_rename table.column, new_column_name

---
Q: Can you change the datatype, or change from null to not null, a column?
A: 
- 11.9.2 and previous: not without selecting out talbe (see 2 q's down)
- 12.0 and greater: alter table modify column will work.

---
Q: Can you drop a column?
A: Yes; using the alter table drop column command.  however...this is
not supported in versions 11.5 and below; unknown for 11.9.2.  In v12.0 the
command is supported.   When you do this in unsupported versions, it leads
to very "strange" behavior in your server (in my experiences; corrupted
syskeys information was seen).  Better to create a new table, select data 
into the table, drop the old and sp_rename the new. (see next question)

---
Q: Whats the best way to add a column to a table?
A: Follow this process:
- create table_new with columns in the correct order (say t1,t2,t4,t3)
(I usually just take the old DDL and change the table name...this way
you preserve grants, indexes, PKs and FKs, etc)
- insert into table_new select t1,t2,t4,t3 from table
- sp_rename table, table_old
- sp_rename table_new, table
- drop table_old

this is the only way to add a NON-NULL column as well.  Couple caveats:
- Foreign Key constraints on this table (or referring to this table)
will make this process very difficult...you'll have to alter table drop
constraint on any referring tables before being able to drop the table_old,
and you'll have to physically rename the FK constraints on table_new
when you make it (b/c you can't have two constraints w/ the same name)
(a good reason to enforce RI through triggers and not constraints, if you
have to do table alterations like this all the time)

(you need select into/bulk copy turned on only if you're setting t4=value
onthe fly using select into new_table insert from old_table).

Three reasons i like to do things like this
- only way to add a non-null column
- aesthetics; often i put in "poor mans auditing" fields at the "end"
of a table (create_user, create_date, modify_user, modify_date) and
want to put relevant columns "before" these columns.  This is more important
to developers using GUI sql tools than me personally, but you pick your
battles.
- audit trail; if you just alter table add column, the create date
of the object doesn't change.  If you do it this way instead, you're
creating a new table and the crdate in sysobjects reflects this.  Nice
way to know when the last time a table modification was made.

---
Q: How can you tell what port your Sybase engine is listening on, from
within Sybase?
A: select * from master..syslisteners (you must have select permission on
this particular table...as in sa priv).  However, the syslisteners string
is in Hex, the same as the interfaces file is on Solaris (and some other)
boxes.  See prev. question for decoding a Hex string.  Or run:
select hextoint(substring(address_info, 17, 3)) from syslisteners

---
Q: How do i emulate Oracle's ability to "purge library cache" in a stored
procedure so as to force a re-optimization at each execution?
A: create proc with recompile option

---
Q: How do i write my own messages to the errorlog?
A: dbcc logprint("msg")

---
Q: How do I start my server w/o any transaction logging?
A: (never do this, but) add "-T699" to your dataserver line
in your Run_server file.

---
Q: What are isolation levels?  What does each mean?
A: As of 11.9.2, Sybase supports 4 levels of isolation (configured w/ the
set command, active for the life of the transaction/session).
- Ansi isolation level 0: Read uncommitted (Dirty Reads): no read locks; 
allows data to be changed underneath a scan.  
- Ansi isolation level 1: Read committed: (Default): shared read locks
- Ansi isolation level 2: Repeatable reads: prevents non-repeatable reads.
- Ansi isolation level 3: Serializable: forces a lock on selects.  Deadlocks
are frequent w/ this level.

---
Q: What kind of lock is a sh_intent lock (which is commonly created on
data when performing selects)?
A: A shared intent lock does not block other read-only selects to data
you may be selecting, but will prevent and block any attempt to get
an exclusive table lock that will include the data you are selecting.
This is standard behavior of Isolation Level 1 (Sybase default).

---
Q: What is an EBF?  What is an ESD?  What is the normal process for installing
and updating my Sybase distribution?
A: Sybase distributes main copies of its server product on burned CDs, then
(obviously) has to begin issuing bug fixes for things.  When installing these
distribution modifications I suggest tar -tvf the EBF, see what files it will
replace, then make backup copies of those binaries before extracting the EBF
in case it makes things worse (rare but possible outcome).

An ESD is an "Electronic Software Distribution" and represents the current 
stable version of the software which you should install immediately *after* 
installing the product off a distributed CD.  Its distinguished from EBF, SWR 
and One-offs because its made regularly available for download off Sybase.com 
(the others are only provided by Sybase Tech support).  Its essentially a SWR
made generally available.

An EBF is an "Emergency Bug Fix" and is actually a tar package that (usually)
replaces the main binaries of one particular Sybase product (in $SYBASE/bin 
dataserver, bcp, etc).    

SWR: Periodically, Sybase will collect all the bugs they've solved and
issue a "Rollup EBF" or a "SWR" (software rollup/release) which it recommends 
to install.  (Rollups are like kernel patches for Solaris...you never say you're
at Solaris 2.6, you say 2.6 Kernel patch xxx to indicate how uptodate you are.
Similarly, you say Sybase 11.9.2 rollup ebf 8376 or whatever).

ONE-OFF: At times, there is such a severe bug that Sybase issues a "One-Off"
EBF targeted for select customers experiencing a very specific 
emergency/critical bug.

Process: install from CD.  Obtain latest ESD.  Start working w/ the product.
If you run into errors, call Tech Support and possibly install EBFs.  If you
hit critical errors, install ONE-OFFs as provided.  Monitor for new ESDs and
regularly keep product up-to-date.

Note: ESD in Sybase also stands for an organization: Enterprise Systems
Division.  This causes some confusion from time to time.

--- 
Q: How can I get a list of all orphaned SQL processes?
A: Run this sql (in master)
1> select * from syslocks 
2> where spid in (select spid from sysprocesses)
3> go

---
Q: Can you have all numeric Sybase logins?
A: apparently not.

---


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
bcp specific

Q: How do I create a format file for my huge table? 
A: A quick trick for creating format files is to bcp out your table without
specifying ascii mode (-c) or a format file (-f).  Then, bcp will prompt
you for all the fields in the table and will create a format file for you
(defaulting to bcp.fmt).  Then, you can quickly Ctrl-C the process and use
the format file for other purposes (including bcping out in ascii mode).

---
Q: Can you bcp out of a view?
A: Bcping out of a view is a feature added in v11.5 (actually; introduced 
w/ Open Client 11.1, first shipped w/ Sybase 11.5).  If you have neither, 
you can use Scott Gray's sqsh to perform selects and return the result set 
in bcp format.

---
Q: What is the difference between "Fast" and "Slow" bcp?
A: 
- "Fast": non-logged; database must have bcp/select into set on, table must 
be w/o indexes OR triggers.  Even though the triggers are not fired, their 
existance causes the inserts to be logged.  In later versions Parallel bcp
is available, which can make it even "faster."  Note; re-creating the 
clustered index will be costly, but perhaps worth it.
- "Slower": w/ triggers, no indexes.  Logged but no index maintenance reqd.
- "Slow": w/ index:  logged, don't need bcp/select into set on.

---
Q: How do you bcp using pipes (logically, cat file | bcp db..table in...)
A: You must use an intermediate named pipe, like this:
% mkfifo MYFIFO
(or % mknod p MYFIFO)
% cat datafile > MYFIFO &
% bcp db..tbl in MYFIFO -Usa -Ppw -c

also:

$ cat datafile > $$ | bcp db..tbl in $$ -Usa -Ppw -c
$ rm $$

---
Q: If i have a 2gb file size limitation in my Unix OS, how do I bcp a 
table that will be greater than 2gb?
A: (thanks to Patrick Cain patrick.cain@ants.co.uk 4/9/01)
The easiest way I've found to do this is to use the parameters for
specifying first and last row (-F and -L).
- Use sp_spaceused to get the approx table size.
- Do a rowcount (ie. select count(*)) as the sp_spaceused rowcount isn't
always accurate
- Break into appropriate sized chunks and bcp out, eg.
    bcp table out file1.bcp -F1 -L 10000000
    bcp table out file2.bcp -F10000001 -L 20000000, etc

- Note: You should minimise the number of chunks you break it into as the 
server has to scan through the table to reach the first row (which takes some 
time when you specify row 60,000,000 as the first row).

---
Q: how do i query data in a particular partition?
A: you can't in SQL, but you can bcp table_name :partition_id out ...
and query the data in the bcp flat file (or create a temp table,
bcp a partition out and back into the temp table, and view the data
that way).

---
Q: how do i get old bcp v 10.0.4 behavior regarding null columns being
bcp'd as spaces in bcp v11.1.1?
A: bcp -Q option along with EBF #8376.  See tech_note #20439 in Sybase's
technical documentation site.

---
Q: Is there a setting in bcp to print the column names on the first
line of the output data file?
A: no.  However, you can automatically generate a format file as described
above to get the column names matching each column.  PLus, its obtainable
via a custom shell script wrapper to bcp.

---
Q: I'm constantly getting log full when I bcp in my huge file.  How do 
I fix this?
A: bcp -b flag.  -b == batch size.  Use 10000 or so.  This causes the
engine to commit the changes every 10000 or so instead of the bcp default
of 1000 rows.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
sqsh specific

Note: sqsh is Scott Gray's replacement for isql; its a MUST-HAVE for Dbas.

---
Q: Can you bcp out of a view?
A: Bcping out of a view is a feature added in v11.5 (actually; introduced 
w/ Open Client 11.1, first shipped w/ Sybase 11.5).  If you have neither, 
you can use Scott Gray's sqsh to perform selects and return the result set 
in bcp format.

---
Q: How do I set my prompt in sqsh to show line numbers if i'm customizing?
A: in your .sqshrc file:
\set prompt='[$histnum] ${DSQUERY}.${database}: ${lineno}> '

---
Q: How do I configure sqsh to have tab-completion on potential words?
A: populate a file in your home dir called .sqsh-words, one word or
phrase per line, with words you want completed.  Then put in a .sqshrc line:
\set keyword_completion=smart


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Performance/Tuning

---
Q: Whats a good way to tell where your I/O bottlenecks are in Stored Procs?
A: set statistics time on, set statictics io on within a SP, pipe the
output to one big file, then look for the larger read counts...you
should be able to get down to the line number.  (This is Joel Plotkin's
trick).


---
Q: What situations are best for using parallel processing?
A: Anything that requres massive reads: DSS applications, dbcc, index creation,
etc.  OLTP environments specifically should NOT use parallel processing.

---
Q: how do I disable triggers from firing if i'm reloading a table?
A:
v12.0 and later
alter table tablename disable trigger 
...then truncate tables or do whatever non-trigger firing you'd like
alter table tablename enable trigger

in v11.9.2 and below, you'll have to drop the triggers manually, then
re-create them afterward.

---
Q: What is a good way to measure the "time" it takes to run a query?
A:
- stopwatch method: encapsulate a query w/ "select datetime()" or date
commands in shell.  
- count cpu cycles?  how?

---
Q: What would be a good way to get a report that counts the number of
times in a given period a particular stored procedure was executed?
A: 
- Auditing
- using Monitor server and Historical server to do some logging.
- Sybase Central STP Monitor; realtime

---
Q: What are the standard commands to run to troubleshoot stored procs/sql?
A: 
dbcc traceon(3604,302,310)
set showplan on
set noexec on (or set fmtonly on if you're running a Stored Proc)
go
run your query here...  

---
Q: What are the different types of Fragemtation that can occur, and how
does one measure them?
A: (borrowed from David Cherin post to Sybase-L 8/8/00); Messy page chains, 
Extent interleaving, Unused Pages (on reserved extents), Text/Image and 
Wasted Page space.  To measure, use a 3rd Party tool or analyze some 
statistics (deleted rows, empty pages) and optdiag features (space
utilization).

---
Q: How can i tell if my table is a good candidate to convert to Row
Level Locking (RLL) in 11.9.2 or greater?
A: sp_object_stats (see Sybooks manual).  But, make sure to test your
changes...sometimes recommended tables will not necessarily be the best.

---
Q: is the 20% rule often quoted in Sybase circles valid? (the 20% rule says
the Optimizer will automatically table scan if returning more than 20% of
the table).
A: answer; no.  The optimizer will always search out the least i/o cost
solution by estimating the i/o requirements of resolving a particular
query and then comparing that to the known cost of a table scan.  

Of course, in practice it always seems to turn out that a normal query will
start to table scan all by itself when it reaches that 15-20% threshold.
How does a rumor become a rumor?  Because its based in truth?

---
Q: Why is it better to code your SQL to use "if exists" instead of 
"if not exists?"
A: Because "if exists" stops the select the first time it finds a row,
whereas "if not exists" must do a complete scan of the query before
making the determination.  Here's a good code example (borrowed from
Gene Trussell  to Sybase-L 2/14/01

--Want to find if there person has an address.  If not, then create it. 
if exists (select 1 from address where address_owner = @person_ID) 
begin 
   goto hasAddress 
end 
--Get address for this person from the full complete list. 
insert addresss (line1, line2, city, state) 
   select line, NULL, city, state 
      from bigCustomerTable 
      where person_ID = @person_ID 

hasAddress: 
--Now check other stuff 

However, subsequent posts showed that as long as the fields being 
queried were indexed, there is no performance difference.

---
Q: In 11.9.2 and above, if you bind the clustered index to a Named Cache,
does this in effect bind the table and all its data pages to the cache?
A: Nope:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aserefmn

"If you bind a clustered index to a cache, the binding affects only the
     root and intermediate pages of the index. It does not affect the data
     pages (which are, by definition, the leaf pages of the index)."

This makes sense; one of the complaints w/ 11.9.2 is that it changed the 
functionality of the clustered indexes a bit.  Clustered indexes are now 
treated intermally as non-clustered constructs: the leaf-level pages are 
no longer on the same pages as the data.

---
Q: What is the effect of modifying the configuration parameter "tcp no delay?"
A: If you turn this ON, the server will ship packets of data more quickly
and will not wait for a packet to be filled.  This will increase response
time but will increase network traffic.

---
Q: Does it matter what order I have the tables in my from clause or
the conditions I have in my where clause when it comes to selecting
an index?
A: No.  ASE ignores these things.  The only caveat is, ASE does not
consider all possible join orders when you're joining more than 4 tables 
(this # might have increased in 11.5 and above).  This is an often
misstated rumor; if you're joining 3 tables some developers believe the
where clauses must be "where a=b and b=c" instead of "where a=b and c=b."

---
Q: How many joined tables can Sybase handle at a time?  How many are
recommended/efficient?  How do you configure this parameter?
A: In 11.9.2 and below, the max was 16 simaultaneous tables.  In Sybase
12.0 the limit was increased to 50.  Sybase defaults to 4; you can
configure this with "set tablecount X."  Reports that increasing this
limit to 8 (which is an awful lot of tables in a join, if you think
about it) increases processing time a few seconds but nothing too major.
The default of 4 is considered most efficient but sometimes can be 
insufficent to a user's needs.

---
Q: Does it help the optimizer select an index by having more where clauses 
than necessary?
A: ??

---
Q: What is the fastest/best way to create an index on a table with many
millions of rows?
A: 
- bcp the data out to a flat file
- sort the data in the flat file based on the index columns (do a man sort
and look at the "-k" option on sort)
- truncate the main table
- bcp the data in
- create index w/ sorted option

---
Q: What is the effect of modifying the "Max async per engine" parameter?
A: ?

---
Q: At what string length point does the efficiency different cease to
exist when using char(n) versus varchar(n)?
A: ?? Unproven. I've heard people say 5, 10, 50 and 80 characters.  I personally
believe the break even point is at 10 characters, but as a personal design
issue.  (i'd never define a varchar(n) column containing anything less than 10).

Some points:
- char(n) are implicitly believed to be non-null.  Defining a char(n)
as null-able is the same as defining it as a varchar(n)
- char(n) are padded to the size of n.  varchar(n) are not padded and are
null terminated at the end of the data.
- the first varchar(n)/nullable string per row requires 5 bytes of overhead;
each additional requires 1 additional byte.
- nullable colums are about 30% less efficient than non-nullable columns.
- rows containing varchar(n) cannot be updated in place; less efficient.
char(n) updates are always faster.
- The break-even point for STORAGE considerations is data-dependent: take the average size and the max size of strings in a column; if they're
5 bytes or less difference, there's no storage consideration between using
char and varchar.  (this is based on the 5-byte overhead required for varchar)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Replication Server 

---
Q: What are some issues w/ Rep server that I've seen in the field?
A: 
- Can't add more than 1000 records at a time b/c Rep Server can't keep up.
Update; now threshold more around 10,000 rows with newer Rep Svr versions.
- Can't do a setuser command before migrating data b/c it confuses Rep Svr.


---
Q: How do I get a list of all the replicated DBs in a replication server env?
A: rs_helpdb and rs_helprepdb, or

- First log in to the SQL Server and issue a dbcc gettrunc command, 
if you see "1" in column 2 then db is replicated. 

- Log into associated RSSD server and use the associated RSSD database. 
Issue following query: 
select dsname, dbname from rs_databases where dsname = 'name_of_server' 
(use logical server name '%_LOG' for warm standby) 

---
Q: How to turn off a truncation marker on a Replicated database?
A: 
1> use name_of_db 
2> go 
3> dbcc settrunc (ltm, ignore) 
4> go 

---
Q: What changes do I have to make to my databases to enable them to be
Replication candidates?
A: each table must have a unique index.  You cannot have trunc log on
chkpt turned on.

---
Q: Do triggers fire on a replicated database, in the replicated server?
A: Beginning w/ Rep server 11.5, yes.

---
Q: How do I disable the triggers on the fly?
A: alter connection to server_name.db_name set dsi_keep_triggers 'off'

---
Q: What normal parts of a database system does Replication Server specifically
NOT replicate?
A: any database object in master or tempdb.  Beginning 11.5; database-level
operations (create table, create index, etc) will be replicated.

Float variables (because of
their approximate storage nature) can be problematic if you're using them
as search values since comparisons across primary-rep servers will
not always be exact.

---
Q: What special do I need to do to ensure that User defined datatypes get
replicated correctly?
A: No such known workaround, except to specify the underlying system
data type instead.

---
Q: What non-logged operations are Replicated and are Not Replicated?
A: Old answer was; NO non-logged operations were replicated.  Starting w/
Rep Server 11.5, the normally nonlogged "truncate table" command is
replicated.  However, other non logged operations (bcp and select into, eg)
are NOT repliated.

---
Q: How can i setup a low-cost "poor man's" replication environment?
A: From a post to ase-linux-list by dougs@envolved.com 11/29/00

I did this once (as a demo) completely using stored procedures. We used 
to call it "poor man's rep server." It worked but the risk was if the 
transaction rate was too high, the dump/load couldn't keep up with it. 
Of course the more frequent the dump/load tran, the smaller the files 
etc. There was a certain latency: the time it took to dump a transaction 
log and load it on the other side.  

Here's how it worked: Use a stored procedure to dump the log, then after 
each log dump finishes, the procedure immediately executes a load tran on the 
other side via a remote stored procedure call, then re-executes itself. On the 
remote side, there is a procedure to do a load tran which has to 
anticipate the exact file patch etc. The remote side procedure is 
controlled completely through the periodic remote stored procedure (RPC) 
calls from the primary. (Is that cool or what?) 

There were some special sp_dboption settings you needed to have on the 
receiving side: I believe it was "single user" and  
"no checkpoint on recovery". Ask Sybase tech support for details on how 
to set it up. They used to have a FAQ on it somewhere because it was a good  
way to maintain a logical copy on a different machine (most types of 
corruption  - esp. page linkage problems - cant be transmitted via load tran). 

Remember to leave a slight delay so that the t-log files can be 
transmitted across the network- If you dump to an NFS mounted remote 
file system this is automated as well. 

---
Q: What precautions are needed when replicating tables w/ Identity columns?
A: When doing replication with a multiple primary situation, you can 
'set identity insert on' in the repdefs to avoid collisions of PK.

---
Q: How do you confirm that Replication is working on a table (i.e., if a user
comes to you and says "table x is different on the primary and rep server")
A: Several possibilities
- rs_subcmp: easy for small tables: more difficult for large tables
- rowcounts: only if your data isn't dynamic
- checksums: same issue as rowcounts
- Check for skipped transactions
- Create a stored proc that does rowcounts and stores them in non-replicated
tables; check the results.
- Auditing


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-