Conference ulysse::rdb_vms_competition

1279.0. "Internet comp.databases.sybase Freq. Asked Quest." by MSDOA::SECRIST (Behind the eight ball.) Mon Aug 16 1993 19:41

Article: 2443
Xref: jac.nuo.dec.com comp.databases.sybase:2443 comp.databases:8451 comp.answers:1599 news.answers:8019
Path: jac.nuo.dec.com!pa.dec.com!decwrl!ames!agate!howland.reston.ans.net!europa.eng.gtefsd.com!uunet!meaddata!news
From: [email protected] (David Pledger)
Newsgroups: comp.databases.sybase,comp.databases,comp.answers,news.answers
Subject: comp.databases.sybase Frequently Asked Questions (FAQ)
Supersedes: <[email protected]>
Followup-To: comp.databases.sybase
Date: 13 Aug 1993 12:47:38 GMT
Organization: Strategic Data Systems, Dayton, OH
Lines: 2196
Approved: [email protected]
Distribution: world
Expires: 26 Sep 1993 12:47:35 GMT
Message-ID: <[email protected]>
NNTP-Posting-Host: ibis.meaddata.com
Summary: This monthly posting contains a list of Frequently Asked Questions 
         about the Sybase Relational Database Management System (RDBMS).
Keywords: Sybase,RDBMS,FAQ,Frequently Asked Questions,isql,T-SQL
 
Archive-name: sybase-faq
Last-modified: 1993/08/11
Version: 1.0
 
 
                           S Y B A S E
 
              Frequently Asked Questions (FAQ), Version 1.0
 
 
Copyright 1993 by David W. Pledger.
 
	All rights reserved. Permission for non-commercial distribution is 
hereby granted, provided that this file is distributed intact, 
including this copyright notice and the version information above. 
Permission for commercial distribution may be obtained from the 
author. SHARE THIS INFORMATION FREELY AND IN GOOD FAITH. DO NOT 
DISTRIBUTE MODIFIED VERSIONS OF THIS DOCUMENT.
 
======================================================================
 
                  T A B L E   O F   C O N T E N T S
 
 
1.0	Introduction
 
  1.1	Summary of changes
 
  1.2	Posting Hints
 
  1.3	Introduction
 
  1.4	Acknowledgments
 
  1.5	Terms and Abbreviations
 
2.0	General Questions
 
    Question 2.0-1:  How can I get in touch with Sybase?
 
    Question 2.0-2:  What periodicals exist for Sybase?
 
    Question 2.0-3:  What's a good book about Sybase?
 
    Question 2.0-4:  Who are my local user groups and how can I get 
    in touch with them?
 
    Question 2.0-5:  Has anyone implemented a C++ class library for 
    Sybase?
 
    Question 2.0-6:  What are good front-ends for Sybase?
 
    Question 2.0-7:  Does Sybase support the X/Open XA interface?
 
    Question 2.0-8:  Does Sybase support ODBC (Microsoft Windows Open 
    Database Connectivity)?
 
3.0	Database Server
 
  3.1	Sybase ISQL
 
    Question 3.1-1:  How do I prevent isql output from wrapping around 
    at 80 columns?
 
    Question 3.1-2:  How do I send isql output to a file? The -o switch 
    doesn't work.
 
    Question 3.1-3:  Can I submit a multiline statement as input to 
    isql without creating a file with the commands in it 
    first>
 
    Question 3.1-4:  How do I prevent the password from being 
    displayed when someone does a UNIX *ps* command?
 
    Question 3.1-5:  I want to add some new features to isql. Does 
    anyone have the source code?
 
  3.2	Sybase Transact-SQL
 
    Question 3.2-1:  What exactly does sp_primarykey, sp_foreignkey, 
    and sp_commonkey do?
 
    Question 3.2-2:  I want to write a new system stored procedure 
    that gives me information not provided by the existing 
    stored procedures. How do I make these available to all 
    users like the provided stored procedures.
 
    Question 3.2-3:  How can I do a "row level select" (built-in "if" 
    function) without having to create a temporary table, 
    etc.?
    
    Question 3.2-4:  How do I use a table name as a parameter to a 
    stored procedure, which will then run a query on the 
    specified table?
    
    Question 3.2-5:  Can you change the definition of a table to 
    prohibit nulls once you've defined it to permit them?
    
    Question 3.2-6:  Is there a simple way to solve the Sybase 
    ""matching quotes"" requirement in a character field?
    
    Question 3.2-7:  How can I do a case-insensitive search?
    
    Question 3.2-8:  How do wildcards used for pattern matching work 
    in the context of the LIKE operator.
    
    Question 3.2-9:  How do I put a unique serial number on a table?
    
    Question 3.2-10:  Is it possible to explicitly lock a table in 
    Sybase to prevent other users from inserting into the 
    table for a very short time?
    
    Question 3.2-11:  Exactly when does a trigger fire?
    
    Question 3.2-12:  Is there an easy way within the server to 
    determine how many days are in the current month?
    
  3.3	Sybase Bulk Copy
    
    Question 3.3-1:  When using BCP to copy a database, is the copy 
    equivalent to the original in terms of performance?
    
    Question 3.3-2:  Can BCP load null dates?
    
  3.4	Sybase Backup and Recovery
    
    Question 3.4-1:  How can I dump more than one database to a single 
    tape?
    
  3.5	Upgrading the Sybase Server
    
    Question 3.5-1:  I'm upgrading from version <x> and/or operating 
    system <p> to version <y> and/or operating system <q>. 
    Any advice?
    
  3.6	Sybase Security
    
    Question 3.6-1:  What different mechanisms are there to control 
    Sybase security?
    
  3.7	Sybase Database Administration
    
    Question 3.7-1:  Why does the transaction log on the model 
    database keep filling up?
    
    Question 3.7-2:  Why does my transaction log fill up even when I 
    have allocated lots of space for it?
    
    Question 3.7-3:  Is there a way to trun off logging altogether? 
    How about putting the transaction logs on `/dev/null'? 
    How does tempdb avoid logging?
    
    Question 3.7-4:  Is there any reason not to have `truncate log on 
    checkpoint' turned on for the model database?
    
    Question 3.7-5:  Why doesn't the Sybase kill command work?
    
    Question 3.7-6:  What are some of the undocumented features of 
    DBCC?
    
    Question 3.7-7:  What are the trace flags used for and what are 
    some of the more common flags.
    
    Question 3.7-8:  Is there a way to accurately estimate how much 
    space a table and its indeces are going to take?
    
    Question 3.7-9:  Can I recover a database that comes up marked 
    `SUSPECT'?
    
    Question 3.7-10:  My database tables often get locked by the 
    client's hung workstation. Is there a way that I can 
    unlock those locked tables?
    
    Question 3.7-11:  Does the server sort order affect performance? 
    Is binary sort order the fastest way?
    
    Question 3.7-12:  Does Sybase have a memory limit?
    
  3.8	Sybase Performance Tuning
    
    Question 3.8-1:  How much overhead do variable size and NULL 
    columns require?
    
    Question 3.8-2:  How does the query optimizer work? Does the 
    ordering of tables in the from clause or the conditionals 
    in the where clauses affect the performance of the query?
    
    Question 3.8-3:  Can I force the optimizer to access tables in a 
    certain order or to use a particular index?
    
    Question 3.8-4:  Does dropping an index cause recompilation of a 
    stored procedure?
    
    Question 3.8-5:  Does the time for a select that yields 1000 rows 
    from a table of 10,000 differ much from the same select 
    when the table contains 100,000 rows?
    
  3.9	Sybase Network Issues
    
    Question 3.9-1:  How can I make Sybase talk to two separate 
    ethernet interfaces on our server?
    
    Question 3.9-2:  Can I use Sybase over PPP (Peer-to-Peer 
    protocol)?
    
4.0	Open Server
 
5.0	Open Client
 
    Question 5.0-1:  How can I use the Sybase Open Client with my C++ 
    code?
    
    Question 5.0-2:  Which C compilers is the DOS version of the Open 
    Client software compatible with?
 
6.0	APT
 
    Question 6.0-1:  Is it possible to place other visible fields on 
    top of invisible fields, or do I have to have big open 
    spaces?
 
7.0	DWB
 
8.0	Report Writer
 
9.0	Third Party Applications
 
9.1	User Interface Client Applications
 
9.2	Class Libraries
 
9.3	Other Miscellaneous Products and Tools
 
 
 
======================================================================
 
1.0  Introduction
 
======================================================================
 
1.1  Summary of changes
 
	This is the Sybase FAQ, version 1.0. This does not supercede any 
other version and is being issued as a draft. Updates will be posted 
monthly.
 
======================================================================
 
1.2  Posting Hints
 
Before posting to comp.databases.sybase, please consider that many 
people in Netland are reading News using an 80 column display. If you 
set your right margin to 75 it will make your article much easier to 
read for those people.
 
======================================================================
 
1.3  Introduction
 
	This is the first release of the Sybase FAQ. It undoubtedly contains 
typos, mistakes, and other misinformation. Comments and corrections 
are welcomed and encouraged. Please direct all comments to 
[email protected]. Include the phrase `Sybase FAQ' as the subject of 
your message. I will include your changes as appropriate and give 
credit where credit is due.
 
	The intent of this document is to answer many of the frequently asked 
questions about the various products that Sybase offers. The emphasis 
of this FAQ is on the database server, primarily because that is my 
expertise. I will include questions and discussion on Open Server, Open 
Client, and other topics as I receive pertinent information.
 
	This article is posted to the news group comp.database.sybase monthly. 
This article is also cross-posted to news groups news.answers, 
comp.databases, and comp.answers.
 
	Please send me a note if you have any particular topic you would like 
to see addressed or any comments on the content or organization of this 
document. I extracted much of this information from both personal 
experience and from posts which have occurred during the past year. I 
did not keep the name or email address of much of the information I have 
archived, so if you see something that you wrote, but weren't given 
credit for, drop me a line and I give you the proper credit.
 
	This FAQ is archived at the ftp site: 
 
straylight.acs.ncsu.edu:/pub/ sybase
 
	As an aside, you might want to refer to the newsgroup 
news.newusers.questions for translations of IMHO, IMO, BTW, wrt, 8-), 
etc.
 
======================================================================
 
1.4  Acknowledgments
 
	The following individuals have made significant contributions 
toward the compilation of this document.
 
Name			Email Address
---------------------------------------------
David Pledger			[email protected]
Tom Warfield			[email protected]
David Joyner			[email protected]
 
======================================================================
 
1.5  Terms and Abbreviations
 
	The following list contains terms and abbreviations that are used 
within this document. Reference the glossary, section 1.6 for a detailed 
description of the meaning of each of the items identified below.
 
APT - Application Programming Toolkit (Sybase Product)
 
dbid - Database Id
 
DDL - Database Definition Language (SQL Create & Index 
Statements)
 
DML - Database Manipulation Language (SQL Insert, Update, 
Delete, & Select)
 
DWB - Data WorkBench (Sybase Product)
 
EBF - Emergency Bug Fix
 
GAM - global allocation map
 
indid- Index Id
 
LFS - logical file system
 
LRU - least recently used
 
OAM - Object Allocation Map
 
objid - Object Id
 
PSS - Process Slot Structure
 
Rid - Row Id
 
Rollup - Collection of bug fixes issued as an upgrade release.
 
SPID - Server Process Id
 
sproc - Stored Procedure
 
SQR - Structured Query Report Writer (Sybase Product)
 
STS - Sybase Technical Support
 
T-SQL - Transact SQL, Sybase's version of SQL with extensions.
 
vdevno- virtual device number
 
======================================================================
 
2.0  General Questions
 
---------------------------------------------------
Question 2.0-1:  How can I get in touch with Sybase?
 
Answer:    
 
Corporate Address
	Sybase, Inc.
	6475 Christie Avenue
	Emeryville, CA 94608
	Phone:
 
Dial Up Service:
	INSIGHT 1-510-601-4991.
	To register, dial up that number with your computer/modem and have 
your company number handy. Your contact id helps. Type "new" at the 
login prompt to create a new account.
 
Support Renewals
	1-510-596-4524
 
Customer Service
	1-510-596-3333. This is the main customer service line. They 
can answer or direct any non-technical, non-support renewal 
questions and expedite service.
 
---------------------------------------------------
Question 2.0-2:  What periodicals exist for Sybase?
 
Answer:    The following magazines are either Sybase specific or related 
to relational database design.
 
SQL Forum
PO Box 240
Lynnwood, WA 98046-0240
Phone (206)382-6607
Published bi-monthly (6 issues yearly)
us$60/year.
 
The Relational Journal
Codd & Date, Inc.
1772A Technology Drive
San Jose, CA 95110-1306
Phone: (408) 441-6400
Published bi-monthly
us$249.00/year
 
---------------------------------------------------
Question 2.0-3:  What's a good book about Sybase?
 
Answer:    Consider the following texts.
 
A Guide to Sybase and SQL Server
McGoveran and Date
Addison Wesley Publishers
 
Sybase Architecture and Administration
John Kirkwood
Ellis Horwood Publishers
ISBN 0-13-100330-5
 
---------------------------------------------------
Question 2.0-4:  Who are my local user groups and how can I get in touch 
with them?
 
Answer:    There are a number of groups in different areas of the 
country, some of which include...
 
BAWASLUG: Baltimore / Washington Area Sybase Local User's Group
Meets Quarterly
Contact:
 
---------------------------------------------------
Question 2.0-5:  Has anyone implemented a C++ class library for Sybase?
 
Answer:    A class library in this context provides a mechanism for 
allowing an object-oriented language such as C++ to access and 
manipulate database objects. Some of these class libraries provide an 
abstraction of multiple databases, such as Oracle, Ingres, and Sybase, 
to provide a single library of routines to access all of these different 
products.
 
See the archive ftp.acs.ncsu.edu:/pub/sybase++ for info. Section 9.2 
below, also provides sources of commercial products that have 
implemented database class libraries.
 
---------------------------------------------------
Question 2.0-6:  What are good front-ends for Sybase?
 
Answer:    See section 9 of this document.
 
---------------------------------------------------
Question 2.0-7:  Does Sybase support the X/Open XA interface?
 
Answer:    Currently, Sybase does not support the X/Open XA interface. 
You cannot use it with either Encina or Tuxedo for global transaction 
management in the X/Open DTP environment. System 10 is supposed to be XA 
complient.
 
However, you CAN use it with TOP END, NCR's TP Monitor. TOP END's XA 
Veneer Technology allows Sybase's non-XA compliant DBMS product to 
participate in global transactions in an X/Open DTP environment. This XA 
Veneer DOES make use of Sybase's two-phase commit feature.
 
Ray Niety, [email protected]
 
---------------------------------------------------
Question 2.0-8:  Does Sybase support ODBC (Microsoft Windows Open 
Database Connectivity)?
 
Answer:    Yes, but you may need to install additional stored procedures 
in the master database to get it to work. If you are running Sybase 
under Novell, these come pre-installed.
 
======================================================================
 
3.0  Database Server
 
======================================================================
 
3.1  Sybase ISQL
 
---------------------------------------------------
Question 3.1-1:  How do I prevent isql output from wrapping around at 80 
columns?
 
Answer:    Use the -w switch.
 
---------------------------------------------------
Question 3.1-2:  How do I send isql output to a file? The -o switch 
doesn't work.
 
Answer:    Use the redirection symbol, ">", as in 
 
isql -i script.sql > results.sql
 
---------------------------------------------------
Question 3.1-3:  Can I submit a multiline statement as input to isql 
without creating a file with the commands in it first>
 
Answer:    Yes, try...
 
isql -Ulogin -Ppassword >outfile_name <<!
use database
go
select column
from table
where condition is true
order by column
 
go
!
 
This is referenced as a "here document" in most UNIX manuals. This will 
also result in the password being visible by anybody happening to do a 
`ps' command when the command is run.
 
---------------------------------------------------
Question 3.1-4:  How do I prevent the password from being displayed when 
someone does a UNIX *ps* command?
 
Answer:    Depending on the version of Sybase and the port, this may or 
may not already be supported. In the cases where it is not supported, 
several tricks have been used.
 
For those cases where the password shows up, try using the command line 
options -i and -o rather than the shell redirects (< and >). This is 
nice because the "Password:" prompt shows up to receive your input.
 
isql -U login -i input.sql -o output.out
Password: password
 
You can also put the password as the first line that isql receives from 
standard input.
 
isql -U logins >output.out <<EOT
password
 
use database
go
sp_help
go
quit
EOT
 
---------------------------------------------------
Question 3.1-5:  I want to add some new features to isql. Does anyone 
have the source code?
 
Answer:    David Joyner at NCSU has published a shareware version, 
called "dsql". It is available via anonymous ftp from 
straylight.acs.ncsu.edu:/pub/sybase
 
======================================================================
 
3.2  Sybase Transact-SQL
 
---------------------------------------------------
Question 3.2-1:  What exactly does sp_primarykey, sp_foreignkey, and 
sp_commonkey do?
 
Answer:    They register the key relationships in syskeys. They DON'T 
create indexes and they DON'T make Sybase automatically enforce 
referential integrity. the key relationships registered in syskeys may 
be used by a front-end product to infer the logical schema.
 
---------------------------------------------------
Question 3.2-2:  I want to write a new system stored procedure that 
gives me information not provided by the existing stored procedures. How 
do I make these available to all users like the provided stored 
procedures.
 
Answer:    All system stored procedures MUST start with the prefix 
`sp_'. Procedures starting with this prefix have two main properties (1) 
They are visible from all databases, and (2) They switch context to the 
local database when executed. For example, a reference to the sysusers 
table does not read from the master database, but from the local 
database.
 
Do NOT replace any of the existing stored procedures with procedures of 
your own design. Any upgrade which runs the `installmaster' script will 
delete and overwrite your change.
 
---------------------------------------------------
Question 3.2-3:  How can I do a "row level select" (built-in "if" 
function) without having to create a temporary table, etc.?
 
Answer:    This is a hint from [email protected] (Tony Langdon 
FISD), found in SQL Forum magazine:
 
I'm trying to create a view on a table selecting one oftwo fields 
depending on the value of a third e.g...
 
select field1 from table where field3 = 1
union
select field2 from table where field3 = 2
 
If field1 & field2 are integers then this will work
 
SELECT ISNULL( field1 / (1 - ABS (SIGN (field3 - 1))), ISNULL( 
field2 / (1 - ABS( SIGN( field3 - 2))), <other value>))
FROM table
 
The function (1 - ABS( SIGN(x - y))) is fuction which will return 1 
if x = y and 0 otherwise. Division by zero returns NULL hence the 
argument to the ISNULL function is returned.
 
Denoting (1 - ABS( SIGN(x - y))) as eqfn(x,y).
 
The solution where the fields are characters is more complicated.
 
SELECT SUBSTRING(field1 + field2,
ISNULL(1 / eqfn( field3, 1), datalength(field1) + 1),
ISNULL( datalength( field1 ) / eqfn( field3, 1 ), datalength( field2 )))
FROM table
 
only this will return field1 if field3 is 1 and field2 otherwise. It 
can be modified to return a different value if field3 is not 1 or 2.
 
Allthough this uses division by zero to get a NULL value and an 
error will be displayed in SQL, this does not affect the results of 
the query.
 
To the best of my knowledge it does not set @@error and the division 
by zero error is returned, to an open client app, as if it was an SQL 
print statement.
 
---------------------------------------------------
Question 3.2-4:  How do I use a table name as a parameter to a stored 
procedure, which will then run a query on the specified table?
 
Answer:    You can't; also you can't do "dynamic queries". However, you 
might want to try using sp_rename to "fool" Sybase, as suggested 
(although not wholeheartedly recommended) by 
[email protected] (Robert N Thomas) [this won't work with 
temporary tables, though]:
 
1.  Create a view of each table you will want to access as a 
parameter.(this will allow other sessions to continue accessesing the 
tables without interruption).
 
2.  Set the permissions on the views so that NOBODY can access them. 
Only through the MAGIC stored procedure is access granted to the views.
 
3.  Figure out how to declare a section of your stored procedure as 
critical, so that only one sybase process can access the below code at 
one time.
 
4.  Setup the procedure to look something like:
 
 CRITICAL (I forget the exact command).
 sp_rename inuse, @vartable
 select * from inuse
 sp_rename @vartable, inuse
 END CRITICAL portion
 
---------------------------------------------------
Question 3.2-5:  Can you change the definition of a table to prohibit 
nulls once you've defined it to permit them?
 
Answer:    No, but you can prevent NULLs using triggers. A trigger can 
use the `IS NULL' test to check if any column has a NULL value. A RULE 
will not work. The rule check is not executed against columns that 
contain a NULL value.
 
---------------------------------------------------
Question 3.2-6:  Is there a simple way to solve the Sybase ""matching 
quotes"" requirement in a character field?
 
Answer:    An application program can use the dbsafestr() call, which is 
part of DB-Library. This routine will double any and all quotes in a 
character string, making that string "safe" for inclusion within any SQL 
statement.
 
---------------------------------------------------
Question 3.2-7:  How can I do a case-insensitive search?
 
Answer:    [Was there ever an answer to this one?]
 
---------------------------------------------------
Question 3.2-8:  How do wildcards used for pattern matching work in the 
context of the LIKE operator.
 
Answer:    This question is best answered with an example:
 
Given that table1 contains col1 and has the values
 
table1
----------
Bob
Ricky
 
The following query:
 
select * 
from table1 
where col1 not like '____'
will return "Ricky" and will NOT return "Bob", "Ricky"
 
Here's why:
 
1.  ["Bob" = "Bob "] is TRUE. This is a given, since ANSI says that in 
comparing two strings, the shorter string will be conceptually padded 
with blanks to equal the length of the longer string before comparing.
 
2.  If 1 is TRUE, then ["Bob" LIKE "Bob "] is also TRUE. Otherwise, a 
LIKE comparison would differ fundamentally from an EQUAL comparison.
 
3.  ["Bob" LIKE "___"] and [" " LIKE "_"] are both TRUE, by Sybase's 
definitions of the wildcards.
 
4.  By 2 and 3, ["Bob" LIKE "Bob_"] is TRUE.
 
Therefore, ["Bob" LIKE "____"] is TRUE, and ["Bob" NOT LIKE "____"] is 
FALSE. The query should NOT return "Bob", because the string has been 
extended with blanks to pad it out to the length of the "longer" 
(pattern) string.
 
To select all names of NOT EXACTLY 4 characters, use
 
 NOT LIKE "[^ ][^ ][^ ][^ ]"
 
This pattern string will match ONLY non-blank characters, so the query 
will fail to match all strings with blanks in them ("Bob ") as well as 
all strings longer than 4 characters ("Ricky").
 
 -- Elton Wildermuth, Sybase SQL Server Development
 
---------------------------------------------------
Question 3.2-9:  How do I put a unique serial number on a table?
 
Answer:    [email protected] (Michael Keirnan) writes:
 
	Create a reference table . . . with one row (I've also heard them 
referred to as surrogate id tables). Create a stored procedure called 
something like get_next_id. This stored procedure increments the current 
id and returns, via a parameter, the new id. This of course is done 
inside a transaction, and the increment (UPDATE statement) should be 
done first. No trigger required. For example:
 
create table ID
(NextId int)
go
 
create procedure GetNextId
@SurrogateId int out
as
	/* Start a transaction */
	begin transaction
 
	/* Update the ID first to lock the table 
	** and block others from changing the value.
	*/
	update ID
	set NextId = NextId + 1
 
	/* Safe to select, others calls blocked. */
	select @SurrogateId = NextId
	from ID
 
	/* Commit the completed transaction */
	commit transaction
go
 
There is an important disclaimer to this method. This approach 
guarantees that all inserts into the table are single threaded and that 
concurrent inserts will never happen. Each request for an ID will be 
blocked and wait for any preceeding requests for an ID since the page 
containing the ID is locked. This could be a bottleneck for a multi-user 
system.
 
---------------------------------------------------
Question 3.2-10:  Is it possible to explicitly lock a table in Sybase to 
prevent other users from inserting into the table for a very short time?
 
Answer:    So far, the best solution that I have come across was 
supplied by Scott Elliott of AT&T Network Systems. I have implemented 
his method and it works fine. Here is what needs to be done:
 
declare @id
begin transaction
if not exists (select * from table HOLDLOCK where id=@id)
begin
		insert into table values (@id,...)
 	.
 	.
 	.
end
commit transaction
 
If two users execute this at the same time, the following will happen:
 
User 1 and User 2 acquire shared locks on the table. They will each hold 
these locks until the end of the transaction (thanks to the HOLDLOCK 
keyword). User 1 will request an exclusive lock of the transactions. End 
result: ONLY 1 USER HAS ADDED THE ROW.You should also include some code 
to check the return value of the transaction to see if it was rolled 
back due to deadlock. If so, repeat the request on the table in order to 
insert the new row. He will be blocked because User 2 still holds a 
shared lock! User 2 will now request an exclusive lock to insert a row 
also. He is also blocked since User 1 still holds his shared lock. We 
now have DEADLOCK!!!! Sybase will then choose a victim and abort one.
 
---------------------------------------------------
Question 3.2-11:  Exactly when does a trigger fire?
 
Answer:    A trigger will fire once per statement affecting the table 
(insert, update, and/or delete), even if NO rows are affected. It fires 
after the physical table has been modified. This gives rise to coding 
like:
 
create trigger happy_trails 
on the_range 
for update
as
 
define @rows_altered int
select @rows_altered = count(*) from inserted
if (@rows_altered = 0) return
....
 
This eliminates the expense of going through later trigger code which 
will have no effect. A similar method can be used if, for example, you 
want to allow only one row inserted per statement.
 
---------------------------------------------------
Question 3.2-12:  Is there an easy way within the server to determine 
how many days are in the current month?
 
Answer:    This solution comes from Elton Wildermuth at Sybase
 
Obtain the month number, M.
If (M = 2) /* February is a special case */
 
	Obtain the 4 digit year, Y
	if	((Y % 4 = 0) and
		((Y % 100 != 0) or
		(Y % 400 = 0)))
 
		days := 29
	else
		days := 28
else
	if (M > 7) /* If month is after "July" */
		M := M - 7 /* subtract 7 from month */
 
	days := 30 + (M & 1)
 
	/* Now, if month is odd, it has 31 days */
 
Why this works:
 
 31 30 31 30 31 30 31
 Ja -- Ma Ap My Ju Jy
 Au Se Oc No De
 
Suggestion: build this into a stored procedure, and call it; assign 
its return value to a variable. Give the procedure an optional 
datetime param, so that it can calculate days-in-month for a random 
date; let the date default to getdate(). Example:
 
create procedure get_days
	@days int OUTPUT,
	@date datetime=NULL
as
 
declare		@m int,
		@y int
 
if (@date is NULL)
	select @date = getdate()
 
select @m = datepart(mm, @date)
 
if (@m = 2)
begin
	select @y = datepart(yy, @date)
	if	(@y % 4 = 0) and 
		((@y % 100 != 0) or	(@y % 400 = 0))
		select @days = 29
	else
		select @days = 28
	end
else
begin
	if (@m > 7)
		select @m = @m - 7
	select @days = (30 + (@m & 1))
end
return
 
 
======================================================================
 
3.3  Sybase Bulk Copy
 
---------------------------------------------------
Question 3.3-1:  When using BCP to copy a database, is the copy 
equivalent to the original in terms of performance?
 
Answer:    Copying will remove the "holes" and compact the rows more 
contiguously than the original so that the copy will perform 
substantially better. bcp-ing into an empty table with indexes, or 
building the indexes after the data is all in WILL indeed fill in the 
gaps in the extent chains where rows had been deleted in the original 
source table. Rows are always compacted to have no spaces between rows, 
but pages can be right around half-full if many many deletes have taken 
place all over the table space. The easy way to "even the score" is to 
re-create the clustered index on the production table, if you can.
 
Unless you note a substantial difference between the reserved space 
given by sp_spaceused for both tables, the performance difference is 
typically not that great.
 
The best way to copy a database is to use DUMP DATABASE and LOAD 
DATABASE... it's just one operation, and produces an exact page-by-page 
copy of the original database, "spaces" and all.
 
Benjamin von Ullrich [email protected]
 
---------------------------------------------------
Question 3.3-2:  Can BCP load null dates?
 
Answer:    BCP can load null dates if there is nothing between the 
delimiters for the columns. If it encounters a space it converts that to 
Jan 1, 1900. Here is an example:
 
create table foo
 (seq_no int not null,
 date1 datetime null,
 date2 datetime null)
 
The following is the contents of a file that we are going to bcp into 
table foo. I am using a tilde to delimit columns and a tilde followed by 
a return (\n) as a row terminator.
 
1~ ~~
2~~ ~
3~~~
 
Now we use bcp with the delimiters specified above.
 
bcp foo in foo.dat -c -t~ -r"~\n"
 
Starting copy...
3 rows copied.
Clock Time (ms.): total = 37 Avg = 12 (81.08 rows per sec.)
 
Via isql let's look at the results.
 
1> select * from foo
2> go
 
 seq_no      date1                      date2
 ----------- -------------------------- --------------------------
 1           Jan 1 1900 12:00AM         NULL
 2           NULL                       Jan 1 1900 12:00AM
 3           NULL                       NULL
 
(3 rows affected)
 
======================================================================
 
3.4  Sybase Backup and Recovery
 
---------------------------------------------------
Question 3.4-1:  How can I dump more than one database to a single tape?
 
Answer:    Tell Sybase that the tape device is really a disk.Declare the 
tape /dev/nrst? as a "disk" device (sp_addumpdevice "disk", ...). Then 
successive dumps will follow each other on tape. Of course, you've got 
to maintain your own directory of what's on the tape. Use the "mt -f /
dev/nrst0 sta" to check. Or write your databases to files and write the 
files to tape using standard unix commands.
 
Note that this is commonly practiced, BUT here is what one article 
originating from Sybase stated...
 
Please save yourself a lot of grief and don't do this. The various 
platforms handle tapes in slightly different ways and the various Sybase 
server ports make slightly different attempts to work around this. On 
some platforms the above suggestion will work, but on some other 
platforms, you overwrite your dump, and on yet others, it just fails. 
Worse yet, from OS release to OS release, and Sybase release to Sybase 
release, the behavior of any specific platform can change.
 
The point of doing dumps is that you know your data is safe. If you are 
doing something that is "not really supported", then how do you know 
your data is safe? If you don't care if your data is safe, save even 
more tapes and don't do dumps at all.
 
None of the above is meant to imply that the Sybase dump mechanism is 
better or worse than any other possibility. However, it is the mechanism 
Sybase provides and supports. 
 
David Gould, [email protected]
 
And one final word, Sybase System 10 includes a Backup Server, which 
will handle this problem.
 
======================================================================
 
3.5  Upgrading the Sybase Server
 
---------------------------------------------------
Question 3.5-1:  I'm upgrading from version <x> and/or operating system 
<p> to version <y> and/or operating system <q>. Any advice?
 
Answer:    The answers vary from "No problem" to "You're doomed". Since 
this type of question comes up often, perhaps it should have a sub-FAQ?
 
 
 
======================================================================
 
3.6  Sybase Security
 
---------------------------------------------------
Question 3.6-1:  What different mechanisms are there to control Sybase 
security?
 
Answer:    The following summarizes techniques to control security with 
SQL Server that I have received from various sources plus some comments 
of my own. My concern was how to control updates to a database in an 
environment with end-user "query"" tools that include update 
capabilities (e.g. Pioneer, Q+E, Microsoft Access). I want to especially 
thank those who responded to my question. All responses where useful.
 
       There are four fundamental methods, each described in more detail below: 
(1) adopted authority; (2) login ID; (3)gatekeeper; (4) triggers. All 
techniques are premised on fundamental security features of user 
authentication and grant/revoke permissions to resources. Each 
technique has a cost and must be weighed against the risk/benefit.
 
	Where available, references are cited. Particularly useful for those 
with access to CompuServe are Microsoft's Knowledge Base (MSKB) and 
Microsoft's Software Library (MSL).
 
1.  Adopted authority.
 
	This seems to be the most common approach making use of SQL Servers 
authority checking structure -- if the owner of an object (stored 
procedure or view) has necessary authority to all underlying objects, 
then authorized users of this object have the same authority. All 
updates are done via stored procedures owned by a user with update 
authority to underlying objects. Users are granted authority to stored 
procedures but do not have update authority to any tables. As Michele 
Sherry wrote, ". . . encapsulate your database as much as possible using 
views and stored procedures."
 
	The assumption here is that users are not aware of stored procedures 
since they will only be used by application programs for database 
maintenance. It is possible to "hide" the stored procedures; possibly in 
a totally different database or see comment below by Robert Thomas. 
Still the knowledgeable user could find them and execute them. 
Programmers might object to the use of stored procedures versus direct 
use of SQL.
 
	In the case where applications are written using end-user tools such as 
Q+E and Excel, Robert Thomas describes hiding the calls to stored 
procedures using DDE or DLL calls; possibly using a special password as 
a parameter to the stored procedure. He also recommends making sure 
sp_helptext for these procedures return nothing. He sometimes uses a 
technique of mixing DDE and DLL calls in which in the middle of a DDE 
conversation he establishes a temporary second login to SQL Server for 
update purposes using DLL calls.
 
	See MSKB article Q47270: "INF: SQL Access Permissions and Trigger 
Execution" which describes SQL Server adopted authority structure using 
triggers. However, this concept applies as well to stored procedures and 
views.
 
2.  Login IDs
 
	The basic idea is to use different login IDs for update and query use. 
The trick is to keep the one for update hidden and unobtrusive. There 
are several techniques for doing this with varying degrees of 
sophistication.
 
	Maintenance applications could use a single special ID and password that
allows update privileges; while normal user IDs have read only 
authority. This is based on the assumption that access to applications 
is controlled and that steps are taken to make sure the special login 
does not become common knowledge. One problem is that it is difficult to 
tell who is logged onto the database since they all use the same ID.
 
	Lawrence Bertolini wrote with a table driven variation of the above. A 
special login is used by an application to access, of course, a special 
table. This table cross references a normal login ID to a special login 
ID. Once the special ID is located, the application logoffs and then 
back on using the special ID. For example, my normal ID might be "seth" 
but my special ID might be"seth_12x9t". Again, normal precautions must 
be taken to ensure that this scheme is not compromised.
 
3.  Gatekeeper
 
	The most sophisticated approach is to control all logins with a custom 
written front-end gatekeeper to SQL Server. All requests to SQL Server 
must pass through this program which can determine the privileges needed 
by the requester. The action taken by the program is flexible. Two 
possibilities are to use the two login ID approach as above or to 
analyze each request rejecting those that are not acceptable (e.g. 
update from Q+E). This approach also allows maintaining an audit trail 
of SQL Server logins and requests. The key issue is authenticating the 
requesting program. The login ID can be authenticated using the native 
operating system security.
 
	This technique is described in Microsoft's "Open Data Services User's 
Guide" as the SECURE application. Manual and source are included with 
SQL Server 4.2. It is also described in MSKB article Q79958: "INF: ODS 
Security and Auditing Application". Source for SECURE42 should be in MSL 
as "S13264" however it is missing as of this writing but I am told it 
will be added within a week or two. This program requires Microsoft's 
SQL Server Programmer's Reference for C.
 
4.  Triggers
 
	This technique places update control logic inside triggers associated 
with each table. For example, the trigger could check a table to make 
sure the requesting application was authorized for updates. This 
technique is described in MSKB article Q66678: "INF: Providing 
Application Security Through Triggers in SQL". Obviously a trigger needs 
to be written for each table however the update check could be placed in 
its own stored procedure and work for all tables.
 
5.  Other ideas
 
	Possibly an obvious answer: don't provide tools for ad hoc queries that 
include update capabilities. It seems in the personal computer arena 
this is unrealistic.
 
	Another option is physically separate database for update and ad hoc 
query. There is a fair amount of overhead but actually might work well 
where performance is critical for maintenance transactions.
 
Disclaimer:
 
	I have tried to present the above information as accurately as possible 
including citations. However, I leave it up to you to verify the 
information and determine its correctness and applicability to your 
needs.
 
Provided by: Seth Siegal, [email protected]
 
======================================================================
 
3.7  Sybase Database Administration
 
---------------------------------------------------
Question 3.7-1:  Why does the transaction log on the model database keep 
filling up?
 
Answer:    Up to release 4.8, SQL server stored tempdb's next object_id 
in the log of the model database.I don't remember exactly why this was 
necessary, but i think it has something to do with avoiding re-issuance 
of object_ids that may be in stored procedures and/or transaction logs 
of all server databases. Since model is copied into tempdb at boot time, 
it seemed logical to store the next object id in model. All that was 
logged was a 4-byte integer, so it could take months for the log in 
model to fill up. This problem was fixed in version 4.8 . The next 
object id is now stored elsewhere.
 
---------------------------------------------------
Question 3.7-2:  Why does my transaction log fill up even when I have 
allocated lots of space for it?
 
Answer:    Due to the sequential nature of the log, only the inactive 
portion of the log may be truncated by any DUMP TRANSACTION command. The 
inactive portion of the log runs from the "beginning" to the page which 
has the BEGIN XACT record for the oldest *active* (uncommitted) 
transaction. Pages which follow this oldest active transaction in the 
log are considered active for the purposes of DUMP TRANSACTION, since 
they may depend on changes made (yet to be committed or still to be 
rolled back) by this transaction. Recovery (at LOAD TRAN or system 
startup time) replays transactions as committed or rolled back in the 
exact order in which they appear in the log, so portions appearing in 
the log after an uncommitted transaction may not be removed.
 
The implication here is that given a large enough or long-running enough 
transaction, one can hold up the entire log (from dumping, not from 
continued logging!) while the transaction is still pending. If your log 
fills up, and you have a very old transaction that started at the 
beginning of the log, no DUMP TRAN command can or will clear it until 
the transaction COMMITs or is ROLLed BACK.
 
The only things you can do in this case are:
 
1.  ALTER DATABASE to add more space to the log, hopefully allowing 
enough space & time for your old transaction(s) to commit (find that 
user who typed BEGIN TRAN ... UPDATE/INSERT/DELETE ... and the went to 
lunch!). KILL the long-running process/transaction.
 
2.  Shut down the sql server to terminate the long-running/old 
transaction.
 
These last two effectively terminate the transaction without a COMMIT, 
making it get rolled back upon recovery. This is a fairly drastic action 
to kill a process to clear a log.... if you can issue a COMMIT TRAN in 
the open session, or type ^C to abort from the same, please do so to 
achieve a cleaner and easier return to normal processing. 
 
Long-term, it is best to avoid long/log-intensive transactions. To begin 
with, break up large deletes into smaller parts by adding a where 
clause. This advice on use of a WHERE clause to break up DELETEs into 
chunks applies the same to UPDATEs, and similarly for INSERT.
 
If your problem transaction is to delete all rows in a table, consider 
using the TRUNCATE TABLE command. This command uses a minuscule 
proportion of log vs.a DELETE of all rows, as it merely logs the 
deallocation of pages for the table, not the image of every row deleted. 
For this reason, it is also MUCH faster than DELETE for most good-sized 
tables.
 
Benjamin von Ullrich, [email protected]
 
---------------------------------------------------
Question 3.7-3:  Is there a way to trun off logging altogether? How 
about putting the transaction logs on `/dev/null'? How does tempdb avoid 
logging?
 
Answer:    The transaction logs are an integral part of Sybase 
operations. It must be able to read from as well as write to the log 
device. This is why /dev/null won't work. 
 
What you can do is use `sp_changedboption dbname, trunc, true' to set 
the truncate log on checkpoint option to true. This will automatically 
clear out the log every minute or so. This is how tempdb works. Keep in 
mind that you have just prevented recovery from incremental transaction 
log dumps (dump tran) and that you can ONLY recover the database from 
the last full database backup (dump database).
 
---------------------------------------------------
Question 3.7-4:  Is there any reason not to have `truncate log on 
checkpoint' turned on for the model database?
 
Answer:    Since this database is the template for all databases at 
CREATE DATABASE time, setting this option on in model makes it be 
automatically set on for all new databases as they are created. Aside 
fro the simple fact that this may not be what you want on all new 
databases, if you are in the midst of a frenzied recovery of a major 
production database (say, in the middle of the day, while all your users 
are down), and you load your database backup, the first gift your clever 
option on model will give you is a truncated log in front of all of the 
transaction log dumps you were about to apply to bring the database you 
just loaded up to the time of failure. Truncating the log at any time 
between LOAD DATABASE and your last LOAD TRANSACTION blasts a hole in 
the log chain and halts the recovery operation then and there.
 
---------------------------------------------------
Question 3.7-5:  Why doesn't the Sybase kill command work?
 
Answer:    Killing a Sybase process will result in one of four 
reactions:
 
1.  The process is an ordinary retrieve transaction, i.e. SELECT, and it 
dies immediately.
 
2.  The process is an update transaction. It does not die until the 
server has rolled back the transaction. The time is directly related to 
the size of the transaction.
 
3.  The process is a DBCC transaction. Sybase forks a separate process 
for the transaction, and the new one is out of the users' control. DBCC 
checks tables index by index and can only be killed when it finishes one 
index and is ready for the next one. It may take anywhere from several 
minutes to four hours to die.
 
4.  The process is sleeping. We cannot kill a sleeping process. When an 
end-user process gets disconnected, we cannot kill the Sybase process 
and release the locks. To deal with this, we have installed an EBF 
(ebf989 for version 4.8 on Vax/VMS) to kill disconnected processes when 
our clients turn off thir PCs. Sybase claims that System 10 will provide 
an unconditional kill
 
---------------------------------------------------
Question 3.7-6:  What are some of the undocumented features of DBCC?
 
Answer:    There are a number of undocumented DBCC options that tech 
support uses to analyze your database. Some of these are DESTRUCTIVE and 
tech support will not help you if you screw up your database using one 
of these commands. They can also tell what you have done. With that in 
mind, here is a summary of the DBCC commands:
 
Stay tuned, summary will follow in next release.
 
---------------------------------------------------
Question 3.7-7:  What are the trace flags used for and what are some of 
the more common flags.
 
Answer:    There are a number of trace flags that can be used.  An 
initial list follows:
 
dbcc traceon(3604)  redirects dbcc output to your screen rather than 
the console.
 
dbcc traceon(3605)  redirects dbcc output to the errorlog.
 
---------------------------------------------------
Question 3.7-8:  Is there a way to accurately estimate how much space a 
table and its indeces are going to take?
 
Answer:    FYI, lot's of people have asked for it, and here it is! the 
officially UNSUPPORTED stored procedure sp_estspace. It works under 
4.9.2, but I make no guarantees. What's it good for: estimating the size 
of tables and their indexes given an existing table and index schema.
 
Have fun.
 
**************************************************
Doug Smith Sr. Instructor
Sybase Professional Services, Northwest District
[email protected]
***************************************************
 
create procedure sp_estspace
/*	A procedure to estimate the disk space requirements of a table
**	and its associated indexes.
**	November 21, 1991
**	Written by Malcolm Colton with assistance from Hal Spitz
**	Modified by Jim Panttaja November 25, 1991
*/
 
	(@table_name	varchar(30)=null, /* name of table to estimate */
	 @no_of_rows	float = 1,	 /* number of rows in the table */
	 @fill_factor	float = 0,	/* the fill factor */
	 @cols_to_max	varchar(255) =null /* variable length columns for 
                                              which to use the maximum rather 
                                              than 50% of the maximum length */
	 )
as
 
declare @msg	varchar(120)
 
/*	Give usage statement if @table_name is null */
if @table_name = null or @no_of_rows = 1
begin
	print `Usage is:'
	print ` estspace table_name, no_of_rows, fill_factor, cols_to_max'
	print `where table_name is the name of the table,'
	print ` no_of_rows is the number of rows in the table,' 
	print ` fill_factor is the index fill factor (default = 0) ` 
	print ` cols_to_max is a list of the variable length columns for which'
	print ` to use the maximum length instead of the average'
	print `			 (default = null)'
	print `Examples: estspace titles, 10000, 50, "title, notes"'
	print ` estspace titles, 50000'
	print ` estspace titles, 50000, 0, null, 40'
 
	return
end
 
declare	@sum_fixed	int,
	@sum_var	int,
	@sum_avgvar	int,
	@table_id	int,
	@num_var	int,
	@data_pages	float,
	@sysstat	tinyint,
	@temp		float,
	@index_id	int,
	@last_id	int,
	@i		int,
	@level_pages	float,
	@key		varchar(30),
	@usertype	tinyint,
	@type		tinyint,
	@level		tinyint,
	@vartype	smallint,
	@more		bit,
	@next_level	float,
	@rows_per_page	smallint,
	@row_len	smallint,
	@length		tinyint,
	@index_name	varchar(30),
	@page_size	smallint,
	@page_K		tinyint,
	@index_type	varchar(20),
	@factor		float
 
select	@sum_fixed=0,
	@sum_var=0,
	@sum_avgvar=0,	
	@table_id=0,
	@num_var=0,
	@data_pages=0,
	@row_len=0,
	@sysstat=0
 
set nocount on
 
/* Make sure table exists */
select @sysstat = sysstat,
	@table_id = id
		from sysobjects where name = @table_name
		and uid = user_id()
 
if @sysstat & 7 not in (1,3)
begin
	select @msg = "I can't find the table "+@table_name
	print @msg
 
	return
end
 
/* Get machine page size */
select 	@page_size = low - 32
	from master.dbo.spt_values
		where type = `E'
		and number = 1
 
select @page_K = (@page_size +32) /1024
 
if @fill_factor !=0
	select @fill_factor = @fill_factor / 100.0
 
/* Create tables for results */
create table #results
	(name	varchar(30),
	 type	varchar(12),
	 level	tinyint,
	 pages	float,
	 Kbytes float)
 
create table #times
	(name 		varchar(30),
	 type	 	varchar(12) null,
	 tot_pages	float,
	 time_mins	float 	null)
 
/* Create table of column info for the table to be estimated */ 
select length, type, name, offset
	into #col_table
		from syscolumns
			where id = @table_id
 
/* Look up the important values from this table */
select @sum_fixed = isnull(sum(length),0)
	from #col_table
		where offset !< 0
 
select @num_var = isnull(count(*),0), 
       @sum_var = isnull(sum(length),0)
	from #col_table
		where offset < 0
			and charindex(name, @cols_to_max) > 0
 
select @num_var = @num_var + isnull(count(*),0), 
       @sum_avgvar = isnull(sum(length / 2),0)
	from #col_table
		where offset < 0
			and charindex(name, @cols_to_max) = 0
 
/* Calculate the data page requirements */
if @num_var = 0
	select @row_len = 4.0 + @sum_fixed
else
	select @row_len = 8.0 + @sum_fixed + @sum_var +@sum_avgvar + @num_var
				+ (@sum_var +@sum_avgvar) / 256.0
 
/* Allow for fill-factor if set to other than zero */
if @fill_factor = 0	
	select @temp = convert(float, @no_of_rows) * 
		( convert(float, @row_len) / convert(float, @page_size) )
else
begin
	select @temp = convert(float, @no_of_rows) / 
		(convert(float, @page_size) * convert(float, @fill_factor) )
 
	select @temp = convert(float, @row_len) * @temp
end
 
/* Now add in allocation pages */
select @temp = @temp +(@temp / 256.0)
 
select @data_pages = @temp + 1.0
 
if @data_pages < 8.0
	select @data_pages = 8.0
 
insert #results values 
	(@table_name, `data', 0, @data_pages, @data_pages * @page_K)
 
/* See if the table has any indexes */
select @index_id = min(indid)
	from sysindexes 
		where id = @table_id
			and indid > 0
if @index_id = null	/* We've finished if there are no indexes */
begin
	select @msg = @table_name + ` has no indexes'
	print @msg
 
	select name, type, level, 
		Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0) 
		from #results
 
	select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0)
		from #results
 
	drop table #results
 
	return
end
 
select 	@sum_fixed = 0,
	@sum_var = 0,
	@num_var = 0,
	@temp = 0
/* For each index, calculate the important variables
** use them to calculate the index size, and print it */
while @index_id != null
begin
	select @index_name = name 
		from sysindexes
			where id = @table_id
			and indid = @index_id
 
	if @index_id = 1
		select @index_type = `clustered'
	else
		select @index_type = `nonclustered'
 
	select 	@num_var = 0,
		@sum_var = 0,
		@sum_fixed = 0
 
	select @i = 1
 
	/* Look up each of the key fields for the index */
	while @i <= 16
	begin
		select @key = index_col(@table_name, @index_id, @i)
		if @key = null
			break
		else			/* Process one key field */
		begin
			select @type = type, @length = length, @vartype = offset
				from syscolumns
					where id = @table_id
					and name = @key
 
			if @vartype < 0
				select @num_var = @num_var + 1
			else
				select @sum_fixed = @sum_fixed + @length
 
			if @vartype < 0	/* variable:check if in @cols_to_max */ 
			begin
				if charindex(@key, @cols_to_max) = 0
				 select @sum_var = @sum_var + (@length / 2)
				else
				 select @sum_var = @sum_var + @length
			end
		end
 
		select @i = @i + 1	/* Get next key field in this index */
	end
 
	/* Calculate the space used by this index */
	if @num_var = 0
		select @row_len = 5 + @sum_fixed
	else
		select @row_len = @sum_fixed + @sum_var + @num_var + 8
 
	if @index_id != 1	/* add row id for nc indexes */
		select @row_len = @row_len + 4
 
	select @level = 0
 
	/* Allow for fill-factor if set to other than zero */
	if @fill_factor = 0	
		select @rows_per_page = @page_size / @row_len - 2
	else
		select @rows_per_page = @page_size / @row_len * @fill_factor 
 
	if @rows_per_page > 256
		select @rows_per_page = 256
 
	/* For clustered indexes, the first level of index is based on the 
	** number of data pages. 
	** For nonclustered, it is the number of data rows	*/
	if @index_id = 1
		select @next_level = @data_pages
	else
		select @next_level = @no_of_rows
 
	select @more = 1	/* Flag for end of index levels */
	while @more = 1
	begin
		/* calculate the number of pages at a single index level */
		select @temp = @next_level / convert(float, @rows_per_page)
 
		/* Add in a factor for allocation pages */
		if @temp > 200.0
			select @temp = @temp + (@temp /256.0) + 1.0
 
		select @level_pages = @temp
 
		insert #results values
			(@index_name, @index_type, @level, @level_pages, 
				@level_pages * @page_K)
 
		if @index_id != 1 and @level = 0 /* adjust NC non-leaf rows */ 
		begin
			select @row_len = @row_len + 4 
 
			/* Allow for fill-factor if set to other than zero */
			if @fill_factor = 0	
				select @rows_per_page = @page_size/@row_len - 2
			else
				select @rows_per_page = @page_size/
                                       @row_len*@fill_factor
			end
 
		if @rows_per_page > 256
			select @rows_per_page = 256
 
		select @next_level = @level_pages
 
		select @level = @level + 1
 
		/* see if we can fit the next level in 1 page */
		if @rows_per_page >= @next_level 
			select @more = 0
	end
 
	/* Account for single root page */
	if @level_pages > 1
		insert #results values
			(@index_name, @index_type, @level, 1, @page_K)
 
	/* Now look for next index id for this table */
	select @last_id = @index_id
 
	select @index_id = null
 
	select @index_id = min(indid)
		from sysindexes 
			where id = @table_id
			and indid > @last_id
end
 
select name, type, level, Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0) 
from #results
 
select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0)
from #results
 
drop table #results
 
drop table #col_table
 
return
 
 
---------------------------------------------------
Question 3.7-9:  Can I recover a database that comes up marked 
`SUSPECT'?
 
Answer:    Assuming that you have got a backup of the database files or 
partitions!!!!
 
1.  start the dataserver. watch the database come up "suspect"
2.  execute isql as "sa"
 > sp_configure "allow",1
 > go
 > reconfigure with override
 > go
 > update master..sysdatabases set status = -32768 
         where name='<suspect_db_name>'
 > go
 > use <suspect_db_name>
 > go (to verify that you can actually open the database!)
 > use master
 > go
 > dbcc save_rebuild_log (<suspect_db_name>)
 > go
 
* note: the above should recreate your destroyed transaction log. The 
setting of status=-32768 in master..sysdatabases means "bypass 
recovery", causing SQL server to ignore all of the conditions which are 
causing your database to come up suspect. 
 
Do not try this at home without a backup.
 
 > update master..sysdatabases set status=0
      where name='<suspect_db_name>'
 > go
 > sp_configure "allow",0
 > go
 > reconfigure
 > go
 > shutdown
 
3.  try to restart the SQL Server. watch to see if the database comes up 
OK. if it does, you dodged a bullet. count your blessings and take a 
database dump next time.
 
Lee McGee [email protected]
 
---------------------------------------------------
Question 3.7-10:  My database tables often get locked by the client's 
hung workstation. Is there a way that I can unlock those locked tables?
 
Answer:    The most common reasons for this kind of behavior is a PC 
client where the user in the middle of the query assumes he has had 
enough and reboots the PC. This will leave a sleeping process with all 
locks on the table being held as is. A kill command will not be able to 
kill this process since an attention cannot be raised on a sleeping 
process. The only way to get around this problem is to make sure that 
users do not reboot their machines in the middle of a query.
 
Also if you are using Q+E you might want to change cancel = 1 your 
qex.ini / qe.ini depending on the version of Q+E. This will force a 
dbcancel to be issued when the query window is closed. If a dbcancel is 
not issued then a call to dbclose is made. Most often than not the 
connection is not closed properly since there is pending data on that 
socket.
 
One other option is to set the keepalive parameter on the server machine 
to a fairly low value if this is a configurable parameter on your 
platform. The result of setting this option is that at the specified 
time frame if there is no response from the client socket the server 
will drop that process. This will clear all the locks that are being 
held by that process.
 
---------------------------------------------------
Question 3.7-11:  Does the server sort order affect performance? Is 
binary sort order the fastest way?
 
Answer:    Yes, binary sort order is fastest because no lookup is 
needed. Please keep in mind that sort order only has impact on 
operations that involve comparison of character data like creating 
indexes and evaluating qualifications on character values.
 
Sort orders are defined in .srt files found under in the character set 
directories. There are three values associated with each character. 
Looking at the character file defining the sort order, you can correlate 
those three values with the placement of that character in the file.
 
o	Primary sort value is determined by the line in the file.
 
o	Secondary sort value is determined by the position within the line.
 
o	Tertiary sort value is also dependent on the position of the character 
on the line.
 
Some examples from files in the iso_1 directory of a 4.9.1 installation:
 
dictionary.srt
==============
char=0x41,0x61,0xC0,0xE0,0xC1,0xE1,0xC2,0xE2,0xC3,0xE3,0xC4,0xE4,0xC5,0xE5
 ;A, a, A-grave, a-grave, A-acute, a-acute, A-circumflex, a-circumflex,
 ;A-tilde, a-tilde, ;A-diaeresis, a-diaeresis, A-ring, a-ring
 
char = 0x42, 0x62 ;letter B, b
 
With dictionary sorting, every "a" is sorted before every "b" and among 
different "a" values there is sorting based on the different secondary 
sort values.
 
 
nocase.srt
==========
char=0x41=0x61,0xC0=0xE0,0xC1=0xE1,0xC2=0xE2,0xC3=0xE3,0xC4=0xE4,0xC5=0xE5
 ;A, a, A-grave, a-grave, A-acute, a-acute, A-circumflex, a-circumflex,
 ;A-tilde, a-tilde, ;A-diaeresis, a-diaeresis, A-ring, a-ring
 
char = 0x42=0x62 ;letter B, b
 
With case insensitivity, "A" and "a" have the same secondary as well as 
primary sort order. That is denoted in the file by the equal sign 
between the two hex values for their encondings in the ISO 8859-1 
character set. The case insensitivity also applies to names in the SQL 
Server so you could not have two objects in the same database with names 
differing only in case, such as SuperBowl and SuperbOwl.
 
noaccent.srt
============
char=0x41=0x61=0xC0=0xE0=0xC1=0xE1=0xC2=0xE2=0xC3=0xE3=0xC4=0xE4=0xC5=0xE5
 ;A, a, A-grave, a-grave, A-acute, a-acute, A-circumflex, a-circumflex,
 ;A-tilde, a-tilde, ;A-diaeresis, a-diaeresis, A-ring, a-ring
 
char = 0x42=0x62 ;letter B, b
 
With no accent, any "a" is equal to another. This could be useful if an 
application searches on last names and the entry is not exactly correct, 
like an A-grave instead of A-acute. This sort order is new with the 
4.9.1. It is considered very useful by some European customers.
 
The only difference between the files nocase.srt and nocasepref.srt is 
the line "preference=true" in the latter. With preference, "A" is equal 
to "a". However, in the results of a query with ORDER BY on a character 
column, "A" will precede "a". This has important performance 
implications. An index on character data can not ensure values are 
already in the order you prefer and comparisons using tertiary sort 
values must be done in a worktable.
 
Robert Garvey [email protected] {sun, lll-tis, pyramid, pacbell}!sybase!robert
 
---------------------------------------------------
Question 3.7-12:  Does Sybase have a memory limit?
 
Answer:    Sybase has no memory limit. The typical problem with getting 
the memory you want on UNIX is due to the OS's insistence that there be 
enough swap space to accommodate the entire data space of a process at 
startup time.
 
UNIX doesn't want to give out memory it cannot in theory write 
*entirely* to disk at some point. Thus, when SQL Server asks for 16MB of 
memory, for example, unless you have that much swap space available, the 
request will be denied, and the server will live with less or abort. Run 
the utility your UNIX provides to tell how much swap is in use when you 
have this memory problem with SQL Server. If it varies a lot, consider 
putting your RUNSERVER command file in your system startup procedure, so 
SQL Server can start up when memory is most clear. If your swap space is 
often lacking in large amounts of space regardless of other system 
activity, you'll need to add more. The general rule is to have between 2 
and 3 times physical memory size in swap space.
 
Benjamin von Ullrich, [email protected]
 
======================================================================
 
3.8  Sybase Performance Tuning
 
---------------------------------------------------
Question 3.8-1:  How much overhead do variable size and NULL columns 
require?
 
Answer:    The Sybase Performance and Tuning class notes give the 
following information:
 
An additional 5 bytes are used if there are ANY variable length fields.
 
An additional 1 byte is used for each variable length field.
 
Therefore, if you have two variable length fields, you have an extra 
seven bytes per row. Also, note that any field defined as allowing nulls 
is treated as variable length.
 
---------------------------------------------------
Question 3.8-2:  How does the query optimizer work? Does the ordering of 
tables in the from clause or the conditionals in the where clauses 
affect the performance of the query?
 
Answer:    Normally, the ordering in the from clause and the where 
clause will not affect the performance of the query. The only time that 
it can have this effect is if there is more than one query plan that the 
optimizer estimates will take exactly the same time as the best plan. In 
this case, the optimizer will choose the first of these plans that it 
sees. The ordering in the from and where clauses will change which of 
these plans it sees first. Only in this case will the ordering affect 
the query plan.
 
This will affect the performance if some of these plans with identical 
cost estimates are significantly faster or slower than the others. This 
should not happen - the optimizer's cost estimates should reflect the 
true cost of running the query. But in practice, the optimizer sometimes 
has a bug or other problem that causes the cost estimates to be 
inaccurate.
 
So, for the ordering in the from or where clause to affect the 
performance, the following must be true:
 
- Two or more query plans have the same cost estimate, and this is 
the lowest cost estimate for the query.
 
- A bug in the optimizer causes one of these identical cost 
estimates to be significantly inaccurate.
 
Needless to say, these two things don't happen very often at the same 
time.
 
Jeff Lichtman at Sybase, [email protected]
 
---------------------------------------------------
Question 3.8-3:  Can I force the optimizer to access tables in a certain 
order or to use a particular index?
 
Answer:    Yes, if one of your problems is that tables are being 
accessed in the wrong order (the showplan is screwed up) then you can 
try the following:
 
set forceplan on
 
select . . .. 
from table_a a, table_b b, table_c c 
where . . .
 
set forceplan off
 
The 'set forceplan on/off' will tell the optimizer to access the tables 
in the order that they've been listed in the 'from' clause. Mind you, 
you have to make this determination as to which tables should come first 
in the list.
 
You can force the server to use a particular index by putting the index 
id ('indid' from sysindexes) in parentheses after the table name in the 
'from' clause, but I recommend reconfiguring the query so that the 
optimizer can figure out which index to use on its own. Usually you can 
specify enough relationships to "nudge" the optimizer the right way.
 
Steve Medin had these comments on this topic:
 
Force index is implemented by placing a number after the table name in 
the from clause. The number refers tothe index that will be used by the 
optimizer, where the clustered index is always (1) and the nonclustered 
indices are sequenced in the order of your DDL create index statements, 
or chronologically if you have several scripts that build your indices. 
The possibility that a nonclustered index will get out of sequence is 
fairly high, but this feature can be quite useful if the optimizer 
refuses to use the clustered index on a table and you have provided 
where criteria for all the index columns. To force use of the clustered 
index on you ORDERS table, try:
....
 
FROM ORDERS(1),
....
 
This, again, can be useful when you can make an assumption about a 
table's size and you would rather tablescan a tiny table than get a 
clustered index iteration on the larger table that will not use the 
clustered index.
 
Try these out with showplan and stats io on. If you're really daring, 
try putting them in live application code. when you call tech support, 
they will tell you to remove the statements and recreate the problem 
before they will open a case.
 
---------------------------------------------------
Question 3.8-4:  Does dropping an index cause recompilation of a stored 
procedure?
 
Answer:    Yes, dropping an index will cause recompilation of stored 
procedures which `touch' the indexed table. Adding an index, or updating 
statistics will NOT.
 
---------------------------------------------------
Question 3.8-5:  Does the time for a select that yields 1000 rows from a 
table of 10,000 differ much from the same select when the table contains 
100,000 rows?
 
Answer:    Table size would not be a factor iff you have a clustered 
index on the columns used to locate the SELECTed rows. Since clustering 
orders the rows by the columns which make up the index keys, we would 
locate the first data page where the key matches the qualification, and 
follow the page chain until the next key is encountered, and stop 
scanning. This all depends on the type of qualification, but this 
illustrates that a clustered index orders a table such that any part of 
it is just as locatable as any other, regardless of total size. B-trees 
properly maintained are never very deep, so index depth is never an 
issue in SQL Server.
 
Actually, you could also achieve like response time on a small vs. large 
table if the result columns of the query are covered by a nonclustered 
index. This is a poor way to accomplish this, however, since non-
clustered indexes on multi-million row tables take up a good deal of 
room, but this can be your only alternative if you are already using the 
clustered index for something else and can't change it.
 
Be careful not to add so much to the table if it is wide (has many 
fields, and/or many large character fields). Normalize out these "big 
text" fields to other table(s) that you only look at when you need to. 
some of the best performance gains can be had by having more rows per 
page.
 
Benjamin von Ullrich, [email protected]
 
======================================================================
 
3.9  Sybase Network Issues
 
---------------------------------------------------
Question 3.9-1:  How can I make Sybase talk to two separate ethernet 
interfaces on our server?
 
Answer:     You can have as many master entries in the interfaces file 
for the protocol/port combinations that you have. Simply add a new line 
for the alternate hostname assigned to the second ethernet port, e.g.
 
The interfaces entry was:
 
SYBASE
 query tcp sun-ether primename 2025
 master tcp sun-ether primename 2025
 console tcp sun-ether primename 2026
 debug tcp sun-ether primename 2027
 
And it now is
 
SYBASE
 query tcp sun-ether primename 2025
 query tcp sun-ether secondname 2025
 master tcp sun-ether primename 2025
 master tcp sun-ether secondname 2025
 console tcp sun-ether primename 2026
 debug tcp sun-ether primename 2027
 
The key on the server end is the master line not the query line.
 
---------------------------------------------------
Question 3.9-2:  Can I use Sybase over PPP (Peer-to-Peer protocol)?
 
Answer:    Yes. The PPP interface to your host is an extra interface 
with a new hostname. If you look in Sybase's interface file you'll see 
that you specify a hostname and a portnumber. This means that Sybase 
will listen to that particular portnumber on the interface that 
corresponds with the hostname specified in the interfaces file. This is 
probably your ethernet. Telnet and friends listen to ANYHOST, a special 
ip-address that translates to any interface that is up in the kernel.
 
The solution is simple and a bit Sybase version specific. First the 
hacks.
 
1.  ANYHOST is implemented as ip address 0.0.0.0. If you add a host ALL 
to your hostfile and use ALL as hostname in the interfaces file, Sybase 
will pass 0.0.0.0 as ip address to the kernel and listens to its 
portnumber on all interfaces.
 
2.  Some versions of Sybase appear to have the constant hostname 
NULLHOST built in. Principle the same as 1.
 
3.  Now the proper solution. I don't know which version you need. 
Probably at least 4.8. May also be platform specific. But you can add 
more than one tcp line to the interfaces file (See previous Question). 
You can duplicate the line for "master" for each interface you want 
Sybase to listen to (that is duplicate with the appropriate hostname).
 
[email protected] (Dave St.Clair)
 
======================================================================
 
4.0  Open Server
 
======================================================================
 
5.0  Open Client
 
---------------------------------------------------
Question 5.0-1:  How can I use the Sybase Open Client with my C++ code?
 
Answer:    Create a header file like the following and you're all set.
 
#ifndef _FIX_SYBASE_H
#define _FIX_SYBASE_H
#define COMPILE_STYLE CPP_COMPILE
 
extern "C"
{
#include "sybfront.h"
#include "sybdb.h"
};
#endif /* ifndef _FIX_SYBASE_H */
 
---------------------------------------------------
Question 5.0-2:  Which C compilers is the DOS version of the Open Client 
software compatible with?
 
Answer:    The Open Client was compiled using Microsoft C. The Borland 
C++ 3.1 compiler will not link properly with the Open Client.
 
======================================================================
 
6.0  APT 
 
---------------------------------------------------
Question 6.0-1:  Is it possible to place other visible fields on top of 
invisible fields, or do I have to have big open spaces?
 
Answer:    A general guideline is to NOT have hidden fields at all. They 
clutter and generally confuse the form itself. According to some sources 
(I can't lay my hands on them t this time) hidden fields slow form 
processing and require more overhead than performing the same functions 
within an APT procedure. ANYTHING you can do with a hidden field you can 
do in your .fpl files.
 
======================================================================
 
7.0  DWB
 
======================================================================
 
8.0  Report Writer
 
======================================================================
 
9.0  Third Party Applications
 
======================================================================
 
9.1  User Interface Client Applications
 
1.  APT
	Sybase, Inc.
	Comments:
 
2.  Gain
	Sybase/Gain Technologies
	Comments:
 
3.  Database WorkBench
	Sybase, Inc.
	Comments:
 
4.  JYACC JAM/DBi
	JYACC, Inc.
	116 John Street 
	-or- One Sansome St., Suite 2100
	New York, NY 10038 San Francisco, CA 94104
	800-458-3313 415-951-1070
	Comments:
 
5.  Uniface
	410-740-8745 -or- 510-748-6145
	Comments:
 
6.  Power Builder (Microsoft Windows only)
	Powersoft Corporation
	70 Blanchard Road
	Burlington, MA 01803
	617-229-2200
	Comments:
 
7.  Microsoft Access/Visual Basic
	Microsoft Corp.
	Comments:
	Windows 3.1
 
8.  DataEase
	DataEase
	Comments:
 
9.  Unify
	3901 Lennane Drive
	Sacramento, CA 95834-1922
	800-24-UNIFY
	Comments:
 
10.  Focus
	Information Builders, Inc.
	1250 Broadway
	New York, NY
	212-736-4433
	Comments:
 
11.  Q+E
	Pioneer Software
	Comments:
	Windows 3.1.
	Simple spreadsheet-like browser.
	Can be used as an OLE object.
 
12.  Superbase
	SPC Software
	Comments:
	Windows 3.1
	Complete database forms/report/application package.
	SQL link purchased separately.
	Can be used as an OLE object.
 
======================================================================
 
9.2  Class Libraries
 
1.  DBh++
	Rogue Wave
 
2.  C++ API
	Qualix email at [email protected]
 
3.  Persistence
	Persistence Software
 
======================================================================
 
9.3  Other Miscellaneous Products and Tools
 
1.  SybPERL
 
2.  SQL-BackTrack
	Qualix
 
3.  dbViewer
	Qualix
-- 
+==============================+=============================================+
| David W. Pledger             | S T R A T E G I C   D A T A   S Y S T E M S |
| [email protected]          |           PO Box 498, Springboro, OH  45066 |
| Custom Database Applications | Phone (513)748-2460, (800)253-5624 ext 2940 |
--

Article: 2444
Newsgroups: comp.databases.sybase
Path: jac.nuo.dec.com!pa.dec.com!decwrl!uunet!fi.gs.com!delacy!langdont
From: [email protected] (Tony Langdon)
Subject: Re: Row level locking ?
Message-ID: <[email protected]>
Sender: [email protected]
Nntp-Posting-Host: delacy.fi.gs.com
Reply-To: [email protected]
Organization: Goldman Sachs International Limited.
References: <[email protected]>
Date: Fri, 13 Aug 1993 12:29:43 GMT
Lines: 10
 
Sybase locks are applied at the page level and as a result multiple rows are
locked. If you require "row level" locking in a Sybase table then pad out
your rows (with extra columns) to make each page (normally 2k) contain only
one row.
 
Has anyone actually done this in a production system? What are your feelings
about it.
---
Email address: [email protected]