专注于数据库与信息检索技术![sql9.com]

Sybase ASE FAQ List (Frequently asked questions)

2012-04-20

原文来自:http://www.sybaseteam.com/my-own-personal-sybase-ase-frequently-asked-questions-t-94.html

(note: LOTS of this also works for Microsoft SQL Server)

SQL Theory/Database Theory/ERD

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

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

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

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

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

- use stuff command to strip the final character

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

You can of course append strings to varchar and char columns easily:
update boss_test3 set col2 = col2 + 'fgh' where col1=1;

---
Q: Can I populate a variable with a where clause and call it dynamically:
1> select * from table
2> where @where_clause

A: No, not in straight T-SQL. You can use a case statement or an external
program wrapper to get around this.

Version 12.0 purportedly adds in the ability to call SQL dynamically through
the execute() function.

Note: no matter what the version, you can't use dynamic sql in the order by
clause.

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

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

---
Q: What are the row-size limitations in Sybase and other competitors?
A:
- Sybase pre 11.9.2: exactly 1962 bytes per row (86 bytes overhead)
- Sybase 11.9.2, 12.0: ~1962: exact number depended on your locking
scheme, data only or all pages, the overhead changed slightly per page.
- Sybase post 12.5: Configurable page sizes up to 16k pages increase the
row size. 16k - some overhead = ~15696bytes. Its probably enough that
you'd never hit it. Still defaults to 2K, just like in the old days.

(and remember, text fields do NOT count in row size calculation)

Competitors:
- Microsoft SQL Server v6.5 and below: 1962, since still using sybase's engine.
- Microsoft SQL Server v7.0 and up: 8060: Max allowed column size is a varchar(8000).
- Mysql: 64K
- Oracle 8i and below?: Somewhere around 4mb; they allow 999 varchar2(4000) fields max
- Oracle 9i: w/ increase of varchar2(x) limit to 4gb, 999x4gb or about 4TB.
Oracle defaults to 8K.
- DB/2, Informix, others: ??


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

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

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


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

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


---
Q: I've got a huge table (many millions of rows, many GBs) that I need to move
from one place to another temporarily. What's the best way?
A: methods
- bcp: very long (-F, -L options)
- insert into select from ...
- bcp w/ named pipes: no need for filesystem
- SQL Backtrack: object only dump; good option
- CIS: move table across network


---
Q: What kinds of joins are available in Sybase?
A: Regular and Outer. Must describe.


---
Q: What do the various ERD diagram notations mean? Dots, triangles, etc?
A: It seemingly varies, from ERD tool to tool. However; these are the basics:

- Table/Entity: rectangle w/ rounded corners
- FK relationship: line between two tables.
- Arrow on a FK line: In simplistic models, the arrow points at the PK/parent table.
- Crows feet: indicates a "one-to-many" relationship, with the table at the
crows feet side being the "many" table. Also can be interpreted as having
the crows feet "point" to the PK/parent table
- Perpendicular bar on FK line: indicates mandatory relationship: the FK table
MUST have a parent record
- Circle on FK line: indicates optional relationship: the FK table MAY have
a parent record.

---
Q: If you update a row that doesn't exist, will the engine insert it for you?
A: Nope. Though, this would be a nice feature (not necessarily default
behavior though; how about an insertorupdate statement).

---



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

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

open cursor_name
fetch cursor_name into @variable1, @variable2

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

fetch cursor_name into @variable1, @variable2

end

(cleanup)
close cursor_name
deallocate cursor cursor_name


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

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

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

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

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

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

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

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

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

---
Q: How do I get distinct instances of a group of columns, since you cannot
pass more than one column to the distinct() function?
A: You can do this:

select cola, colb
from table
group by cola, colb

or
select distinct cola, colb
from table

however you can't do any counts in there. (see next question)

---
Q; How do I get a count(distinct(cola,colb)) from a table?
A: To Get counts, you need to be trickier:
Options (from Sybase-L discussion 6/19/03, from Kevin Sherlock (kevin.sherlock@DEXMEDIA.COM)

Use a Temp table:
select distinct customer_name,postal_street_address
into #countme
from customers
select count(*) from #countme

Concatenate strings:
select count( distinct convert(char(50),customer_name) +
convert(char(50),postal_street_address)))
from customers

Slight of Hand: (my favorite, of course)
select count(sum(1))
from customers
group by customer_name,postal_street_address


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

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

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


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

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

open test
fetch test into @result

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

fetch test into @result
end

close test
deallocate cursor test

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


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

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

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

OR

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

output is limited to the first 400 bytes of text

---
Q: How do I count the number of characters in a string? Or, How can you
tell exactly how much data is actually entered into a large varchar() field?
A: select char_length(rtrim(COLUMN NAME)), COLUMN NAME from TABLE NAME
(you need the rtrim if the column is defined as not null). Or try
datalength(rtrim(column))

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

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

---
Q: How do I search more than the first 255 characters of a text field?
A: set textsize [desired size]

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

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

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

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

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


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

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

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

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

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

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

Note: PatIndex function - Returns the starting position of the first occurrence
of a pattern in a specified expression, or zeros if the pattern is not found,
on all valid text and character data types.
Syntax: PATINDEX('%pattern%', expression)

---
Q: Is there an equivalent to ISNUMERIC function in MS-Sql Server?
A: No, but you can code a function (from Brian Davignon on Sybase-L 4/3/02
select (1 - sign (patindex ("%[^0-9]%", isnull (@var, ""))))

If it returns 1, then at least one letter was found. 0 otherwise. However
this only covers strings that contain letters and numbers, not any special chars.
AND, it only can tell if something is a positive integer; can't handle negative #s.

---
Q: How about an equivalent to ISDATE in MS-Sql Server?
A: ???


---
Q: Is it better to use insert/update or readtext/writetext when working w/ text fields?
A: (from Sybase-L discussion 5/15/02)
- if using a client api, definitly use the built-in text writing capabilities
(ct_send_data(), or dbwritetext() etc).
- using straight writetext() function in t-sql is unlogged; will corrupt your
transaction log string.
- You can use regular insert/update statements for text fields, but are susceptible
to embedded odd characters (quotes, control characters, etc) invalidating your sql call.

---
Q: How can I get the last day in a month in a query, not worrying about whether
there's 28/30/31 days and/or a leap year? (emulating last_day(sysdate) in Oracle)?
A: Posted to Sybase-L by Fred Cathey (fcathey@cos.com) 11/13/02

declare @today datetime
select @today=convert(binary(4),getdate())
/* this convert strips off the time element */

select dateadd(dd,-(datepart(dd,@today))+1,@today)
/* This is the 1st day of the current month */

select dateadd(mm,1,dateadd(dd,-(datepart(dd,@today)),@today))
/* This is the last day of the current month */

OR in one line (as posted to Sybase-L 2/26/03 by ghanshyam kapadia (ghanshyam786k@yahoo.com)
select dateadd(day,-1, '1 ' +
datename(mm,dateadd(mm,1,getDate())) +', '
+datename(yy,getDate()))




---
Q: How do I get the last day of the current month?
A: Posted to Sybase-L by Dave Ellam (EllamDav@EXCHANGE.UK.ML.COM), and Paul Flint (flint@VEX.NET)


dateadd (dd, -1, dateadd (mm, 1, stuff (convert (varchar, @my_date, 106), 1, 2, '01')))
or
dateadd(dd, -1 * datepart(dd, @mydate), dateadd(mm, 1, @mydate))

---

Q: Does Sybase have a "between" operator like other databases for comparison?
A: Yes indeed. Between is inclusive.
select * from table where a between 1 and 10 is equivalent to
select * from table where a >= 1 and a <= 10

---

---
Q: What does the ^ function do?
A: Exclusive Or. XOR

---
Q: How do you convert a fraction (i.e. 5 1/4) to a numeric?
A: from Dave Ellam (EllamDav@EXCHANGE.UK.ML.COM) on Sybase-L 1/14/03

SELECT CONVERT (NUMERIC, SUBSTRING (FractionString, 1, CHARINDEX (' ',
FractionString))) + (CONVERT (NUMERIC, SUBSTRING (FractionString, CHARINDEX
(' ', FractionString), CHARINDEX ('/', FractionString) - CHARINDEX (' ',
FractionString))) / CONVERT (NUMERIC, SUBSTRING (FractionString, CHARINDEX
('/', FractionString) + 1, 255)))
FROM MyTable


---
Q: What is the difference between varchar and nvarchar?
A: nvarchar fields support foreign character sets (n == national).
These are multi-byte character sets that require additional
administration internally beyond standard ISO/Ascii sets.

---
Q: Is it possible to get @@rowcount and @@error in the same query?
A: sure: select @rws = @@rowcount, @err = @@error

---
Q: How do you select the first x characters of a field?
A: select substring(field,0,desired_limit) from table

---
Q: How do I do a query and get a running count of the rows returned?
A: Two methods (thanks to Michael Peppler, David Owen):

select *, rownum = identity(10) into #tmp from table_x
select * from #tmp

select x.[primary_key], count(*) as row_number from x,

y
where x.[primary_key] >= y.[primary_key] group by x.[primary_key]

---
Q: How do you emulate the Oracle select a,b,c from (select d,e,f from table)
subselect syntax in Sybase?
A: ??? perhaps through views or temp tables

---
Q: How can you emulate Oracle's "&&varname" prompting in t-sql?
A: ??? I don't think you can


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

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

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

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

---



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

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




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

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

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

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

---
Q: Can you turn off logging in tempdb?
A: No

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

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

---
Q: how do I tell what database objects may be affected by dropping a table?
A: sp_depends [table_name]

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

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

From Sybase L discussion 8/12/03
- Use java encryption techniques to secure data upon insert
- Use client side encryption, store the results in varbinary columns in database
- Protegrity; Sybase partner solution using Open Servers
- RSA toolkit to encrypt/decrypt data from front end

---
Q: Is there a way to reverse sp_hidetext, once you've done it?
A: No.

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

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

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

---
Q: I didn't actually set my sa password during init; now I want to change it
from Null to something else. How?
A: sp_password null, "[password]"


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



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

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

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

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

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

---
Q: Is there a good way to tell how often an Index is used? Usage stats
for indexes?
A: Not as of 12.5. A feature being looked at by Sybase Engineering.
12.0: you can capture query plans, grep through the output to see index
usage. But there's no easy way to tell if an index even WAS used during
an execution period.

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


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

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

---
Q: How do you get a Stored Procedure to execute immediately after a
server boots? A Sybase "rc" stored proc?
A: ???

There is no known function within Sybase...perhaps an external program
called from the Start RC script? Still not reliable..what if Sybase
server doesn't come up all the way?

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

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

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

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

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

as of 12.0: offline database [dbname]

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

Best solution is probably to write a stored procedure to do the function
and call it in your proc before you get to this select statement. No
way to do it inline.

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

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

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

---
Q: What is the maximum size of a blob/image/text field in Sybase?
A: No limit; but docs probably say 2gb.

---
Q: What is Oracle equivalent of "blob" datatype? "Long" datatype?
A: blob == image, long = text.

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

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

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

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

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

---
Q: What is a good rule of thumb for your log size?
A: Of course this answer is "it depends." It depends on the nature of your
activity, the type of transactions (large table wide updates will require
more log space than normal). But, 20% of your dataspace is a good rule of
thumb.

see http://www.sybase.com/detail/1,6904,1024123,00.html

---

Q: What are good methods to keep transaction logs from filling up?
A:
You can modify existing queries to use a rowcount and periodically
commit to clean out transaction logs during operations, and sp_thresholdaction
can assist as well.

Also, keeping the logs as a very small percentage of total data space
allows for quicker recovery when a transaction goes south.

see http://www.sybase.com/detail/1,6904,1024123,00.html

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

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

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

---
Q: Can you rename a whole database?
A: Yes: sp_renamedb

(old school: update sysdatabases by hand)



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

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

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

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

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

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

UPDATE: with 12.5, you can add a column non-null!
alter table test add flag smallint default 1 not null


---
Q: How can you tell what port your Sybase engine is listening on, from
within Sybase?
A:

1> select * from master..syslisteners
or
1> select hextoint(substring(address_info, 17, 3)) from syslisteners

(you must have select permission on this particular table...as in sa priv).

The second will decode the hexadecimal port number, since the syslisteners
string is in Hex, the same as the interfaces file is on Solaris
(and some other) os's.


---
Q: How do you tell what port Sybase engine is listening on from Unix? (without
looking at the interfaces file, that is)
A: lsof most likely

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

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

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

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

You can perform "select ... at isolation 0" to override the default isolation level.

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

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

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

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

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

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

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

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

Update: Effective June, 2002 there's a new standardized naming scheme for software
releases. See doc id 1019468 (http://my.sybase.com/detail?id=1019468). Basically,
this eliminates all types of releases except for EBFs. Thus, no more ESD, SWR,
One-Off, GA, IR, MR, Controlled, Instrumented.

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

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

---
Q: When restoring databases from tape, how do you specify to restore a
particular database midway through the tape dump?
A: ???

---
Q: What is the Veritas vxvm command to alter the permissions of a Veritas
object to always be owned by Sybase?
A: vxedit set user=sybase group=sybase mode=600 volume_name

---
Q: How do you get a list of columns per table?
A: Several methods, depending on your needs
- sp_help tablename
- select * from table where 1=2
- select name from syscolumns where id=(select id from sysobjects
where name='tablename')


---
Q: How can you tell in Sybase/MS-Sql server if a column exists in a table?
A:
1> select count(*) from syscolumns where name='targetcolumn'
2> and id=(select id from sysobjects where name='tablename')

if count=1, it exists, if count=0, it doesn't

---
Q: Why can't I issue "truncate table" but I can do a delete * from table?
A: because the truncate command requires dbo capabilities... permissions are
granted by the dbo to regular users (including delete), but you would have
to be aliased to dbo to be able to truncate. Why is this the case? Because
deletes are logged, truncate is not.

Note: 12.5.1 will include grant/revoke for truncate table and update stats commands.

---
Q: What is faster for Sybase Devices? Raw or Veritas quick i/o?
A: Depends. 1998 numbers from Veritas showed the following: (Solaris, ? version Sybase)
- Std file systems perform 40% less than raw
- Veritas file systems w/ quick i/o performed 1-4% less than raw
(depending on buffer size)
- Veritas file systems w/ quick i/o performed 25% BETTER than raw in
systems w/ more than 4Gb ram (because of its ability to address more than
32bits of memory).

The advantage of Veritas filesystems is, of course, that its a filesystem, making
administration of Sybase data files a bit easier.

---
Q: How can I get a list of all tables in my server that use an Identity field?
A:
select DB_name = convert(char(15),db_name()),
Table_name = convert(char(25),so.name),
Col_name = convert(char(15),sc.name),
Type_name = convert(char(15), s.name)
from sysobjects so
, syscolumns sc
, systypes s
, systypes st
where so.id = sc.id
and sc.usertype = s.usertype
and s.type = st.type
and st.name not in ("sysname", "nchar", "nvarchar")
and st.usertype < 100
and so.type = "U"
and convert(bit, (sc.status & 0x80)) = 1
order by 1, 2, 3

---
Q: Can I insert a 0 into an Identity column (with identity_insert set to on?)
A: No; the minimum value is 1.

---
Q: What is the dataserver -X option do?
A: an undocumented Sybase Tech Support backdoor, this allows querying of
the Sybase memory region for processes and process information. You must have
DSQUERY set correctly

You are prompted for a password when used: "quine"

---
Q: Can I have an alias for a column name in the database?
A: ??? (don't think so)

---
Q: Can I install Sybase and Oracle (e.g.) on the same machine and run
them simaultaneously?
A: Definitly no issues installing the two software packages on the same
machine. Running them together will be challenging because they both
use shared memory ... and both typically are CPU and System resource hogs
(Oracle much more than Sybase).

You can run them simaultaneously but it will be slow and you'll have to
have a TON of ram to give each the memory they need to be efficient.

---
Q: Is there any way to compare data in two tables for differences? As in,
if you believe two tables are exactly the same
A: No known tool, but it shouldn't be too hard via one of two methods:
- bcp data out into seperate files, do a unix diff
- (better) write a sybperl program that pulls the lines out one at a
time and compares them

----
Q: Is there any difference between creating a constraint or creating an index
to enforce a unique referential integrity column?
A: Not too much. Notes:
- From an administrative standpoint; you can simply drop an index, but you have
to alter table drop constrant. A bit more work involved.

---
Q: What is a quick way to clean out "orphaned" data so I can create a referential
integrity intended foreign key constraint?
A:
delete from fk_table where key in (
select distinct key from fk_table
where key not in
(select key from pk_table))

---
Q: How do I change the default character set on my Sybase server?
A:
old way: bcp out all data, rebuild the server w/ sybinit,
new way: use combination of charset and sp_configure commands
% charset -Usa -P binary.srt iso15 (replace "binary.srt" as appropriate)
% isql -Usa -P
> sp_configure "enable unicode conversions", 1
> go
> sp_configure "default character set id", 14
> go
> shutdown
> go

You can also use the UNIX sqlloc utility or the NT sybconfig tool to change the default character set.

---
Q: How can you tell if a trigger is inactive from sql statement?
A: select name from sysobjects where (sysstat2 & 1048576) = 1048576 and type='U'

---
Q: Is it stored anywhere in the database when the last time a backup/database dump/
transaction log dump was performed?
A:
- in master..sysdatabases, dumptrdate holds last transaction log dump
- ??? no known field; Look through the backup server logs

MSSql server has a field for last database dump.

---
Q: How can you tell when the last database LOAD was?
A: Nothing internal to the database; you can get it out of the Backup server log


---
Q: How are Sybase's products bundled and how do you install them?
A: Depends on the platform

Linux: RPM: which unfortunately are buggy and people report problems. Use a
program called "alien" to convert RPMs to "tarballs" (tarred gzipped files tgz)

Mac: disk image (dmg file)

WinNT: some sort of Java installer. slow, painful to use.

All other Unix: "tarballs" or gzipped tar files. To install, gunzip and then
tar -xvf the file. Word of warning: before untarring a file, do a tar -tvf on
the package to see what's being installed. I always make backup copies of
my primary Sybase executables before untarring a patch (for safety, incase
the batch is corrupted or it doesn't work).

---
Q: What is the default port number Sybase products will install to (if a
port is not specified?)
A:
- ASE: no default; sybinit always asks you to specify one.
- SQL Anywhere/ASA: 1498, 2638
- ASE on Linux: 7100 on 11.0.3.3 version, no default on 11.9.2 and above.
- Microsoft SQL Server: v7 and below: 1433, v8: 1434
- Oracle: 1521
- Informix: 1526
- IBM DB/2: 6789, 6790 (db/2 windows client is 50000)
- Postgresql: 5432
- Ingres: 21064
- Filemaker: 5003
- mySql: 3306

---
Q: How do I get a list of all aliases in my Server
A: ???


---
Q: What do you do when a database is in "suspect mode?"
A:

A database gets marked suspect when there's an error somewhere in the database
that causes the Server to not be able to bring the database online upon a
reboot. See FAQ question 1.4.1 for a procedure for resetting the system
tables in sysdatabases to reset the status of a database.

Then, once the database is online again, you must fix the problem that
caused it to be suspect. Sometimes a reboot of the server will clear
a suspect database (but only if the error is limited to the cached pages).
Run a series of dbcc's on the database to find and fix the problems.
Sometimes, recovery from backups may be neccesary.

Indexes get marked suspect when the underlying rules of the index change
(character set changes, for example). A suspect index merely denotes that
it cannot be used by the optimizer until reset or rebuilt manually. You
can find these (in 12.0+) by running sp_indsuspect and fix them by
running dbcc reindex(tablename).

See the System Administration guide Chapter 26 for more info on diagnosing
suspect databases/tables.


----
Q: What are the major changes in system tables between 4.9.x and 11.x and beyond?
A: ???

---
Q: What is a good way to get DDL for database objects?
A: several options
- sp_genddl
- ddlgen: built into 12.5
- any DBA tool should be able to generate ddl (dbarchitect, Sybase Central, etc)
- dbschema.pl
- sp__helpprotect


---
Q: is there a workaround to allow dumping a database to a null device
(ala /dev/null?)
A: Yes: 3 step process: Posted to Sybase-L 7/4/03 by Olaf Waldheim
(Olaf.Waldheim@DE.DEBITEL.COM)

1. Create a symbolic link to /dev/null and dump to it
# ln -s /dev/null /dump05a/NULL

2. Create a dump device for it within Sybase
1> exec sp_addumpdevice "disk", nulldev, "/dump05a/NULL"

3. Dump your database (note; you must give the option "with init" because
ASE cannot read a tape header:
1> dump database model to nulldev with init


---
Q: How do you forcibly flush an object from memory?
A: (from Sybase-L conversations sept 2003)
- bounce the server (duh)
- bind the object to a different named cache than the current one
- if its a table/index and currently in the default cache, just unbindit
exec sp_unbindcache "dbname","tablename"
go

---
Q: How do you prevent users from truncating tables?
A:
- revoke dbo privledges
- put an RI constraint on the table, pointing elsewhere
- 12.5.1+: revoke truncate privledge

---



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

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

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

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

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

also:

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

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

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

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

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

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

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

---
Q: How do I bcp w/ text fields? I keep getting truncation and overflow errors?
A: bcp w/ -T option and specify a new max column size of your text column
and set it appropriately (it defaults to 32K).

---
Q: Can I bcp two tables w/ identical structures into one file?
A:
12.5 and up: unions in views allowed; so union the two tables and then bcp
out of the view (which has been avail since 11.5).
12.0 and lower: don't think so; suggestion is to bcp out to two files, then cat one
file >> other.

---
Q: If I have a table with default values (say, getdate() on a datefield) and
i bcp data into the table (excluding this column), will the default work?
A: Yes. However, the getdate() won't be set until the batch is committed,
so the datetime will be the same for an entire batch. To work around this,
set the batch size lower (even to 1 for true getdate()) or you can program in
the getdate() into the data file before bcping in.

---
Q: Is it true that bcp stores each batch on a new page, thus a bcp with a batch
size of 1 will use up one page per row??
A: Yes, as of bcp v11.1.

---


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

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

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

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

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


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

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


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

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

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

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

---
Q: What would be a good way to get a report that counts the number of
times in a given period a particular stored procedure was executed?
A:
- Auditing
- using Monitor server and Historical server to

类别:Sybase | 阅读:4998 | 评论:0 | 标签:Sybase ASE

想收藏或者和大家分享这篇好文章→分享家:Addthis中文版

“Sybase ASE FAQ List (Frequently asked questions)”共有0条留言

发表评论

姓名: (*必填)

邮箱: (*必填)

网址:

验证码: (*必填)

公告

欢迎光临本站, SQL9.com,联系方式:
Tel:

欢迎使用SQL6生活搜索[由iihero labs提供]
天气|IP|身份证搜索|邮编|电话区号|JSON格式化

标签云