Home‎ > ‎

Sybase Interview Questions

ASE Interview Questions :

Adaptive Server Enterprises :

Q1: Please let me know system db. names, what is the purpose of sybsystemdb?

There are four kind of databases in SYBASE ASE:

1. System databases

2. Optional databases

3. Sample databases

4. User defined databases

1. System Database

There are five required system databases in Sybase ASE :

master DB

model DB

temp DB

sybsystemprocs DB

sybsystemdb

master DB  : contain metadata and brain of Sybase ASE. Its first DB which come online after boot.

model DB  : template DB. Whenever new DB is created , a copy of model DB is copied.

temp DB  : temporary database. Used during sorting and other temporary work.

sybsystemprocs DB : store all system procedure info.

sybsystemdb  : used for distributed transaction management system.

2. Optional Database

There are optional databases which are installed on the requirement of environment.

sybsecurity DB

dbccdb DB

sybmgmt DB

sybsecurity DB  : installed in server for auditing purpose.

dbcc DB  : for maintaing a consitensy information of database.

sybmgmt DB  : stores job schedule information.

3.Sample database

SYBASE ASE provided two sample database for learning and practice purpose.

pubs2

pubs3

3.User Defined Database

These databases created by userin SYBASE ASE.

 


System and Optional Databases

Master Database Controls the user databases and the operation of SQL Server as a whole. Known as master, it keeps track of such things as user accounts, ongoing processes, and system error messages. If the master database fails or repaired than server will became unavailable until you repair

Top Ten Crucial table in master database:

sysdevices

sysdatabases

sysusages

sysconfigure

syslogins

syssrvroles

sysloginroles

sysservers

sysresourcelimit

sysremotelogins

 

Keeping a copy (preferably offline) of these system tables: sysusages, sysdatabases, sysdevices, sysloginroles, and syslogins. You may want to create a script to perform these commands:

select * from sysusages order by vstart

select * from sysdatabases

select * from sysdevices

select * from sysloginroles

select * from syslogins

 

Model database

A template for new user databases. The build master program and the install  model script create model when SQL Server is installed. Each time the create

database command is issued; SQL Server makes a copy of model and extends it

to the size requested, if necessary.

It is house for those items you want available across all databases (rules,

defaults, user defined data types)

Temp DB can be referred as a workspace for the users to perform operations. It’s a volatile memory, so whenever the server is rebooted, it has to be recreated using the template from Model DB. Three kinds of table are created in the Tempdb,

Session level (table name prefixed with #) - A session level temporary table exists till the expiry of the session of the user

Global level (table name prefixed with tempdb) - A global level temporary tables exists till the server is rebooted

Workable tables- System creates this kind of tables like for sorting purpose.

SYBSYSTEMPROCS

 It is a modular program keep it all SP in it database, If Sybsystemprocs exists it

  will check to see whether you execute system SP.

 There are two types of Sybsystemprocs

  Stored Procedure

  System procedure

            Stored procedure:

A collection of SQL statements and optional control-of-flow statements stored

under a name. SQL Server-supplied stored procedures are called system

procedures.

_________________________________________________________________

System procedures:

_ Stored procedures that SQL Server supplies for use in system administration.

  These procedures are provided as shortcuts for retrieving information from the

        System tables, or mechanisms for accomplishing database administration and

         other  tasks that involve updating system tables.

        Sp_help, Sp_helptext, Sp_dboption, Sp_lock etc.

sybsystemprocs Database

Sybase system procedures are stored in the database sybsystemprocs. When a user in any database executes any stored procedure that starts with the characters "sp_", SQL Server first looks for that procedure in the user's current database. If there is no procedure there with that name, SQL Server looks for it in sybsystemprocs. If there is no procedure in sybsystemprocs by that name, SQL Server looks for the procedure in master.

If the procedure modifies system tables (for example, sp_adduser modifies the sysusers table), the changes are made in the database from which the procedure was executed.

To change the default permissions on system procedures, you must modify these permissions in sybsystemprocs.

Note: If you make changes to sybsystemprocs or add your own stored procedures to the database, you should back up the database regularly.

tempdb Database

SQL Server has a temporary database, tempdb. It provides a storage area for temporary tables and other temporary working storage needs (for example, intermediate results of group by and order by). The space in tempdb is shared among all users of all databases on the server.

The default size of tempdb is 2MB. Certain activities may make it necessary to increase the size of tempdb. The most common of these are:

Large temporary tables.

A lot of activity on temporary tables, which fills up the tempdb logs.

Large sorts or many simultaneous sorts. Subqueries and aggregates with group by also cause some activity in tempdb.

You can increase the size of tempdb with the alter database command. tempdb is initially created on the master device. Space can be added from the master device or from any other database device.

 

sybsystemdb database

Adaptive Server uses this database for tracking transactions and during recovery. In addition, it is used for applications using two-phase commit and Distributed Transaction Management (DTM).

The two-phase commit protocol allows client applications to coordinate transaction updates across two or more Adaptive Servers  treats those transactions as a single transaction. Two-phase commit guarantees that either all or none of the databases in the participating Adaptive Servers are updated.

The sybsystemdb database stores information about distributed transactions. Adaptive Server versions 12.0 and later can provide transaction coordination services for transactions that are propagated to remote servers using remote procedure calls (RPCs) or Component Integration System (CIS). Information about remote servers participating in distributed transactions is stored in the syscoordinations table.

The sybsystemdb database also stores information about SYB2PC transactions that use the Sybase two-phase commit protocol. The spt_committab table, which stores information about and tracks the completion status of each two-phase commit transaction, is stored in the sybsystemdb database.

sybsecurity Database

The sybsecurity database contains the audit system for SQL Server.
It consists of:

The sysaudits table, which contains the audit trail. All audit records are written into sysaudits.

The sysauditoptions table, which contains rows describing the global audit options.

All other default system tables that are derived from model.

pubs2 Sample Database

Installing the sample database (pubs2) is optional. Provided as a learning tool, pubs2 is the basis of most of the examples in the SQL Server documentation. See the SQL Server installation and configuration guide for information about installing pubs2.

sybsyntax Database

The syntax database, sybsyntax, contains syntax help for
Transact-SQL commands, Sybase system procedures, SQL Server utilities, and Open Client DB-Library(TM) routines. Users can retrieve this information using the system procedure sp_syntax. For example, to learn the syntax of the Transact-SQL select command, type:

sp_syntax "select"

SQL Server includes two scripts for creating the sybsyntax database. The first script, usually named ins_syn_sql, installs syntax help for Transact-SQL commands, Sybase system procedures, and SQL Server utilities. The second script, usually named ins_syn_dblib, installs syntax help for Open Client DB-Library routines. You can install one or both of these scripts.

Q2: Suppose our tempdb is filling up or filled up, you cant recycle the db server, then what would be your steps?

Dump tran < db_name > to < file_name >
OR
Dump tran < db_name > with truncate_only
OR
Dump tran < db_name > with no_log

Please read the sybase documents for more details.

Note : Dump tran will not work, if your database option is set
to "truncate log on checkpoint".

if still log is not cleared then, either you can kill the process
or can abort the transaction log by using lct_admin command.

If you want to increase the size of the transaction log of a database, you can increase it by using:
alter database < db_name > log on < logical_device_name > = '100M'

(ll)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.

Q3: Business Team(AD) is reporting the query slow performance, how will you investigate, pls consider all case.  (Hint: memory, stats, indexes, reorg,locks etc)

 

Q4: Suppose our temdb is not recovered ,can we create new database?

 

Q5:  We have configured 7 dataserver engines  for our PROD server(we have sufficient cpus), still we are facing the performance hit? Possible root cause?

 

Q6: Suppose we are doing the ASe 15 upgrade by dump & load , and in 12.5 server having 2000 logins. Since syslogins having different table structure in both enviorment, we cant use bcp,  how will we move these logins from 12.5 to 15.0?

HowTO migrate syslogins from ASE v12.5 to ASE v15 through bcp

First, “bcp OUT” syslogins from your ASE v12.5 dataserver:

bcp tempdb..syslogins out master.v125.syslogins.bcp -c -t"|" -r"\n" -Usa -SSYBPARFRDEV02_DS

Then, connect to your ASE v15 dataserver and create in tempdb a table based on the v12.5 syslogins table (you can reverse the syslogins ddl from Sybase Central or from DDLgen tool):

isql -Usa -SSYBPARFRDEV01_DS -w1000

create table tempdb..ase125logins (

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

)

lock allpages

on 'default'

go

Now you can “bcp IN” the v12.5 syslogins table into the ASE v15 dataserver:

bcp tempdb..ase125logins in master.v125.syslogins.bcp -c -t"|" -r"\n" -Usa -SSYBPARFRDEV01_DS

What’s next ? Connect to the ASE v15 dataserver, and insert the logins from the v12.5 syslogins table into the ASE v15 master..syslogins table. To avoid insert failures because of duplicate logins id, I

exclude here the logins ’sa’,'probe’ and ‘guest’:

isql -Usa -SSYBPARFRDEV01_DS -w1000

sp_configure 'allow updates',1

go

insert master..syslogins

select *,null,null,null,null,null

from tempdb..ase125logins

where name not in ('sa','probe','guest')

go

sp_configure 'allow updates',0

go

Character Set convertion issue:

Before to bcp syslogins, consider each dataserver’s character set. If character set are different between dataservers, you’ll need to modify your bcp commands accordingly. Let’s imagine this particular

case: SYBPARFRDEV02_DS is roman8 and SYBPARFRDEV01_DS is utf8.

“bcp OUT” then would be:

bcp tempdb..syslogins out master.v125.syslogins.bcp -c -t"|" -r"\n" -Jroman8 -Usa -SSYBPARFRDEV02_DS

“bcp IN” then would be:

bcp tempdb..ase125logins in master.v125.syslogins.bcp -c -t"|" -r"\n" -Jroman8 -Y -Usa -SSYBPARFRDEV01_DS

Option -Y specifies character-set conversion is disabled in the server, and is instead performed by bcp on the client side when using “bcp IN”.

Cross-platform issue:

Your syslogins is coming from a ASE dataserver prior to version 15.0.2 ? Then end-user passwords may not work anymore after a cross-platform bcp. Actually the hash value stored in

syslogins..password column is computed natively inside ASE, regarding the byte ordering of the platform (little/big endian). So, depending of platform endianess, you may have to reset all logins

passwords. Your syslogins comes from ASE 15.0.2 or above ? Then you should not have this problem. Indeed the hash value stored in syslogins..password column is no longer implemented natively

inside ASE. As of ASE 15.0.2, password hash value generation has been improved and is now platform-independent thanks to 3rd-party cryptographic libraries standing outside the ASE executable.

Q7: Which feature of ASE15.0 most impressed you and why?

 

 Q8: What is your org’s backup policy, what is dump tran with standby_access?

If user database log is completely full? or


How to take dump of transaction?


Start back_up server

 If user database log is rapidly filling up, then we dump transaction with truncate options.

 There are different -2 way to dump transaction, which are given below :

*  dump tran

*  dump tran with no truncate

*  dump tran truncate_only

*  dump tran with no_log

 

 -- STARTING BACK_UP SERVER

 It is mandatory to start backup serevr.

startserver -f SYB_BACKUP

go

                     /* dump tran */

 Its take back-up all transaction log and truncate inactive part of log, which means truncate executed part of transaction.

 -- SYNTAX : dump tran database_name to dump_device_name

  -- Example: dump tran testdb to dump_dev1

          /* dump tran with no truncate */

 Its take back-up all transaction log and does bot truncate any part of  transaction log.

  -- SYNTAX: dump tran database_name to dump_device_name with no truncate

  -- Example: dump tran testdb to dump_dev2 with no truncate

                  /* dump tran  with truncate_only */

 It’s just store allocation and deal location, It’s not taking complete back and truncate inactive part of log.

  -- SYNTAX: dump transaction database_name with truncate_only

  -- Example: dump tran testdb to dump_dev3 with truncate_only

                   /* dump tran  with no_log*/

 Its could not store allocation and deallocation, Its not taking complete back and truncate inactive part of log.

 After dump tran  with no_log dump of databases is in practice.

  -- SYNTAX: dump transaction database_name with no_log

  -- Example: dump tran testdb to dump_dev4 with no_log

Dangers of using with truncate_only and with no_log

with truncate_only and with no_log allow you to truncate a log that has become disastrously short of free space. Neither option provides a means to recover transactions that have committed since the last routine dump.

WARNING! Run dump database at the earliest opportunity to ensure that your data can be recovered.

The following example truncates the transaction log for mydb and then dumps the database:

dump transaction mydb

    with no_log

dump database mydb to ...

 

 

Bringing databases online with standby_access

with standby_access causes dump transaction to dump only completed transactions. It dumps the transaction log up to the point at which there are no active transactions. If you do not use with standby_access, the entire transaction log, including records for all open transactions is dumped

The syntax for with standby_access is:

dump tran[saction] database_name to...

            [with standby_access]

When do I use with standby_access?

Use dump tran[saction]...with standby_access when you are loading two or more transaction logs in sequence, and you want the database to be online between loads. For example, if you have a read-only database that gets its data by loading transaction dumps from a primary database. In this case, if the read-only database is used for generating a daily report based on transactions in the primary database, and the primary database’s transaction log is dumped at the end of day, the daily cycle of operations is:

On the primary database: dump tran[saction]...with standby_access

On the read-only database: load tran[saction]...

On the read-only database: online database for standby_access

WARNING! If a transaction log contains open transactions, and you dump it without using with standby_access, Adaptive Server does not allow you to load the log, bring the database online, and then load a subsequent transaction dump. If you are going to load a series of transaction dumps, you can bring the database online only after loading a dump originally made with standby_access, or after loading the entire series.

Bring databases online with standby_access

The online database command also includes a with standby_access option. Use for standby_access to bring a database online after loading it with a dump that was made using the with standby_access option.

WARNING! If you try to use online database for standby_access with a transaction log that was not dumped using the with standby_access option, the command fails.

Syntax

The syntax for online database is:

online database database_name [for standby_access]

 

How to take compresed back up in SYBASE ASE?

We have tot 9 compression levels on Sybase, the default compress level is on Sybase is "1".

We can use Compress and Stripes at the same time.

1) Using the compress level to reduce the file size.
2) Using the strips to create multiple device files.

Protecting tape devices:

While your taking the dump you can keep the "Retaindays" meanwhile
for example i have keep the retaindays as 5 days to my dump file .If i am trying to take same dump data with the same name it won't allow me to take dump with the same name before the 5 days.

syntax:

dump database dbname to 'compress::path' at  retaindays=num_of_days

Hope this wil be help for you....

It works only on UNIX Os...

 

Q9: What is log suicide ?

While suiciding the log can usually bring the server back online, it also frequently results in both physical and logical corruption in the database, because it bypasses the normal process of rolling back incomplete transactions in the log (and rolling forward completed transactions) that takes place during online recovery. Frequently, the resulting corruption is not encountered until a later time, and the connection with the earlier log suicide is not always recognized.
WARNING! Log suicide is highly risky, and is not recommended except in extreme cases.

Q10: When we require log suicide of a DB?                 

Database recovery with no transaction logs (in sybase it's termed suicide recovery)

If the dataserver crashes after an uncommitted transaction is written to the log but before the transaction completes, recovery upon startup reads the log and ensures that no uncommittedchanges are reflected in the database by rolling back the changes. Likewise,online recovery ensures that any changes recorded in the log for committedtransactions that have not yet been flushed to disk are updated on the datapages and written to disk by rolling forward the transactions.In prior versions of ASE partial recovery of a database was not possible. If recovery failed due to some corruption, there was no way to recover theuncorrupt portion of the database and bring it online. The only option was toeither recover from backups or “suicide” the log

 

Q11: What is the bypass recovery, when we require the bypass recovery?

Bypass recovery starts in Adaptive Server without recovering one or more databases. A database in this mode can be accessed even though it is not recovered. You use this mode to allow access to the database for problem analysis, to copy out data, and so on.

The reasons for having a bypass recovery could be full/corrupt/lost
transaction log, doing admin jobs or many more. 

You can give it a try to do a bypass recovery with setting status -32768
for the database and see if you can get this database fixed or repaired
with the common dbcc checks.

Using Aliases in Databases

The alias mechanism allows you to treat more than one person as the same user inside a database, so that they all have the same privileges. It is often used so that several users can assume the role of Database Owner. The alias mechanism can also be used to set up a collective user identity, within which the identities of individual users can be traced by auditing their activities.

For example, say several vice presidents want to use a database with identical privileges and ownerships. One way to accomplish this is to add a login named "vp" to SQL Server and the database; each vice president logs in as "vp". The problem with this method is that there is no way to tell the individual users apart. The other approach is to alias all the vice presidents, each of whom has his or her own SQL Server account, to the database user name "vp".

The following system procedures are used to manage aliases:

Table 4-5: : System Procedures for Managing Aliases

System Procedure

Task

Executed By

Where

sp_addalias

Add an alias for a user

DBO or SA

user database

sp_dropalias

Drop an alias

DBO or SA

user database

Adding Aliases: sp_addalias

Here's the syntax for sp_addalias :

 sp_addalias login_name, name_in_db 

login_name is the name of the user who wants an alternate identity in the current database. This user must have an account on SQL Server but cannot be a user in the current database.

name_in_db is the name of the database user to whom the first user wishes to be linked. This name must exist in both master..syslogins and in sysusers in the current database. Both parameters are required.

Executing sp_addalias maps the user with the specified login name to the user with the specified name_in_db . It does this by adding a row to the system tablesysalternates .

When a user tries to use a database, SQL Server checks for the user's server user ID number ( suid ) in sysusers . If it is not found, SQL Server then checkssysalternates . If the user's suid is found there, mapped to a database user's suid , the first user is treated as the second user while using the database.

As an example, suppose that Mary owns the database. She wishes to allow both Jane and Sarah to use the database as if they were its owner. Jane and Sarah have logins on SQL Server but are not authorized to use Mary's database. Mary executes these commands:

 sp_addalias jane, dbo 
 exec sp_addalias sarah, dbo

Now both Jane and Sarah can access Mary's database, and be recognized as its owner.

Dropping Aliases: sp_dropalias

The system procedure sp_dropalias drops the mapping of an alternate suid to a user ID, deleting the relevant row from sysalternates . Its syntax is:

 sp_dropalias   login_name   

login_name is the name of the user who was mapped to another user. Once a user's alias is dropped, the user no longer has access to the database, unless his or her name is then added to sysusers (with sp_adduser ) or the database has a guest" user in sysusers .

Getting Information on Aliases

To display information about aliases, use the sp_helpuser procedure. Here's an example:

 sp_helpuser dbo

 Users_name     ID_in_db    Group_name   Login_name   Default_db
 ----------     --------    ----------   ----------   --------- 
 dbo            1           public       sa           master 

 (1 row affected)

 Users aliased to user. 
 Login_name 
 ---------------------- 
 andy
 christa
 howard
 linda
  
 (4 rows affected)

 

Q12: What is the difference between shutdown and shutdown with no_wait, besides the immediate shutdown difference.

Shutdown with no_wait doesn’t make any checkpoint

sa_role is required for shutting down server.

shutdown will wait for completion of all running processes and do shut down gracefully.

shutdown with no wait will not wait completion of any process. It will kill all process immediately and server will proceed for winding up.

Example : Shutting down server

1> shutdown

2> go

Example : Shutting down with nowait

1> shutdown with nowait

2> go

Caution :: Please use shutdown with nowait on your risk. It can put database under risk and delay database recovery/downtime during startup.

 

Q13: Suppose in our one database  huge trans are going on, we issued the shutdown with no wait . Will it hit the server restart and how?

 

Q14: What is the named data cache, what is buffer pooling and how the cache hit effects the system performance?

 

Q15: We are getting stack traces for one of our databases? How will you investigate?

 

Q16: Is object level recovery possible in ASE?

 

Q17: What is the difference between sysstats and systabstats table?

 

Q18: What is histogram and what its default step value?

 

Q19: Why we requires non default step value in histogram ?

 

Q20: Can we run the update stat on one table one two-step (halt table in first time and after that  rest half of table)?

 

What are groups and what are roles ? What is difference between them ?

Group :

Administrator can grant and revoke certain set of permissions to group.

By default, every users belongs to a public group and avail all permission for public group. Users can grant or revoke from public group.In addition to public, users can defined only one dbo defined group.

Groups are exist within the database. Its database wide setting.

Commands for Group:-

 Creating group            : sp_addgroup <group_name>

 Adding Users in Group     : sp_adduser <login_name>, <group_name>

 Changing Group            : sp_changegroup <login_name>, <group_name>

 Dropping Users from Group : sp_dropuser <login_name>, <group_name>

 Dropping Group            : sp_dropgroup <group_name>

 Group Information         : sp_helpgroup <group_name>

Roles :

Roles are similar to groups while it is server wide setting.

Multiple roles permission granted to single users.

Roles are much preferred over group.

Commands for Roles:-

 Creating Roles                :  create role <user_defined_role_name>

 Granting Permission to Roles  :  grant <permission> to <user_defined_role>

 Assigning Roles to Login      :  grant role <user_defined_role_name> to <login_name> /*must be assigned from master database*/

 Enabling Roles                :  set role <user_defined_role_name> on

 Disabling Roles               :  set role <user_defined_role_name> off

 Dropping user defined role    :  drop role <role_name>

 Display Information of a role :  sp_displayroles <role_name>

How can we add login in the server and user in a database?

User should have privilage of sso_role.

1. Adding login in Server :

1> sp_addlogin login_name,passwd,default_database

2> go

If default_database is not specify then deault database will be master database.

Example:

1> sp_addlogin abhay,prod

2> go

Here login_name will be abhay and password will be prod. Default database is not specify hence default database will be master.

2. Adding User in a database

1> sp_adduser login_name

2> go

Example :

1> use testdb

2> go

1> sp_adduser abhay

2> go

abhay as user will be added in testdb database.

sp_adduser will make entry sysusers table in current database i.e. testdb.

What is sybase security model for any user/login?

SYBASE ASE follow 3 layered security model :

1.Server Level

2.Database Level

3.Object Level

1. Server Level: End User must have access to the server by log in. So for that, end user must have valid login on the server.

2. Database Level: End user must have permission to access database.So for that, end user must have valid user of the database.

3. Object Level: End user must have permission to use a given object.So for that, end user must have valid permission on the object.

What is the role required for user management?

sso_role required for user management.

 

What is guest user in database and why we require guest user?

For accessing any database,any login should have user of that database.

Guest user facilitate to any login for accessing the database without being user of database.

It is required for accessing any database without being user of database, just like tempdb .

1> select db_name()

2> go

------------------------------------------------------------

master

(1 row affected)

1> sp_helpuser

2> go

Users_name           ID_in_db         Group_name           Login_name

-------------------- ---------------- -------------------- --------------------

dbo                         1         public               sa

guest                       2         public               NULL

probe                       3         public               probe

(return status = 0)

1> use tempdb

2> go

1> select db_name()

2> go

------------------------------------------------------------

tempdb

(1 row affected)

1> sp_helpuser

2> go

Users_name           ID_in_db         Group_name           Login_name

-------------------- ---------------- -------------------- --------------------

dbo                         1         public               sa

guest                       2         public               NULL

(return status = 0)

 

Explain syslogins syssrvroles, sysloginroles and sysroles and whts the linkup among all?

Whats the importance of sysroles table in each database?

syssrvroles: It contain all existed roles information of a server. This table existed is master database. This table contain unique srid for pertaining roles.

syslogins:It contain all existed logins information of a server. This table existed in master database. This table contain unique suid for each pertaining login.

sysloginroles: It contain all allotted role information which is mapped to each login. Also existed in master database. It contain three column till 15.X. - srid,suid,status.status column signify that allotted role for pertaining srid is activated  0-Off, 1-on.

sysroles: It existed in every database. Whenever any database permission to allotted to any role , a entry is made in sysroles table. It contain srid column from master..syssrvroles and mapped to database lrid (local role ID).

 

Diffrence between 12.5 syslogins and 15.7 syslogins?


There are some more column are added in SYBASE ASE 15.7 syslogins table from comparison SYBASE ASE 12.5 syslogins :

SYBASE ASE 12.5 syslogins column and it's description

suid        : Server user ID

status      : Status of the account

accdate     : Date totcpu and totio were last cleared

totcpu      : CPU time accumulated by login

totio       : I/O accumulated by login

spacelimit  : Reserved

timelimit   : Reserved

resultlimit : Reserved

dbname      : Name of database in which to put user when connection established

name        : Login name of user

password    : Password of user (encrypted)

language    : User’s default language

pwdate      : Date the password was last changed

audflags    : User’s audit settings

fullname    : Full name of the user

srvname     : Name of server to which a passthrough connection must be established if the AUTOCONNECT flag is turned on.

logincount  : Number of failed login attempts; reset to 0 by a successful login.

procid      : Stores the login trigger registered with the login script option in sp_modifylogin

SYBASE ASE 15.7 syslogins new added column and it's description

lastlogindate : Timestamp for the user’s last login.

crdate        : Timestamp when the login or login profile was created.

locksuid      : The server user ID (suid) responsible for locking the login.

lockreason    : Reasons for lock; one of:

lockdate      : Timestamp when the login was locked.

crsuid        : Server user ID of the creator of login or login profile.

lpid          : Login profile ID

 

Can we change the password of other login, if yes, how?

Yes, Any user with sso_role can change password of other user.

Steps for changing other user password.

1. Login to SYBASE ASE

2. Check your assigned priviledge.You should have sso_role

3. Changing Passowrd of Other User.

SYNTAX : sp_password  'your_passord', 'New_password_for _other_user', 'other_user_name'

1. Login to SYBASE ASE.

2.Check your assigned priviledge

  1> sp_activeroles

  2> go

      Role Name

      ------------------------------------------------------------

3.Suppose that user who has log in with sso role ,his name is 'abhay' and his password 'abhay123'.

 And abhay is changing password of other user whose name is 'rambo' and his new password is 'rambo123'.

 1> sp_password 'abhay123','rambo123','rambo'

 2> go

 Password correctly set.

 (return status = 0)

 

What is the difference between syslogins and sysusers?

syslogins : existed in master database, contain information of every login in SYBASE ASE.

sysusers : existed in every database, contain information of every user of pertaining database.

 

Why do we require aliases?

Any login can access any database as user of database. If any login require access to any database , and we do not want him add as a user of that database, we add login as alias of existing user of database.

It means if we add any alias as existing user of database,alias will have all privilege of that existing login.

Whenever we add a user in any database, it makes entry in sysusers table.

While whenever we add a alias in any database, it makes entry in sysalternates table.

What is the difference between Update Lock and Exclusive Lock?

When Exclusive Lock is on any processes no other lock can be placed on that row or table. Every other process have to wait till Exclusive Lock is complete its tasks.

Update Lock is kind of Exclusive Lock except it can be placed on the row which already have Shared Lock on it. Update Lock reads the data of row which has Shared Lock, as soon as Update Lock is ready to change the data it converts itself to Exclusive Lock.

 

 

Interview Questions on User Management & Permissions

 

1. What is sybase security model for any user/login?

 

2. What is the diffrence between syslogins and sysusers?

 

3. How can we add the login in ase? What are the required parameter of sp_addlogin?

 

 

5. What’s the diff between role and group and which one is better?

 

6. How can we sync the logins from prod to uat server, how many tables we need take care for the login sync?

 

7. What is suid mismatch?

Generally, we load production database into DEV/UAT environment. As we know, every database contains sysusers table which contains the user information, and in the table each uid link with syslogins..suid(syslogins resides in master database of the Server). On both environment, there must be/may be different set of logins and associated suids.

When we load the Target Environment with Source environment database backup files ie from UAT to PROD , the loaded database will contain the sysusers table which came from the PROD environment(Source Environment) so it would contain suid of production environment, but here suid's are different (in some case production user does not exists on DEV UAT). So now this loaded database sysusers table will have wrongly mapped values. This issue is call as suid mismatch.

It is also possible that probctionDB..syslogins have a login RAX who has suid 10, while same suid 10 is alloted to WAM login in UAT/DEVdb...syslogins table. Here suid mismatch problem aries.

To Fix the issue , before executing   load database in UAT environment, take the backup (BCP OUT) of sysusers , 

sysalternates,  sysprotects and sysattributes.

After load, truncate the above table in loaded database and bcp in the table with backup which we have taken before starting load database.

This can also solve the issue, but it’s not feasible, after doing all steps both environment will have same set of users as well as password. So it is not a production practice.

delete UAT/DEV..syslogins table manually and upload to UAT/DEV..syslogins from PROD..syslogins.

What is the difference between sysstats and systabstats table?

systabstats It contains table level metrics information .These metrics are number of rows,  rows size in a table and no of page in a table etc.

 sysstatistics It contains column-level statistics for each column or column group of table. It also contains Histograms for columns. So there are many different types of rows, and hundreds or thousands of rows for the Histograms.

8. Why do we require aliases?

Any login can access any database as user of database. If any login require access to any database , and we do not want him add as a user of that database, we add login as alias of existing user of database.

It means if we add any alias as existing user of database,alias will have all privilege of that existing login.

Whenever we add a user in any database, it makes entry in sysusers table.

While whenever we add a alias in any database, it makes entry in sysalternates table.

 

10. Explain syslogins syssrvroles, sysloginroles and sysroles and whts the linkup among all?

Whats the importance of sysroles table in each database?

syssrvroles: It contain all existed roles information of a server. This table existed is master database. This table contain unique srid for pertaining roles.

syslogins:It contain all existed logins information of a server.This table existed is master database. This table contain unique suid for each pertaining login.

sysloginroles: It contain all allotted role information which is mapped to each login. Also existed in master database. It contain three column till 15.X. - srid,suid,status.status column signify that allotted role for pertaining srid is activated -- 0-Off, 1-on.

sysroles: It existed in every database. When ever any database permission to allotted to any role , a entry is made in sysroles table. It contain srid column from master..syssrvroles and mapped to database lrid (local role ID).

Backup & Recovery (DB Dump & Load)

How to add dump device for any database ?

How to take dump of any database ?

How to take stripped back up in SYBASE ASE?

How to take stripped compresed back up in SYBASE ASE?

If tempdb is getting fill?

If tempdb is completley filled up?

Refreshing the UAT data server?

How to perform bcp out ?

How to perform bcp in?

bcp

Tables which we need to take bcp out on daily basis

If tempdb is completley filled up?

----------------------------------- Temp DB Full -------------------------------------

/*There are three cases in which temp db full.

#Case 1. Some query make tempdb filling up rapidly and that make situation by which temp DB can full,very quickly .Then killed query , if required.

#Case 2. Tempdb is almost full and we are unable to do anything. In that case we make another tempdb and do following given below steps.

#Case 3. Tempdb is almost full or fill up and we need to extend space of tempdb.

( It is supposed that user is having sa_role,sso_role.)*/

------------------------------------------- Case 1. -------------------------------------------------------------------------

use master

/* Select MASTER DATABSE */

select * from syslogshold

/* find out (##) spid which is available at syslogshold table */

select * from sysprocess where spid= ##

/* get all information about pertainig spid */


sp_showplan ##,null,null,null

/*Find Out query plan ,##=> spid */


dbccdb traceon(3604)

/* a System Administrator can execute dbcc traceon(3604). Sybase recommends that you use this command if you think the output of error messages might overflow the error log.*/

/*Before you enable dbcc sqltext, you must first enable dbcc traceon to display the output to standard out:*/


dbcc sqltext (##)

/*Know sql_text for spid => ##*/

sp_who "##"

/* Know all information about spid=> "##" */

-- Send all information to application team and kill spid on their suggestion

kill ##

/* Kill spid=> ## if required */


-------------------------------------------- Case 2. -------------------------------------------------------------------------

You have to craete storage devices for creating temporary database. Here, storage devices are datadev and logdev.

/*Creating Temporary Database*/

create temporary database tempdb1 on datadev = '3M' log on logdev = '1M' -- initializing temporary database

sp_tempdb 'add', tempdb1,'default' -- adding it as a default database

sp_tempdb show, db -- list out all temporary database

sp_tempdb "bind", "lg", "sa", "GR", "default" -- binding login to temorary database

/*Binds login “sa” to the default group: The value for objtype in this example is login_name. You can substitute login_name with lg or LG.The value for bindtype in this example is group. You can substitute group with gr or GR*/


/* Repeate all step of Case 1. */

 

-------------------------------------------- Case 3. -------------------------------------------------------------------------

alter database tempdb on device_name='device_size'

/* increasing size of tempdb */

MDA TABLES INTERVIEW QUESTIONS

MDA tables - queries for data caches

There are three MDA tables available that you can use to monitor the behaviour of the data caches:

monDataCache - provides statistics for data caches

monCachePool - provides statistics for the pool defined for a data cache

monCachedObject - provides statistics for objects and indexes currently present in a data cache.

Here are some sample queries to gather information about the behaviour of your caches.

Show cache hit ratio and writes for all defined caches

This query will show the name of cache, the hit ratio (the percentage of requested pages that were already in cache) and how many modified pages were written out of the cache to disk.

select CacheName,

       convert(numeric(4,1),100-((convert(numeric(12,2),PhysicalReads)/convert(numeric(12,2),CacheSearches))*100)) as "Hit %",

       PhysicalWrites as "Writes"

       from monDataCache

       where   PhysicalReads <= CacheSearches

       order   by CacheName

Sample output

CacheName                      Hit %  Writes    

------------------------------ ------ -----------

default data cache               96.4      266340

summitdb                         84.2     1995251

summitdb syslogs                 98.5      193560

tempdb cache                     99.2     1080319

tempdb2 cache                    98.3      864662

The data returned is for the time the server booted until now. It's much better to run this type of query during a specified interval. Below is a sample stored procedure for this.

Show cache hit ratio and writes for all defined caches during a specified interval

create proc sp_mda_cache @interval char(10)

as

 

select *

       into #cache_begin

       from master..monDataCache

 

waitfor delay @interval

 

select *

       into #cache_end

       from master..monDataCache

 

select e.CacheName,

       convert(numeric(4,1),100-((convert(numeric(12,2),e.PhysicalReads-b.PhysicalReads)/

         convert(numeric(12,2),e.CacheSearches-b.CacheSearches))*100)) as "Hit %",

       e.PhysicalWrites - b.PhysicalWrites as "Writes"

       from  #cache_end e,

             #cache_begin b

       where e.CacheID = b.CacheID

       and   e.CacheSearches - b.CacheSearches != 0

       order by e.CacheName

go

Sample output:

1> sp_mda_cache "00:10:00"

2> go

 CacheName                      Hit %   Writes    

 ------------------------------ ------- -----------

 STKPoC                           100.0           0

 default data cache               100.0           0

 summitdb                         100.0           0

 tempdb cache                      99.9           0

 tempdb2 cache                     99.9           0

 

(5 rows affected)

(return status = 0)

Show the utilization of pools within a cache and the number disk reads

This query shows for each pool the effectiveness as a percentage, the number of disk reads and some other statistical info.

select CacheName,

       convert(char(3),IOBufferSize / 1024) + "Kb" as "IO Size",

       convert(numeric(4,1),(((PagesTouched * @@maxpagesize)/1024.) / AllocatedKB) * 100) as "Usage %",

       PhysicalReads,

       Stalls as "Dirty Reads",

       BuffersToMRU,

       BuffersToLRU

       from    master..monCachePool

       order   by CacheName, IOBufferSize

Sample output

CacheName                      IO Size Usage % PhysicalReads Dirty Reads BuffersToMRU BuffersToLRU

------------------------------ ------- ------- ------------- ----------- ------------ ------------

default data cache             2  Kb      85.6         86492           0        84136         2356

default data cache             16 Kb      76.9        188015           0      1446024        58096

 

(2 rows affected)

The data returned is for the time the server booted until now. It's much better to run this type of query during a specified interval. Below is a sample stored procedure for this.

Show the performance of pools within a cache during a specified interval

create proc sp_mda_cachepool @interval char(10)

as

 

select *

       into #cache_begin

       from master..monCachePool

 

waitfor delay @interval

 

select *

       into #cache_end

       from master..monCachePool

 

select e.CacheName,

       convert(char(3),e.IOBufferSize / 1024) + "Kb" as "IO Size",

       e.PhysicalReads - b.PhysicalReads as PhysicalReads,

       e.Stalls - b.Stalls as "Dirty Reads",

       e.BuffersToMRU - b.BuffersToMRU as BuffersToMRU,

       e.BuffersToLRU - b.BuffersToLRU as BuffersToLRU

       from    #cache_end e,

               #cache_begin b

       where   e.CacheID       = b.CacheID

       and     e.IOBufferSize  = b.IOBufferSize

       order   by e.CacheName, e.IOBufferSize

go

Sample output

1> sp_mda_cachepool "00:05:00"

2> go

CacheName                                                    IO Size    PhysicalReads Dirty Reads BuffersToMRU BuffersToLRU

------------------------------------------------------------ ---------- ------------- ----------- ------------ ------------

default data cache                                           2  Kb                211           0          211            0

default data cache                                           16 Kb                  0           0            0            0

 

(2 rows affected)

(return status = 0)

What objects and indexes are in cache and how much space do they take

select CacheName,                                                                                                                       

       case when IndexID = 0 then

         convert(char(60),DBName + "." + isnull(OwnerName,"dbo") + "." + ObjectName)

       else

         convert(char(60),DBName + "." + isnull(OwnerName,"dbo") + "." + object_name(ObjectID,DBID) + "." + ObjectName)

       end as "Table / Index",

       CachedKB

       from    master..monCachedObject

       order   by CacheName,2

The cache wizard of sp_sysmon

The cache wizard of sp_sysmon also uses the MDA tables. This tool can be used to see if your caches are performing well and which objects are present in cache. Sample invocation:

sp_sysmon "00:10:00","cache wizard"

What are MDA Tables?

MDA tables ASE 12.x introduced new Monitoring and Diagnostic (MDA) tables, which store information about currently executing processes and statements and allows a DBA to get detailed information about ASE resources.

Before SYBASE ASE 15.0, MDA table installed seperately (by installmontables scripts).In SYBASE ASE 15.0 and later , it became inbuld property.

You can execute queries to report information about the activity of server processes and applications, query performance, usage of 

database tables,efficiency of data caches, I/O activity on database devices, and many other aspects of the Adaptive Server that 

affect system performance.

The data in the monitoring tables is not stored on disk. The data is calculated when you execute a query on one of the monitoring 

tables.

“You must have the mon_role to query these tables.”

This is helpful for performace and tuning (slow performance, hung queries, queries consuming 100% CPU). These MDA table contain 

historical data.

These hitorical information is based on :

• Process Activity: CPU usage, IO activity, resource usage

• Resource usage: Data cache, procedure cache, engines

• Object usage: Tables, partitions, indexes, stored procedures

• Query history: SQL text, statement metrics, query plans, errors

These tables and with their realtive importance given below :

CPU DISK IO :

monengine

monIOqueue

monDeviceIO

QUERY SLOW PERFORMANCE:

monProcess

monOpenDatabases

monSysWaits

monWaitEventInfo

monProcessWaits

monProcessLookup

monProcessNetIO

monProcessObject

monProcessActivity

monProcessProcedures

monProcessStatement

monSysStatement

monProcessSQLText

monSysSQLText

monSysPlanText

monSysStatement

BLOCKING

monDeadLock

monLocks

TABLE STATISTICS

monOpenObjectActivity

monOpenPartitionActivity

Memory Information

monDataCache

monCachePool

monCachedObject

monProcedureCache

monCachedProcedures

Which role/configuration param is required to assign/enable for executing MDA table ?

The 'enable cis configuration parameter should be enabled (set to a value of 1). By default , this param is enable.

The enable monitoring configuration parameter determines whether other monitoring options are enable; set enable monitoring to 1. It should be enable.

1> sp_configure “enable monitoring”, 1

2> go

Error 12036

 Every MDA table require different set of configuration params enable.If you query the monitoring tables, but have not enabled all 

the configuration parameters the tables require, Adaptive Server issues error 12036 but still runs the query. Although many of the 

monitoring tables contain accurate data even if you have not  enabled all the configuration parameters, some data is incorrect    

because Adaptive Server is not collecting the data required to populate one or more columns in the table

 

mon_role should be assign for executing MDA table.

 

Performance and Tuning

What are the basics of performance tuning?

1: Indexes improve select performance.

2: While writing queries few things must be considered – Use operators =,>,<,>=,<=,like,between.

3: Replace the BETWEEN with >= and <= operators because BETWEEN in turn converted to mentioned oprators. So with we can reduce one step.

4: Don’t use functions in the WHERE clause. For e.g. select col1 from Table1 where upper(col1) = col2 — If we are having index created on columns col1 and col2 then because of function query engine will not use the index in select operation.

5: Similarly don’t use the mathematical expression in WHERE clause. For e.g. select col1 from Table1 where (col1 * 3) = col2

6: One of the most common pitfall is mismatch in datatype on both side of join. for e.g. select col1 from table1 where col1=col2 — if datatype of col1 and col2 are different then it reduces the performance of query.

What is locking and how many types of locking in SYBASE ASE?

Whenever any kind of operation perform in tables, SYBASE ASE implement a locking scheme in that table.So that other operation could not affect current operation and consistency of data in that table. While locking prevent another processes from accessing process data which some time leading blocking and deadlock.


Locking scheme affects tables in SYBASE ASE in three ways.

Row level Locking

Page Level Locking

Table/All page Level Locking

Datarows locking which locks only the data rows.Index rows and pages are not locked.

Datapages locking which locks only the data pages.

Allpages locking which locks datapages and index pages.

 

 

Page / Row level locking


There are three type of lock is made on page/row level locking :

1.Shared locks Shared lock is applied in a table for read operation . Many read transaction can be perform in a table or page while data modification will not allowed during shared lock.Data modification transaction wait for release existing shared lock.

2.Exclusive locks Exclusive lock applies during data modification. When a transaction acquire exclusive lock , no other transaction can acquire exclusive lock.Other transaction wait for releasing existing exclusive lock.

3.Update locks – Initial phase of data modification, ASE applies update lock in a data. Update lock allow for shared lock, while does not allow for exclusive lock.

Table level Locking


1.Intent lock – If any transaction is performing with page-level and row-level lock, and it require other row/page level lock for completion of transaction. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock.

2.Shared lock – similar to a shared page or row lock, except that it affects the entire table. A create nonclustered index command also acquires a shared table lock.

3.Exclusive lock – similar to an exclusive page or row lock, except that it affects the entire table.

Scenarios considered for performance tuning?

Scene 1 -- If you are supposed to search range of values then use clustered index because in range search values will be at consecutive location in sorted order after clustered index creation. So effort for engine would be reduced.

Scene 2 -- Use stored procedure instead of individual queries because

- It reduces the network traffic

- Query plan gets created once for same query which could be re-used instead of recreating again and again.

- Stored procedure can be invoked by passing different parameters instead of sending newly created query again and again.

Scene 3 - Make sure there are no table scan on large tables.

Performance Tuning - Scenario Based Part 1?

Scenario -------- If an application makes multiple statement and each statement calls to a specific stored procedure. Thereby, being a multithreaded application it will call stored procedure multiple times at a time. It has been noticed that for some calls procedure takes longer time for different set of parameters. What could be the issue?

Solution -------- First we can’t blame sqarely on stored procedure for the slower performance. We have few options here

1. Reduce the number of concurrent calls to stored procedure.

2. Check whether the procedure is calling any child procedure with input parameter or not. Suppose child procedure is looking for location and if parent procedure is passing location as an input parameter and while the absence of this input parameter child procedure might be processing all available locations, which could be in huge number, and consuming time. So before calling the child procedure, apply proper check which would reduce the time consumptions for remaining calls.

Performance Tuning - Scenario Based Part 2?

Suppose we have a join order as below in our query

tmp > Table1 > Table2>

but query plan would be making join order as below

Table1 > Table2 > #tmp

and it would be causing a whole index scan on both the tables Table1 and Table2 (which is as per the user expectation) before we use temp table to filter out results. However, still we can get performance improvement.

Solution --> We can use "set forceplan on" to force the join order. Though we will have table scan on Table1 and Table2 because of "forcing", we still could get the performance improvement by avoiding index scan on the large tables Table1 and Table2 because table scan on less number of records (due to filteration by #tmp table) can be faster than index scan on large number of records.

Blocking in the Sybase Dataserver

IF THERE IS BLOCKING IN THE DATASERVER


Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check for blocking spids

Cmd:

1>use master

2>go

1>select spid,blocked from sysprocesses where blocked >0

2>go

Example:

1> select spid,blocked from sysprocesses where blocked >0

2> go

spid   blocked

------ -------

   177     627   177 : blocked spid, 627: blocking spid

   314     627 

   559     843   559 : blocked spid, 843: blocking spid

   778     843 

(4 rows affected)


Step3 :- If in above step if there is some blocked spids, you need to monitor the blocking spid ( which is blocking other process to run ie 627, 843)

If for the blocking spid physical io is not moving , we need to esclate to App Team, If its is moving we can wait for some time(10 -15 mins).

If blocking is not clearing in 10-15 mins , we need to esclate to APP team.


Cmd:

1>use master

2>go

1>select spid,cmd,physical_io from sysprocesses where spid=<blocking spid>

2>go

Example :

1> select spid,cmd,physical_io from sysprocesses where spid=627

2> go

spid   cmd              physical_io

------ ---------------- -----------

   627 REORG               25370412

(1 row affected)

1> select spid,cmd,physical_io from sysprocesses where spid=627

2> go

spid   cmd              physical_io

------ ---------------- -----------

   627 REORG               25370412 <----- Physical io is not moving

(1 row affected)

1> select spid,cmd,physical_io from sysprocesses where spid=843

2> go

spid   cmd              physical_io

------ ---------------- -----------

   843 INSERT                     0

(1 row affected)

1> select spid,cmd,physical_io from sysprocesses where spid=843

2> go

spid   cmd              physical_io

------ ---------------- -----------

   843 INSERT                     0 <----- Physical io is not moving

(1 row affected)


Step 4:- Now we need to find the detail of blocking spid as below

Cmd:

1>use master

2>go

1>select * from sysprocesses where spid=<blocking spid>

2>go

1>sp_who "<blocking spid>"

2>go

1>dbcc traceon(3604)

2>go

1>dbcc sqltext(<blocking spid>)

2>go

1>sp_showplan <blocing spid>,null,null,null

2>go

1>sp_lock <blocking spid>

2>go

Example :

I have colllected the detail for 843 , you need to find out detail for all the blocking spids.

1> select * from sysprocesses where spid=843

2> go

spid   kpid        enginenum   status       suid        hostname   program_name     hostprocess cmd              cpu

        physical_io memusage    blocked dbid   uid         gid

        tran_name                                                        time_blocked network_pktsz fid

        execlass                       priority   affinity                       id          stmtnum     linenum     origsuid

        block_xloid clientname                     clienthostname                 clientapplname                 sys_id

        ses_id      loggedindatetime           ipaddr

------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- -----------

        ----------- ----------- ------- ------ ----------- -----------

        ---------------------------------------------------------------- ------------ ------------- ------

        ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- -----------

        ----------- ------------------------------ ------------------------------ ------------------------------ ------

        ----------- -------------------------- ---------------

   843   283443444           4 lock sleep           546 nyggmgrotc RESETREP         23822       INSERT                     3

                  0          22     627      4         405       16390

        $user_transaction                                                       15143           512      0

        EC2                            HIGH       ANYENGINE                        637764804           5         439        NULL

                  0                                                                                                   0

                  0        Jun 12 2011 11:24AM 10.152.115.100

(1 row affected)

1> sp_who "843"

2> go

fid    spid   status       loginame                       origname                       hostname   blk_spid

        dbname                         cmd              block_xloid

------ ------ ------------ ------------------------------ ------------------------------ ---------- --------

        ------------------------------ ---------------- -----------

     0    843 lock sleep   lcprusr                        lcprusr                        nyggmgrotc 627

        summitdb                       INSERT                     0

(1 row affected)

(return status = 0)

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(843)

2> go

SQL Text: 320110714CONV_AVG

AUTO      CARRY

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_showplan 843,null,null,null

2> go

QUERY PLAN FOR STATEMENT 5 (at line 439).

 

   STEP 1

       The type of query is INSERT.

       The update mode is direct.

       TO TABLE

           dmASSET_HIST

       Using I/O Size 2 Kbytes for data pages.

(return status = 0)

1> sp_lock 843

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    843        1686 Ex_intent                      589764633           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         589764633      199169      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      635720842           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         635720842    22607248      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      667720956           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         667720956    46143296      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1076770418           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1108770532           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1764772869           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb

        Non Cursor Lock

(16 rows affected)

(return status = 0)

Step 5:- With above detail you need to send the mail to APP Team and need to do follwoup untill it is not released.

How can we check for the long running trans?

Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check the long running trans as below

If there is any row in syslogshold, see the starttime column and current time, If there is big diff, it means you have log runningg trans in dataserver and you need to esclate to APP team.

cmd:

use master

go

select * from syslogshold

go

select getdate()

go

Example:

1> select * from syslogshold

2> go

dbid   reserved    spid   page        xactid         masterxactid   starttime

        name                                                                xloid

------ ----------- ------ ----------- -------------- -------------- --------------------------

        ------------------------------------------------------------------- -----------

     4           0    627    43859290 0x029d3d5a0005 0x000000000000        Jun 12 2011  9:34AM

        $REORG REBUILD dmASSET_HIST ID=621764747                                   1254

     2           0    314      332908 0x0005146c000a 0x000000000000        Jun 12 2011  3:02PM

        $ins                                                                        628

(2 rows affected)

1> select getdate()

2> go

 --------------------------

        Jun 12 2011  4:00PM

(1 row affected)

 


Step 3:- Now we need to find the detail of blocking spid as below

cmd:

use master

go

select * from sysprocesses where spid=<blocking spid>

go

sp_who "<blocking spid>"

go

dbcc traceon(3604)

go

dbcc sqltext(<blocking spid>)

go

sp_showplan <blocing spid>,null,null,null

go

sp_lock <blocking spid>

go

example : (I have colllected the detail for 843 , you need to find out detail for all the blocking spids)

1> select * from sysprocesses where spid=843

2> go

spid   kpid        enginenum   status       suid        hostname   program_name     hostprocess cmd              cpu

        physical_io memusage    blocked dbid   uid         gid

        tran_name                                                        time_blocked network_pktsz fid

        execlass                       priority   affinity                       id          stmtnum     linenum     origsuid

        block_xloid clientname                     clienthostname                 clientapplname                 sys_id

        ses_id      loggedindatetime           ipaddr

 ------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- -----------

        ----------- ----------- ------- ------ ----------- -----------

        ---------------------------------------------------------------- ------------ ------------- ------

        ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- -----------

        ----------- ------------------------------ ------------------------------ ------------------------------ ------

        ----------- -------------------------- ---------------

   843   283443444           4 lock sleep           546 nyggmgrotc RESETREP         23822       INSERT                     3

                  0          22     627      4         405       16390

        $user_transaction                                                       15143           512      0

        EC2                            HIGH       ANYENGINE                        637764804           5         439        NULL

                  0                                                                                                   0

                  0        Jun 12 2011 11:24AM 10.152.115.100

(1 row affected)

1> sp_who "843"

2> go

fid    spid   status       loginame                       origname                       hostname   blk_spid

        dbname                         cmd              block_xloid

------ ------ ------------ ------------------------------ ------------------------------ ---------- --------

        ------------------------------ ---------------- -----------

     0    843 lock sleep   lcprusr                        lcprusr                        nyggmgrotc 627

        summitdb                       INSERT                     0

(1 row affected)

(return status = 0)

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(843)

2> go

SQL Text: 320110714CONV_AVG

AUTO      CARRY

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_showplan 843,null,null,null

2> go

 QUERY PLAN FOR STATEMENT 5 (at line 439).

   STEP 1

       The type of query is INSERT.

       The update mode is direct.

       TO TABLE

           dmASSET_HIST

       Using I/O Size 2 Kbytes for data pages.

(return status = 0)

1> sp_lock 843

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    843        1686 Ex_intent                      589764633           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         589764633      199169      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      635720842           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         635720842    22607248      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      667720956           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         667720956    46143296      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1076770418           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1108770532           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1764772869           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb

        Non Cursor Lock

(16 rows affected)

(return status = 0)

Step 4:- With above detail you need to send the mail to APP Team.

How can we check for the long running trans?

Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check the long running trans as below

If there is any row in syslogshold, see the starttime column and current time, If there is big diff, it means you have log runningg trans in dataserver and you need to esclate to APP team.

cmd:

use master

go

select * from syslogshold

go

select getdate()

go

Example:

1> select * from syslogshold

2> go

dbid   reserved    spid   page        xactid         masterxactid   starttime

        name                                                                xloid

------ ----------- ------ ----------- -------------- -------------- --------------------------

        ------------------------------------------------------------------- -----------

     4           0    627    43859290 0x029d3d5a0005 0x000000000000        Jun 12 2011  9:34AM

        $REORG REBUILD dmASSET_HIST ID=621764747                                   1254

     2           0    314      332908 0x0005146c000a 0x000000000000        Jun 12 2011  3:02PM

        $ins                                                                        628

(2 rows affected)

1> select getdate()

2> go

 --------------------------

        Jun 12 2011  4:00PM

(1 row affected)

 


Step 3:- Now we need to find the detail of blocking spid as below

cmd:

use master

go

select * from sysprocesses where spid=<blocking spid>

go

sp_who "<blocking spid>"

go

dbcc traceon(3604)

go

dbcc sqltext(<blocking spid>)

go

sp_showplan <blocing spid>,null,null,null

go

sp_lock <blocking spid>

go

example : (I have colllected the detail for 843 , you need to find out detail for all the blocking spids)

1> select * from sysprocesses where spid=843

2> go

spid   kpid        enginenum   status       suid        hostname   program_name     hostprocess cmd              cpu

        physical_io memusage    blocked dbid   uid         gid

        tran_name                                                        time_blocked network_pktsz fid

        execlass                       priority   affinity                       id          stmtnum     linenum     origsuid

        block_xloid clientname                     clienthostname                 clientapplname                 sys_id

        ses_id      loggedindatetime           ipaddr

 ------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- -----------

        ----------- ----------- ------- ------ ----------- -----------

        ---------------------------------------------------------------- ------------ ------------- ------

        ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- -----------

        ----------- ------------------------------ ------------------------------ ------------------------------ ------

        ----------- -------------------------- ---------------

   843   283443444           4 lock sleep           546 nyggmgrotc RESETREP         23822       INSERT                     3

                  0          22     627      4         405       16390

        $user_transaction                                                       15143           512      0

        EC2                            HIGH       ANYENGINE                        637764804           5         439        NULL

                  0                                                                                                   0

                  0        Jun 12 2011 11:24AM 10.152.115.100

(1 row affected)

1> sp_who "843"

2> go

fid    spid   status       loginame                       origname                       hostname   blk_spid

        dbname                         cmd              block_xloid

------ ------ ------------ ------------------------------ ------------------------------ ---------- --------

        ------------------------------ ---------------- -----------

     0    843 lock sleep   lcprusr                        lcprusr                        nyggmgrotc 627

        summitdb                       INSERT                     0

(1 row affected)

(return status = 0)

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(843)

2> go

SQL Text: 320110714CONV_AVG

AUTO      CARRY

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_showplan 843,null,null,null

2> go

 QUERY PLAN FOR STATEMENT 5 (at line 439).

   STEP 1

       The type of query is INSERT.

       The update mode is direct.

       TO TABLE

           dmASSET_HIST

       Using I/O Size 2 Kbytes for data pages.

(return status = 0)

1> sp_lock 843

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    843        1686 Ex_intent                      589764633           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         589764633      199169      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      635720842           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         635720842    22607248      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                      667720956           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                         667720956    46143296      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1076770418           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1108770532           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_intent                     1764772869           0      0 summitdb

        Non Cursor Lock

     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb

        Non Cursor Lock

(16 rows affected)

(return status = 0)

Step 4:- With above detail you need to send the mail to APP Team.

How can we print Deadlock information in errorlog?

Yes, by enabling configuration parameter print deadlock information into 1, any one can print deadlock information in to  errorlog.

Printing deadlock information into errorlog can degrading performance of SYBASE ASE.

1> sp_helpconfig 'print deadlock information'

2> go

print deadlock information enables printing of deadlock information to the error log.

Minimum Value                  Maximum Value                  Default Value                  Current Value                  Memory 

Used                Unit         Type           Instance Name

------------------------------ ------------------------------ ------------------------------ ------------------------------

0                              2                              0                              0                           

0                number       dynamic        IRVDEVDB02

(return status = 0)

/* Here current value is 0.Its mean this parameter is not enabled.*/

1>  sp_configure 'print deadlock information',1

2> go

Parameter Name                                               Default                                  Memory Used            

Config Value                             Run Value                                Unit                                     

Type                                     Instance Name          

------------------------------------------------------------ ---------------------------------------- ----------------------

print deadlock information                                             0                                        

0                       1                                        0                             

number                                   dynamic                                  cluster-wide           

(1 row affected)

(return status = 0)

/* Here current value is 1.Its mean this parameter is enabled.*/

If user database log is completely full?

If user database log is completely full? or


How to take dump of transaction?


 Start back_up server

 If user database log is rapidly filling up, then we dump transaction with truncate options.

 There are different -2 way to dump transaction, which are given below :

*  dump tran

*  dump tran with no truncate

*  dump tran truncate_only

*  dump tran with no_log

 

 -- STARTING BACK_UP SERVER

 It is mandatory to start backup serevr.

startserver -f SYB_BACKUP

go

                     /* dump tran */

 Its take back-up all transaction log and truncate inactive part of log, which means truncate executed part of transaction.

 -- SYNTAX : dump tran database_name to dump_device_name

  -- Example: dump tran testdb to dump_dev1

          /* dump tran with no truncate */

 Its take back-up all transaction log and does bot truncate any part of  transaction log.

  -- SYNTAX: dump tran database_name to dump_device_name with no truncate

  -- Example: dump tran testdb to dump_dev2 with no truncate

                  /* dump tran  with truncate_only */

 Its just store allocation and deallocation, Its not taking complete back and truncate inactive part of log.

  -- SYNTAX: dump transaction database_name with truncate_only

  -- Example: dump tran testdb to dump_dev3 with truncate_only

                   /* dump tran  with no_log*/

 Its could not store allocation and deallocation, Its not taking complete back and truncate inactive part of log.

 After dump tran  with no_log dump of databases is in practice.

  -- SYNTAX: dump transaction database_name with no_log

  -- Example: dump tran testdb to dump_dev4 with no_log

How can we take striped & compressed backup in ASE?

What is the local and global indexes?

Local and global indexes concept came up with partioned table after 15.x.

A global index – spans all data partitions in a table

A local index – spans in exactly one data partition.

Prior to Adaptive Server 15.0, all indexes were global. With Adaptive Server 15.0, you can create local as well as global indexes. An unpartitioned table can have only unpartitioned, global indexes.

A partitioned table can have partitioned local and unpartitioned global indexes.

Local indexes allow multiple threads to scan each data partition in parallel, which can greatly improve performance

What is update Statistics and how is it useful ?

SYBASE ASE has cost based optimizer. It generate more than one query plan for

given query and execute less costly query plan. Cost is calculated beaed on statistics of table and index.

If statistics are stale than optimizer calculate cost based on stale statistics whichwont be optimize.

When statistics are updated, optimizer calculate accurate cost for given query.

Update statistics update statiscs and optimzer calulate cost affective query plan.

Query - How to get current database segment mapping?

select db_name(dbid),dbid, lstart, segmap, name as 'segment name'

from syssegments s, master..sysusages u

where u.segmap & power(2,s.segment) != 0

and dbid = db_id()

order by 1,2

 

Day-to-Day Administration

How can we see the sql text?

use master

go

-- log in master database

sp_who

go

-- get brief information of all running process with spid.

 

dbcc traceon(3604)

go

-- enable trace flag 3604.

dbcc sqltext(<spid>)

go

-- get information of pertaining spid.

sp_showplan <spid>,null,null,null

go

--get query plan for pertaining spid.

dbcc traceff(3604)

go

-- disable trace flag 3604.

 

 

1> sp_who '756'

2> go

fid    spid     status         loginame               origname               hostname                                         blk_spid         dbname       tempdbname           cmd          block_xloid            instanceid

------ -------- -------------- ---------------------- ---------------------- ------------------------------------------------

0     756     running       moc_user            moc_user            krv.prod.local                                0         master      tempdb2           INSERT                 0                     2

(1 row affected)

(return status = 0)

1> dbcc sqltext(756)

2> go

SQL Text: dbcc sqltext(756)

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> sp_showplan 756,null,null,null

2> go

QUERY PLAN FOR STATEMENT 52 (at line 133).

Optimized using Serial Mode

STEP 1

The type of query is SELECT.

1 operator(s) under root

|ROOT:EMIT Operator (VA = 1)

|

|   |SCALAR Operator (VA = 0)

(return status = 0)

 

Steps for checking Health of Dataserver?

There are following below operation perform for checking health of data server:

1. Ping to database serevr frequently and checking processes in OS level.

2. Run basic command for checking database and ensure for expected result.

If it was just rebooted,

•Did it start up correctly with all options enabled?

•Is the license valid?

•Did it come up with async I/O?

•Did all the databases come up correctly?

•Check for anything abnormal

3.  Check Licence

4.  Database Availavility

5.  Data Storage

6.  Disk Space

7.  Database & Transaction Log dumps

8.  User Activity

9.  Error Logs

10. Blocking

11. Data Consistency

Developer Area

What is the difference between syskeys and sysconstraints?

syskeys: table stores the logical relationship among the columns, which does not enforce the business rules. When we define

the primary key and foreign key constraints using the system sprocs called "sp_primarykey" and "sp_foreignkey", then this 

constraints gets stored in syskeys table which gets used for the sproc "sp_helpjoin"

sysconstraints: table stores the constraints on columns, which strictly enforce the business rules. When we define primary

key and foreign key constraints at the table definition then it gets stored in this table.

What is the difference between login and user?

login: is defined at the server level. Which is used to get access on the server. By default it routes the user to master database if default database has not been defined at the time of login creation.

user is defined at the database level. To access database objects on the user defined database we need to have user created on the given database.

Note: Most of the time login and user gets created by the same name, which creates confusion that user is able to login to server, still not able to access table or stored procedure. In this case user has to ensure two things.

1: Whether user exists, for the give login, in the given database or not. If not then first gets it created.

2: If user also exists then needs to check permissions on database objects for the given user.

 

 

 

 

11. What is proxy authorization?

Adaptive Server provides the proxy authorization capability, which allows one
user to assume the identity of another user on a server-wide basis. A System
Security Officer can grant the ability to assume the security context of another
user to selected logins. If a login has permission to use proxy authorization, the
login can impersonate any other login in Adaptive Server.

Warning! The ability to assume another user’s identity is extremely powerful
and must be strictly limited. A user with this permission could assume the
identity of the “sa” login, which would give the user unlimited power within
Adaptive Server. Plan to limit this permission to trusted administrators and
applications, and to audit their server activity.

A System Security Officer or a System Administrator might want to assume the
permissions of another user to make sure the permissions are correct for a user
or to perform maintenance on a user’s database objects.

An application server can use proxy authorization to login to Adaptive Server
with a generic login, which the application server uses to execute procedures
and commands for several users.

A System Security Officer uses the grant set proxy or grant set session
authorization command to give a user permission to use proxy authorization.
The user with this permission can then execute either set proxy or set session
authorization to become another user. A user executing set proxy or set session
authorization operates with both the login and server user ID of the user being
impersonated. The login and server user ID are active across the entire server
in all databases.

Note :set proxy and set session authorization are identical in function and can
be used interchangeably. The only difference between them is that set session
authorization is SQL92 compatible, and set proxy is a Transact-SQL extension.

 

12. During the refresh from PROD -> UAT env,tables which we require to take care?

 

13. Explain about sysprotect tabel and sp_helprotect sp?

 

14. Can we change the password of other login, if yes, how?

 

15. What is the role required for user management?

    sso_role required for user management.

 

 

16. Diffrence b/w 12.5 syslogins and 15.5 syslogins?

 

17. What is guest user in database and why we require guest user?

For accessing any database,any login should have user of that database.

Guest user facilitate to any login for accessing the database without being user of database.

It is required for accessing any database without being user of database, just like tempdb .

1> select db_name()

2> go

------------------------------------------------------------

master

(1 row affected)

1> sp_helpuser

2> go

Users_name           ID_in_db         Group_name           Login_name

-------------------- ---------------- -------------------- --------------------

dbo                         1         public               sa

guest                       2         public               NULL

probe                       3         public               probe

(return status = 0)

1> use tempdb

2> go

1> select db_name()

2> go

------------------------------------------------------------

tempdb

(1 row affected)

1> sp_helpuser

2> go

Users_name           ID_in_db         Group_name           Login_name

-------------------- ---------------- -------------------- --------------------

dbo                         1         public               sa

guest                       2         public               NULL

(return status = 0)

 

 

18. What is the keycustodian_role in ASE 15.5?

 

19. How can we include the passwordpolicy? explain sp_passwordpolicy?

20. Can we include password history feature? From which version it is avilable and how can we do that?

Establishing a password and login policy

Adaptive Server includes several controls for setting policies for logins and passwords for internal authentication.

In Adaptive Server the System Security Officer can:

Specify the maximum allowable number of times an invalid password can be entered for a login or role before that login or role is automatically locked

Lock and unlock roles manually

Ensure that all user passwords have at least one digit

Specify the minimum password length required server-wide or for a specific login or role

Display all security-related information for logins and roles

Associate a password expiration value with a specified login or role

Negative values may be used for user IDs (uid).

The server user ID (suid) associated with a group or a role in sysusers is not equal to the negation of their user ID (uid). Every suid associated with a group or a role in sysusers is set to -2 (INVALID_SUID).

 

21. Can we include one sql proc which exceute during login and how can we do that?

 

New Ques on 21st Feb 2011

 

How can we get the compression level information from the dump files?

We have tot 9 comperssion levels on Sybase, the default compress level is on Sybase is "6".

We can use Compress and Stripes at the same time.

1) Using the compress level to reduce the file size.
2) Using the strips to create multiple device files.

Protecting tape devices:

While your taking the dump you can keep the "Retaindays" meanwhile
for example i have keep the retaindays as 5 days to my dump file .If i am trying to take same dump data with the same name it won't allow me to take dump with the same name before the 5 days.

syntax:

dump database dbname to 'compress::path' at retaindays=num_of_days

Hope this wil be help for you....

 

2. What is the difference between update and exclusive locks?

 

3. What is isolation level in ASE? And default value of isolation level.?

 

4. How can we avoid the deadlock in the database?

 

5. Is there any way to print the deadlock information in the errorlog?

 

6. Give the two benefits for creating the database using for load option?

 

7.What are new features of the Sybase 15? And let me know which you are using in your day to day operations?

 

8. What is the joining order in ASE ( suppose we have 4-5 tables with different  size)?

 

9. What difference between sysmon and MDA table ?

 

10 . Can we take the output of sybmon in a table?

 

——

 

 

 

Replication Server:

 

Q1:  How can we know, the current ASE and Replication Server Setup is  warm standby setup or not?

 

Q2: What is the function of SQM and SQT?

 

Q3: What is the 1TP & 2TP?

 

Q4: In how many ways we can know the tran details which is causing the thread down?

 

Q5 : Pls explain the functionality of rep server starting from PDB logs to RDB

 

Q6: What is the diff between DSI and DSI EXEC thread?

 

Q7: Can we dump the queues?

 

Q8: Suppose our queues are filling up, in next 2 hrs 100% would be fill, how will you investigate and steps for troubleshooting?

 

Q9: How can we know RSSD server name from replication Server?

 

Q10: What is the importance of materialize & de-materialize queue?

 

Q11: What is DIST thread of Replication server?

 

Q12: What is the difference between connection and route?

 

Q13: What is the purpose of ID server in replication setup?

 

Q14: What is switch active ?

 

New Questions:

 

What is the diffrence between sp_setreplicate and sp_setreptable?

 

What is the diffrence between route and connections?

 

How can we check the current replication setup whether it is WS , table level or db level?

 

What would be  the impact of long running tran running in PDB in whole replication setup?

 

suppose there is temp table in sp and we want to replicate it?

 

What is the importance of rs_locator table in replication server?

 

What is dbcc settruc ltm, valid/ignore? When we use this dbcc command?

 

What is diffrence between rs_zeroltm and dbcc settrucn ltm,valid?

 

What are the diffrent users in common replication setup?

 

What is rs_subcmp?

 

New Question on 21st Feb

 

1. What are the routes?

 

2. How routes can enhance the performance?

 

3. What is function string?

 

4. Replication queues are filling up, Where we need to look into for root cause?

 

5. If DSI is down , how can we make it up? Whats rs_exception?

 

6. In an table level replication setup, we need to alter a coloum, what would be the step for the same?

 

7. Suppose there is size mismatch between table data and replication def between cols? What will happen?

 

8. How can we refresh a database in the replication enviorment?

 

9. What factor affecting the replication agent performance in primary database?

 

10. How can we do the master database replication? Is it possible? What information we can replicate?

 

 

 

New Questions on 11th march 2011

=============================

What is Identity Colum?

What is the advantage and disadvantage of Identity coloums?

From performnace point of view ,which is better if exists or if not exists?

How can we avoid fragmentation in table?

There is update statement on one APL and one DOL table. Which one would be fatser?Consider the cases: where clause on index cluster index coloum , other case not using any index.

Why the reorg is faster on DOL table as compare cluster index rebuild on APL?

Wht cluster index with sorted_data on APL is faster than reorg rebuild in DOL?

What is Sybase recommendation for tempdb size, suppose we have 300GB , 150GB dbs are inserver, wht would be the sybase recommendation for sizing of tempdb?

Whats the difference between dsysnc and direct io?

Suppose we are not concerning about the recovery of the database, which would be better for performance dsync(on/off) or direct io and why?

Whats the asynchronus prefetch ? How is it helping in performance enhance?

We having a 4k page size server, what can be possible pool size in the server?

As Sybase recommends 4K size pool for log usage in 2k page size server , please let me know the pool recommendtaion for 4K pagesize server?

How can we reduce the spinlock without partioning the data cache?

Can we have the spinlock contention with single engine?

In sysmon report what are the five segment you will be looking for performance?

Whta is meta data cache?

Whta is the archive database?

How can we enable the acrhive database for compresssed backup?

Hows the object level recovery is possible in ASE?

How can we find the culprit spid which has filled up th etempdb database?

How can we find the culprit spid which is badly used the log segment of tempdb?

Whats partioning? How partioning helping in increaeing the performance?

Suppose a table is partioned based on a coloum, how dataserver will be handle the insert on the table?

Apart from the query plans, wht else resides in proc cache?

What is new config param “optimization goal”? Whats the parameter we need to provide it?

User is experiancing very slow performace, what can be the reason for this slowness?

What is engine affinity and how can set the engine affinity?

If there are 3 cpus in the box, how many engine we can configure ?

Suppose dataserver is running very slow and sp_monitor is showing 100% cpu usages, what can be possible issue? Where will you look at?

What is the error classes in replication server?

What is the diffrence between Warm standby and table level replication?

Can you please let me know five case when the thread goes down in replication?

What are triggers? What are type of triggers and how many triggers can we configure on a table?

What are diffrecnt locking scheme in ASE and what are the latches?

How can we dump a replication queue?

 

New questions @ Dec 2011

How can we configure the dbcc database?

 

How can you configure sybsecurity?

 

Have you ever worked on terabyte size of  database? How are you taking backup for the same?

 

Whats the diff between MSA and WS?  Can we consider MSA as a Ws?

 

You are not able to execute any command in ASE as tempdb is full and you cant create user defined tempdb on the fly , how will you investigate ?

 

What are the new features fo Sybase ASE 15?

 

What are the different options avilable with reorg ?

 

Why we require reorg ?

 

Suppose if every thing is fine in REplication enviorment  and data is not replicating , how will you troubleshoot the same?

 

What is gen id in rep server?

 

How can you check the latency in the replication enviorment?

 

Whats is HA in Sybase? How can we monitor the HA status

================================================================================

 

What is log suicide ?

 

While suiciding the log can usually bring the server back online, it also frequently results in both physical and logical corruption in the database, because it bypasses the normal process of rolling back incomplete transactions in the log (and rolling forward completed transactions) that takes place during online recovery. Frequently, the resulting corruption is not encountered until a later time, and the connection with the earlier log suicide is not always recognized.

WARNING! Log suicide is highly risky, and is not recommended except in extreme cases.

 

 

When we require log suicide of a DB?

 

Database recovery with no transaction logs (in sybase it's termed suicide recovery)

 

If the dataserver crashes after an uncommitted transaction is written to the log but before the transaction completes, therecovery upon startup reads the log and ensures that no uncommittedchanges are reflected in the database by rolling back the changes. Likewise,online recovery ensures that any changes recorded in the log for committedtransactions that have not yet been flushed to disk are updated on the datapages and written to disk by rolling forward the transactions.In prior versions of ASE partial recovery of a database was not possible. If recovery failed due to some corruption, there was no way to recover theuncorrupt portion of the database and bring it online. The only option was toeither recover from backups or “suicide” the log

 

What is the bypass recovery, when we require the bypass recovery?

Bypass recovery starts Adaptive Server without recovering one or more databases. A database in this mode can be accessed even though it is not recovered. You use this mode to allow access to the database for problem analysis, to copy out data, and so on.

 

MULTIPLE TEMPDB IN SYBASE

Table of Contents

Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

Feature Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

Temporary Database as a System Resource . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

Resource Allocation for Different Application Profiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

Server Consolidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Increased Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Performance Enhancement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Reduced Resource Contention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

tempdb Write Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

1

Overview

Database applications rely on temporary workspace where they can store intermediate results

and data that does not need to be persistent. In Adaptive Server Enterprise (ASE), Sybase’s

flagship database, this workspace is provided in the form of a special system-provided database,

called tempdb. Furthermore, SQL processing also uses this space for temporary work

tables. All this activity leads to heavy usage of tempdb. In the past, extensive use of tempdb

had resulted in performance degradation due to contention on tempdb system catalogs as

well as the tempdb log.

Sybase’s release of Adaptive Server Enterprise, ASE 12.5.0.3, introduced the multiple tempdb

feature, later enhanced in the ASE 12.5.1 release. This feature enables the user to create additional

temporary databases, and allows specific applications to have exclusive access to one

or more temporary databases.With this innovative feature, system administrators will now

have greater control over tempdb resources. This feature provides facilities for better resource

allocation, increased availability, and improved performance. This white paper highlights the

significant elements of the new feature set and discusses how it will help users better allocate

their resources and increase performance.

Feature Overview

The multiple tempdb feature provides users the ability to create temporary databases, which

can then be used for the creation of temporary objects. DBAs can bind any user login as well

as applications to specific temporary databases or to a group of temporary databases via the

system stored procedure interface. The default group is a system-created group that always

has the system tempdb as its member. DBAs can add temporary databases to this group.

At login time, sessions get assigned to a temporary database, based on the existing bindings.

If the binding is to a specific temporary database, and that database is available (online) then

the session gets assigned to it. If no binding is specified, or the binding is to the group, then a

temporary database is selected from the default group using a round robin selection policy.

The temporary database chosen for a session remains in effect for the duration of that session

and never gets changed. Once a session is assigned a temporary database, all temporary objects

created during that session will be created in that temporary database. Stored procedures that

create or access temporary tables do so in the temporary database assigned to the session in

whose context the stored procedure is executed. These objects will be implicitly dropped

when the session is completed or the server exits. Temporary tables may also be dropped

explicitly by the session.

User-created temporary databases are very similar to the system tempdb in that they are

primarily used for the creation of temporary objects, and they are not recovered at recovery

time. Unlike the system tempdb, user-created temporary databases can be dropped by the DBA.

Temporary Database as a System Resource

With pre-ASE 12.5.0.3, administrators had no choice regarding how temporary database space

was allocated to various applications. The system tempdb was shared by all applications. In

this scenario, it is difficult to size the temporary database to fit the needs of individual applications.

An application with large resource requirements could hog the tempdb and cause

other critical applications to fail because of lack of tempdb space. In ASE 12.5.0.3, users can

create additional temporary databases as follows.

create temporary database tempdb_1 on tempdb dev = 50

This creates a new temporary database called tempdb_1. This database can be added to the

default group. For more information please refer to ASE 12.5.0.3 user manual, New Features

in Adaptive Server Version 12.5.0.3. Note that it is always beneficial to create the log for a

normal user database on a separate device. However, with the user-created temporary database

it is not necessary to do so because the log is treated differently while issuing write

commands other devices. This is explained in a later section.

A user-created temporary database can be added to the default group using the stored procedure

interface sp_tempdb as shown below.

sp_tempdb .add., .tempb_1., .default.

tempdb_1 now becomes part of the default group. The temporary databases that are part of

the default group can be seen by executing sp_tempdb as shown.

1> sp_tempdb ’show’

2> go

Temporary Database Groups

-------------------------------

default

(1 row affected)

Database GroupName

-------------------------- -------------------------------

tempdb default

tempdb _1 default

The ASE engine will assign a temporary database from the default group in a round robin

manner to incoming user connections. For instance, if these two databases are the only

members of the default group and all applications are bound to the default group, then if

there are 10 user connections, 5 will be using ‘tempdb and the remaining will be using

tempdb_1.This allows for load balancing between the users of the default group.

Note that an application does not have to change to take advantage of this new feature. All

the temp tables created with a “#” prefix and worktables created by the application will be

created within the temporary database assigned to the application at login time.

You can also dynamically remove a temporary database from the group. During peak load

time you can configure more temporary databases if the workload demands it and remove

them during the off-peak hours. All this can be done without having to bring down ASE or

putting databases into single-user mode.

Resource Allocation for Different Application Profiles

With enhancements to this feature in ASE 12.5.1, any application, or login can be bound to

a specific temporary database. For instance, suppose two applications are running—one with

a high-throughput OLTP profile and the other a background batch profile. The OLTP application

can be bound to a temporary database that sits on RAM disk, whereas the batch

application, which requires large temporary space, can be bound to a large temporary

database on a regular disk. Here is how you can do it.

For the OLTP application, with application name of oltp_app, we do the following:

create temporary database oltp_tempdb on ramdev = 50

go

sp_tempdb ’bind’, .AP., .oltp_app., .DB., .oltp_tempdb .

go

2

For the batch application, with the application name of batch_app, we would do the following:

create temporary database batch_tempdb on dbdev = 500 go sp_tempdb

’bind’, .AP., .batch_app., .DB., .batch_tempdb .

go

The batch application that tends to use large temporary database space will not interfere with

the mission-critical OLTP application that requires relatively small temporary database space

but faster response time.

An important aspect of this feature, is that bindings can be removed dynamically.

Furthermore, a user-created temporary database can be dropped if it is not actively being

used. For example, after the batch application finishes, the temporary database that it uses

can be dropped to free up the disk space. This can be done as shown below.

sp_tempdb .unbind., .AP., .batch_app.

go

drop database batch_tempdb

go

With the ability to dynamically create and drop temporary databases it becomes easy for a

DBA to manage the temporary database resources efficiently.

Another important feature is the ability to bind any login to a specific temporary database.

Especially when used for the ‘sa’ login, this feature allows for critical administrative tasks to

run efficiently without having to compete for temporary database resources with other

applications. Here is how it can be done.

create temporary database SA_tempdb on sadbdev = 100

go

sp_tempdb ’bind’, .LG., .sa, .DB., .SA_tempdb .

go

In pre-ASE 12.5.0.3, if tempdb runs out of space, then ‘sa’ will not be able to run any stored

procedures that use tempdb space.With 12.5.0.3, DBAs can reserve a specific temporary database

for ‘sa’ use only. This ensures that the ‘sa’ will get into the system reliably and be able to

perform administrative tasks without any problem.

In a similar fashion, any user logins can be individually bound to specific temporary databases,

thus providing greater management flexibility over your environment. Note that the ability

to bind any user logins (and not just the ‘sa’ login) is a feature enhancement available in ASE

12.5.1 and later.

Server Consolidation

Currently, one of the trends in the IT world is to bring together various applications in the

enterprise under a single server.When such applications start running on the same ASE server,

they quickly run into each other while sharing single tempdb space.With multiple temporary

databases, a DBA can bind each of these applications to their own temporary databases.

Increased Availability

Availability is a key requirement on which ASE has always placed utmost importance.With

the multiple tempdb feature, administrators can now breathe easier if a temporary database

becomes unusable. In pre-ASE 12.5.0.3, if a tempdb got corrupted, a server had to be shutdown

to recover. However, with the multiple tempdb , DBAs can remove the corrupted database

from the default group and other temporary databases can take the subsequent load. This

considerably reduces down time.

3

Performance Enhancement

Two main performance enhancements in the tempdb feature allow for better throughput and

faster response time.

Reduced Resource Contention

With more than one temporary database, two important resource contention points will be

reduced.

• System catalog contention

• Log contention

In pre-ASE 12.5.0.3, every new object created/deleted in tempdb would require a row to be

inserted/deleted from system catalogs in the tempdb . This was a single point of contention.

With more than one temporary database, this contention is considerably reduced. An

in-house benchmark showed that with a single tempdb , as the number of clients increase,

the contention on the systems catalog increases sharply. In the graph shown below it is evident

that adding more temporary databases eases the contention on the catalog.

Similarly, instead of one log device for tempdb transactions, multiple log devices will substantially

reduce logging bottlenecks. As a result, improved throughput can be expected by

adding more temporary databases. In our setup we have seen at least a 30% improvement

with the addition of a single temporary database to the experimental setup as shown. You

can see that with just the single tempdb the throughput starts dropping after a certain point

as more and more users start using the tempdb . By adding another temporary database, the

contention on the catalog and the log is reduced considerably, resulting in increased throughput.

tempdb Write Optimization

Temporary databases are not recoverable. (During boot time ASE drops and recreates it.)

ASE takes advantage of this characteristic and avoids writing data or log buffers to disk,

which would have been required for normal databases.

4

4000

6000

8000

10000

5 10 15 20 25

0

2000

1 Temp DB

2 Temp DB

Throughput Transactions per Minute

% Contention

Number of Clients

0

5

10

15

5 10 15 20 25

1 Temp DB

2 Temp DB

Contention of Catalog

For instance, if a user does a select into as

select * into tempdb ..temp_table from foo

then ASE would have to flush all the data buffers of newly created table temp_table at the end

of the transaction. This is because select into is a non-logged operation and to ensure the

recoverability we need to make sure that the data hits the disk. This applies to normal databases

but not to a temporary database, since a temporary database is not recovered. ASE

12.5.0.3 avoids flushing data buffers to the disk at the end of this operation.

For DMLs such as insert/update/delete ASE does not force the write of log at the end of the

commit operation. For instance, if a user does the following:

insert into tempdb ..temp_table select * from foo

…then ASE doesn’t force the write of the log records to the disk during the commit operation.

This means fewer context switches and a lighter load on the log or data devices, resulting

in higher throughput. During in-house testing we have seen writes go down as much as

50 % as shown in the following chart.

Conclusion

As part of Sybase’s commitment to provide features that add real value to customers in missioncritical

environments, ASE 12.5.0.3 provides a multiple tempdb feature that is very powerful

and ensures higher throughput. ASE 12.5.1 includes enhancements to provide a higher

degree of flexibility in configuring and managing this feature. This white paper has demonstrated

how DBAs can configure their system to make the best use of their resources without

having to change applications. It is Sybase’s belief that this feature will bring operational

costs down, justifying the investment on this new feature.

What are the most important DBA tasks?

What should you do when you find a stacktrace in the server errorlog?

Is there any disadvantage of splitting up your application data into a number of different databases?

Is it necessary to drop & recreate all procedures and triggers every few months?

What are the main advantages and disadvantages of using identity columns?

What do you do when the server can't start due to a corrupt master database?

When you do a BCP-in from a file to a table, what happens to triggers, constraints, rules and defaults on that table?

How do you BCP only a certain set of rows out of a large table?

What's the difference between managing permissions through users and groups or through user-defined roles?

Is there any advantage in using the 64-bit version of ASE instead of the 32-bit version?

Is it a good idea to use datarows locking for all tables by default?

What would you do when the ASE server's performance is bad?

What do you do when a segment gets full?

Are timestamp columns good candidates for primary keys? (since they're always unique for a row)

Does the DBA candidate hold a Sybase Certification?






Questions to ask a candidate DBA


What are the most important DBA tasks ?
In my opinion, these are (in order of importance): (i) ensure a proper database/log dump schedule for all databases (including master); (ii) run dbcc checkstorage on all databases regularly (at least weekly), and follow up any corruption problems found; (iii) runupdate [index] statistics at least weekly on all user tables; (iv) monitor the server errorlog for messages indicating problems (daily). Of course, a DBA has many other things to do as well, such as supporting users & developers, monitor performance, etc.

What should you do when you find a stacktrace in the server errorlog ?
Open a case with Sybase TechSupport. There's not much you can do yourself with this information, and only TechSupport has the information to determine whether it's related to a bug, for example. It's not a good idea to ignore such things in the errorlog -- 'cos it might indeed indicate you're hitting a bug.

Is there any disadvantage of splitting up your application data into a number of different databases ?
When there are relations between tables/objects across the different databases, then there is a disadvantage indeed: if you would restore a dump of one of the databases, those relations may not be consistent anymore. This means that you should always back up a consistent set of databases; however, this may be difficult when the system is continuously in use, because a single database is the unit of backup/restore. Therefore, when making this kind of design decision, backup/restore issues should be considered (and the DBA should be consulted).

Is it necessary to drop & recreate all procedures and triggers every few months ?
No; in older Sybase versions (4.x), this was sometimes necessary, as query plans could grow bigger over time, hit an upper limit at some point and cause an error. Both the growing plan and the limit have been removed since at least version 11.0 (or was it already fixed in 10 ? -- I'm not sure...).

What are the main advantages and disadvantages of using identity columns ?
The main advantage of an identity column is that it can generate unique, sequential numbers very efficiently, requiring only a minimal amount of I/O. The disadvantage is that the generated values themselves are not transactional, and that the identity values may jump enourmously when the server is shut down the rough way (resulting in "identity gaps"). You should therefore only use identity columns in applications if you've adressed these issues (go here for more information about identity gaps).

What do you do when the server can't start due to a corrupt master database ?
You create a new master device using buildmaster (on 12.5, use dataserver instead); create a RUN_SERVER file and start the server in single-user mode (using the -m option); then manually add an entry for SYB_BACKUP in sysservers; and then load a database dump of the master database. After that, the server will automatically shut down; restart it and see if your application databases are still there.

To turn up the heat a bit: what if you're using a non-default character set or sort order ?
In this case, things are more complicated: you'll first need to create sybsystemprocs and change the sort order/charset of newly created master database before loading the master database dump (thanks to John Langston for this one).

When you do a BCP-in from a file to a table, what happens to triggers, constraints, rules and defaults on that table ?
For both fast BCP and 'normal' BCP, triggers, constraints and rules are ignored. Defaults will be effective though (
go here for a nasty, but little-known side effect).

How do you BCP only a certain set of rows out of a large table ?
If you're in ASE 11.5 or later, create a view for those rows and BCP out from the view. In earlier ASE versions, you'll have to select those rows into a separate table first and BCP out from that table. In both cases, the speed of copying the data depends on whether there is a suitable index for retrieving the rows.

What's the difference between managing permissions through users and groups or through user-defined roles ?
The main difference is that user-defined roles (introduced in ASE 11.5) are server-wide, and are granted to logins. Users and groups (the classic method that has always been there since the first version of Sybase) are limited to a single database. Permissions can be granted/revoked to both user-defined roles and users/groups. Whichever method you choose, don't mix 'm, as the precedence rules are complicated.

Is there any advantage in using the 64-bit version of ASE instead of the 32-bit version ?
The only difference is that the 64-bit version of ASE can handle a larger data cache than the 32-bit version, so you'd optimize on physical I/O. Therefore, this may be an advantage if the amount of data cache is currently a bottleneck. There's no point in using 64-bit ASE with the same amount of "total memory" as for the 32-bit version, because 64-bit ASE comes with an additional overhead in memory usage -- so the net amount of data cache would actually be less for 64-bit than for 32-bit in this case.
(Just for clarity: the 64-bit version is not twice as fast as the 32-bit version, and does notperform its I/O at double the size of the 32-bit version (I once heard someone state these as facts...)).

Is it a good idea to use datarows locking for all tables by default ?
Not by default; only if you're having concurrency (locking) problems on a table, and you're not locking many rows of a table in a single transaction, then you could consider datarowslocking for that table. In all other cases, use either datapages or allpages locking.
(I personally favor datapages locking as the default lock scheme for all tables because switching to datarows locking is fast and easy, whereas for allpages locking, the entire table has to be converted which may take long for large tables. Also, datapages locking has other advantages over allpages, such as not locking index pages, update statistics running at level 0, and the availability of the reorg command).

What would you do when the ASE server's performance is bad ?
"Bad performance" is not a very meaningful term, so you'll need to get a more objective diagnosis first. Find out (i) what such a complaint is based on (clearly increasing response times or just a "feeling" that it's slower?), (ii) for which applications/queries/users this seems to be happening, and (iii) whether it happens continuously or just incidentally. Without identifying the specific, reproducable problem, any action is no better than speculation.

What do you do when a segment gets full ?
Wrong: a segment can never get full (even though some error messages state something to that extent). A segment is a "label" for one or more database device fragments; the fragments to which that label has been mapped can get full, but the segments themselves cannot. (Well, OK, this is a bit of a trick question... when those device fragments full up, you either add more space, or clean up old/redundant data.)

Are timestamp columns good candidates for primary keys? (since they're always unique for a row)
Absolutely not, this would be a bad thing to do; timestamp columns are actually a bit too unique to be used as a primary key. For details, see the quiz question for August 2004.

Does the DBA candidate hold a Sybase Certification ?
If (s)he has, consider that a plus !


Questions for a candidate DBA to ask your potential future employer

When you're being interviewed for a DBA vacancy, there are some things related to the DBA environment you might want to know as well. I'd suggest to check out at least the following:


Does the company have a Technical Support contract with Sybase ? 
A support contract is required for getting EBFs and for being able to ask questions about technical problems. Without a support contract, you're completely on your own; you should ask yourself if your can fulfill the company's expectations in that case.

Which version of ASE are they using, and on which platform ?
This matters: for example, if they appear to be running 11.0.3 on Data General, find out if they are aware that both this ASE version and this platform are no longer supported by Sybase. If they're not planning to upgrade to a supported version/platform soon, ask yourself if you want to be working there; you risk being on your own, without support, and with an out-of-date ASE version that stops you from keeping your ASE knowledge current.

How many servers, database and concurrent users do they have ? What's the database size like ? Is there a 24*7 uptime requirement ?
It helps to know which scale you're talking about. If you're supposed to look after a 500 Gb, never-no-downtime, 3000-user system, check whether the salary you're being offered is of the same magnitude as the system.

Is Sybase Replication Server involved ?
If it is, and if you know RepServer, reconsider your financial demands -- upwards, that is. Reason is that RepServer DBAs are hard to find -- much harder than ASE DBAs.

Are you also supposed to take care of their Oracle, MS-SQL, etc. servers ? Do you have to manage ASIQ or ASA (SQLAnywhere) as well ?
You may want to know this in advance rather than find out on your first working day....

If you want to get a Sybase certification (or get a more recent one) will they pay for this ?
It should make 'm happy that you're willing to get your certification, 'cos it will make you a better DBA; try to get them to pay for at least part of it. Tip: if you're talking to a management person, calling this a "win-win scenario" might help....

Sybase FAQs

1. When performance suffers due to spinlock contention on a named cache, the SA can…

A. create a large I/O pool.
B. partition the cache.
C. use MRU replacement.
D. expand metadata caches.
Answer: B

2. What is the page number of the first GAM page of a database?

A. 72
B. 128
C. 256
D. No fixed value
Answer: D

3. How many allocation pages are created in a 4 MB database (assuming 4K page size)?

A. 2
B. 4
C. 8
D. 16
Answer: B

4. A checkpoint command writes ___________ to disk.

A. just log pages
B. only committed changes
C. committed and uncommitted changes
D. only pages that passed the wash marker
Answer: C

5. What does an index id of 128 mean?

A. A clustered index and its table.
B. A heap table.
C. A nonclustered index.
D. A text or image column.
Answer: C

6. A master device failure has occurred which requires the creation of a new master device. Which program must you execute to build it?

A. bcp
B. dataserver
C. buildmaster
D. installmaster
Answer: B

7. Which of the following dbcc commands can be run with a “fix” option?

A. dbcc checkalloc.
B. dbcc checkcatalog.
C. dbcc checkdb.
D. dbcc checkstorage.
E. dbcc checkverify.
Answer: A

8. Which of the following dbcc commands checks for integrity in and between system tables?

A. dbcc checkalloc
B. dbcc checkcatalog
C. dbcc checkdb
D. dbcc checkstorage
Answer: B

9. The dbcc checkverify command should be run…

A. only once, to aid in configuring the dbccdb.
B. prior to creation of a new index, to ensure the table’s page linkage is accurate
C. prior to most dbcc commands, to ensure the database is in the proper state.
D. immediately following the dbcc checkstorage command
E. immediately following any non-logged action.
Answer: D

10. When setting up dbcc checkstorage, you must:

A. turn on parallelism
B. decrease the number of locks
C. increase the transaction log for the target database
D. set the database in ‘dbo use only’
Answer: A

11. For a database with its log on a separate device, which of the following dbcc commands show the percentage of free log space?

(Choose 2)
A. dbcc checkalloc
B. dbcc checkcatalog.
C. dbcc checkdb
D. dbcc checkstorage
E. dbcc checktable
Answer: CE

12. Running dbcc tablealloc(titles, optimized) will

A. identify all allocation errors on titles.
B. identify all allocation errors on titles and its indexes.
C. identify only allocation errors on referenced pages.
D. identify only allocation errors based on the OAM page entries.
Answer: D

13. What dboption must be true to fix allocation errors on a system table?

A. dbo only
B. no free space acctng
C. select into/bulkcopy/pllsort
D. single user
E. trunc log on chkpt
Answer: D

14. Supported dbcc commands can be granted to… (Choose 2)

A. User defined roles.
B. Groups.
C. Users.
D. System defined roles.
Answer: AC

15. Which of the following attributes are NOT set using sp_dbcc_updateconfig?

A. Maximum number of worker processes to use
B. Size of the named cache to use
C. Sizes of the of the workspaces to use
D. Size of the extent IO buffer pool to use
Answer: B

16. A DBA suspects a leaf-level index pointer does not point to the proper data pages. Which command can be used to verify this?

A. dbcc indexalloc
B. dbcc tablealloc
C. dbcc checkstorage
D. dbcc checktable
Answer: D

17. When should ‘dbcc reindex’ be used?

A. after the sort order in ASE has been changed
B. when a new language has been installed in ASE
C. when corruptions have been found in text/image page chains
D. when corruptions have been found in indexes on system tables
Answer: A

18. Which dbcc command can you use to check that each table in a database has a minimum of one column?

A. dbcc checkverify
B. dbcc dbinfo
C. dbcc checktable
D. dbcc checkcatalog
Answer: D

19. Which of the following factors affect whether ASE will use large i/o when reading a table? (Choose 3)

A. How well the pages of the tables fill its extents.
B. Whether ASE has been built with a page size greater than 2Kb
C. The current data cache hit rate in the cache to which the table is bound.
D. How well the logical clustering of the table matches its physical clustering.
E. At least one large I/O pool is configured in the cache to which the table is bound.
F. At least two large I/O pools are configured in the cache to which the table is bound.
Answer: ADE

20. Which of the following statements are true about named caches? (Choose 2)

A. A named cache can have no more than 2 buffer pools.
B. A transaction log can only be bound to a “mixed” cache.
C. Every named cache must have a single-page buffer pool.
D. The default size of a named cache is 10 MB.
E. The minimum size of a named cache is 256 pages.
Answer: CE

 

Comments