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

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

Q9: What is log suicide ?

Q10: When we require log suicide of a DB?

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

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

Shutdown with no wait never perform the checkpoint before server shutdown.

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?

Yes it will impact, database recovery may take long –long time, Please avoid to do this.

Q14: What’s 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?

Using archive database feature we can do object level recovery. SBT can also perform object level recovery.

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

Interview Questions on User Management & Permissions

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

2. What is the difference between syslogins and sysusers?

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

4. What are aliases?

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’s suid mismatch?

8. Why do we require aliases?

9. Whts the importance of sysrole table in each database?

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

11. What is proxy authorization?

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?

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

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

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?

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

New Ques on 21st Feb 2011

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

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

——

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?

Question 1

How many maximum numbers of columns can create in a table?

Answer 1

Maximum number of columns in a table is based on two parameters

  1. Fixed length or variable length column
  2. APL (all pages locked) or DOL (data only locked) tables

If columns are fixed-length then maximum number would be 1024 irrespective of APL/DOL table.

If columns are variable-length then maximum number would be 1024 for DOL table.

If columns are variable-length then maximum number would be 254 for APL table.

Question 2

What is the maximum number of arguments can be passed in stored procedure?

Answer 2

The maximum number of arguments for stored procedure is 2048.

Question 3

What is the maximum number of references allowed for a table?

Answer 3

The maximum number of references allowed for a table is 200.

Question 4

What is the maximum number of aggregate columns can be used in a compute class?

Answer 4

127

Question 5

What is the maximum number of columns can be used in “group by” clause?

Answer 5

255

Question 6

How many indexes can a table hold?

Answer 6

A table can have maximum 250 indexes wherein 1 is clustered and remaining 249 are nonclustered indexes.

Question 7

How many clustered index can be created in a table and why?

Answer 7

Maximum number of clustered index can be created in a table is 1 because clustered index pages are tightly coupled with table pages and clustered index forces the table to store the data in a sorted way. So sorting can be defined only in one way. Hence, only 1 clustered index is possible in a table.

Question 8

What is the limit of nesting level of sub-queries in a TSQL query?

Answer 8

16 level limit.

Question 9

What is the maximum number of sub-queries can be defined on each side of a union?

Answer 9

The maximum number of sub-queries on each side of a union is 16.

Question 10

How many columns can be referenced by a view?

Answer 10

The number of columns referenced by a view cannot exceed 1024.

Question 11

How many total numbers of table can be used on all sides of a union query?

Answer 11

The total number of tables that can appear on all sides of a union query is 256.

Question 12

How many columns can be specified in a single composite index?

Answer 12

31 columns in a single composite index can be specified. All the columns in a composite index must be in

same table.

The maximum allowable size of the combined index values is 600 bytes. That is, the sum of the lengths of the columns that make up the composite index cannot exceed 600.

Question 13

How many page locks are required to escalate to table lock?

Answer 13

200 page locks are required to escalate to table lock.

Question 14

How many row locks are required to escalate to table lock?

Answer 14

200 rows locks are required to escalate to table lock.

Question 1

What is the difference between Database and DBMS (Database Management System)?

Answer 1

Database is the collection of database objects i.e. Tables, Views, and Procedures etc.

DBMS consists of software that operates on database which provides storage, access, security, backup and other facilities.

DBMS is responsible for storing data in tables and maintains relation among them.

Question 2

What is the basic architectural difference between Sybase, Oracle, UDB/DB2, MS SQL Server?

Answer 2

There are two algorithms to design the databases. So all databases are based on these 2 algorithms and we can group databases accordingly.

Algorithm / Databases
Multiple Instances – Single Database / Oracle, IBM UDB and DB2 on ZOS
Multiple Instances – Multiple Databases / Sybase, MS SQL Server

Question 3

What are the system mandatory databases in Syabse?

Answer 3

Sybase contains the 5 mandatory databases which are required to run ASE.

Mandatory Databases:

  1. Master Database  Master Database is the brain of ASE. It contains the system tables that stores the data used to run and manage ASE.
  2. Model Database  Model is the template database used to create new database.
  3. Sybsystemprocs Database  It stores all the system procedures which are required to perform operations on system tables.
  4. Sybsystemdb Database  It contains data for the distributed transaction management feature.
  5. Tempdb Database  It contains the temporary tables used to perform temporary operations,

Question 4

In addition to Question 3, what are others ASE defined system databases?

Answer 4

In addition to above 5 system databases, ASE has 5 more system defined databases.

  1. Sybsyntax Database  It contains the syntax help for SQL keywords.
  2. Dbccdb Database  Installing dbccdb allows system administrators to check database consistency using parallel processing commands.
  3. Sybsecurity Database  It contains auditing information – Installing sybsecurity allows administrators to audit users.
  4. Sybdiag Database  It is used for diagnostic purpose.
  5. pubs2 and pubs3 Database  These are sample databases for a fictitious book distribution company, only for R&D purpose. We generally do not install these db’s in our application environment.
  6. <Application Databases>  Application databases are user defined databases in production or development environments.

Question 5

What are system tables and their usage?

Answer 5

System tables are an inbuilt table which gets created at the time of server build.

  1. System tables store the data about server and database.
  2. System tables are created and maintained by ASE only.
  3. No user can update the system tables.
  4. System tables are accessed by users using system procedures.

E.g. sysdatabase system table stores details about the databases installed in a given server. Details of those databases can be accessed using system define system procedure sp_helpdb <database name>, etc.

Question 6

What is the total number of system tables exists in master database and other database?

Answer 6

All databases, except master, have 29 system tables which stores the information corresponding to same database only.

There are total 56 system tables exists in master database. As master database is the brain of the ASE server, it stores the information about each and every databases and configuration parameter, so it has some extra tables apart from common 29 tables.

Question 9

What are rules regarding usage of system tables?

Answer 9

Rules for using system tables
Unless noted otherwise, system tables use allpages locking.

Updating system tables
All direct updates on system tables are by default not allowed -even for the database owner. Instead, Adaptive Server supplies system procedures to make any normally needed updates and additions to system tables.
You can allow direct updates to the system tables if it becomes necessary to modify them in a way that cannot be accomplished with a system procedure.

To accomplish this, a System Security Officer must reset the configuration parameter called allow updates to system tables with the system procedure

sp_configure.

Question 10

Why system table “syslogs” should not be altered manually?

Answer 10

Do not attempt to modify syslogs with a delete, update, or insert command. An attempt to delete all rows from syslogs will put Adaptive Server into an infinite loop that eventually fills up the entire database. Because while manually updating the syslog table will insert a record in the same syslog table as a part of its logging function, so every time it will delete a row a new row would insert into the table and eventually sever will put onto an infinite loop.

Question 11

Can we create trigger on system tables?

Answer 11

We cannot create triggers on system tables. If we try to create a trigger on a system table, Adaptive Server returns an error message and cancels the trigger.

Question 12

Can aggregate functions can be used on virtual tables such as syslocks and sysprocesses?

Answer 12

Aggregate functions cannot be used on virtual tables such as syslocks and sysprocesses.

Question 13

What are the tasks involve in performing Auditing ?

Answer 13

Auditing Involves

  1. List the roles which are active.
  2. Subcommand – for alter table command, the options add column or drop column might be used
  3. Previous value – The value prior to the update if the event resulted in the update of a value.
  4. Current value - The new value if the event resulted in the update of a value.
  5. Proxy information – The original login name, if the event occurred while a set proxy was in effect.
  6. Principal information – The principal name from the underlying security mechanism, if the user’s login is the secure default login and the user logged into Adaptive Server via unified login. The value
    of this field is NULL, if the secure default login is not being used

Question 14

Define system procedures?

Answer 14

System procedures are in built stored procedures which act upon the system tables.

We can’t modify the system tables directly, with the use of system stored procedures we can view and modify the system table data.

7 Automatically includes an IDENTITY column with a unique, — ????
nonclustered index for new tables in the pubs2 database:
use master
go
sp_dboption pubs2, “unique auto_identity index”,
true
go
use pubs2
go
checkpoint
go

Usage — The master database option settings cannot be changed
– For a report on which database options are set in a particular database, execute sp_helpdb.
– After sp_dboption has been executed, the change does not take effect until the checkpoint command is issued in the database for which the option was changed.
– The no chkpt on recovery option disables the trunc log on chkpt option when both are set with sp_dboption for the same database. This conflict is especially possible in the tempdb database which has trunc log on chkpt set to on as the default.