Wednesday 20 April 2011

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.

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

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

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

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

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

ASE : SQL to Find out all "Defined Rules" in ASE database

select sysobjects.name as "rule name",
object_name(syscolumns.id) as "table name",
syscolumns.name as "column name"
from sysobjects, syscolumns
where object_name(syscolumns.domain)=sysobjects.name
and sysobjects.type='R'

ASE : To check the platform ASE is running on with a SQL query

select name from master..spt_values where type = 'E' and number =1

ASE : To list tables that use "datapages" or "datarows" lockign scheme

select name, sysstat2 from sysobjects
where type='U' and (sysstate2 & 16384 16384 or sysstate2 & 32768 = 32768)

To list tables that use "allpages"

select name, sysstat2 from sysobjects
where type='U'  and sysstate2 & 8192 = 8192

ASE : When was last full backup of database done?

dbcc traceon(3604)
dbcc dbtable("db_name")

"dbcc dbtale" prints the dbtable structure for a database and the last backup date for this dtable structure.

Look for "dbt_backup_start" in the output

ASE : How to move a datafile using disk mirroring in Sybase12.5

Example: move audit01 device from /dir1/datafile1 to /dir2/datafile2

sp_configure ‘disable disk mirroring’, 0
go
shutdown with nowait
go

- restart dataserver

disk mirror
name = audit01,
mirror = "/dir2/datafile2"
go

disk unmirror
name = audit01,
side = "primary",
mode = remove
go

sp_helpdevice audit01
go
sp_configure 'disable disk mirroring", 1
go

- restart dataserver

ASE : How to change ASE sort order

I recently tried to merge a system requiring case insensitive with a system using case sensitive sort order which is the default setting for Sybase ASE. Here are the steps

- check what sort order is being used
sp_helpsort
- try to change it to the new order
- sp_configure ‘default sortorder id', new_sort_order_value
- If you get error like this

1> sp_configure 'default sortorder id', 52
2> go
Msg 5824, Level 16, State 4:Server 'SNAP_PROD', Procedure 'sp_configure', Line 777:Cannot reconfigure server to use sort order ID 52, because the row for itsunderlying character set (ID 1) does not exist in syscharsets.
(return status = 1)

Then exit from isql and run like this
$SYBASE/bin/charset -Psa_pw -Sservername nocase.srt iso_1

then recycle the instance, the instance will be down automatically once conversion is done.
Start the instance again
Run sp_helpsort to verify

ASE : Veritas Quick I/O

VERITAS Quick I/O bypasses the file system page cache, eliminating extra buffering, and providing direct writes to disk. It thereby enhances performance, enables more efficient use of memory and significantly reduces CPU usage.

Here is a link to a Sybase performance test on Veritas storage. It's done by Veritas Software Corp.

http://eval.symantec.com/mktginfo/products/White_Papers/Storage_Server_Management/sf_sybase_40_perf_solaris.pdf

ASE : How to control Sybase process on OS level

In Sun Solaris 9 and newer versin, there is new feature that can be used for controlling resource usage for Unix process. It's defined in /etc/project. A project is a grouping of processes that are subject to a set of constraints.For example, we could limit a project to how many processes it could have, or how large of a file it could create.

The project file contains a series of one-line entries, one for each configured project. Each line takes the following form:
project-name:project-id:comment:user-list:group-list:attributes

Whith this feature, we can limit cpu/space/file size etc of Sybase processes. This is especially useful for the powerful server with multiple Sybase instances running. It can prevent one screwed process from taking over the whole box.

ASE : How to find out the orphaned users

As we refresh UAT server with production backup, it's possible that some orphaned users are left on UAT server. Security auditor treated each orphaned user as 1 count of Medium level vulnerability finding. So it's important to delete orphaned users from servers.

Here is the sql I used to find out orphaned users

select * from userDbName1..sysusers where suid not in (select suid from master..syslogins) and suid > 0
go
select * from userDbName2..sysusers where suid not in (select suid from master..syslogins) and suid > 0
go

ASE : How to change tempdb ownership

When I created second tempdb using my own DBA id due to SOX compliance, the db was under my ID. Now I need to change the ownership to sa. Sybase doc says you can change ownership with sp_changedbowner, but that's not right. and Sybase needs to update the doc to avoid too many support calls.

Here is how to change, I tested it, it works perfectly.

1>use master
2>go
1>sp_configure 'allow updates', 1
2>go
1>begin tran
2>go
1>update sysdatabases set suid = suser_id("") where name = ""
2>go
1>update ..sysusers set suid = suser_id("1>owner's name") where suid=
2>go
1>dbcc dbrepair(, "updowner")
2>go
1>select suid from master..sysdatabases where name = 1>tempdb>
2>go
1>select * from ..sysusers where name = ''
2>go
1>commit tran
2>go

*****If the data from the selects do not look right, then issue a rollback tran instead of a commit tran

1>sp_configure 'allow updates', 0
2>go

By running the dbcc dbrepair command you should not have to recycle the ASE.

ASE : Create second tempdb

steps to follow for creating second tempdb on sybase server

- create data and log devices using disk init
- turn off dsync on both devices

sp_deviceattr tempdb_report_data, dsync, false
go

sp_deviceattr tempdb_report_log, dsync, false
go

- create second tempdb
create temporary database tempdb_report
on tempdb_report_data = 2000
log on tempdb_report_log = 500
go

- make the second tempdb the first user db to be recovered.

sp_dbrecovery_order tempdb_report, 1
go

sp_tempdb 'bind', 'lg', loginname, 'db', 'tempdb_report'
go

Monday 18 April 2011

ASE : extend dbccdb

After a user database was expanded, the space requirement for dbccdb will be different. More space is required. To expand dbccdb, I will follow the steps as below.

use master
go
disk init (or resize an existing device)
go
alter database dbccdb on newdevice = xxx
go
use dbccdb
go
sp_extendsegment scanseg, dbccdb, newdevice
go
sp_extendsegment textseg, dbccdb, newdevice
go

ASE : ssh to remote host without password

I found I lost my posts when I migrated my blog from xingstech.com to this free blog. Here is one I was looking this morning. steps used to setup ssh to remote host without password

How to ssh from Host1 to Host2 without password

on host1
- ssh host2
answer yes, then provide password

- Once get on host2,
cd $HOME
mkdir .ssh
cd .ssh
scp host1:$HOME/.ssh/id_dsa.pub authorized_keys

ASE : turn off dsync for tempdb device

sp_deviceattr ,dsync,false
go

reboot serve

ASE : How to separate data and log segment

1. use disk init to create the new log device for your database

2. dump tran with truncate_only- to make sure we clear the log

3. use sp_logdevice to move the log to the new device
- sp_logdevice ,
- this will change the sgmaps of 7 to 3 and move the log to the new device which has a segmap of 4

4. dump tran with truncate_only- to make sure we clear any log that might remain on the data device

5. use sp_helplog to make sure that the log starts on the log device

ASE : What if logsegment is full because of replication

It’s very frustrating when this happens. you can not dump transaction because the sendary truncation point from replication. the dump tran command works but logsegment is still full. Then you can not disable the replication, because the log is almost full. Below is the steps to handle this situation

1) sp_stop_rep_agent dbname
2) dbcc settrunc(ltm,ignore)
3) dump tran dbname -or-
dump tran dbname with truncate_only
4) (in the rssd): rs_zeroltm dsname, dbanme
afterward, we have to resync the databases.

ASE : How to see the sql running on ASE

In Oracle, there is nice feature that we can see the top sql statement running. In Sybase, it’s not easy. But there is
work around. searching sybase support site, there seems a few way to do this. The most practical ways are

1) run dbcc traceon(3605), dbcc pss(0, spid, 0), dbcc traceoff(3605)

2) run dbcc traceon(3604), dbcc sqltext(spid), dbcc traceoff(3604), the limitation of this is there is 400 byte
limitation.

Other options are 1) monitoring server, which requires extra setup 2) third party tool, which requires extra dollar.
They are not possible at my site.

ASE : Sybase row level locking

Last Friday night, I implemented row level locking on one of our critical stock trading system. As result, the
deadlocking issue is greatly reduced. so far we have not seen any after the implementation. The command used is as follow

alter table TableName lock datarows

Of course, I used a shell script to run against every table in the database, after that drop and recreate triggers
with ”if update(column)” clause.

I also bumped up default data cache, large I/O and number of locks. Everything looks good so far. Need to monitor it
for one more week to get a weekly report.

An interesting finding from this promotion is that the Replication Server seems has a bug, even after the I changed maintainence user’s password, it couldn’t pick up the new password and kept complaining about the login failure. A Rep server recycle solved the problem.

ASE : Change all files' group to sybase

cd $SYBASE
find . -exec chgrp sybase {} \;

This will change all files under $SYBASE and sub-directory to group sybase

ASE : How to delete all those sybase cfg files

Sybase configuration file is not used properly when Sybase auditing is enabled. every time sybase switches audit trail, it creates a new cfg file. In case there are lots of activities on the server, this could be very messy in the cfg directory.

What one can do is to setup a cronjob to archive the cfg files to backup directory, this will keep the Sybase binary
directory clean.

00 01 * * * find <$SYBASE/$SYBASE_ASE -name ‘$DSQUERY.[0-9][0-9][0-9]’ -exec mv {} backupDirectory \;

ASE : How to increase the size of existing device

Sybase 12.5 has a nice new feature to expand the size of existing device. In the past, the only way to add space to a
database is to create a new device, then expand db on the new device. Now we have anther option. Below is the usage

disk init
name = ‘existingDevice’,
size = ‘additionalM’
Please note, this is to add space to exisiting device. i.e the device is 100M, the following command will make the
device 500M

disk resize name = testDev, size = “400M”

ASE : How to setup Sybase auditing

The best way to speed up the auditing is as follow
1. prepare first sql script to backup databases, create devices, create sybsecurity and sybsecurity_archive database, then setup dboptions i.e trun log, select into.
2. telnet to the unix db server, run $SYBASE/$SYBASE_ASE/scripts/installsecurity
3. alter database sybsecurity onto new audit trail device, sp_addaudittable to all new audit trail devices, setup audit options with sp_audit. i.e. sp_audit “all”,”sa_role”,”all”,”on”, then shutdown the server
4. startup the server,
turn on auditing - sp_configure ‘auditing’, 1
configure auditing - sp_configure ’suspend audit when device full’, 0
create auditing archive table in sybsecurity_archive - select * into sybsecurity_archive..sysaudits from sysaudits_01 where 1=2
create procedure audit_threshold.
sp_addthreshold to sybsecurity
create ArchiveSysaudits procedure in sybsecurity_archive database.
create database dump devices for sybsecurity and sybsecurity_archive db.
5. schedule cronjobs to backup sybsecurity and sybsecurity_archive db daily, purge and archive auditing records. create dump directories for all the dump devices added.

ASE : How to log everything you did on unix server

Unix has a nice command called “script” used for just this purpose. below is the usage.

$script audit.log
ls -lR $SYBASE
ls -lR /sybase_devices/
……
exit

the audit.log will show the commands typed and their output. 

ASE : Sybase HA failback procedure

below are the steps used to failback Sybase HA server from secondary node to primary node.

-check if failed node has rebooted cleanly(no hardware/OS errors)

-check companion status of Secondary server via sp_companion command. sp_companion should give the companion state as “Secondary Failover”

-To have current primary host release data devices and prepare for failback, exec sp_companion “”, “prepare_failback”

-During the failback /opt/VRTSvcs/bin/hagrp -state should give the value change as
ONLINE->ONLINE/STOPPING->OFFLINE or OFFLINE->PARTIAL/STARTING->ONLINE
- Once the switch is completed, check the companion state on both Sybase servers, node0 should be “Primary failback”, node1 should be “Secondary failback”

- go to the primary server, issue sp_companion , resume
This will initiate recovery on database failback

- sp_companion , do_advisory, “all”

-Verify companion state on both servers
sp_companion should show “Primary Normal” on node0 and “Secondary Normal” on node1

Sunday 17 April 2011

ASE : Script to generate bcp out for all tables

As DBA, we are asked to export tables quite often, to speed up the process, also for our own benefit, I wrote a small korn shell script to do the job.

isql -Usa -P -w 1000 <> bcp.ksh
use
go
set nocount on
select "echo bcping table "+ name+char(10)+ "bcp ..'" + name + "' out '"+name+".bcp' -c -Usa -P -t''"from sysobjectswhere type = 'U'
go
EOF

#Can be enhanced to interactively ask for values of loginId, Password, ServerName, dbName and export location

ASE : What's next available device number?

In the old version (before 12.5), whenever we need to create a new device, we have to calculate the next availabe device number. The way to do it is run

select max(low/16777216) from sysdevices

then use the number + 1 as next available device number. and make sure sp_configure ‘nubmer of device’ is higer enough. In Sybase 12.5, device no is no longer required, sybase will automatically pick the next available number for any new device.

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

ASE : How to find out tables missing primary key

Before setup replication server, we need to find out which table is missing primary key. I used following query to find out

use dbname
go

select name
from sysobjects
where id not in (select id from syskeys where type = 1)
and type = 'U'
go

So many tables are missing primary key, then I try to reduce the list by checking if they have unique index using following query

select o.name
from sysobjects o
where o.id not in (select id from syskeys where type = 1)
and o.type = 'U'
and o.id not in (select id from sysindexes where status & 2 = 2 )
order by o.name

the list of tables is reduced to half about 55. Then I further reduce the list by checking the size of each table.

select distinct o.name, rowcnt(i.ioampg)
from sysobjects o, sysindexes i
where o.id not in (select id from syskeys where type = 1)
and o.type = 'U'
and o.id not in (select id from sysindexes where status & 2 = 2 )
and o.id = i.id
order by rowcnt(i.ioampg)
go

Take out all the empty tables. Now the list is short and I can work on the few tables (adding PK etc).

ASE : How to drop Sybase xp_cmdshell

Sybase has xp_cmdshell extended stored procedure. This procedure is a security concern, as power user can use this procedure to call OS commands under the security context of the service sybase runs under. What can I do to remove the procedure, I did a lot of research on this, couldn’t find anythng on the web. Some of the information on the web are already out of date, as they assume the xp_cmdsell is still in master db. In Sybase 12.5, the xp_cmdshell is in sybsystemprocs. but the sp_dropextendedproc requires the user to be in master db.

1> use sybsystemprocs
2> go

1> exec sp_dropextendedproc xp_cmdshell
2> go
Msg 18388, Level 16, State 1:Server ‘TESTServer’, Procedure ’sp_dropextendedproc’, Line 23:You must be in the master database in order to run ’sp_dropextendedproc’.
(return status = 1)

1> use master
2> go

1> sp_dropextendedproc xp_cmdshell
2> go
Msg 3701, Level 11, State 1:Server ‘TESTServer’, Line 1:Cannot drop the procedure ‘xp_cmdshell’, because it doesn’t exist in the systemcatalogs.
Msg 18389, Level 16, State 1:Server ‘TOR_GSF_UAT’, Procedure ’sp_dropextendedproc’, Line 66:sp_dropextendedproc failed. Check the SQL Server error log file.
(return status = 1)

We can do it this way :

1> use sybsystemprocs
2> go

1> select name from sysobjects where type = ‘XP’
2> go
name
——————————
xp_cmdshell
xp_freedll
(2 rows affected)

1> dbcc dropextendedproc(xp_cmdshell)
2> go

1> select name from sysobjects where type = ‘XP’
2> go
name
——————————
xp_freedll
(1 row affected)

Monday 11 April 2011

ASE : RAID and Sybase

Here's a short summary of what you need to know about Sybase and RAID.

The newsgroup comp.arch.storage has a detailed FAQ on RAID, but here are a few definitions:

RAID
RAID means several things at once. It provides increased performance through disk striping, and/or resistance to hardware failure through either mirroring (fast) or parity (slower but cheaper).

RAID 0
RAID 0 is just striping. It allows you to read and write quickly, but provides no protection against failure.

RAID 1
RAID 1 is just mirroring. It protects you against failure, and generally reads and writes as fast as a normal disk. It uses twice as many disks as normal (and sends twice as much data across your SCSI bus, but most machines have plenty of extra capacity on their SCSI busses.)

Sybase mirroring always reads from the primary copy, so it does not increase read performance.

RAID 0+1
RAID 0+1 (also called RAID 10) is striping and mirroring together. This gives you the highest read and write performance of any of the raid options, but uses twice as many disks as normal.

RAID 4/RAID 5
RAID 4 and 5 have disk striping and use 1 extra disk to provide parity. Various vendors have various optimizations, but this RAID level is generally much slower at writes than any other kind of RAID.

RAID 7
I am not sure if this is a genuine RAID standard, further checking on your part is required.


Details
Most hardware RAID controllers also provide a battery-backed RAM cache for writing. This is very useful, because it allows the disk to claim that the write succeeded before it has done anything. If there is a power failure, the information will (hopefully) be written to disk when the power is restored. The cache is very important because database log writes cause the process doing the writes to stop until the write is successful. Systems with write caching thus complete transactions much more quickly than systems without.

What RAID levels should my data, log, etc be on? Well, the log disk is frequently written, so it should not be on RAID 4 or 5. If your data is infrequently written, you could use RAID 4 or 5 for it, because you don't mind that writes are slow. If your data is frequently written, you should use RAID 0+1 for it. Striping your data is a very effective way of avoiding any one disk becoming a hot-spot. Traditionally Sybase databases were divided among devices by a human attempting to determine where the hot-spots are. Striping does this in a straight-forward fashion, and also continues to work if your data access patterns change.

Your tempdb is data but it is frequently written, so it should not be on RAID 4 or 5.

If your RAID controller does not allow you to create several different kinds of RAID volumes on it, then your only hope is to create a huge RAID 0+1 set. If your RAID controller does not support RAID 0+1, you shouldn't be using it for database work.

ASE : How do I move tempdb off of the Master Device?

To drop the master device from the segments:

Alter tempdb onto another device, if you have not already done so.
For example:alter database tempdb on tune3 = 20

Issue a use tempdb command, and then drop the master device from the segments:

sp_dropsegment "default", tempdb, master
sp_dropdegment system, tempdb, master
sp_dropdegment logsegment, tempdb, master

To verify that the default segment no longer includes the master device, issue this command:

select dbid, name, segmap
from sysusages, sysdevices
where sysdevices.low <= sysusages.size + vstart
and sysdevices.high >= sysusages.size + vstart -1
and dbid = 2
and (status = 2 or status = 3)

The segmap column should report "1" for any allocations on the master device, indicating that only the system segment still uses the device: dbid name segmap
------ --------------- -----------
2 master 1
2 tune3 7

ASE : ASE commands to play with (for Begineers)

sp_who  -- all current sessions in the ASE server
go

select @@spid -- this is your session
go

select suser_name() -- the username you're logged in with
go

select getdate() -- what's the time?
go

select convert(varchar,getdate(),109)  -- what's the time precisely?
go

sp_helpdb  -- list of databases in this server
go

sp_helpdevice  -- list of the disk files used by this ASE server
go


-- Create your own database to mess around (never create tables etc. in the 'master' database):

create database mydb
go

use mydb
go

select db_name()  -- this should return 'mydb'
go

create table mytab (mycol int, mystring varchar(40), when_added datetime)
go

sp_help
go

sp_help mytab
go

select * from sysobjects where name = 'mytab'
go

insert mytab values (1, 'my first row', getdate())
go
insert mytab values (2, 'my second row', getdate())
go
insert mytab values (3, 'my third -- getting boring now', getdate())
go

select * from mytab
go


-- Create another login:

use master
go

sp_addlogin yourname, yoursecretpassword
go

use mydb
go

sp_adduser yourname  -- needed to get access to database 'mydb'
go


Now log out (or start a new isql session) and log in with the new account:
 isql -U yourname -P yoursecretpassword -Sxxxx
(xxxx is your server's name)

Now do the following:
sp_who  -- all current sessions in the ASE server
go

select @@spid -- this is your session
go

select suser_name() -- the username you're logged in with
go

use mydb
go

select * from mytab
go   

insert mytab values (4, 'my 4th row....', getdate())
go

select * from mytab
go   

ASE : Sybase ASE 15 Enhancements

Table Structure Enhancements

Partitioned tables: Tables can be partitioned by range, value, or round-robin (Semantic Partitioning)

What is a partition?

A partition is a segment of a table, usually with rows sharing a common value (e.g. period_id);

Partitions allow for
• fast deletion of data
• simple bulk replacement of data
• ultra high volume OLTP, via round-robin distribution
• better query performance through partition elimination (homing in on a single partition)
• super-large tables (hundreds of gigabytes) that are manageable

All ASE 15 tables are partitioned (most tables will have a single partition)


Computed columns
• materialized (stored, calc'd upon datachange) or non-materialized (calc'd upon retrival)
• computed columns can be indexed (function-based index)


Statistics
• Update statistics is not necessary after index rebuild
• sp_recompile is not necessary after index rebuild


Encrypted columns : meets US Government encryption standards


Object names (tables, etc) can be up to 255 characters long


Temp table (#temp) improvements, including naming


Object level recovery: allows single objects to be recovered from dump files


Query engine enhancements
• Joins / searches now optimized for queries with calculated columns e.g. select account_id from account_header where floor(gic_subindustry/100) = 451020
• Joins / searches now optimized for queries with mismatched data types e.g. -- join an integer with a real/float select t1.account_id from account_header t1, account_attribute t2 where t1.acct_id = t2.svalue and t2.cat_cd = 'BC'
• Hash joins * i/o is dramatically reduced for non-index type queries, dramatically improving performance. e.g. -- join 2 tables, against non-indexed or computed columns select t2.sales_nm, count(1) from account_header t1, sales_historical_data t2 where convert( integer, ( left(convert(varchar(10),account_id),5) ) ) = t2.sales_id ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries
Work table optimization, hash-based distinct e.g. select count(distinct round(svalue) ) from account_attribute ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries

ASE : Check for phantom process in sybase

select * from master..syslocks where spid not in (select spid from master..sysprocesses)

ASE : Transaction Log Filled Up - Log Suspended : What To Do

This session shows how to debug a transaction log full situation, by checking the
actual query that is causing the problem.


Some transactions can actually prevent the log dump from occuring .. the only recourse
may be:

dump transaction dbname with no_log

1> sp_helpsegment logsegment
2> go

|segment|name |status
|-------|------------------------------|------
| 2|logsegment | 0
|device |size
|------------------------------|----------------------
|data08 |500.0MB
|free_pages
|-----------
| 915
|table_name |index_name |indid
|------------------------------|------------------------------|------
|syslogs |syslogs | 0
|total_size |total_pages |free_pages |used_pages |reserved_pages
|-----------------|---------------|---------------|---------------|---------------
|500.0MB |64000 |915 |63071 |14
(return status = 0)


1> select @@servername
2> go

|
|------------------------------
|PTEST01

> select spid,cmd,blocked,dbid from sysprocesses where cmd like "LOG SUSPEND"
2> go
|spid |cmd |blocked|dbid
|------|----------------|-------|------
| 548|LOG SUSPEND | 0| 7
(1 row affected)

1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> dbcc sqltext(548)
2> go
SQL Text:
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_showplan 548,null,null,null
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is BULK INSERT.
TO TABLE
p_dbtest1..reporting_data
Using I/O Size 8 Kbytes for data pages.
(return status = 0)


1> sp_lock 548
2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
|fid |spid |loid |locktype |table_id |page |row |dbname |class |context
|------|------|-----------|----------------------------|-----------|-----------|------|---------------|------------------------------|----------------------------
| 0| 548| 1096|Ex_table | 1378100919| 0| 0|p_dbtest1 |Non Cursor Lock |
(1 row affected)
(return status = 0

ASE : Transaction Log Checking - Wait Until It Clears

This procedure will check the state of the transaction log,
and will pause until is is cleared or until @pct_limit mb are free.
This is helpful for batch systems which hammer a lot if data into the
database at a quick pace.

create proc sp_sybase_logcheck (@mode varchar(50) = null, @pct_limit money = 80 ) as

declare
@pct money,
@mb1 money,
@log_size money

set nocount on

select @mb1 = ( data_pgs (8, doampg) ) * 8 / 1000
from sysindexes where id = 8

select @log_size = sum(size)
from master.dbo.sysusages u
where u.dbid = db_id() and u.segmap = 4

select @pct = @mb1 / @log_size * 100

while @pct > @pct_limit
begin

if @mode like '%verbose%'
select 'Trans log % full is .... ', @pct

waitfor delay "00:00:10"

select @mb1 = ( data_pgs (8, doampg) ) * 8 / 1000
from sysindexes where id = 8

select @log_size = sum(size)
from master.dbo.sysusages u
where u.dbid = db_id() and u.segmap = 4

select @pct = @mb1 / @log_size * 100
end

return
go

ASE : Striping Dump Devices

Sybase (prior to version 12) has a 2 GB dump file size limitation for most
platforms. Getting around this is easy - simply stripe the dumps across multiple
files or devices. The examples below use file names instead of device names.

dump database hr_db to '/usr2/dumps/remote/db_hr05121318.dmp'
stripe on '/usr2/dumps/remote/db_hr_S1_05121318.dmp'
stripe on '/usr2/dumps/remote/db_hr_S2_05121318.dmp'
go

load database hr_db from '/usr2/dumps/remote/db_hr05121318.dmp'
stripe on '/usr2/dumps/remote/db_hr_S1_05121318.dmp'
stripe on '/usr2/dumps/remote/db_hr_S2_05121318.dmp'
go
online database hr_db
go

ASE : Show Users / Logins with Access to the Database

This handy procedure will display users in the current database .. in
addition to aliases defined.

create proc sp_users as

select 'user:' 'Type', t1.suid, t1.name 'dbuser', t2.name
from sysusers t1, master..syslogins t2
where t2.suid=*t1.suid
union
select 'alias:' 'Type', t1.suid, ' ' 'dbuser', t2.name
from sysalternates t1, master..syslogins t2
where t2.suid=*t1.suid
order by 2

return

ASE : Setting the thresholds

Threshold settings allow customized procedures to be run when database segments
approach a defined capacity.

The "last chance threshold" is set by default, to execute sp_thresholdaction within the
current database, when a segment reaches 95% of capacity. The procedure sp_thresholdaction
needs to be created by the DBA. Here is a sample:

create proc sp_thresholdaction (
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int ) as

declare @msg varchar(80),
@date1 datetime,
@fname varchar(80),
@fdate varchar(20),
@fpath varchar(40)

select @fpath = '/usr/dumps/logs/'

select @date1 = getdate()

select @fdate =
convert(varchar(2),datepart(MM,@date1)) +
convert(varchar(2),datepart(DD,@date1)) +
convert(varchar(2),datepart(HH,@date1)) +
convert(varchar(2),datepart(MI,@date1))

select @fname = @fpath + 'log_' + @dbname + @fdate + '.dmp'

select @msg = '***!! Last Chance Threshold reached, for ' + @dbname + '(' + @segmentname + ')'

print @msg

if @segmentname = 'logsegment'
dump tran @dbname to @fname

return



Other threshold levels can be created, for specific segments. They can be set up
to print informational messages to the error log, as a forewarning to the DBA. Here's a
sample which reflects the command syntax:

1> sp_addthreshold dbname,logsegment,400,'proc_log_threshold'
2> go
Adding threshold for segment 'logsegment' at '400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

ASE : Setting Process Priorities

With Sybase 11.9.5 and above, you can set the run class for processes to LOW, MEDIUM, or HIGH.

Here's a sample call which sets the priority for a specific spid:

sp_setpsexe 14, 'priority', 'LOW'

Here are sample calls which define a class, and sets the priority for a login, forever:

sp_addexeclass 'rpt_class',LOW,null,'ANYENGINE'

sp_bindexeclass 'bjenner','lg','null','rpt_class'

ASE : Server Configuration - example for 64 bit ASE

It is difficult to get an actual example of a system config from Sybase support. Here is
such a configuration, for a Sun server with 12 GB memory, running only an ASE 12.5 64-bit server.
There are 8 databases, each 5 - 18 GB in size.

-- Server Note: shared memory is set to 11500000000 (11.5 GB)
-- shared segments is set to 20


-- Set procedure cache to 500mb

sp_configure 'procedure cache size',500000
go
sp_configure 'cpu grace time', 1000
go


-- 10 GB max memory

sp_configure 'max memory', 10000000
go


-- Set up 32K pool for this financial table, that has an image column

sp_poolconfig 'cache02', '300M', '32K'
go
sp_bindcache 'cache02','account_db','base_account_finanacial_data'
go


-- Default data cache config - this is critical! 8 GB

sp_cacheconfig 'default data cache','8000M'
go

ASE : Security Tasks

/* create a super user, along with database ownership */

use silvermaster
go
sp_addlogin 'silveruser','silver','silvermaster'
go
sp_role 'grant','sa_role','silveruser'
go
sp_changedbowner silveruser
go


/* create a developer profile */
sp_addlogin 'jsmith','yankees','silvermaster'
go
use silvermaster
go
sp_addalias 'jsmith','dbo'
go


/* change jsmith password, note how SA/SSO pwd is required here */
sp_password 'sa_pwd','dodgers','jsmith'
go

ASE : Reorg - rebuilding a table

For active tables in OLTP systems, it is necessary at times to run a reorg to assure the
page chains and indexes are optimized, and unallocated space is freeed.

1> reorg rebuild account_sales
2> go
Beginning REORG REBUILD of 'account_sales'.
There are approximately 15 pages to be processed.
Non-clustered index (index id = 2) is being rebuilt.
REORG REBUILD of 'account_sales' completed

ASE : Renaming a database

Renaming a database requires that it be put in single-user mode first.

-- Renaming a database
use master
go

sp_dboption 'warehouse003','single user',true
go
use warehouse003
go
checkpoint
go
use master
go

sp_renamedb 'warehouse003','warehouse009'
go

sp_dboption 'warehouse009','single user',false
go
use warehouse009
go
checkpoint
go

use master
go

sp_helpdb warehouse009
go

ASE : Removing the Replication Marker or Transaction Log Marker

When loading a database from a database which was replicated, it is necessary
to remove the replication transaction log marker - otherwise a good chunk
of the transaction log will go to waste (and may cause log-full situations

1> dbcc gettrunc
2> go
secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version
-------------------- --------------------- --------- ------------- ----------- ------------------------------ -----------
45626 1 167 0 16 database702 400


1> dbcc settrunc(ltm,ignore)
2> go
secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version
-------------------- --------------------- --------- ------------- ----------- ------------------------------ -----------
45626 0 166 0 16 database702 400

ASE : Moving the transaction log to another device

1> alter database dbname log on device19 = 10
2>go

1> sp_logdevice dbname, device19
2>go
The last-chance threshold for database dbname is now 1232 pages.

... sql inserts, to fill old log segment ...
1> dump tran dbname with truncate_only
2>go

1> sp_helplog dbname
2> go
In database 'dbname', the log starts on device 'device19'.
(return status = 0)

ASE : Drop an alias, with objects linked to login

sp_configure 'allow updates',1
go

begin tran
go

update sysobjects set loginame = 'jsmith' where loginame = 'rjones'
go

commit tran
go

sp_configure 'allow updates',0
go

sp_dropalias 'rjones'
go

ASE : Displaying access information from sysprotects

The below queries illusrate how to extract database privileges which are in effect
for users in the database; note the dbo aliases are ignored.

-- 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


-- generate revoke commands for non-typical access

select 'revoke', name from sysobjects where id in
(
select p.id
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'
and v.name not in ('Delete','Execute','Insert','References','Select','Update')
)
go

ASE : Display grants, effective rights to objects

The below queries display information which answers the question:

Who has access to what objects?

-- Display all privileges which have been granted to users or groups

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


-- Display objects which have 'update' granted to them

select crdate,type,name from sysobjects where id in
(
select p.id
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.actionfiltered=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
and v.name = 'Update'
)
go


-- Display objects which have non-standard access granted to them.
-- This might include UPDATE STATISTICS or TRUNCATE

select crdate,type,name from sysobjects where id in
(
select p.id
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.actionfiltered=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
and v.name not in ('Delete','Execute','Insert','References','Select','Update')
)
go

ASE : Display Locks on Tables

This procedure will display the names of the locked tables
(shared, update, etc)
across databases.

create procedure sp_locks
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to c heck for locks */
as

declare @length int
declare @msg varchar(250)

if @@trancount = 0
begin
set transaction isolation level 1
set chained off
end
/* Print warning message about cursor lock info:
** 18052, "The class column will display the cursor
name for locks associated
** with a cursor for the current user and the cursor id for other
** users."
*/
exec sp_getmessage 18052, @msg out
print @msg

/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks
where spid in (@spid1, @spid2)

if (@length > 15)

select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l,
master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
end

/*
** No paramete
rs, so show all the locks.
*/
else
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks

if (@length
> 15)
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page

else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page
end

return (0)

ASE : Display Available Device Space - Free Disk Space

Determining the amount of free space on a server's set of devices
is not an easy task; the procedure below produces a nice report,
showing total allocations and available space on each device.
Thanks to Stephanie L for this example.


create procedure sp_freedisk
as

set nocount on

select
logical_name = substring(d.name, 1, 12),
physical_name = substring(d.phyname, 1, 17),
vdevno = d.low / power(2, 24),
size = (d.high - d.low + 1) * 2 / 1024,
reserved = isnull(sum(u.size) * 2 / 1024, 0),
left = (d.high - d.low + 1) * 2 / 1024 -
isnull(sum(u.size) * 2 / 1024, 0)
from
master.dbo.sysdevices d, master.dbo.sysusages u
where
d.status & 2 = 2
and u.vstart / power(2, 24)=*d.low / power(2, 24)
group by
substring(d.name, 1, 12),
substring(d.phyname, 1, 17),
d.low / power(2, 24),
(d.high - d.low + 1) * 2 / 1024
order by vdevno

return

ASE : Device initialization

/* create a 2 gig device */

1> disk init name = 'device19',
2> physname = '/dev/md/rdsk/d19',
3> vdevno = 6,
4> size = 1024000
5> go

ASE : Dealing with a Server Failure

There are rare instances when the server crashes down so hard that it cannot
be started again. In the synopsis that follows, the crash was due to extremely
high database activity after the transaction log filled up - making it impossbile
to clear. The server was brought down, and could not be restarted. The trick here
was to bring up the server in "non-recovery" mode, and then clear the transaction
log using some tricks from the Sybase support team.


/* Note: dbname = the database name, X = the dbid */

/* In the runserver file, add the following flags: */

-m
-T3608 (recover master and nothing else)

-or-

-T3607 (no recovery)


/* Now, recycle the server */

Then, in isql:

sp_configure 'allow updates',1
go

update sysdatabases set status=-32768
where name = 'dbname'
go

select config_admin(1,102,1,0,null,null)
go

update sysdatabases set status=0 where dbid=X
go

/* recycle again, things should be OK */

ASE : Dealing with a Corrupted Database

Hardware failures can result in databases that are corrupt and will not open
upon restart of the server. In some cases the database is marked suspect, and
then cannot be opened.
The best way to deal with a database in this state is to
nuke it and reload it from a backup. Here's a code snippet which will
force the drop to occur, when drop database fails.


/* note: X=the dbid of the database (from sysdatabases) */

use master
go
sp_configure "allow updates",1
go
begin tran
go
update sysdatabases set status = 320 where dbid = X
go


/* always make sure the status has been changed to 320 */

select dbid, status from sysdatabases where dbid = X
go
commit tran
go
sp_configure 'allow updates', 0
go
checkpoint
go


/* recycle the server */

dbcc dbrepair (database_name, dropdb)
go


/* now, recycle the server and rebuild the database */

ASE : Database maintenance script

This script performs DBCCs, transaction dumps, or database dumps for a
specified database.

#!/usr/bin/ksh
#-------------------------------------
# syb_maint
#
# Sybase database maintenance: perform DBCCs / log backups / db backups
#
# Parms: database, dump dir, mode (dump | tran_only | dbcc)
#
# Step 1: DBCCs (dbcc mode only)
# Step 2: Backup
#
# Output is routed to backup.log & dbcc.log
#-------------------------------------

if test $# -lt 3
then
echo " "
echo "usage:"
echo "------"
echo "syb_maint <dbname> <dump path> <mode (tran_only,dump,dbcc)>"
echo " "
echo " "

exit
fi

if test ! -d $2 ; then
echo " "
echo " Invalid path: "
echo $2
echo " "

exit
fi

if test ! -f /usr2/dumps/scripts/contact.txt ; then
echo " contact.txt file not found "
exit
fi

contact=`cat /usr2/dumps/scripts/contact.txt`
logfile1=/usr2/dumps/cronlogs/syb_maint/dbcc.log
logfile2=/usr2/dumps/cronlogs/syb_maint/backup.log

if test -f /tmp/syb_stop ; then
echo " ***** db stop detected ***** " >> $logfile1
exit
fi

echo "=============================" > /dev/null
echo $1 > /dev/null
echo "=============================" > /dev/null

if test "$3" = "dbcc" ; then

echo "Running dbcc step ..." > /dev/null

eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis >> $logfile1

print '***** DBCC $1 **************************************'
go
use master
go
sp_dboption $1, "single user", true
go
use $1
go
dbcc checkdb ($1,skip_ncindex)
go
dbcc checkcatalog
go
dbcc checkalloc
go
checkpoint
go
use master
go
sp_dboption $1, "single user", false
go

quit

finis

# check output

if egrep "error|corrupt" $logfile1 | egrep -v "printed|TABLE|Checking" > /dev/null
then

echo "*** Errors found in DBCC log file."
rmail $contact@focal.com << endmsg
*** Errors found in DBCC log file
.
endmsg

fi

fi

echo "Running dump step ..." > /dev/null

eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis2 >> $logfile2

print '***** DUMP $1 **************************************'
go

use master
go

exec sp_syb_maint $1, '$2', '$3'
go

quit

finis2

if grep "error|corrupt" $logfile2 > /dev/null
then

echo "*** Errors found in backup log file"
rmail $contact@mycompany.com << endmsg2
*** Errors found in backup log file
.
endmsg2

fi

echo "Sybase maintenance complete" > /dev/null

ASE : Database maintenance procedure

This stored proc performs transaction dumps, or database dumps for a
specified database. It is used in the script below.


use master
go

create proc sp_syb_maint (@dbname varchar(30),
@fpath varchar(50),
@mode varchar(15)) as
declare @fname1 varchar(50),
@fname2 varchar(50),
@fdate varchar(12),
@fdate1 varchar(12),
@fdate2 varchar(12),
@date1 datetime,
@msg varchar(80),
@char1 char(1),
@dbprefix char(3)

if (@mode = 'dbcc')
return

select @fpath = rtrim(@fpath)

select @char1 = right(@fpath,1)

if (@char1 != char(47))
select @fpath = @fpath + char(47)
select @date1 = getdate()
select @fdate1 = convert(varchar(12),@date1,112),
@fdate2 = convert(varchar(12),@date1,108)
select @fdate =
substring(@fdate1,5,4) +
substring(@fdate2,1,2) +
substring(@fdate2,4,2)
select @dbprefix = substring(@dbname,1,3)
select @fname1 = @fpath + 'log_' + @dbprefix + @fdate + '.dmp'
select @fname2 = @fpath + 'db_' + @dbprefix + @fdate + '.dmp'

if ((@mode = 'dump') or (@mode = 'tran_only')) and
charindex(@dbname,'master-model-tempdb-sybsystemprocs')=0
begin
select @msg = '*** Dumping transaction log to ' + @fname1
print @msg
dump tran @dbname to @fname1
end

if (@mode = 'dump') and
charindex(@dbname,'model-tempdb-sybsystemprocs')=0
begin
select @msg = '*** Dumping database to ' + @fname2
print @msg
dump database @dbname to @fname2
end

return

ASE : Database Engines: Status and Settings

Setting and Displaying Database Engines

Two configuration parameters control the number of dataserver processes (engines) which are active
in the server: "max online engines" and "number of engines at startup".
Most shops with 4-cpu servers will set these to 3.


If the number of engines at startup is lower than the max online engines, how do you tell the
number of engines which are active?

There are two ways:

1) select * from master..sysengines

2) at the UNIX prompt, use the ps -ef command to list all running processes. Note the dataserver
processes, each is an engine.


How to start/stop an engine?

-- start engine #4

sp_engine 'online',4
go

-- stop engine #4

sp_engine 'offline',4
go

ASE : Database Creation

/* create a 1 gig database, with a 50 mb transaction log */
/* for load clause allows quick creation when dump is available */

1> create database
2> dbname
3> on device18 = 1000
4> log on device8 = 50
5> for load
6> go
CREATE DATABASE: allocating 512000 pages on disk 'device18'
CREATE DATABASE: allocating 25600 pages on disk 'device8'


/* change the database owner */

use dbname
go
1> sp_changedbowner 'jmith'
2> go


/* set up automatic log truncate, for development mode */

use master
go
sp_dboption 'dbname','trunc log on chkpt',true
go

ASE : DBCC Notes

DBCCs should be run on a regular basis to check for allocation errors, which occur due
to hardware issues (in most cases). For 24x7 needs, DBCCs can be run on a separate server
that is loaded from a current database dump.


Here is a script which will perform the basic DBCC functions

use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go


use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkdb
go
dbcc checkalloc
go
dbcc checkcatalog
go


use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go


Table or index allocation errors can be fixed by simply dropping the object and recreating
it (using BCP as needed). See below for other repair methods.

Here is a script which will fix many table allocation errors

use invoice_db
go
dbcc tablealloc(tablename, full, fix)
go


Here is a script which will fix most page allocation errors

use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go


use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkalloc(invoice_db,fix)
go


use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go

ASE : Create proxy tables and external logins

exec sp_addexternlogin SYBASE63, ar_admin4, ar_dbo17, maxima

create proxy_table retail_prices at 'PRODSERVER.db_invoices.dbo.retail_prices'

create proxy_table company_profile at 'DEVSERVER.db001.dbo.company_profile'

ASE : Configuring the cache

Important: for ASE 12.5, the default data cache MUST be configured !

use master
go
sp_cacheconfig 'cache01','4M'
go


Entry in config file looks like this:

[Named Cache:dev_cache1]
cache size = 4M
cache status = mixed cache


Next, database objects need to be bound to the cache

use dev_main_db
go
sp_bindcache 'dev_cache01','dev_main_db','customer'
go
sp_helpcache
go

ASE : Apply multiple transaction dumps

This script accepts a directory and dbname as parameters, and applies
the dumps in the directory, in filename order

#!/usr/bin/ksh
#-------------------------------------------------------
# Log File Applier
# Parms: database name, dump directory containing logs
#-------------------------------------------------------
if test $# -lt 2 ; then
echo " "
echo "usage: "
echo "syb_applylogs dbname sourcedir"
echo " "
exit
fi

if test -d $2 ; then
mstatus="OK"
else
echo " "
echo " Invalid path: "
echo $2
echo " "

exit
fi

for fname in $2/log*.dmp ; do
echo $fname
if test -f $fname ; then
/usr2/dumps/scripts/syb_applylog $1 $fname
fi
done

ASE : Apply a transaction dump

This script accepts a transaction file and dbname as parameters, and applies the data

#!/usr/bin/ksh
#-------------------------------------
# Sybase database loader
# Parms: database, log dump file
#-------------------------------------

if test $# -lt 2
then
echo " "
echo "usage:"
echo "------"
echo "syb_applylog <dbname> <dump file>"
echo " "
echo " "

exit
fi

if test ! -f $2 ; then
echo " "
echo "Invalid dump file: "
echo $2
echo " "
exit
fi

echo "-----------------------------------------------"
echo "`date`"
echo "**** Loading transaction dump file ..." $2

eval /apps/sybase/bin/isql -SFocal1 -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis

load transaction $1 from '$2'
go

finis

echo "-----------------------------------------------"
echo "`date`"
echo '**** Load complete.'

ASE : Adding a segment to a database

1> use dbname
2> go

1> sp_addsegment 'idx_seg1','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created.

1> use dbname
2> go

1> sp_dropsegment 'system','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)

1> sp_dropsegment 'default','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped

ASE : ASE 15 Hints: Query Plan Optimization

ASE 15 Set Plan Commands

These commands allow you to set an optimization goal which fits your database environment.


DSS / Reporting Environments may benefit by giving the optimizer more time to compile a query plan. Sample command
-- set time to 999 ms
set plan opttimeoutlimit 999


Using the "hash" join operator introduced in ASE 15
Hash Joins have been shown to improve queries performance by as much as 500%

This example illustrates a forced hash join.

select t1.invoice_id, t1.total, t1.status_cd, t1.fiscal_qtr, t1.margin
from invoice_master t1, client_master d
where d.region_id = 2001
and t1.invoice_id = d.invoice_id
and t1.fiscal_qtr between 20031 and 20044
order by t1.invoice_id, t1.fiscal_qtr
plan " (h_join (scan t1) (scan d))"
go


Join Queries: Choosing an optimization goal

--Examples: session level

set plan optgoal allrows_mix
go
set plan optgoal allrows_oltp
go
set plan optgoal allrows_dss
go

--Example: server level
sp_configure "optimization goal", 0, "allrows_oltp"
go

Optimization Goal Details

allrows_oltp [nested loop join]
Nested-loop joins provide efficient access when tables are indexed on join columns.

allrows_mix [merge joins + allrows_oltp]
A merge join can use multiple worker processes to perform:. The scan that selects rows into a worktable, note a merge join may requires a sort.

allrows_dss [hash joins + allrows_mix]
The hash join algorithm builds an in-memory hash table of the smaller of its targets.

DDL : Tables which span multiple segments

Tables containg large amounts of data (> 2 GB) need to be spread across
several devices, using sp_placeobject. Note that this procedure affects only
future operations - if a table load of more the 2 GB is to be performed, it
would have to be split into two or more stages.

/* Future inserts will reside on data_seg2 */
sp_placeobject 'data_seg2','invoice'
go

DDL : Table Partitioning

Insert performance on partitioned tables is improved, as multiple
'entry points' (last page entries) are created. Partitioned tables
require slightly more disk space and need a chunk of memory also.

/* create 4 partitions for the invoice table */
alter table invoice
partition 4
go


Partitioning in ASE 15

Semantic "smart" partitioning is new in ASE 15.
Tables can be partitioned by range, value, or round-robin. By default, all tables in ASE 15 are partitioned, round-robin.

DDL : Sybase data types

data type storage range/length comments
----------------- -------- ------------------- ----------------------------
integer 4 +/- 2.1 billion
smallint 2 +/- 32768
tinyint 1 0 .. 255
float 4 storage req is machine dependant
real 4
double precision 8
smallmoney 4 +/- 214,748 4 decimal places
money 8 +/- 922 trillion 4 decimal places
decimal/numeric varies
decimal(9,0) 4
decimal(12,0) 5

char(n) n length <= 255
varchar(n) varies length <= 255 ( over 4000 allowed in ASE 12.5 )

text varies length up to 2 GB 16 bytes stored in record (default)
image varies length up to 2 GB 16 bytes stored in record (default)

datetime 8 1/1/1753 .. 12/31/9999 precision to 1/300 second
smalldatetime 4 1/1/1900 .. 6/6/2079 precision to minutes
timestamp 8 same as varbinary(8)

bit 1 0/1 up to 8 bit fields stored within 1 byte

binary(n) length n
varbinary(n) length n


Only numeric data types with a precision of zero can be used for an identity column.

Historical Note on the Datetime Datatype

It starts with 1753 because the English-speaking world converted from the
Julian to the Gregorian calendar in 1752. To do that, the day after September 2, 1752
was decreed to be September 14, 1752.

DDL : Object Permissions

Object security is fairly straightforward. Here are some examples:

grant all on invoice to jsmith
go
grant select on invoice to wriker
go
grant update on invoice to wriker
go
revoke select on invoice from wriker
go

Stored procedure security allows you to grant access on a business logic basis.
For example, if you had a stored proc that updated the invoice table and selected
data from the customer table, you could grant the execute priviledge on the stored
proc, and you're done. The user would be able to run the procedure to update/select
from the tables, but could not get at the tables directly.

grant execute on proc_upd_invoice to jsmith
go

DDL : Moving an object to another segment

When databases contain more segments than the usual default, it is
often necessary to move tables between segments.


/* move a table, in its entirety, to the new segment */

drop index 'employee.idx_employee'
go
create clustered index on employee (emp_id) on new_seg
go


/* leave table where it is, but future allocations go to the new segment */

sp_placeobject new_seg , 'employee'
go


/* leave table where it is, but future allocations for the text column (employee_notes) go to the new segment*/

sp_placeobject new_seg , 'employee.temployee'
go


/* leave table where it is, but future allocations for the text column (resume) go to the new segment */

sp_placeobject new_seg , 'student.tstudent'
go

DDL : Modifying a Colunm

With Sybase 12.1.X and higher, a column type can be altered. You need to be dbo and have select into turned on, in the database defaults.

create table employee (
emp_id integer not null,
salary money default 0,
hire_dt datetime default getdate(),
last_name varchar(5) null
)
go


/* make the name column longer */
alter table employee modify last_name varchar(80) not null
go


/* rename the column (works with Sybase 11 and 12) */
sp_rename 'employee.last_name',last_nm
go

DDL : Creating an index

create unique clustered index emp_idx
on employee (emp_id)
go

create index emp_name_idx
on employee (lname)
go


-- With sorted data!

create unique clustered index pk_invoice_data on invoice_data with sorted_data on segment1
go

DDL : Creating a table

create table employee (
emp_id numeric(8,0) identity,
fname varchar(10) not null,
lname varchar(25) not null,
salary money not null,
dept_cd char(3) not null,
fax_no integer null
)
go

create table invoice (
invoice_id numeric(8,0) identity,
sales_rep_id numeric(8,0) not null,
date smalldatetime not null,
comment varchar(255) null )
on data_seg2
go

create table err_cd (
err_id integer not null,
err_desc varchar(60) not null,
constraint pk_err_cd primary key clustered (err_id)
)
go

DDL : Creating a proxy table

create proxy_table invoice_items at 'SERVERXXX01.dbxxx001.dbo.invoice_items'
go

DDL : Creating a constraint

Contraints are used to define primary keys, enforce uniqueness, and to describe
foreign key relationships. Note that unique or primary key constraints
create indexes upon creation.


/* primary key for the employee table */
alter table employee add constraint
emp_constr primary key(emp_id)
go

/* add unique requirement for invoice table */
alter table invoice add constraint
inv_constr unique nonclustered(cust_id,inv_date)
go

/* add foreign key for relationship between invoice and employee */
alter table invoice add constraint inv_fk_emp
foreign key (sales_rep_id)
references employee(emp_id)
go

DDL : Clustered vs non-clustered indexes

Typically, a clustered index will be created on the primary key of a table, and non-clustered indexes are used where needed.

Non-clustered indexes
Leaves are stored in b-tree
Lower overhead on inserts, vs clustered
Best for single key queries
Last page of index can become a 'hot spot'
Clustered indexes
Records in table are sorted physically by key values
Only one clustered index per table
Higher overhead on inserts, if re-org on table is required
Best for queries requesting a range of records
Index must exist on same segment as table

Note! With "lock datapages" or "lock datarows" ... clustered indexes are sorted physically only upon creation. After that, the indexes behave like non-clustered indexes.

DDL : Binding Rules to Colunms

Default values for colunms can be specified at the time a table is created, or
afterwards via the modify command.

/* Employee will have a default salary of 10000, and a hire date of today */

create table employee (
emp_id integer not null,
salary money default 0,
hire_dt datetime default getdate()
)
go


/* alter the table, so salary starts at 49000 */

ALTER TABLE employee REPLACE salary DEFAULT 49000
go


/* create a new default; bind the column to your custom default */

create default def_highsal as 55000
go
sp_bindefault def_highsal,'employee.salary'
go

-- creating your own custom defaults has a significant advantage:
-- you can actually choose the name of the default.

DDL : Altering a table

alter table employee add cell_no numeric(10) null
go
alter table employee drop constraint 'emp_dept_constr'
go

/* add default */
alter table charge_item replace price_overridable_ind default 0
go

/* change column name -- quotes are required */
sp_rename 'employee.dept',dept_name
go

DDL : Alter a column

Note - for some table modifications, you'll need to set option 'select into' ON

-- modify a column

alter table invoice_detail modify
inv_type varchar(50) not null
go

-- modify multiple columns

alter table invoice_detail modify
inv_type varchar(50) not null,
inv_priority varchar(50) null,
inv_strategic varchar(50) null,
inv_total money null
go

Tuesday 5 April 2011

ASE : A Backup Routine

use master
go
sp_dboption dbname, "single user", true
go
use dbname
go
checkpoint
go
dbcc checkdb (dname,skip_ncindex)
go
dbcc checkcatalog
go
dbcc checkalloc
go
use master
go
sp_dboption dbname, "single user", false
go
use dbname
go
checkpoint
go
dump tran dbname to device1
go
dump database dbname to device1
go

ASE : Post-installation check

Do not leave master as the default device, database creates without a device
specification will be created in master.

1> sp_diskdefault master, defaultoff
2> go
(return status = 0)
1> sp_diskdefault device26, defaulton
2> go


Routine memory check
dbcc traceon(3604)
go
dbcc memusage
go
dbcc traceoff(3604)
go


/* sample post-install config, for 12.5 ASE */

-- send results, no wait
sp_configure 'tcp no delay',1
go

-- allocate 1.2 gb to sybase
sp_configure 'max memory',600000
go
-- allocate at sybase boot time
sp_configure 'lock shared memory',1
go

-- additional data cache
sp_cacheconfig 'default data cache','600M'
go
-- additional procedure cache
sp_cacheconfig 'procedure cache','50M'
go

-- cache for tempdb
sp_cacheconfig 'cache01','80M'
go

/* reboot ASE */

-- Additional config, for server w/several CPUs

sp_configure "number of user connections",500
go
sp_configure "number of worker processes",100
go
sp_configure "max parallel degree",3
go
sp_configure "max scan parallel degree",3
go
sp_configure "global cache partition number",2
go
sp_configure "number of locks",50000
go
sp_configure "number of open objects",50000
go
sp_configure "number of open databases",32
go
sp_configure "number of devices",50
go

/* reboot ASE */

-- Additional config, for system using text/blob data

sp_configure 'additional network memory',4096
go
sp_configure 'max network packet size',2048
go
sp_configure 'default network packet size',1024
go
sp_configure 'heap memory per user',4096
go


/*
UNIX Sybase >= 11.9, allow device buffering in O/S;
- improves performance
- increases chance of device corruption during failure
*/

sp_deviceattr "device21","dsync","false"
go

/* LINUX: may need to set shared memory */
echo 134217728 > /proc/sys/kernel/shmmax
echo 999999999 > /proc/sys/kernel/shmmax


Extend tempdb:
size should be about 20% of the main production database's size.
/* configure tempdb to 20 mb ... this command adds an additional
18 meg to the 2 mb already present on the master device */
1> alter database tempdb on device26 = 18
2> go


/* Add local server name */
sp_addserver snoopy, local
go

ASE : ASE Overview, Architecture

A Sybase server consists of:

A) two processes, data server and backup server ;
B) devices which house the databases; one database (master) contains system and configuration data ;
C) a configuration file which contains the server attributes .

Memory Model

The Sybase memory model consists of:
A) the program area, which is where the dataserver executable is stored;
B) the data cache, stores recently fetched pages from the database device
C) the stored procedure cache, which contains optimized sql calls
The Sybase dataserver runs as a single process within the operating system; when multiple users are connected to the database, only one process is managed by the OS. Each Sybase database connection requires 40-60k of memory.
The "total memory" configuration parameter determines the amount of memory allocated to the server. This memory is taken immediately upon startup, and does not increase.


Transaction Processing

Transactions are written to the data cache, where they advance to the transaction log, and database device. When a rollback occurs, pages are discarded from the data cache. The transaction logs are used to restore data in event of a hardware failure. A checkpoint operation flushes all updated (committed) memory pages to their respective tables.
Transaction logging is required for all databases; only image (blob) fields may be exempt.
During an update transaction, the data page(s) containing the row(s) are locked. This will cause contention if the transaction is not efficiently written. Record locking can be turned on in certain cases, but this requires sizing the table structure with respect to the page size.


Backup Procedures

A "dump database" operation can be performed when the database is on-line or offline. Subsequent "dump transaction" commands need to be issued during the day, to ensure acceptable recovery windows.


Recovery Procedures

A "load database" command loads the designated database with the named dump file. Subsequent "load transaction" commands can then be issued to load multiple transaction dump files.


Security and Account Setup

The initial login shipped with Sybase is "sa" (system administrator). This login has the role "sa_role" which is the super-user, in Sybase terms.
User logins are added at the server level, and then granted access to each database, as needed. Within each database, access to tables can be granted per application requirements. A user can also be aliased as "dbo", which automatically grants them all rights within a database.


Database Creation

Databases are initialized with the "create database" command. It is not unusual for a Sybase server to contain many different databases. Tables are created within each database; users refer to tables by using ownername.tablename nomenclature. "Aliasing" users with the database eliminates the need for the prefix. Typically, a user will be aliased as "dbo" (database owner), which also gives the same result.
A typical Sybase database will consist of six segments spread across various devices.


Data Types

Supported data types include integer, decimal, float, money, char, varchar, datetime, image, and text datatypes.
Text and image datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated pages. As a result, each text or image field requires at least 2K or more of storage (depending on your page size setting).
For string data, the varchar type can be used for lengths up to 4000; the text type can be used for longer field data.
Datetime fields are stored as a number which is accurate to 1/300 of a second.
Within a "create table" statement, a column can be flagged as an "identity" column, which causes it to be incremented automatically when rows are inserted.


Storage Concepts

Tables are stored in segments; a segment is an area within a device, with a name and a size, that is allocated for a database. The transaction log is stored in its own segment, usually on a separate device.
Note that all tables in Sybase ASE 15 are partitioned - even if partitions are note defined by the DBA.


Transact-SQL

Transact-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Temporary tables are supported, which allows customized, private work tables to be created for complex processes. Any number of result sets can be returned to calling applications via SELECT statements.


Performance and scalability

Sybase ASE continues to break TPC benchmark records - search for Sybase TPC in Google to see recent results.
Sybase databases scale from handheld devices (SQL Anywhere) to enterprise level servers (Adaptive Server Enterprise).
The latest version, ASE 15 with Semantic Partitioning, makes terabyte-sized OLTP databases a reality.


Management and Development Tools (for Windows)

ISQL is the interactive query tool used with Sybase ; it is useful for entering queries and stored procedures.


Sybase Central is shipped with all Sybase database server products. It offers a good interface for performing basic database tasks. The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies.


Recently, Sybase released "Workspace" which includes a real stored procedure debugger.

For development, Sybase Inc. offers Powerbuilder, Powerdesigner, Power J and its "Studio" line products. Powerbuilder remains the most robust, straightforward, and practical choice for windows development, supporting many other RDBMs in addition to Sybase System ASE.