Nebula 1994 June

home *** CD-ROM | disk | FTP | other *** search

/ Nebula 1994 June / NEBULA_SE.ISO / Documents / FAQ / Sybase-faq < prev next >

Wrap

Internet Message Format | 1993-08-15 | 74.2 KB

Path: senator-bedfellow.mit.edu!bloom-beacon.mit.edu!news.kei.com!ddsw1!meaddata!news From: davidp@meaddata.com (David Pledger) Newsgroups: comp.databases.sybase,comp.databases,comp.answers,news.answers Subject: comp.databases.sybase Frequently Asked Questions (FAQ) Supersedes: <sybfaq_745104004@meaddata.com> Followup-To: comp.databases.sybase Date: 13 Aug 1993 12:47:38 GMT Organization: Strategic Data Systems, Dayton, OH Lines: 2196 Approved: news-answers-request@mit.edu Distribution: world Expires: 26 Sep 1993 12:47:35 GMT Message-ID: <sybfaq_745246055@meaddata.com> 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 Xref: senator-bedfellow.mit.edu comp.databases.sybase:2786 comp.databases:27841 comp.answers:1593 news.answers:11333 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 davidp@meaddata.com. 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 davidp@meaddata.com Tom Warfield twarfield@vnunet.uucp David Joyner nsysdbj@acs.ncsu.edu ====================================================================== 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, ray.nieto@sandiegoca.ncr.com --------------------------------------------------- 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 langdont@penny.fir.london (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 rthomas@hakatac.almanac.bc.ca (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: michael.keirnan@sybase.com (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 Benjamin.von.Ullrich@sybase.com --------------------------------------------------- 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, dg@sybase.com 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, ssiegal@hebron.connected.com ====================================================================== 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, ben@sybase.com --------------------------------------------------- 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 dougs@sybase.com *************************************************** 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 lmcgee@sgi.com --------------------------------------------------- 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 robert@sybase.com {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, Benjamin.von.Ullrich@sybase.com ====================================================================== 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, jeffl@sybase.com --------------------------------------------------- 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, Benjamin.von.Ullrich@sybase.com ====================================================================== 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). dave@exlog.com (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 info@qualix.com 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 | | davidp@meaddata.com | PO Box 498, Springboro, OH 45066 | | Custom Database Applications | Phone (513)748-2460, (800)253-5624 ext 2940 | -- +==============================+=============================================+ | David W. Pledger | S T R A T E G I C D A T A S Y S T E M S | | davidp@meaddata.com | PO Box 498, Springboro, OH 45066 | | Custom Database Applications | Phone (513)748-2460, (800)253-5624 ext 2940 |