This page contains some suggestions for questions to ask when interviewing an applicant for a Sybase ASE DBA job. There are also some questions the candidate might want to ask before (s)he takes the job (see the end of this page). Please bear in mind that these are just some suggestions which I personally think are relevant. I'm certainly not claiming that these are the "best" or "most representative" questions you could ask. You could use these as a starting point and add further questions of your own.
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) run update [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