http://goo.gl/EmKxy0

Sybase Interview Ques

April 29th, 2013 Leave a comment Go to comments


Note : Please take a look for Question and Answer on page :

Full Edition : http://sybaseblog.com/2013/04/29/complete-ebook-sap-sybase-ase-qa-bank/

Limited Edition : http://sybaseblog.com/2012/10/09/sybase-dba-interview-questions/

Adaptive Server Enterprises :

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

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

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?

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.

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

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?

4. What are aliases?

5. Whats 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. Whats 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 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?

Question on 20th Dec 2012

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

1. What is the diffrence between MSA and Warm standby ?

2. How can we perform failover in Case of Warm Stand by and MSA?

3. What will happen, if the queue has data in case of failover to warm standby?

4. For standby point of view which one is better warm standby or MSA?

5. How can we sync the warm standby database ? If there is some data in queue, what will you do?

6. What is the config parameter you can use to tune a rep agent?

7. Suppose there are 20,000 trans which are getting fail and you need to skip all these trans using error class. Please let me know high level overview to setup
error class?

8.  How the mulitple replication server can improve the performance as compare to single replication server ?

9.  Suppose the query is running slow , how will you investigate?

10. Suppose tempdb log segment is full, how will you troubleshoot?

11. Suppose for a very big database , backup takes 4 hrs to complete . During the backup, due to heavy activity from user log is full, after 2 hours ? Wht will you do now?

12. How can we guess, in how much time queue will be drain?

13. Suppose queue is getting full, what can be point of bottelneck and why?

14. Suppose log for a database is getting full, how will you trouble shoot? Why  log increase is not a better idea? ( Server version is below 15.7)

15. How will you check the latency and what is the latency ?

16. Suppose you want to sync a table  in a warm stand  replication , How will you do?

17. What are the various factor which can effect rep agent performance?

18. How can you make sure rep agent is running fine ( means scanning is going well in Primary site)?

19. How can we see the number of commands in a transaction in queue?

20. Suppose you have 5000 locks at primary side , and your one delete statement ( which is effeeting 7000 rows)went fine. Now in case of Replicate dataserver ( has same number of locks), you are getting out of lock situation? Why it was successfull at primary side , what it did not go well in replicate side, desite of primary and replicate have same number of locks?

21. What are the situations when we require to drain the queues?

22. In unix , suppose one dba modifies some of files including dataserver binary to non sybase user, and you want to fix this situation asap? What you will do?

23. Suppose there is therad down issue in replication for target side, and you fetched that its down due to duplicate row? How can we make sure , is it due to duplicate row or duplicate key?

  1. krish@sybase
    August 3rd, 2014 at 20:12 | #1

    Thank u very much sir

  2. Rajesh Neemkar
    June 14th, 2013 at 00:08 | #2

    This calls for a celeration!
    people behind this…are just awesome!
    I think they have not only understand the problems of a beginner/Intermediate as a Sybase DBA but also given them excellent material as a solution.
    Its like “search ka THE END!” for me.
    Kudos to the team!

  3. remoteali
    June 10th, 2013 at 12:00 | #3

    awesome post …. everyday there is always something to learn ….

  4. Infant_DBA
    January 25th, 2013 at 15:24 | #4

    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
    ………………
    Read more … http://sybaseblog.com/sybasewiki/index.php?title=What_are_system_required_databases_for_Sybase_ASE_12.X_%26_15.x%3F

  5. infant_dba
    March 19th, 2012 at 16:02 | #5

    I find some interview question in this blog and its is quite helpful :
    http://sybaseblog.com/admin/index.php?title=Main_Page

    • remoteali
      June 10th, 2013 at 12:01 | #6

      Where is your post friend ????

  6. infant_dba
    March 19th, 2012 at 16:01 | #7

    I find some interview question in this blog and its is quite helful :
    http://sybaseblog.com/admin/index.php?title=Main_Page

  7. rahul
    March 2nd, 2012 at 13:27 | #8

    can any body help in differentiate dump tran dbname with truncate_only and dump tran dbname with no_log commands in real time? pls

  8. Purusothaman
    September 13th, 2011 at 21:00 | #9

    Good and provided very useful information

  9. August 23rd, 2011 at 13:16 | #10

    Hi,

    I have a doubt in tempdb bindings.

    As per my understanding “binding a particular login to a specifice database” is all the activities carried out for example sorting, calculating agregates etc…. will be done in the binded temporary database for that particular login.

    Am i correct? if wrong please suggest.

    Regards,
    Vinz

    • August 23rd, 2011 at 23:37 | #11

      Whatever u said , its correct!

      Will appreciate if u send your ques on mail. as this is comment section for interview questions. Thnx.

  10. August 22nd, 2011 at 13:26 | #12

    Please explain the Process of Database refresh from PROD to UAT. (Step by step in detail)

    Very much required for me.

    Thanks,
    Vinz

  11. August 22nd, 2011 at 13:23 | #13

    Hi,

    I have set the environment variable DSQUERY.It worked fine.

    When i shutdown and start the server once again, DSQUERY was disabled.

    How to fix this permanently. Please suggest ASAP.

    Regards,
    Vinz

    • August 22nd, 2011 at 13:26 | #14

      Declare this DSQUERY environmental variable in .profile( in solaris), .bashrc(in Linux).
      These will will be in $HOME directory.

      • August 22nd, 2011 at 14:23 | #15

        Thanks, Will check it out.

      • August 22nd, 2011 at 20:15 | #16

        Hi

        It worked successfully for one server. if we want to set for multiple servers how to set it.

        I tried it but dint get the result. Please help me.

        Regards,
        Vinz

  12. August 22nd, 2011 at 11:26 | #17

    Hi,

    Can i get the answers for the above mentioned Questions.

    Thanks,
    Vinay P S

  13. GhostDBA
    July 20th, 2011 at 10:11 | #18

    Its really gr8 post.. Keep it up… Where can I address answare of these questions..

  14. SCar
    June 29th, 2011 at 17:11 | #19

    Great post.

  15. June 4th, 2011 at 17:54 | #20

    Hi Marcos,

    Yeah sure you can post your queries/thoughts/concern.

    Thanks.

  16. Marcos Oliveira
    June 4th, 2011 at 00:56 | #21

    Hi.

    I just started with Sybase.
    I have a 12.5.03 server on Windows 2003 std to take care.
    Can I post questions in your blog?

  17. indra
    May 30th, 2011 at 14:46 | #22

    Hi,

    Only you provided questiones only here ? If you have answers for these questions on this site, please point me to that link.

    INDRA.

    • June 5th, 2011 at 09:27 | #23

      Ans is not available yet.
      If you go through the sybooks you can get all of ans.
      Anyway I will be posting ans soon.

  18. May 26th, 2011 at 21:58 | #24

  19. prince Hancey
    February 18th, 2011 at 15:29 | #25

    What is the diffrence between sp_setreplicate and sp_setreptable?

    sp_setreptable marks the text/image/raw object columns to always replicate

    sp_setrepLICATE marks the text/image/raw object columns to DO_NOT_replicate

    What is the diffrence between route and connections?
    Route –> Between Replication Server
    Connection –> Between Replication server and database

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

    admin logical_status,dataserver,database

    What would be the impact of long running tran running in PDB in whole replication setup?
    May fillup the inbound queue.

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

    Just the execution of SP is replicated.

    What is the importance of rs_locator table in replication server?
    rs_locator table stores the secondary truncation point of the primary databse transaction log

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

    dbcc settrunc (ltm,ignore)
    clears the secondary truncation point of transaction log
    dbcc settrunc (ltm,valid)
    Marks the secondary truncation point of transaction log

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

    rs_zeroltm set the seconadry truncation point of the primary database transation log to 0.

    dbcc settrunc (ltm,valid)
    Marks the secondary truncation point of transaction log

    What are the diffrent users in common replication setup?

    maintenanace user
    RSSD primary user
    RSSD maintenance user

  20. abhi32002
    November 13th, 2010 at 11:22 | #26

    Even though these are interview questions, if you find out the answers of all above questions, it will relatively increase your knowledge about replication. Thanks for uploading sybanva.

  21. Johnnie Casinos
    May 7th, 2010 at 22:10 | #27

    I’ve never learned so much from any other site. Really enjoyed reading this today.

  1. January 2nd, 2011 at 16:13 | #1
  2. February 21st, 2011 at 18:59 | #2