Sybase Tips,Solutions,Administration

ASE Error messages on Android mobile

noreply@blogger.com (Sri) — Thu, 19 Jan 2012 11:18:00 +0000

Yes,today i found this interesting application DBA Sidekick(sybase) on android market.But not tried.I think it is useful to DBAs.
If any one used this application ,post a comment on this.

Here is the link for the android application

https://market.android.com/details?id=com.businesscompassllc.dbasidekicksybase&hl=en

List all stored procedures in ASE

noreply@blogger.com (Sri) — Wed, 11 Jan 2012 05:08:00 +0000

use sybsystemprocs
select name from sysobjects where type='P'

sp_dbremap

noreply@blogger.com (Sri) — Wed, 11 Jan 2012 05:04:00 +0000

A special procedure to be run only when instructed by ASE error message number 946. The procedure synchronizes the definition of a database in sysusages and memory.

sp_dbremap

If an alter database statement issued on a database that is in the process of being dumped is interrupted, Adaptive Server prints a message instructing the user to execute sp_dbremap.
Any changes to sysusages during a database or transaction dump are not copied into active memory until the dump completes, to ensure that database mapping does not change during the dump. Running alter database while doing database dump makes changes to system tables on the disk immediately. In-memory allocations cannot be changed until a dump completes. so alter database operation pauses.
When you execute sp_dbremap, it must wait until the dump process completes.
If you are instructed to run sp_dbremap, but do not do it, the space you have allocated with alter database does not become available to Adaptive Server until the next restart.

Troubleshoot if tempdb gets filled

noreply@blogger.com (Sri) — Mon, 09 Jan 2012 15:26:00 +0000

Try to find out the Active process that is filling up the temp db space.

1) If the transaction log of tempdb is full then you can login through sa and type following command.

1> dump tran tempdb with truncate_only
2> go

2) If the database is full then you can increase the size of the database on a free device.

1> alter database tempdb on device_name = size
2> go

3) use following command It will abort all open transactions.

But be sure the task by confirming with the concern users.

1>select lct_admin(0,2)
2>go

Restarting the server is not recommanded.
lct_admin (0,2) would abort all open transactions, or you can go for altering the tempdb space. Multiple tempdb's is a feature which can be implemented to minimize such issues of tempdb getting full.

Previously posted article on same topic refer that also.
http://sybaseadmin.blogspot.com/2011/12/temp-db-full.html

List Server Reserved Words

noreply@blogger.com (Sri) — Wed, 28 Dec 2011 11:08:00 +0000

Run this simple query in isql to find all the reserved words for Adaptive Server Enterprise .
To list all reserved words for Adaptive Server Enterprise, log in to isql and run this query:

select name from spt_values where type = "W"

The spt_values table is a look-up table in the master database that stores system values used by other server processes.

Installing sybase on ubuntu

noreply@blogger.com (Sri) — Mon, 26 Dec 2011 17:18:00 +0000

Just installed ASE15.0.3 ESD#1 on ubuntu 7.04 inside virtual box.

Download the ASE Developer’s Edition.

1)Install the following package(Need to put ubuntu 7.04 CD /DVD in Drive).

apt-get install libaio1

2)Sybase requires at lea Ist 300 MB shared memory,so increase.

/sbin/sysctl -w kernerl.shmmax=300000000

3)Unzip sybase installation file

tar -xvf ase_1503_linuxx86.tgz

4)Now run the setup

chmod +x ./setup

Choose Express Edition install while installing.

5)Append the contents of the SYBASE.sh to /etc/bash.bashrc.

6)We need to add sybase shared libraries to the library path.

gedit /etc/ld.so.conf.d/sybase.conf

7)Add these lines to the above file

/opt/sybase/OCS-15_0/lib3p
/opt/sybase/ASE-15_0/lib

8)Afterwards, update the paths

/sbin/ldconfig

9)In /opt/sybase/locales/locales.dat

Find the section:
[linux]

Add the line:

locale = en_IN, us_english, iso_1

Temp db full

noreply@blogger.com (Sri) — Mon, 26 Dec 2011 04:41:00 +0000

Since ASE 12.5 we can create a user defined tempdb (using sp_tempdb) and bind an admin login
to this new tempdb. This admin account can then run sp_who to find out who filled tempdb and kill the process.

Sometimes the kill won't work The new 'multiple tempdb' feature in 12.5.0.3 allows you to avoid the problem that tempdb is full and cannot access the fake tables in master.
In pre-12.5.0.3, we can get some information from different sources, like dbcc pss, dbcc lock and dbcc log

Follow these steps to solve tempdb full

(i) use 'tempdb_space' resource limits (in 12.5+) and

(ii) if your tempdb gets full anyway,use lct_admin to abort the transactions causing this.
Instead of this it's a good idea to

(iii) enable the 'abort tran on log full' option for tempdb so that LOG SUSPEND statuses will not remain until the DBA takes action.

orphan tables in tempdb

noreply@blogger.com (Sri) — Sun, 25 Dec 2011 15:21:00 +0000

If an spid that owns the table no longer exists in sysprocesses.
This happens sometimes when a killed spid isn't cleaned up properly,
leaving temp tables behind. These are called "orphaned" temp tables.

Running

    dbcc orphantables

will list orphaned tables in tempdb .

    dbcc orphantables ("drop")

will try to clean them up.

Top 10 large tables in database

noreply@blogger.com (Sri) — Fri, 23 Dec 2011 11:41:00 +0000

Top 10 largest tables in a database by rowcount:

select

top 10

    name,

    row_count(db_id(), id)

from

    sysobjects

order by

    row_count(db_id(),id) desc

Top 10 largest tables in a database by allocated space:

select

top 10

    name,

    reserved_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Allocated MB"

from

    sysobjects

order by

    reserved_pages(db_id(),id) desc

go

Top 10 largest tables in a database by used allocated space:

select

top 10

    name,

    used_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Used MB"

from

    sysobjects

order by

    used_pages(db_id(),id)(db_id(),id) desc

go

**These are not my own queries..Credit goes to Bret Halford on sybase forums.

sa Login locked?

noreply@blogger.com (Sri) — Thu, 22 Dec 2011 18:28:00 +0000

Add "-T4044" to the command line in the RUN_server file.
This will allow you to log in as 'sa'.

Hi to All

noreply@blogger.com (Sri) — Tue, 20 Dec 2011 13:28:00 +0000

Hi, All will get timely updates on this blog!!!

Maximum device size for ASE 12.5 and ASE 15.0

noreply@blogger.com (Sri) — Wed, 26 Jan 2011 04:09:00 +0000

Up to ASE 12.5.x the maximum device size is 32GB (provided
that the OS knows how to handle large [>2GB] files) because
of the way the device number is encoded in the sysdevices
table (i.e. only 24 bits are available to describe the file
itself).

In ASE 15 this limit has been lifted.

ASE-15 device and database limits per server are as follow:

- device size limit: 4 terabytes per device
- server limit: 2 billion logical devices per server
- database size limit: 32 terabytes per database
- server limit: 32767 databases per server

So, maximum storage per ASE server is over one million terabytes (32TB x
32767).

dump database three phases

noreply@blogger.com (Sri) — Mon, 24 Jan 2011 17:16:00 +0000

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.

how to get host/port,host name,server name,and db name in syabse

noreply@blogger.com (Sri) — Mon, 24 Jan 2011 15:21:00 +0000

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

Use of "with overrride" option in database creation

noreply@blogger.com (Sri) — Mon, 24 Jan 2011 12:27:00 +0000

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

dsync,direct io

noreply@blogger.com (Sri) — Wed, 19 Jan 2011 16:46:00 +0000

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)

Get a free copy of "Replication For Dummies"

noreply@blogger.com (Sri) — Sun, 29 Aug 2010 05:52:00 +0000

Click the following link ..to get a free copy of "Replication For Dummies"

http://goo.gl/8oEF

Clustered,Non-clustered Indexes

noreply@blogger.com (Sri) — Mon, 17 Aug 2009 16:01:00 +0000

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

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

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

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

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

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

**Unfortunately the manuals use the incorrect term**

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

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

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

APL/DPL/DRL are lockschems for the tables.

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

ASE 15.0 Release Schedule and CR list Information

noreply@blogger.com (Sri) — Thu, 13 Aug 2009 13:53:00 +0000

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

Zombie processes in ASE

noreply@blogger.com (Sri) — Mon, 27 Jul 2009 17:18:00 +0000

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

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

Displaying access information from sysprotects

noreply@blogger.com (Sri) — Fri, 17 Jul 2009 03:45:00 +0000

list privileges for each user

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

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

noreply@blogger.com (Sri) — Sun, 28 Jun 2009 14:36:00 +0000

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

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

Notes:

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

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

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

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

noreply@blogger.com (Sri) — Sat, 27 Jun 2009 17:18:00 +0000

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

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

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

sp_configure 'enable compatibility mode', 1

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

  set compatibility_mode on

How to convert a sybase backtrack dump to native dump

noreply@blogger.com (Sri) — Mon, 18 May 2009 14:43:00 +0000

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

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

How know when your ASE server was started??

noreply@blogger.com (Sri) — Tue, 17 Feb 2009 07:55:00 +0000

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