ASE : Renaming the Adaptive Server

Before doing any step ;

i) Please take the backup of master database, including all other important critial databases.
ii) Please take the backup of all files which you are modifying to backout in case of any issue.
iii) Stop the client/app activity on the server.

1. First modify the sysservers table in master database and shutdown the dataserver.
Enable allow update on system table, modify sysservers for srvname and srvnetname with new server.

2. Copy the RUN Server file with new name.

3. Modify the new RUN server file for new server name, errorlog file name(optional), config file name (optional).

4. If you are modifying any cfg file name in new RUN server file, please copy the old cfg file with new name.

5. Take the backup of the interfaces file, after that modify the new servername are just add the new server name with different port number.

6. Now restart your dataserver with new RUN server file.

7. Verify the connection from isql/app team.

Same step we can repeat for the Sybase backup Server.

ASE : Transportable Databases

Very Useful feature to migrate the database between two Production Server, for creating a HA  Server or DR Server.

It can migrate the database between different version where in place upgrade is not possible.

Concept :


The current version of ASE 12.5.4 or possibly earlier on ones allow us to use a Sybase utility called transportable databases to move database at device level between servers. In this scenario, databases technically freeze(by quieseing), the underlying device become stagnant and copies of devices can be made.

In the heart of this operation lies a Sybase utility that allow us to create a binary file called as menifest file that has all the details of devices, the databases  and their layout.


Testing :


Suppose we have two devices on file system(as opposed to raw partitions).  These devices were created in Sybase on ASE 12.5.4 on server called SYB_DS1. They were called SYB_DS1_data01.dat and SYB_DS1_log01.dat respectively.
On these two devices I have created two databases transportable1 and transportable2.

Now I logged in SYB_DS1 server and quiesced these two databases and created a menifest file.
1> QUIESCE DATABASE transport HOLD transportable1,transportable2 FOR EXTERNAL DUMP TO  ’/var/tmp/menifest_file’  WITH OVERRIDE
2>go

Note : The manifest file is called as /var/tmp/manifest_file.

Now copy above mentioned these two devices to new location with new name for the other server SYB_DS2 on the same host with new name SYB_DS2_data01.dat and SYB_DS2_log01.dat.

Now  Mount these database from two devices on other Sybase Server SYB_DS2. This SYB_DS2 can be ASE 15.0.

Logged in SYB_DS2, First get the information out from menifest file.

1> MOUNT DATABASE ALL FROM ‘/var/tmp/menifest_file’ WITH LISTONLY
2>go
[database]
trasnportable1
transportable2
[device]
‘/export/sybase/SYB_DS1/dev/SYB_DS1_data01.dat’='datadev01′
‘/export/sybase/SYB_DS1/dev/SYB_DS1_log01.dat’='datalog01′

Now mount the new copied devices with menifest file in SYB_DS2.

1> MOUNT DATABASE ALL FROM ‘/var/tmp/menifest_file’ using
2>’/export/sybase/SYB_DS2/dev/SYB_DS2_data01.dat’='datadev01′
3>’/export/sybase/SYB_DS2/dev/SYB_DS2_log01.dat’='datalog01′
4>go

It will give long output with successful message as

MOUNT DATABASE: Completed reovery of mounted datbase ‘transportable1′.
MOUNT DATABASE: Completed reovery of mounted datbase ‘transportable2′.

Both the databases would be in offline in SYB_DS2. You need to bring them online by online database comand.
If the SYB_DS2 is ASE15 server, it will upgrade the database.

ASE : dump database three phases

The dump database command makes a copy of the entire
database, including both the data and the transaction log.
dump database does not truncate the log.
dump database allows dynamic dumps. Users can continue to
make changes to the database while the dump takes place.
This makes it convenient to back up databases on a regular
basis.
dump database executes in three phases. A progress message
informs you when each phase completes. When the dump is
finished, it reflects all changes that were made during its
execution, except for those initiated during phase 3."

Phase 1
* Phase 1 is the DBPAGES phase
* The Backup Server scans and dumps all the allocated
pages (both data and log pages) for the given database,
without checking to see whether any of the pages were
updated while this phase is going on.

Phase 2
* Phase 2 is the FLUSHPAGES phase:
* All the data pages that were modified during but not
logged in phase 1 (for example, a fast bcp) are dumped.
* The dump instant is the end of this phase. Recovery
restores the database to the state after phase 2.

Phase 3
* Phase 3 is the SCANLOGPAGES phase:
* The log pages are dumped again to capture
up-to-the-minute changes.
* dump transaction has only one phase: the SCANLOGPAGES
phase.

ASE : How to get host/port,host name,server name,and db name in syabse

select address_info as "host/port"
      ,host_name() as "hostname"
      ,@@servername as "servername"
      ,db_name() "current_dbname"
from master..syslisteners

ASE : Use of "with overrride" option in database creation

Suppose we have a device, test_dev and you wish to create a database
with data and log on it.

 create database testdb1
on test_dev = 100
log on test_dev = 20
with override
You now have distinct data and log segments on the same device.
You can dump this log as usual with dump tran.

The log is always on it's own segment (the "log" segment).
The problem with dumping the tran log comes when the log segment is mixed with any other
kind of segment on the same fragment
(yielding a sysusages segmap that is something other than "4")

ASE : dsync,direct io

The directio and dsync parameters are mutually exclusive. If a device has dsync set to “true,” you cannot set
directio to “true” for this device. To enable directio for a device, you must first reset dsync to “false.”

Direct I/O is always the preferred way.

dsync only applies to operating system files (meaning that it has no effect on "raw" devices used as Sybase disk devices)

ASE : 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.