Sybase

Remember Douglas Adams famous quote "Don't panic" is the first thing!

I know that most people use the 'sa' account all of the time, which is fine if there is only ever one dba administering the system. If you have more than one person accessing the server using the 'sa' account, consider using sa_role enabled accounts and disabling the 'sa' account. Funnily enough, this is obviously what Sybase think because it is one of the questions in the certification exams.

If you see that someone is logged using the 'sa' account or is using an account with 'sa_role' enabled, then you can do the following:

sp_configure "allow updates to system tables",1

go

update syslogins set password=null where name = 'sa'

go

sp_password null,newPassword

go

Creating a partitioned table that spans multiple devices is a multi-step procedure. In this example, we assume the following:

* We want to create a new segment rather than using the default segment.

* We want to spread the partitioned table across three devices, data_dev1, data_dev2, and data_dev3.

Here are the steps:

1. Define a segment:

sp_addsegment newsegment, my_database,data_dev1

2. Extend the segment across all three devices:

sp_extendsegment newsegment, my_database, data_dev2

sp_extendsegment newsegment, my_database, data_dev3

3. Create the table on the segment:

create table my_table

(names, varchar(80) not null)

on newsegment

4. Partition the table:

alter table my_table partition 30

Table partitioning is a procedure that creates multiple page chains for a single table.

The primary purpose of table partitioning is to improve the performance of concurrent inserts to a table by reducing contention for the last page of a page chain.

Partitioning can also potentially improve performance by making it possible to distribute a table's I/O over multiple database devices. Page Contention for Inserts

By default, ASE stores a table's data in one double-linked set of pages called a page chain. If the table does not have a clustered index, ASE makes all inserts to the table in the last page of the page chain.

When a transaction inserts a row into a table, ASE holds an exclusive page lock on the last page while it inserts the row. If the current last page becomes full, ASE allocates and links a new last page.

As multiple transactions attempt to insert data into the table at the same time, performance problems can occur. Only one transaction at a time can obtain an exclusive lock on the last page, so other concurrent insert transactions block each other.

People occasionally ask the following valid question:

v Suppose I have varying lengths of character strings none of which should exceed 50 characters.

Is there any advantage of last_name varchar(50) over this last_name varchar(255)?

That is, for simplicity, can I just define all my varying strings to be varchar(255) without even thinking about how long they may actually be? Is there any storage or performance penalty for this.

There is no performance penalty by doing this but as another netter pointed out:

If you want to define indexes on these fields, then you should specify the smallest size because the sum of the maximal lengths of the fields in the index can't be greater than 256 bytes.

and someone else wrote in saying:

Your data structures should match the business requirements. This way the data structure themselves becomes a data dictionary for others to model their applications (report generation and the like).

You can take advantage of table partitioning with bcp in by following these guidelines:

1. Break up the data file into multiple files and simultaneously run each of these files as a separate bcp job against one table.

Running simultaneous jobs increases throughput.

2. Choose a number of partitions greater than the number of bcp jobs.

Having more partitions than processes (jobs) decreases the probability of page lock contention.

3. Use the batch option of bcp in. For example, after every 100 rows, force a commit. Here is the syntax of this command:

bcp table_name in filename -b100

Each time a transaction commits, ASE randomly assigns a new partition for the next insert. This, in turn, reduces the probability of page lock contention

To activate trace flags, add them to the RUN_* script. The following example is using the 1611 and 260 trace flags. Note that there is no space between the '-T' and the traceflag, despite what is written in some documentation.

Use of these traceflags is not recommended by Sybase. Please use at your own risk.

% cd ~sybase/install

% cat RUN_BLAND

#!/bin/sh

#

# SQL Server Information:

# name: BLAND

# master device: /usr/sybase/dbf/BLAND/master.dat

# master device size: 25600

# errorlog: /usr/sybase/install/errorlog_BLAND

# interfaces: /usr/sybase

#

/usr/sybase/dataserver -d/usr/sybase/dbf/BLAND/master.dat

-sBLAND -e/usr/sybase/install/errorlog_BLAND -i/usr/sybase

-T1611 -T260

This does not seem to be well documented, so here is a quick means of auditing the SQL text that is sent to the server. Note that this simply audits the SQL sent to the server. So, if your user process executes a big stored procedure, all you will see here is a call to the stored procedure. None of the SQL that is executed as part of the stored procedure will be listed.

Firstly, you need to have installed Sybase security (which involves installing the sybsecurity database and loading it using the script $SYBASE/scripts/installsecurity). Read the Sybase Security Administration Manual, you may want to enable a threshold procedure to toggle between a couple of audit tables. Be warned, that the default configuration option "suspend auditing when device full" is set to 1. This means that the server will suspend all normal SQL operations if the audit database becomes full and the sso logs in and gets rid of some data. You might want to consider changing this to 0 unless yours is a particularly sensitive installation.

It is crucial to understand that cschedspins is a tunable parameter (recommended values being between 1-2000) and the optimum value is completely dependent on the customer's environment. cschedspins is used by the scheduler only when it finds that there are no runnable tasks. If there are no runnable tasks, the scheduler has two options:

1. Let the engine go to sleep (which is done by an OS call) for a specified interval or until an event happens. This option assumes that tasks won't become runnable because of tasks executing on other engines. This would happen when the tasks are waiting for I/O more than any other resource such as locks. Which means that we could free up the CPU resource (by going to sleep) and let the system use it to expedite completion of system tasks including I/O.

2. Go and look for a ready task again. This option assumes that a task would become runnable in the near term and so incurring the extra cost of an OS context switch through the OS sleep/wakeup mechanism is unacceptable. This scenario assumes that tasks are waiting on resources such as locks, which could free up because of tasks executing on other engines, more than they wait for I/O.

Let me think back to my database class... okay, I can't think that far so I'll paraphrase... essentially, a natural key is a key for a given table that uniquely identifies the row. It's natural in the sense that it follows the business or real world need.

For example, assume that social security numbers are unique (I believe it is strived to be unique but it's not always the case), then if you had the following employee table:

employee:

ssn char(09)

f_name char(20)

l_name char(20)

title char(03)

Then a natural key would be ssn. If the combination of _name and l_name were unique at this company, then another natural key would be f_name, l_name. As a matter of fact, you can have many natural keys in a given table but in practice what one does is build a surrogate (or artificial) key.

The surrogate key is guaranteed to be unique because (wait, get back, here it goes again) it's typically a monotonically increasing value. Okay, my mathematician wife would be proud of me... really all it means is that the key is increasing linearly: i+1