Clustered,Non-clustered Indexes

Heap = the data in a table, rows in chronological (insert) order

Index = B-Tree structure of keys, used to speed up the access to data rows.
Except for APL/CI, it is detached (separate allocation) from the heap.

Clustered = A special type of Index for APL tables only; where the Index is 'clustered' with the data; the leaf-level of the Index is the row (and therefore every access to data is one less than with a Nonclustered index).____Not available for DPL/DRL

Nonclustered = the ordinary Index, any index other than Clustered, detached from the Heap.
For performnce reasons, these should be placed on a different segment to the Heap or CI.

Placement : A special form of Nonclustered, the attempt to provide a semblance of 'clustering' for DPL/DRL tables only. Lives on the same segment as the Heap. The physical order of rows is attempted (it exists after a fresh create_index or reorg_rebuild, but it is lost after a few insert/deletes)

**The term Clustered when used with DPL/DRL, is incorrect**.

**Unfortunately the manuals use the incorrect term**

Unique = the indexed column(s) uniquely identify a single row. Prevents another row with the same values as the indexed column(s) being inserted.

Nonunique = the indexed column(s) identify more than one row.

Composite Key (not composite index) = The Key has more than one column.

APL/DPL/DRL are lockschems for the tables.

Credit goes to Derek Asirvadem for his clear explanation about indexes on sybase forums...

Heap = the data in a table, rows in chronological (insert) order

Index = B-Tree structure of keys, used to speed up the access to data rows.
Except for APL/CI, it is detached (separate allocation) from the heap.

Clustered = A special type of Index for APL tables only; where the Index is 'clustered' with the data; the leaf-level of the Index is the row (and therefore every access to data is one less than with a Nonclustered index).____Not available for DPL/DRL

Nonclustered = the ordinary Index, any index other than Clustered, detached from the Heap.
For performnce reasons, these should be placed on a different segment to the Heap or CI.

Placement : A special form of Nonclustered, the attempt to provide a semblance of 'clustering' for DPL/DRL tables only. Lives on the same segment as the Heap. The physical order of rows is attempted (it exists after a fresh create_index or reorg_rebuild, but it is lost after a few insert/deletes)

**The term Clustered when used with DPL/DRL, is incorrect**.

**Unfortunately the manuals use the incorrect term**

Unique = the indexed column(s) uniquely identify a single row. Prevents another row with the same values as the indexed column(s) being inserted.

Nonunique = the indexed column(s) identify more than one row.

Composite Key (not composite index) = The Key has more than one column.

APL/DPL/DRL are lockschems for the tables.

Credit goes to Derek Asirvadem for his clear explanation about indexes on sybase forums...

ASE 15.0 Release Schedule and CR list Information

In the following link you will CR list information and Release terminology
Have a look at this link

In the following link you will CR list information and Release terminology
Have a look at this link

Zombie processes in ASE

When you do not have the row in sysprocesses but you do have in
syslocks that is zombie  lock ( i.e. phantom lock )
You will need to either recycle ASE server or use  dbcc . If you are
using  ASE 12.5.4 or later version , you can try using 'dbcc
lock_release'

A phantom [lock] is when there is an entry in syslocks for
a spid,  but the spid does not exist in sysobjects (phantoms
have no body, just cast a shadow).  Phantom locks are usually
due to bugs in process cleanup in the ASE. 

When you do not have the row in sysprocesses but you do have in
syslocks that is zombie  lock ( i.e. phantom lock )
You will need to either recycle ASE server or use  dbcc . If you are
using  ASE 12.5.4 or later version , you can try using 'dbcc
lock_release'

A phantom [lock] is when there is an entry in syslocks for
a spid,  but the spid does not exist in sysobjects (phantoms
have no body, just cast a shadow).  Phantom locks are usually
due to bugs in process cleanup in the ASE. 

Displaying access information from sysprotects

list privileges for each user

select u.name,v.name
 from sysprotects p, master.dbo.spt_values v, sysusers u
 where p.uid=u.uid
 and p.action=v.number
 and p.protecttype=1
 and v.type = 'T'
 and u.name!= 'dbo'
 go

list privileges for each user

select u.name,v.name
 from sysprotects p, master.dbo.spt_values v, sysusers u
 where p.uid=u.uid
 and p.action=v.number
 and p.protecttype=1
 and v.type = 'T'
 and u.name!= 'dbo'
 go

Maximum Addressable Shared Memory in ASE 12.0.x and 12.5.x

The following table lists, by platform, the maximum size of shared memory for Adaptive Server Enterprise (ASE) 12.0.x and 12.5.x:
 

Platform	32-bit ASE	64-bit ASE	Comments
HP-UX 11.x (PA-RISC processor)	2.75 GB	16 EB
HP-UX 11.x (Itanium2 processor)	N/A	16 EB	Available Q4 2003
HP Tru64 5.x	N/A	16 EB
IBM AIX 5.x	2.75 GB	16 EB
SGI IRIX 6.x	4 GB	16 EB
Sun Solaris 8 (sparc processor)	3.78 GB	16 EB
Sun Solaris 8 (Intel x86 processor)	3.75 GB	N/A
Windows 2000, 2003 and XP	~1.75 GB *	N/A	See note below
Red Hat Enterprise Linux (Intel x86 processor)	2.7 GB	N/A
Red Hat Enterprise Linux (Itanium2 processor)	N/A	16 EB	Available Q4 2003
Mac OSX	2 GB	N/A

Notes:

• One Exabyte equals 2^60, or 1024 PetaByte. 16 EB is a theoretical limit; in practice, the actual value is limited by the total memory available on the system. ASE has been tested with a maximum of 256 GB of shared memory.

 

•  * The maximum size of shared memory for 32-bit windows is approximately 1.75GB. This value can vary depending on the size of the ASE code and associated dlls, which also occupies the 2GB application space. You may need to reduce the max memory value in order to start ASE.
  
  
• On some Windows 32-bit OS, you can use the /3GB option to boot the operating system; this allows ASE to use upto 3 GB shared memory. Check your specific OS version to see if it allows the /3GB option.
  
  
• Each operating system has a default maximum shared-memory segment (for example, shm-max on HP Tru64). Check that the operating system is configured to allow the allocation of a shared-memory segment at least as large as ASE's total memory (12.0.x) or max memory (12.5.x) configuration parameter. See your platform's Installation Guide for Adaptive Server Enterprise for details.

ASE 12.5 changes how memory is allocated, resulting in changes to memory-related configuration parameters and introduction of new parameters. Be sure you understand the new memory configurations in ASE 12.5 when modifying server and/or operating system memory parameters. See What's New in Adaptive Server Enterprise? and the System Administration Guide for details.

The following table lists, by platform, the maximum size of shared memory for Adaptive Server Enterprise (ASE) 12.0.x and 12.5.x:
 

Platform	32-bit ASE	64-bit ASE	Comments
HP-UX 11.x (PA-RISC processor)	2.75 GB	16 EB
HP-UX 11.x (Itanium2 processor)	N/A	16 EB	Available Q4 2003
HP Tru64 5.x	N/A	16 EB
IBM AIX 5.x	2.75 GB	16 EB
SGI IRIX 6.x	4 GB	16 EB
Sun Solaris 8 (sparc processor)	3.78 GB	16 EB
Sun Solaris 8 (Intel x86 processor)	3.75 GB	N/A
Windows 2000, 2003 and XP	~1.75 GB *	N/A	See note below
Red Hat Enterprise Linux (Intel x86 processor)	2.7 GB	N/A
Red Hat Enterprise Linux (Itanium2 processor)	N/A	16 EB	Available Q4 2003
Mac OSX	2 GB	N/A

Notes:

• One Exabyte equals 2^60, or 1024 PetaByte. 16 EB is a theoretical limit; in practice, the actual value is limited by the total memory available on the system. ASE has been tested with a maximum of 256 GB of shared memory.

 

•  * The maximum size of shared memory for 32-bit windows is approximately 1.75GB. This value can vary depending on the size of the ASE code and associated dlls, which also occupies the 2GB application space. You may need to reduce the max memory value in order to start ASE.
  
  
• On some Windows 32-bit OS, you can use the /3GB option to boot the operating system; this allows ASE to use upto 3 GB shared memory. Check your specific OS version to see if it allows the /3GB option.
  
  
• Each operating system has a default maximum shared-memory segment (for example, shm-max on HP Tru64). Check that the operating system is configured to allow the allocation of a shared-memory segment at least as large as ASE's total memory (12.0.x) or max memory (12.5.x) configuration parameter. See your platform's Installation Guide for Adaptive Server Enterprise for details.

ASE 12.5 changes how memory is allocated, resulting in changes to memory-related configuration parameters and introduction of new parameters. Be sure you understand the new memory configurations in ASE 12.5 when modifying server and/or operating system memory parameters. See What's New in Adaptive Server Enterprise? and the System Administration Guide for details.

What is 'Compatibility Mode' in ASE 15.0.3 ESD#1?

At the end of March 2009, version 15.0.3 ESD#1 of Sybase Adaptive Server Enterprise (ASE) was released. This ESD
contained a new feature named 'compatibility mode'.

Compatibility mode is a query processing enhancement in ASE 15.0.3 ESD#1 that allows qualifying T‐SQL queries to
be processed with a method of query optimization and query execution very similar to that used in ASE 12.5,
instead of using the ASE 15 query processing algorithms.

Compatibility mode can be enabled server‐wide for the entire ASE server, as well as for an individual session only.
By default, compatibility mode is disabled. To enable it on server level, set the configuration parameter 'enable
compatibility mode' to 1 (when set to 0, it is disabled):

        

sp_configure 'enable compatibility mode', 1

To override the server‐wide compatibility mode setting for an individual session, use this syntax:

      

  set compatibility_mode on

At the end of March 2009, version 15.0.3 ESD#1 of Sybase Adaptive Server Enterprise (ASE) was released. This ESD
contained a new feature named 'compatibility mode'.

Compatibility mode is a query processing enhancement in ASE 15.0.3 ESD#1 that allows qualifying T‐SQL queries to
be processed with a method of query optimization and query execution very similar to that used in ASE 12.5,
instead of using the ASE 15 query processing algorithms.

Compatibility mode can be enabled server‐wide for the entire ASE server, as well as for an individual session only.
By default, compatibility mode is disabled. To enable it on server level, set the configuration parameter 'enable
compatibility mode' to 1 (when set to 0, it is disabled):

        

sp_configure 'enable compatibility mode', 1

To override the server‐wide compatibility mode setting for an individual session, use this syntax:

      

  set compatibility_mode on

How to convert a sybase backtrack dump to native dump

We need to have the sql bactrack installed and configured on the server. The below command on execution will change the sql-backtrack dump to sybase native dump

 
 
dtsload -physical -from "/$PATH/master-0.11-04-2005.20:02:58_23434" -sybase_dump master.dmp

We need to have the sql bactrack installed and configured on the server. The below command on execution will change the sql-backtrack dump to sybase native dump

 
 
dtsload -physical -from "/$PATH/master-0.11-04-2005.20:02:58_23434" -sybase_dump master.dmp

How know when your ASE server was started??

# In ASE 12.5, the server boot time has been captured in loggedindatetime of sysprocesses for the internal system processes:

select min(loggedindatetime)
from master..sysprocesses where suid=0

# In ASE 12.5.0.1, it's even simpler:

select @@boottime

The static global variable @@boottime is undocumented, but happens to be identical to sysdatabases.crdate for tempdb.

# In ASE 12.5, the server boot time has been captured in loggedindatetime of sysprocesses for the internal system processes:

select min(loggedindatetime)
from master..sysprocesses where suid=0

# In ASE 12.5.0.1, it's even simpler:

select @@boottime

The static global variable @@boottime is undocumented, but happens to be identical to sysdatabases.crdate for tempdb.

Device Sizes for ASE 15.0

The maximum device size is 4TB.

The maximum number of database devices is 2,147,483,647. However, Adaptive
Server must retain a description of each device in memory, so in practice
this number is limited by your system's memory. Your operating system also
limits how many devices one program can open simultaneously.

A database can contain up to 2,147,483,648 logical pages, so its maximum
size depends on its logical page size:

The maximum database size on a 2K page server is 4TB.

The maximum database size on a 4K page server is 8TB.

The maximum database size on an 8K page server is 16TB.

The maximum database size on a 16K page server is 32TB.

The maximum device size is 4TB.

The maximum number of database devices is 2,147,483,647. However, Adaptive
Server must retain a description of each device in memory, so in practice
this number is limited by your system's memory. Your operating system also
limits how many devices one program can open simultaneously.

A database can contain up to 2,147,483,648 logical pages, so its maximum
size depends on its logical page size:

The maximum database size on a 2K page server is 4TB.

The maximum database size on a 4K page server is 8TB.

The maximum database size on an 8K page server is 16TB.

The maximum database size on a 16K page server is 32TB.

Model Database Corrupt

The server will start, but tempdb may not get created properly, you cannot create any new databases, and thus it may not be useable.

Load the model database from your dumpfile restart the server.
If you do not have backups, you can restore the original model via script in the script directory, for windows it is:

c:\sybase\ASE-XX_X\scripts\instmodl

You can alternately use the command:

sqlsrvr -d  -w model

Of course you would have lost anything that you added to model, so you will have to create those objects again.

The server will start, but tempdb may not get created properly, you cannot create any new databases, and thus it may not be useable.

Load the model database from your dumpfile restart the server.
If you do not have backups, you can restore the original model via script in the script directory, for windows it is:

c:\sybase\ASE-XX_X\scripts\instmodl

You can alternately use the command:

sqlsrvr -d  -w model

Of course you would have lost anything that you added to model, so you will have to create those objects again.

Finding number of user connections

select S.config, SY.name, S.value2 from syscurconfigs S, sysconfigures SY
where SY.name="number of user connections" and SY.config = S.config 

select S.config, SY.name, S.value2 from syscurconfigs S, sysconfigures SY
where SY.name="number of user connections" and SY.config = S.config