Sybase

Why Increase It?

Basically, it will allow a task to be scheduled onto the CPU for a longer time. Each task on the system is scheduled onto the CPU for a fixed period of time, called the timeslice, during which it does some work, which is resumed when its next turn comes around.

The process has up until the value of ctimemax (a config block variable) to finish its task. As the task is working away, the scheduler counts down ctimemax units. When it gets to the value of ctimemax - 1, if it gets stuck and for some reason cannot be taken off the CPU, then a timeslice error gets generated and the process gets infected.

On the other hand, ASE will allow a server process to run as long as it needs to. It will not swap the process out for another process to run. The process will decide when it is "done" with the server CPU. If, however, a process goes on and on and never relinquishes the server CPU, then Server will timeslice the process. Potential Fix

1. Shutdown the ASE

2. %buildmaster -dyour_device -yctimemax=2000

3. Restart your ASE. If the problem persists contact Sybase Technical Support notifying them what you have done already.

There used to be a section in the FAQ describing how to drop all of tempdb's devices physically from the master device. This can make recovery of the server impossible in case of a serious error and so it strongly recommended that you do not do this but simply drop the segments as outlined below.

Sybase TS Preferred Method of Moving tempdb off the Master Device.

This is the Sybase TS method of removing most activity from the master device:

1. Alter tempdb on another device:

1. alter database tempdb on ...

2. go

2. Use the tempdb:

1. use tempdb

2. go

3. Drop the segments:

1.sp_dropsegment "default", tempdb, master

2. go

1. sp_dropsegment "logsegment", tempdb, master

2. go

1. sp_dropsegment "system", tempdb, master

2. go

Note that there is still some activity on the master device. On a three connection test that I ran:

while ( 1 = 1 )

begin

create table #x (col_a int)

drop table #x

end

A ASE (SQL Server) is simply a Unix process. It is also known as the database engine. It has multiple threads to handle asynchronous I/O and other tasks. The number of threads spawned is the number of engines (more on this in a second) times five. This is the current implementation of Sybase System 10, 10.0.1 and 10.0.2 on IRIX 5.3.

Adaptive Server Enterprise (ASE) has long been noted for its reliability, low total cost of ownership and superior performance. With its latest version, ASE 15, it has been dramatically enhanced to deliver capabilities urgently needed by enterprises today. It lays the long-term foundation for strategic agility and continuing innovation in mission-critical environments. ASE 15 provides unique security options and a host of other new features that boost performance while reducing operational costs and risk. Find out how you can exploit new technologies such as grids and clusters, service-oriented architectures and real-time messaging.

As always, this answer depends on the vendor's implementation on a cooked file system for the ASE... Performance Hit (synchronous vs asynchronous)

If on this platform, the ASE performs file system I/O synchronously then the ASE is blocked on the read/write and throughput is decreased tremendously.

The way the ASE typically works is that it will issue an I/O (read/write) and save the I/O control block and continue to do other work (on behalf of other connections). It'll periodically poll the workq's (network, I/O) and resume connections when their work has completed (I/O completed, network data xmit'd...).

Performance Hit (bcopy issue)

Assuming that the file system I/O is asynchronous (this can be done on SGI), a performance hit may be realized when bcopy'ing the data from kernel space to user space.

How to Run the ASE Upgrade Manually

This document describes the steps required to perform a manual upgrade for ASE from release 4.x or 10.0x to release 11.02. In most cases, however, you should use sybinit to perform the upgrade.

BE SURE TO HAVE GOOD BACKUPS BEFORE STARTING THIS PROCEDURE.

1. Use release 11.0x sybinit to run the pre-eligibility test and Check Reserved words. Make any necessary changes that are mentioned in the sybinit log. The sybinit log is located in $SYBASE/init/logs/logxxxx.yyy.

2. Use isql to connect to the 4.x or 10.0x ASE and do the following tasks:

a. Turn on option to allow updates to system tables:

1. sp_configure "allow updates", 1

2. go

b. Checkpoint all databases:

1. use "dbname"

2. go

1. checkpoint

2. go

c. Shutdown the 4.x or 10.0x ASE.

1. shutdown

2. go

3. Copy the interfaces file to the release 11.0x directory.

4. Set the environment variable SYBASE to the release 11.0x directory.

5. Copy the runserver file to the release 11.0x $SYBASE/install directory.

6. Edit the $SYBASE/install/RUN_SYBASE (runserver file) to change the path from the 4.x or 10.x dataserver directory to the new release 11.0x directory.

7. Start ASE using the new runserver file.

Before you start:

select max(datalength(column_name))

from affected_table

In other words, please be sure you're going into this with your head on straight.

How To Change System Catalogs

This information is Critical To The Defense Of The Free World, and you would be Well Advised To Do It Exactly As Specified:

use master

go

sp_configure "allow updates", 1

go

reconfigure with override /* System 10 and below */

go

use victim_database

go

select name, colid

from syscolumns

where id = object_id("affected_table")

go

begin tran

go

update syscolumns

set length = new_value

where id = object_id("affected_table")

and colid = value_from_above

go

update sysindexes

set maxlen = maxlen + increase/decrease?

where id=object_id("affected_table")

and indid = 0

go

With Adaptive Server Enterprise 11.9 Sybase introduced row level locking into its product. In fact it went further than that, it introduced 3 different locking levels:

* All Pages Locking

This is the scheme that is implemented in all servers prior to 11.9. Here locks are taken out at the page level, which may included many rows. The name refers to the fact that all of the pages in any data manipulation statement are locked, both data and index.

* Data Page Locking

The other two locking schemes are bundled together under the title Data Page Locking, refering to the fact that only data pages are ever locked in the conventional sense. Data Page Locking is divided into two categories

* Data Only Locking

This locking scheme still locks a page at a time, including all of the rows contained within that page, but uses a new mechanism, called latches, to lock index pages for the shortest amount of time. One of the consequences of this scheme is that it does not update index pages. In order to support this Sybase has introduced a new concept, forwarded rows. These are rows that have had to move because they have grown beyond space allowed for them on the page they were created. 2002 bytes per page.

* Row Level Locking

Since ASE 11 (I cannot remember if it was with the very first release of 11, but certainly not before) the password column in syslogins has been encrypted. Setting this column to NULL does not equate to that login having a NULL password. A NULL password still requires the correct binary string to be in place.

In release 12 and above, set the minimum password length to be 0 using sp_configure and give that account a null password, and all should be fine.

Before 12, it is not possible to set the minimum password length, so the direct approach is not possible. So, update the relevant record in syslogins setting the password column to be the same as that of an account with a NULL password already.

How does one get the correct binary value? When a new ASE is built, the 'sa' account has a NULL password to start with. Setting an account to have the same binary value as such an 'sa' account should work. Remember that the binary string is going to be specific to the operating system and the exact release of ASE etc. Obviously, if you have set the password of your 'sa' accounts to be something other than NULL (sensible move), then you are going to have to build yourself a dummy server just to get the correct string. If this is important to you, then you may wish to store the value somewhere safe once you have generated it.