Sybase

This is a brief run-down of the features and ideas you can use to implement database security: Logins, Roles, Users, Aliases and Groups

* sp_addlogin - Creating a login adds a basic authorisation for an account - a username and password - to connect to the server. By default, no access is granted to any individual databases.

* sp_adduser - A user is the addition of an account to a specific database.

* sp_addalias - An alias is a method of allowing an account to use a specific database by impersonating an existing database user or owner.

* sp_addgroup - Groups are collections of users at the database level. Users can be added to groups via the sp_adduser command.

A user can belong to only one group - a serious limitation that Sybase might be addressing soon according to the ISUG enhancements requests. Permissions on objects can be granted or revoked to or from users or groups.

* sp_role - A role is a high-level Sybase authorisation to act in a specific capacity for administration purposes. Refer to the Sybase documentation for details.

One of your database is marked suspect as the SQL Server is coming up. Here are the steps to take to unset the flag.

Remember to fix the problem that caused the database to be marked suspect after switching the flag.

System 11

1. sp_configure "allow updates", 1

2. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") -- save this value.

3. begin transaction

4. update sysdatabases set status = -32768 where dbid = db_id("my_hosed_db")

5. commit transaction

6. shutdown

7. startserver -f RUN_*

8. fix the problem that caused the database to be marked suspect

9. begin transaction

10. update sysdatabases set status = saved_value where dbid = db_id("my_hosed_db")

11. commit transaction

12. sp_configure "allow updates", 0

13. reconfigure

14. shutdown

15. startserver -f RUN_*

The answer to this relies somewhat on the platform that you are using.

Unix

ASE Versions Before 12.0

This applies to Unix and variants, Linux included. Install the various releases of software into logical places within your filesystem. I like to store all application software below a single directory for ease of maintenance, choose something like /sw. I know that some are keen on /opt and others /usr/local. It is all down to preference and server usage. If you have both Oracle and Sybase on the same server you might want /sw/sybase or /opt/sybase. Be a little careful here if your platform is Linux or FreeBSD. The standard installation directories for Sybase on those platforms is /opt/sybase. Finally, have a directory for the release, say ASE11_9_2 or simply 11.9.2 if you only ever have Sybase ASE running on this server. A little imagination is called for!

So, now you have a directory such as /sw/sybase/ASE/11.9.2 (my preferred choice :-), and some software installed under the directories, what now? In the most minimal form, that is all you need. Non of the environment variables are essential. You could quite successfully run

This answer is geared for Online Transaction Processing (OTLP) applications.

To gain maximum throughput all your transactions should be in stored procedures - see Q1.5.8. The transactions within each stored procedure should be short and simple. All validation should be done outside of the transaction and only the modification to the database should be done within the transaction. Also, don't forget to name the transaction for sp_whodo - see Q9.2.

The following is an example of a good transaction:

/* perform validation */

select ...

if ... /* error */

/* give error message */

else /* proceed */

begin

begin transaction acct_addition

update ...

insert ...

commit transaction acct_addition

end

A connection that is in a log suspend state is there because the transaction that it was performing couldn't be logged. The reason it couldn't be logged is because the database transaction log is full. Typically, the connection that caused the log to fill is the one suspended. We'll get to that later.

In order to clear the problem you must dump the transaction log. This can be done as follows:

dump tran db_name to data_device

go

At this point, any completed transactions will be flushed out to disk. If you don't care about the recoverability of the database, you can issue the following command:

dump tran db_name with truncate_only

If that doesn't work, you can use the with no_log option instead of the with truncate_only.

After successfully clearing the log the suspended connection(s) will resume.

Unfortunately, as mentioned above, there is the situation where the connection that is suspended is the culprit that filled the log. Remember that dumping the log only clears out completed transaction. If the connection filled the log with one large transaction, then dumping the log isn't going to clear the suspension.

v System 10

What you need to do is issue an ASE kill command on the connection and then un-suspend it:

Several reasons why this may be happening.

* Are you doing the updates from within a cursor?

The lock promotion only happens if you are attempting to take out 200 locks in a single operation ie a single insert, update or delete. If you continually loop over a table using a cursor, locking one row at time, the lock promotion never fires. Either use an explicit mechanism to lock the whole table, if that is required, or remove the cursor replacing it with an appropriate join.

* A single operation is failing to escalate?

Even if you are performing a single insert, update or delete, Sybase only attempts to lock the whole table when the lock escalation point is reached. If this attempt fails because there is another lock which prevents the escalation, the attempt is aborted and individual page locking continues.

It has historically been difficult to shrink any database except tempdb (because it is created fresh every boot time). The two methods commonly used have been:

1. Ensure that you have scripts for all your objects (some tools like SA Companion, DB Artisan or dbschema.pl from Sybperl can create scripts from an existing database), then bcp out your data, drop the database, recreate it smaller, run your scripts, and bcp in your data.

2. Use a third-party tool such as DataTool's SQL Backtrack, which in essence automates the first process.

This technote outlines a third possibility that can work in most cases.

An Unsupported Method to Shrink a Database

This process is fairly trivial in some cases, such as removing a recently added fragment or trimming a database that has a log fragment as its final allocation, but can also be much more complicated or time consuming than the script and bcp method.

General Outline

The general outline of how to do it is:

1. Make a backup of the current database

2. Migrate data from sysusages fragments with high lstart values to fragments with low lstart values.

3. Edit sysusages to remove high lstart fragments that no longer have data allocations.

4. Reboot ASE.

Occasionally you may find that after issuing a drop table command that the ASE crashed and consequently the table didn't drop entirely. Sure you can't see it but that sucker is still floating around somewhere.

Here's a list of instructions to follow when trying to drop a corrupt table:

1. sp_configure allow, 1

go

reconfigure with override

go

2. Write db_id down.

use db_name

go

select db_id()

go

3. Write down the id of the bad_table:

select id

from sysobjects

where name = bad_table_name

go

4. You will need these index IDs to run dbcc extentzap. Also, remember that if the table has a clustered index you will need to run extentzap on index "0", even though there is no sysindexes entry for that indid.

select indid

from sysindexes

where id = table_id

go

This is a bit of a wide question, and there are many answers to it. Primarily, it depends on why you are trying to capture it. If you are trying to debug a troublesome stored procedure that is behaving differently in production to how it did in testing, then you might look at the DBCC method. Alternatively, if you wanted to do some longer term profiling, then auditing or one of the third party tools might be the way forward. If you know of methods that are not included here, please let me know.

DBCCs

If you want to look at the SQL a particular process is running at the moment, one of the following should work. Not sure which versions of ASE these work with. Remember to issue dbcc traceon(3604) before running any of the dbcc's so that you can see the output at your terminal.

* dbcc sqltext(spid)

* dbcc pss(0, spid, 0)

The first of the commands issues the SQL of the spid only a bit like this:

[27] BISCAY.master.1> dbcc sqltext(9)

[27] BISCAY.master.2> go

SQL Text: select spid, status, suser_name(suid), hostname,

db_name(dbid), cmd, cpu, physical_io, memusage,

convert(char(5),blocked) from master..sysprocesses

DBCC execution completed. If DBCC printed error messages, contact a user with

System Administrator (SA) role.

[28] BISCAY.master.1>

CIS is the new name for Omni ASE. The biggest difference is that CIS is included with Adaptive Server Enterprise as standard. Actually, this is not completely accurate; the ability to connect to other ASEs and ASEs, including Microsoft's, is included as standard. If you need to connect to DB2 or Oracle you have to obtain an additional licence. So, what is it?

CIS is a means of connecting two servers together so that seamless cross-server joins can be executed. It is not just restricted to selects, pretty much any operation that can be performed on a local table can also be performed on a remote table. This includes dropping it, so be careful!

What servers can I connect to?

* Sybase ASE

* Microsoft SQL Server

* IBM DB2

* Oracle

Intimate Shared Memory or ISM is a specific feature of Sun Solaris. The feature was developed so that when multiple processes (at OS level) try to access a shared memory region, they do not use multiple TLBs (Transalation Lookaside Buffers) at OS kernel level. This saves lot of kernel memory space.

I don't think that does a whole lot for Sybase, more for Oracle I suppose. However, there is a side effect that is useful. If there is enough memory available on the machine, typically Solaris will not swap out process memory marked as ISM if it can possibly help it.

Swapping in Solaris is done in three phases, reserved, allocated and used. Locking the shared memory has the advantage of increasing performance. Of course, if there are lot's of processes on the machine and if new processes starve for memory, there is a potential that ISM will get swapped.

For performance reasons, it is worth ensuring that Sybase can allocated its shared memory segment using ISM. ASE tries by default to use ISM and will display an error message during start up if this is not possible. It is probably worth starting Sybase soon after a machine is rebooted to give it the best possible chance of using ISM.

Solution #1

Use a timestamp on a header table that would be shared by the common data. This timestamp field is a Sybase datatype and has nothing to do with the current time. Do not attempt to do any operations on this column other than comparisons. What you do is when you grab data to present to the end-user, have the client software also grab the timestamp column value. After some thing time, if the end-user wishes to update the database, compare the client timestamp with what's in the database and it it's changed, then you can take appropriate action: again this is dictated by the business.

Problem #1

If users are sharing tables but columns are not shared, there's no way to detect this using timestamps because it's not sufficiently granular.

Solution #2

... Also are you coding to ct-lib directly? If so there's something that you could have done, or may still be able to do if you are using cursors.

5101

Normally, each engine issues and checks for its own Disk I/O on behalf of the tasks it runs. In completely symmetric operating systems, this behavior provides maximum I/O throughput for ASE. Some operating systems are not completely symmetric in their Disk I/O routines. For these environments, the server can be booted with the 5101 trace flag. While tasks still request disk I/O from any engine, the actual request to/from the OS is performed by engine 0. The performance benefit comes from the reduced or eliminated contention on the locking mechanism inside the OS kernel. To enable I/O affinity to engine 0, start ASE with the 5101 Trace Flag.

Your errorlog will indicate the use of this option with the message:

Disk I/O affinitied to engine: 0

This trace flag only provides performance gains for servers with 3 or more dataserver engines configured and being significantly utilized.

Use of this trace flag with fully symmetric operating systems will degrade performance!

Single ASE on host

Script #1

Assuming that you are using bourne shell sh(1) as your scripting language you can put the password in a file and substitute the file where the password is needed.

#!/bin/sh

# invoke say ISQL or something...

(cat $HOME/dba/password_file

cat << EOD

dbcc ...

go

EOD ) | $SYBASE/bin/isql -Usa -w1000

Script #2

#!/bin/sh

umask 077

cat <<-endOfCat | isql -Umyuserid -Smyserver

mypassword

use mydb

go

sp_who

go

endOfCat

*

Script #3

#!/bin/sh

umask 077

cat <<-endOfCat | isql -Umyuserid -Smyserver

`myScriptForGeneratingPasswords myServer`

use mydb

go

sp_who

go

endOfCat

*

Script #3

#!/bin/sh

umask 077

isql -Umyuserid -Smyserver <<-endOfIsql

mypassword

use mydb

go

sp_who

go

endOfIsql

*

cmaxpktsz corresponds to the parameter "maximum network packet size" which you can see through sp_configure. I recommend only updating this value through sp_configure. If some of your applications send or receive large amounts of data across the network, these applications can achieve significant performance improvement by using larger packet sizes. Two examples are large bulk copy operations and applications reading or writing large text or image values. Generally, you want to keep the value of default network packet size small for users performing short queries, and allow users who send or receive large volumes of data to request larger packet sizes by setting the maximum network packet size configuration variable.

caddnetmem corresponds to the parameter "additional netmem" which you can see through sp_configure. Again, I recommend only updating this value through sp_configure. "additional netmem" sets the maximum size of additional memory that can be used for network packets that are larger than ASE's default packet size. The default value for additional netmem is 0, which means that no extra space has been allocated for large packets. See the discussion below, under maximum network packet size, for information on setting this configuration variable. Memory allocated with additional netmem is added to the memory allocated by memory. It does not affect other ASE memory uses.

It is not possible to extend the master database across another device, so the following from Eric McGrane (recently of Sybase Product Support Engineering) should help.

* dump the current master database

* Pre-12.5 users use buildmaster to create a new master device with a larger size. ASE 12.5 users use dataserver to build the new, larger, master database.

* start the server in single user mode using the new master device

* login to the server and execute the following tsql:

select * from sysdevices

* take note of the high value

* load the dump of the master you had just taken

* restart the server (as it will be shut down when master is done loading), again

in single user mode so that you can update system tables

* login to the server and update sysdevices setting high for master to the value

that you noted previously

* shut the server down and start it back up, but this time not in single user mode.