James Xing's Sybase DBA blog

closed comment section

2010-08-07T22:20:00.003-04:00

I closed the comment section today, as I am getting daily spam from this site. I have tried to delete the spam comments, but it's still took too much of my time. so I leave it for now. When I have time, will clean it up.

Nothing really exciting for me in the last little while, so I have no new post. The best news was probably the sybase buy out from SAP. Sybase will be safe for another 10-20 year, I guess. wish I had bought sybase stock before the buyout :)

2009-06-28T21:26:00.002-04:00

What a historical year it is? I haven't posted for over a year now, simply because it's so many changes happened in the past 1 year, the fall of bear stern, lehman brothers, stock market crash, layoff, company strategy change ..... though I was not impacted, but it's amazing to watch all these happening around me and I am not too interested on the techinical stuff at this moment.

2008-06-09T23:18:00.002-04:00

Sybase stock was up more than 20% in May

I noticed Sybase stock (symbol SY) was up more than 20% in May. That's pretty good run. Right now it's $31.47.

2008-06-01T11:37:00.004-04:00

DDL for master..syslogins

The DDL for syslogins comes in handy when you work on syslogins related tasks, i.e. Migrate syslogins from Sybase 12.0 to Sybase 12.5. Most DBA tools reverse DDL for user tables only.

USE
tempdb
go
CREATE TABLE dbo.jx_syslogins
(
suid int NOT NULL,
status smallint NOT NULL,
accdate datetime NOT NULL,
totcpu int NOT NULL,
totio int NOT NULL,
spacelimit int NOT NULL,
timelimit int NOT NULL,
resultlimit int NOT NULL,
dbname sysname(30) NULL,
name sysname(30) NOT NULL,
password varbinary(30) NULL,
language varchar(30) NULL,
pwdate datetime NULL,
audflags int NULL,
fullname varchar(30) NULL,
srvname varchar(30) NULL,
logincount smallint NULL,
procid int NULL)
go

1> insert master..syslogins (suid, status, accdate , totcpu , totio , spacelimit , timelimit , resultlimit , dbname , name , password , language , pwdate , audflags , fullname , srvname , logincount )
2> select * from jx_syslogins
3> go

2008-05-15T16:12:00.000-04:00

How to stop Oracle Archive log mode

startup mount;
alter database noachivelog;
alter database open;
archive log list;

2008-05-09T11:28:00.001-04:00

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

2008-05-02T22:23:00.004-04:00

Sybase reports record First Quarter Results

Highlights:
Total revenue of $260.1 million, up 13% year over year
License revenue increased 13% year over year
Services revenue increased 8% year over year
Database license revenue increased 19% year over year
Messaging revenue increased 37% year over year
GAAP operating margin of 14%, versus 9% a year ago; non-GAAP operating margin of 19%, versus 15% a year ago
GAAP net income of $24.2 million, up 60% year over year; GAAP EPS up 65% year over year
Non-GAAP net income of $35.3 million, up 42% year over year; non-GAAP EPS up 46% year over year
Cash flow from operations of $94.6 million, up 36% year over year

I think the license increase are primarily due to the fact that license key is mandatory in version 15. The older version of sybase does not need to have a key to run. Many Sybase users have setup additional sybase servers without license. As they migrate to Sybase15, they have to buy extra licenses.

2008-04-19T14:44:00.003-04:00

Asus unveils Eee PC 9000 XP laptop

checked ebay, I was surprised that this laptop is in market already. below are the desc from 1 ebay seller. I am interested to get one with GPS build in.

Mobility & Reliability
• At half the size of regular notebooks and weighing less than 1kg, you can take the Eee PC anywhere.
• Bumps and shocks are no longer issues. With a dependable solid-state disk, you get unparalleled shock-protection and reliability.
• Power-efficient design provides longer operating time when on the go.

Ease & Technology
• With a rapid start-up time, the Eee PC 900 is always ready to get into action.
• No technical manual required with the specially designed, user-friendly and intuitive graphic interface .

Work & Connectivity
• You’re always connected with built-in WiFi 802.11 b/g that automatically detects and connects to the Internet at any hotspot.
• The Eee PC 900 includes the documents and the e-mails software, and a suite of other productivity software to help keep you on track.
• Upload photos and videos and share them instantly on Flickr or YouTube without waiting till you get home.

Media & Entertainment
• Enjoy music and videos with extensive support for a wide range of digital multimedia.
• Log on to Skype or other network, and you can connect with friends anywhere, anytime.
• Clear up wire clutter with the built-in card reader, webcam, speakers, and microphone.

2008-03-24T13:58:00.001-04:00

Sybase DBA certificate resource

1) Sybase Certificate home page
2) Sybase DBA certificate study guide
3) Sybase manual
4) Sybase exam
5) Review from other experienced DBA

How to change ASE sort order

2008-03-11T11:50:00.000-04:00

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

Compare Sybase vs MS SQL Server

2008-03-05T13:42:00.000-05:00

a nice comparison between Sybase 12.5 and MS SQL Server 2000.

http://www.mssqlcity.com/Articles/Compare/sql_server_vs_sybase.htm

Veritas Quick I/O

2008-02-26T16:12:00.000-05:00

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

DBArtisan

2008-02-15T16:19:00.000-05:00

DBArtisan is a popular DBA tool. You may find some DBA job posting even has requirement on familiarity on this tool.

The tool itself is very simple and not much different from the native tool from database vendors, the main strength is it provide same interface across all platform (Oracle, Sybase, DB2 and SQL Server).

The tool is quite expensive. I found a trick here that is totally legal. It can save significant money for the business and greatly increase ROI. But the sales people from the vendor definitely don’t want you know. I will keep my mouth shut.

backup/recovery strategy consideration

2008-02-13T10:29:00.000-05:00

Below are some of the consideration when design database backup/recovery strategy

* How and where will you store your recovery-related files? Will you store backups on tape or other offline storage, or only on disk?
* At what intervals will you take scheduled backups? And what form of physical backups will you take in each situation?
* What situations require you to take a database backup outside of the regular schedule? Sometimes you must take an unscheduled backup to ensure that you can recover your data. You may also have business requirements that require backups for auditing purposes or other reasons not related to database recovery.
* How can you validate your backups, to ensure that you can recover your database when necessary?
* How do you manage records of your backups?
* Do you have detailed recovery plans that cover each type of failure? How do your DBAs can execute these plans in a crisis? Can scripts be written to automate execution of these plans in a crisis?
* Can you apply Sybase database availability technologies, such as Warm Standby, Sybase HA, vcs, to improve availability during a database failure? How does using these availability technologies affect your backup and recovery strategy?

These are of course only a few of the considerations you should take into account. Available resources (hardware, media, staff, budget, and so on) will also be factors in your decision.

Trace flag 298

2008-02-10T15:58:00.000-05:00

Recently when we upgraded an Sybase server from v11.5 to v12.5.4, the application got some outer join error (11055, "Query contains an illegal outer-join request". ).

Somehow, it's very difficult to get the developer fix his program. To keep the project moving, I turned on the trace flag 298, this flag is to reverse sybase outer join behaviour back to the way used in prior to v12.0. From ASE 12.0 onwards the behaviour of correlated subqueries containing T-SQL outer joins has changed. The problem (error) disppeared after sybase is recycled with trace flag 298. The project team was so amazed as this solution was not expected.

The drawback is that this db can not be put into other sybase servers because of the trace flag used.

Chinese New Year's day

2008-02-08T14:32:00.000-05:00

Yesterday was Chinese New Year's day. To celebrate this holiday, I joined a few friends for lunch at Acqua, an exceptionally stylish restaurant at downtown Toronto.

The meal was great. Price was good too, as it’s also the last day of this year’s famous Toronto Winterlicious.

tempdb full

2008-02-05T15:21:00.000-05:00

When tempdb is full, you really can't do anything, even for DBA. The only solution at the moment is either expand tempdb or do a server recycle. Once the server is recycled, all the traces are gone. There is no way to find out who/what caused the trouble.

The key here is to capture the related information before tempdb is full. Use threshold action procedure to capture process info in sysprocesses, syslogshold, syslocks and other related info, save it in permanent tables. Even after the server is recycled, the info is still there to be analyzed. But remember thresholdaction definition will be gone after server recycle. So a tip I am using is to add the create threshold statement into server RUN file.

There are other options to use, ie. Resource limit, multiple tempdb etc.

Bought a new laptop

2008-02-02T22:32:00.000-05:00

I made some profit from the recent stock market turmoil. To reward myself, I decided to buy a new laptop from DELL. Order was placed. I will get it in couple weeks. can't wait to see it.

Here is the link. It's a good deal valid till Feb 8, 2008
http://www.redflagdeals.com/deals/main.php/alldeals/comments/dellca_vostro_1400_laptop_w_c2d_t5270_1gb_ram_120gb_hd_windows_xp_home_599

With the laptop, I can be relax if I am in gym or public library. No need to worry about getting support calls and having to go home to access computer. I also expect more fun playing on Window XP. My desktop computer purchased last year is on Window vista which is too new for many software.

DST patch

2008-01-22T15:41:00.000-05:00

If your systems are used in US, then you probably have heard about DST patch. and I was surprised that even the latest EBF from Sybase contains the out-of-date java, which is not DST compliant. From enterprise point of view, it's better to upgrade all java binary up-to-date.

ASE has no logic for Daylight Savings Time. It relies entirely on the Operating System to return the date.
Basically ASE will change when the Operating System does. If the Operating System time changes, ASE gradually speeds up or gradually slows down its clock until in sync with the operating system.
From the official Sybase DST response document ("Daylight Savings Time and Sybase Products") :
"Most Sybase Server Products do not have any direct support for handling daylight saving time. ... Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. ... The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time."
For more information, see:
http://www.sybase.com/detail?id=1048699

There are 2 ways to update java to be DST compliant.
1) upgrade JDK to higher version than 1.4.2 - download from Sun web site, then cp it to the right directory
2) run tzupdater like this if the java is already 1.4.2
java -jar /$PATHTOtzupdater/tzupdater.jar -u

How to control Sybase process on OS level

2008-01-09T11:35:00.000-05:00

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.

Naming convention for Sybase instance

2007-12-28T15:47:00.000-05:00

There are many ways of naming Sybase instance, especially Sybase instance name can be as long as 30 characters. Much better than Oracle instance name limitation.

The key is to 1) keep it short 2) keep it meaningful 3)easy to distinguish server type.

The best naming convention I think is in following format
ApplicationName_ServerType

i.e. PAYROLL_PROD, PAYROLL_UAT, PAYROLL_DEV

Other information can be added to names are line_of_business, server location etc. but those are changing fast in today's business environment.

new job next Monday

2007-10-21T10:39:00.000-04:00

Due to office move and long commute time, I have decided to choose my own office.

Luckily I not only found a place with better office location, but also better position with leadership role required. yesterday was my last day. It's mixed feeling leaving this position as the people there are just terrific. It was great pleasure working with these peoples, especially the management team. They arranged farewell lunch on Thursday and just before I left office yesterday, the DBA team got together. Representing the team, our director gave me a watch and a greeting card donated by the team.

This is the best place I have ever worked in and best group of people. I will treasure every moment I had with them. I hope my new job will have similar experience for me. I am prepared to do my best in my new job and advance my career quickly as the new place is a bigger environment, which means more opportunities. In the future, I will post things I learned on technical side as well as soft skill side.

How to find out the orphaned users

2007-09-06T15:52:00.000-04:00

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

Replication server location

2007-08-16T13:45:00.000-04:00

Currently we have Sybase Replication residing on Unix server which has primary ASE server, the BRT server is on another unix box.

The problem with this setup is once the production server is down, the replication server will go down as well. The transaction in stable queue will be lost too.

The better option if there is no standalone machine for Replication server is to have Sybase replication server sitting on BRT box. This is what I am going to do in the next few weeks. Move the replication server from prod box to BRT box. I have to

1) move the RSSD db from primary server to secondary server.
2) setup Rep server and stable queues on BRT server.

I will post the steps required for this migration in another post.

finally found the root cause of a performance problem

2007-08-07T16:31:00.000-04:00

After I created a few new db on production server, the server become very slow. I have been looking around to find the root cause.

Finally found the problem last Friday. The problem was not caused by the new dbs. It's caused by the application. The app generated tons of log files in /tmp on the Unix server. Which slowed down the db server. After the files in /tmp were purged, the server is back to normal. As I observed, the server was slow before I implemented the new dbs, but sometime there is some office politics to play....

How to change tempdb ownership

2007-08-02T10:32:00.000-04:00

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.

create second tempdb

2007-05-16T11:22:00.000-04:00

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

extend dbccdb

2007-05-07T15:52:00.000-04:00

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

ssh to remote host without password

2007-04-05T09:52:00.000-04:00

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

turn off dsync for tempdb device

2007-03-27T14:53:00.000-04:00

sp_deviceattr ,dsync,false
go

reboot server

How to move tempdb off from master device

2007-03-27T14:45:00.000-04:00

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

How to separate data and log segment

2007-03-27T14:38:00.000-04:00

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

Sybase replication server performance tuning docs

2007-03-22T13:15:00.000-04:00

Found a tunning guide on sybase web site. Though it's out of date, but still useful for the most part.
http://www.sybase.com/content/1015811/ReplicationServerPNTv1_4.pdf

What's the best way to setup dbccdb?

2007-03-07T10:56:00.000-05:00

dbccdb is used to run dbcc checkstorage. Setting up dbccdb is time consuming. Once it's setup on one server, it can be copied to another server with similar configuration. In my case, I setup dbccdb on UAT server, it can be copied to UATBRT server via dump and load method. This is much easier and faster way to get dbccdb setup. Please note this is for servers with similar configuration and setup.
For servers with different configuration, you have to follow the procedures outlined in sybase system admin docs.

How to find out tables missing primary key

2007-03-01T14:25:00.000-05:00

Before setup replication server, I 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 76. 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).

what if logsegment is full because of replication

2007-02-22T13:58:00.001-05:00

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.

How to see the sql running on ASE

2007-02-22T13:56:00.000-05:00

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.

I will look into writing a perl script for this when I have time.

What to do when Sybase stable queue filled up?

2007-02-22T13:55:00.002-05:00

Last night, the stable queue of one of Sybase replication server was filled up very quickly. I was able to disabled the replication for a few db before the sq was completly full. To summarize, here are the things I will do next time.

1. take a snapshot of both Primary server and BRT server (sysprocesses, syslock, sp_who, syslogshold)

2. Rep Server: admin disk_space to check space usage in sq

3. Disable replication on ASE: sp_stop_rep_agent dbname, then sp_config_rep_agent dbname, ‘disable’

To prevend this from happenning again, we need to add repdef, patch Rep Server with newer EBF, enhance our monitoring to find out the problem earlier.

SAIC - California

2007-02-22T13:55:00.001-05:00

I noticed there is a reader from SAIC california. That reminded me my time in California a few years ago.

I went to California during the dot com boom. Instead of going to SF, I went to LA instead. I worked as DBA consultant for Arco in downtown LA, a well known oil company in California. The company was sold to BP (at the wrong time I guess). And BP decided to outsource the whole IT to SAIC. So I was helping them make the transition and worked for SAIC for short term of period. Once mission was completed, then it’s 911, the US market was stalled. I left California and came back to Canada for a full time job as DBA team lead in a health care organization. I still miss the good time I had in California. one of the best place in the world.

Sybase backup performance

2007-02-22T13:54:00.001-05:00

To improve Sybase backup performance, there are a few ways

1. test different -m value in backup server RUN file.
2. use stripe, the best is to stripe to different disks.
3. dump to disk, instead of tape,
4. use local backup server, instead of remote backup server.
5. don’t use compression

Sybase 15 upgrade over last weekend

2007-02-22T13:53:00.000-05:00

Over the weekend, I upgraded one of our production server from Sybase 12.5.0.3 to Sybase 15.0 ESD #2.

The upgrade was successful, though there were a scary situation happened. What I learned from this upgrade

1), never trust a script to do the db backup. DBA needs to manually create the script and run the script, create a separate directory to store the backup.

2), sybsecurity upgrade was not successful. I had to reinstall them from scratch. System tables are changed in Sybase 15.

What is SOX compliance

2007-02-22T13:51:00.000-05:00

I have been diligently working on SOX related issues for all the sybase servers in my company. What is really SOX compliance? why it’s named SOX.

Here is what I got from wiki (http://en.wikipedia.org/wiki/Sarbanes-Oxley_Act)

The Sarbanes–Oxley Act of 2002 (Pub. L. No. 107-204, 116 Stat. 745, also known as the Public Company Accounting Reform and Investor Protection Act of 2002 and commonly called SOX or Sarbox; July 30, 2002) is a United States federal law passed in response to a number of major corporate and accounting scandals including those affecting Enron, Tyco International, Peregrine Systems and WorldCom (recently MCI and currently now part of Verizon Businesses). These scandals resulted in a decline of public trust in accounting and reporting practices. Named after sponsors Senator Paul Sarbanes (D–Md.) and Representative Michael G. Oxley (R–Oh.), the Act was approved by the House by a vote of 423-3 and by the Senate 99-0. The legislation is wide ranging and establishes new or enhanced standards for all U.S. public company boards, management, and public accounting firms. The Act contains 11 titles, or sections, ranging from additional Corporate Board responsibilities to criminal penalties, and requires the Securities and Exchange Commission (SEC) to implement rulings on requirements to comply with the new law. Some believe the legislation was necessary and useful, others believe it does more economic damage than it prevents, and yet others observe how essentially modest the Act is compared to the heavy rhetoric accompanying it.

Holiday

2007-02-22T13:49:00.000-05:00

It has been busy for the last few weeks. especially for the weekend. we have too many tasks. Finally it slow down this week as Christmas is coming. This is another week of system freeze.

I also bought a new laptop for myself. Time to enjoy a bit. By the way, Yearly Bonus is coming this week. That’s extra sweet!

Sybase row level locking

2007-02-21T16:35:00.000-05:00

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.

setfile - File access control command

2007-02-21T16:34:00.000-05:00

Today we had a case that smurf account needs to read our sybase audit log files. The smurf account is not a member of Sybase group, so to make our sybase files readable by smurf account, In the old days, we can either create a new group, then put smurf and sybase account in to same group, then grant read acess to group member, or add sybase account to the smurf group. Both option requires some level of extra work or security concern.
A nice new feature from Solaris new version is the file access control. I used “setfacl” to grant the read access to smurf account without doing anything else. the command is

setfacl -m user:smurf:r– FileName

use getfacl FileName to display the status.

a nice new feature from Solaris is the file access control.

How to drop Sybase xp_cmdshell

2007-02-21T16:31:00.000-05:00

It’s long weekend here, well, another day, another trick learned. 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)

finally I figured out I 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)

Unix ENV variable - save some typing work

2007-02-21T16:29:00.000-05:00

Today I found a new trick, that when I setup ENV=~/.kshrc;export ENV in $HOME/.profile on Solaris, when I type ksh, the .kshrc gets automatically sourced.

If I don’t have ENV setup in .profile, I have to run “ksh” first, then ”. .kshrc” to source the file.

It saved a few typing each time I get on the server.

Copy directory to another machine using scp

2007-02-21T16:28:00.000-05:00

On solaris 10, scp is recommended, to copy a directory, I used following command

scp -r localfile RemotedBox:/xxxx/yyyy/

I was surprised that our Unix admin didn’t know the option and he had to search google.

How to delete file named "-l" on Unix box

2007-02-21T16:27:00.000-05:00

By accident, I created a huge file named “-I” on a critical production file system and made space usage 100% full. In very short time, I tried all the options I knew to delete it before I found the right way.

rm -I
rm “-I”
rm ‘-I’
rm ‘\-I’
rm \-I

the last one works, the right way to delete it is “rm ./-I”.

What's next available device number?

2007-02-21T16:26:00.000-05:00

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. Nice feature for DBA.

refresh UAT server from production server

2007-02-21T16:25:00.000-05:00

One of the tasks I do very often is to refresh our UAT server with production db dump. In addition to dump and load the user db, there are some additional things I have to copy it over.

master..syslogins
master..sysloginroles
master..syssrvroles - the srid maps to id in userdb..sysroles

There are some other system tables, ie. sysremotelogins etc.

How to see if a process is still running

2007-02-21T16:24:00.001-05:00

Once a process is started, we have no way to know if it’s doing anything. Sometimes, people are concerned that it’s hanging. A work around is to check the I/O counter value for the running process in sysprocesses table.

select physical_io from master..sysprocesses where spid = MYID

wait a second, then run again. If the second result is higher, that means it’s doing something.

change all files' group to sybase

2007-02-21T16:23:00.000-05:00

I found some of the files in sybase directory are under group name not sybase. To change every file to sybase. I issued command

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

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

How to delete all those sybase cfg files

2007-02-21T16:21:00.000-05:00

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 I did 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 \;

How to increase the size of existing device

2007-02-21T16:20:00.000-05:00

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”

How to recycle Sybase ASE without failover

2007-02-21T16:19:00.002-05:00

Our Sybase ASE server is built on VCS, the method to recycle Sybase Server with failover is as follow
Method 1:
disable the automatic failover for the resource as root,
shutdown the sybase server, shutdown or shutdown with nowait
startup sybase server
enable the automatic failover for the resource
Method 2
hares -offline xxxx -sys yyybox
wait until the sybase server is down completely
do ps -ef grep sybase to make sure all dataserver are down
hares -online -sys ( to startup sybase server and mark resource as online)

How to setup Sybase auditing

2007-02-21T16:19:00.001-05:00

have been setting up Sybase auditing across all UAT/production servers. 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.

How to log everything you did on unix server

2007-02-21T16:10:00.001-05:00

yesterday I was asked to check file permission of different directories and their subdirectories by auditors. Apparently, I can not do copy and paste from telnet window to MS word. 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 I typted and their output. All I need is to send the output file to the auditors. It may take weeks for them just to read the files, but that’s their business.

How to reset maint user password in Replication Server

2007-02-21T16:09:00.001-05:00

We have warm standby here, Due to security restriction, DBA is not allowed to know the maint user password, as maint user is an dbo, who have access to everything in the database. below is the steps I used to reset maint user password
1. login to Replication server,
resume connection to BRTserver.dbname
resume connection to PRODserver.dbname

2. login to BRT ASE server, run “sp_password” to reset maint user password to a new one.
3. Login to PROD ASE server, run “sp_password” to reset maint user password to a new one.
4, login to Replication server,
run “alter connection to BRTserver.dbname set password ”
run “alter connection to PRODserver.dbname set password ”
resume connection to BRTserver.dbname
resume connection to PRODserver.dbname

Sybase HA failback procedure

2007-02-21T16:07:00.000-05:00

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

It's tough last night

2007-02-21T16:04:00.001-05:00

There were three production problem calls last night, I had to wake up to fix the problems as I am primary oncall this week. 1 before mid night, 2 after mid night.

The first one is a space issue, the second one is the replication server issue, the third one is user connection issue that we run out of user connections, I had to use unix command to kill the database server to reconfigure the server. All were well, but I didn’t get into sleep well after 2am this morning. still managed to get into office on time this morning. Tough job as DBA.

It's tough last night

2007-02-21T16:04:00.000-05:00

Why the timestamp is not the date of upgrade?

2007-02-21T16:03:00.000-05:00

One interesting thing that’s confusing to people is that the timestamp of files changed during patch or upgrade is not the datetime of the day we were making the changes.

For example, I applied EBF13324 in July for one of our servers. dataserver was replaced, you can find the list of files in the release in EBF13324_Filelist.txt of the tar file. But when you go to the directory and do “ls -l”, you will find the timestamp is “Mar 24″. This has confused our auditors. They asked “did you really applied EBF in July?”

The reason is that we used Sybase installer to unload the files and the original timestamp was copied over. All the servers with same EBF will have the same timestamp, no matter when it’s applied.

ASE server setup: post-server-setup script

2007-02-21T16:01:00.000-05:00

Tasks to be done after sybase database server is setup.
# Create diskdump directory for all dbs using mkdir
echo “drop data dump device”
echo “setup data dump device for all dbs”
echo “setup log dump device for all dbs”
echo “make backup for all databases”
echo “bcp out important system tables
echo “change ASE configuration”
echo “create threshold action stored proc”
echo “create DBA personal ID”

Sybase ASE15 new features

2007-02-21T15:48:00.000-05:00

Today I browsed through Sybase ASE 15.0.1 new features. This version was released just a few days ago. Below are some useful new features I found.

configuration parameter - "startup delay", it controls when repagent is started during the server start. In old version, repagent starts at the same time and printed some error messages in sybase errorlog, saying couldn't call to rep server. This is a nice feature, but the name is little confusing. Why not name it "repagent startup delay"?

function - isdate(), isnumberic(). which return true/false regarding the input string.

check stable queue

2007-02-21T15:44:00.000-05:00

Over the weekend, I have added all the missing function repdef on one of our Replication server, the stable queue usage was down sharply after the change. To monitor the space usage of stable queue, I wrote a small shell script and schedule it to run once per hour for 1 week.

isql -Usa -S <<>> stableq.log
`cat pwd`
use RSSD
go
select getdate()
go
rs_helppartition
go
EOF

ASE server setup script : pre-setup

2007-02-20T16:28:00.000-05:00

Due to the large number of servers at the bank, DBA has to setup server quite often. Just like other large database shop, we have server setup standard. Below is the script I just created recently for server setup, Nothing fancy, it's very handy during setup.

export DSQUERY=
export DISKDUMP='
export DEVICE_DIR=''
export ERRORLOGDIR=''

###################################################
echo "create Sybase server home directory"

###################################################
mkdir ${DSQUERY}

###################################################
echo "creating sybase errorlog directory"

###################################################
ls -l $ERRORLOGDIRmkdir $ERRORLOGDIR/${DSQUERY}mkdir $ERRORLOGDIR/${DSQUERY}/history

###################################################
echo "creating sybase dump directory

###################################################
mkdir $DISKDUMP/${DSQUERY}ls -l $DISKDUMP/${DSQUERY}

###################################################
echo "check if we have write permission in ${DEVICE_DIR}"###################################################
touch ${DEVICE_DIR}/testfilels -l $${DEVICE_DIR}/testfilerm ${DEVICE_DIR}/testfile

###################################################
echo "check size of file systems for Sybase Server"

###################################################
df -k $ERRORLOGDIRdf -k $DISKDUMPdf -k $DEVICE_DIR

###################################################
echo "check if shared memory is setup"

###################################################
grep "shmsys:shminfo_shmmax" /etc/system

###################################################
echo "Next step: setup .kshrc in home directory"
echo " ftp sybase tar file to local box" #######################################

a script to generate bcp out for all tables

2007-02-20T16:24:00.000-05:00

As DBA, I was asked to export tables quite often, to speed up the process, also for my 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

#future enhancements are to interactively ask for values of loginId, Password, ServerName, dbName and export location

Welcome to James' sybase collection

2007-02-20T16:16:00.000-05:00

Welcome to James Xing’s Sybase DBA blog.

I am Sr Sybase DBA for one of the largest investment banks in Canada. Among the 100+ sybase servers, some of the systems are very critical, i.e. online banking system, derivative trading system, stock trading system. This site is a collection of my notes and thoughts during my DBA career. Sybase DBA web site is very rare on the internet due to different reasons. I hope this blog can be useful for the Sybase community.