Sybase Interview Questions & Answers

Download PDF

You can tell if a table has identities one of two ways:

1. sp_help [tablename]: there is a field included in the sp_help output describing a table called "Identity." It is set to 1 for identity fields, 0 otherwise.
2. Within a database, execute this query:

1. select object_name(id) "table",name "column", prec "precision"
2. from syscolumns
3. where convert(bit, (status & 0x80)) = 1
4. go

this will list all the tables and the field within the table that serves as an identity, and the size of the identity field.

The number of identity values that gets "burned" upon a crash or a shutdown can by found by logging into the server and typing:
1.sp_configure "identity burning set factor"
2. go

the Default value set upon install is 5000. The number "5000" in this case is read as ".05% of all the potential identity values you can have in this particular case will be burned upon an unexpected shutdown." The actual number depends on the size of the identity field as you specified it when you created your table.

To set the burn factor, type:

1. sp_configure "identity burning set factor", [new value]
2. go

This is a static change; the server must be rebooted before it takes effect

You can either create your table initially with the identity column:

1. create table ident_test
2. (text_field varchar(10),
3. ident_field numeric(5,0) identity)
4. go


Or alter an existing table and add an identity column:

1. alter table existing_table
2. add new_identity_field numeric(7,0) identity
3. go


When you alter a table and add an identity column, the System locks the table while systematically incrementing and adding unique values to each row. IF YOU DON'T SPECIFY a precision, Sybase defaults the size to 18! Thats 1,000,000,000,000,000,000-1 possible values and some major major problems if you ever crash your ASE and burn a default number of values... (10^18 with the default burn factor will burn 5^14 or 500,000,000,000,000 values...yikes).

Adaptive Server Enterprise: System 12

ASE 12 supports dynamic SQL, allowing the following:

declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
exec (@sqlstring)
go


Adaptive Server Enterprise: 11.5 and 11.9

* Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
go

* Enable CIS
sp_configure "enable cis",1
go

* Finally, use sp_remotesql, sending the sql to the server defined in point 1.
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
go

The TDS protocol that *is* Open Client is built so that either the client or server will fallback to a common dialect. I suppose that it is theoretically possible that both would fallback for some reason, but it seems unlikely. I was recently working with a client that was using Open/Client 4.2 to speak to a version 11.5 ASE using Powerbuilder 3 and 4! Amazing, it all worked! The main problem that you will encounter is not lack of communication but lack of features. The facility to bcp out of views was added to the 11.1.1 release. You will still be able to connect to servers with old copies of Open/Client, you just won't have all of the features.

There is also another fairly neat feature of the later releases of Open/Client, it has a very good compatibility mode for working with old applications. The client that was running Open/Client 4.2 with Powerbuilder 3 is now connecting to the database using version 11.1.1. Which is not bad when you remember that Powerbuilder 3 only talked 4.2 DBLib!

There are pros and cons to both, however, I think that it should be stated up front that RepAgents are the latest offering and I believe that Sybase would expect you you to use that. Certainly the documentation for LTMs is a little buried implying that they do not consider it to be as current as LTMs.

LTM Cons:
* Older technology. Not sure if it is being actively supported.
* Not integrated within ASE, so there is a (small) performance penalty.
* Separate processes, so need additional monitoring in production environments.


LTM Pros:
* Possible to restart LTM without having to restart ASE.

RepAgent Cons
* If it crashes it is possible that you will have to restart ASE in order to restart RepAgent.


RepAgent Pros
* Latest, and presumably greatest, offering.
* Tightly integrated with ASE so good performance.
* Less to manage, no extra entries in the interfaces file.

Check the Obvious

First, ensure that you are only replicating those parts of the system that need to be replicated. Some of this is obvious. Don't replicate any table that does not need to be replicated. Check that you are only replicating the columns you need. Replication is very sophisticated and will allow you to replicate both a subset of the columns as well as a subset of the rows.

Replicate Minimum Columns

Once the replication is set up and synchronised, it is only necessary to replicate those parts of the primary system that actually change. You are only replicating those rows and columns that need to be replicated, but you only need to replicate the actual changes. Check that each replication definition is defined using the clause:

create replication definition rep_def_name
with primary

Log Transfer Managers (LTMs) and RepAgents are the processes that transfer data between ASE and the Replication Server.

LTMs were delivered with the first releases of Replication Server. Each LTM is a separate process at the operating system level that runs along side ASE and Replication Server. As with ASE and Replication Server, a RUN_ and configuration file is required for each LTM. One LTM is required for each database being replicated.

Along with ASE 11.5 a new concept was introduced, that of RepAgent. I am not sure if you needed to use RepServer 11.5 as well, or whether the RepAgents could talk to earlier versions of Replication Server. Each RepAgent is, in effect, a slot-in replacement for an LTM. However, instead of running as separate operating system process, it runs as a thread within ASE. Pretty much all of the requirements for replication using an LTM apply to the RepAgents. One per database being replicated, etc. but now you do not need to have separate configuration files.

Both SQL Remote and Replication Server perform replication. SQL Remote was originally part of the Adaptive Server Anywhere tool kit and is intended for intermittent replication. (The classic example is that of a salesman connecting on a daily basis to upload sales and download new prices and inventory.) Replication Server is intended for near real-time replication scenarios.

Replication Server moves transactions (insert, updates and deletes) at the table level from a source dataserver to one or more destination dataservers. The dataserver could be ASE or other major DBMS flavour (including DB2, Informix, Oracle). The source and destinations need not be of the same type.

What can it do ?
* Move data from one source to another.
* Move only a subset of data from source to destination. So, you can ‘subscribe’ to a subset of data, or a subset of the columns, in the source table, e.g. select * from clients where state = ‘NY’
* Manipulation/transformation of data when moving from source to destination. E.g. it can map data from a data-type in DB2 to an equivalent in Sybase.*
* Provide a warm-standby system. Can be incorporated with Open Switch to provide a fairly seamless fail-over environment.
* Merge data from several source databases into one destination database (could be for a warehouse type environment for example).
* Move data through a complicated network down to branch offices, say, only sending the relevant data to each branch.

ASA seems to have a number of niches. It is generally good at OLTP and can be used as a basis for a general database project. There are certainly examples of implementations supporting 100 or more users.

A major area for ASA databases is with applications that need to distribute the database with the application as a general storage area for internal components, but the database is not a major part of the deliverable. Sybase themselves have done this with the IQ meta data storage. Prior to release 11 of IQ, the meta data was stored in an ASE database. Now, with IQ 12, the meta data has moved to being stored in ASA. This makes the installation of IQ into production environments much simpler.

ASA has excellent ODBC support, which makes it very attractive to tools oriented towards ODBC.

* Windows 95/98/ME, NT, 2000, CE
* Novell NetWare
* Solaris/SPARC
* Solaris/Intel
* IBM AIX
* Linux (RedHat)
* HP-UX

ASA is a fully featured DBMS with transactional integrity, automatic rollback and recovery, declarative RI, triggers and stored procedures.

While it comes out of Sybase's "Mobile and Embedded" division, it is NOT limited to "small, desktop applications". There are many ASA implementations supporting over 100 concurrent users. While not as scalable as ASE, it does offer SMP support and versions for various Unix flavors as well as Netware and NT/w2k. Multi-gigabyte databases are commonly used.

ASA offers a number of features that are not to be found in ASE:
* row level BEFORE and AFTER triggers
* long varchar and BLOB up to 2 gigabytes
* varchar up to 32k
* declarative RI with cascade actions
* all character and decimal data is stored var-len, using only the space
it needs

Tabular Data Streams or TDS is the name given to the protocol that is used to connect Sybase clients with Sybase servers. A specification for the protocol can be obtained from Sybase, I had a copy but cannot seem to find it now.

The is a project that is reverse engineering the protocol and building a set of libraries independent of either Sybase or Microsoft, but able to connect to either of their servers. FreeTDS is a considerable way down the line, although I do not believe that it is production ready yet!

Both DB-lib and CT-lib are libraries that implement the TDS protocol from the client side.

DB-lib
DB-lib was Sybase's first version. It was a good first attempt, but has/had a number of inconsistencies. There are, or possibly were, a lot of applications written using DB-lib. If you are about to start a new Open Client development, consider using CT-lib, it is the preferred choice. (What version of TDS does DB-lib, is it only 4.2?)

Having said that you should use CT-lib for new developments, there is one case that this may not be true for and that is 2 phase commit. 2 phase commit is supported directly by DB-lib but is not supported directly by CT-lib.

CT-lib
CT-lib is a completely re-written version of Open Client that was released in the early '90s. The API is totally different from DB-lib, and is much more consistent. Applications written using DB-lib cannot simply be compiled using CT-lib, they need a significant amount of porting effort. CT-lib is newer, more consistent and, in several people's opinions, including mine, slightly longer winded. Having said that, the future of DB-lib is uncertain and is certainly not being developed any more, as a result all new apps should be written using CT-lib.

Open Client is the interface (API) between client systems and Sybase servers. Fundamentally, it comes in two forms:

Runtime

The runtime version is a set of dynamic libraries (dlls on W32 platforms) that allow client applications to connect to Sybase and Microsoft servers, or, in fact, any server that implements the Tabular Data Streams (TDS) protocol. You need some form of Open Client in order to be able to connect to ASE in any way, shape or form. Even if you are running isql on exactly the same machine as ASE itself, communication will still be via Open Client. That is not to say that client to server communication on the same machine will go via the physical network, that decision is left entirely to the protocol implementation on the machine in question.

Identities are created type numeric, scale 0, and precision X. A precision of 9 means the largest identity value the server will be able to process is 10^9-1, or 1,000,000,000-1, or 999,999,999. However, when it comes to Burning identities, the server will burn (based on the default value of 5000) .05% of 1,000,000,000 or 500,000 values in the case of a crash. (You may think an identity precision allowing for 1 Billion rows is optimistic, but I once saw a precision set at 14...then the database crashed and their identity values jumped 5 TRILLION. Needless to say they abandoned their original design. Even worse, SQL server defaults precision to 18 if you don't specify it upon table creation...that's a MINIMUM 10,000,000,000 jump in identity values upon a crash with the absolute minimum burn factor)

By example:
declare @Integer int
/* Good for positive numbers only. */
select @Integer = 1000

select "Positives Only" =
right( replicate("0", 12) + convert(varchar, @Integer), 12)

/* Good for positive and negative numbers. */
select @Integer = -1000

select "Both Signs" =
substring( "- +", (sign(@Integer) + 2), 1) +
right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)

select @Integer = 1000

select "Both Signs" =
substring( "- +", (sign(@Integer) + 2), 1) +
right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)

go

Produces the following results:

Positives Only
--------------
000000001000

Both Signs
-------------
-000000001000

Both Signs
-------------
+000000001000

During processing, if a divide by zero error occurs you will not get the answer you want. If you want the result set to come back and null to be displayed where divide by zero occurs do the following:

1. select * from total_temp
2. go
field1 field2
----------- -----------
10 10
10 0
10 NULL

(3 rows affected)
1. select field1, field1/(field2*convert(int,
substring('1',1,abs(sign(field2))))) from total_temp
2. go
field1
----------- -----------
10 1
10 NULL
10 NULL

As long as there is nothing between the field delimiters in your data, a null will be entered. If there's a space, the value will be Jan 1, 1900.

You can use sed(1) to squeeze blanks out of fields:
sed -e 's/|[ ]*|/||/g' old_file > new_file

Fortunately this is one of the simple ones. For batch sessions, simply use the the -o command line switch to specify the name of the output file. For interactive sessions, the simplest method is turn on the capture functionality of your xterm window while running the query. If this isn't possible (i.e., you are not running on UNIX), then there are a couple of options:

* Use sqsh instead
* Use the tee(1) command which takes the output from a command and writes/appends it to a file, as well as displaying it on the screen. (Again, this may be UNIX-specific; Windows users should research the CYGWIN toolset for similar functionality.)

isql uses the open/client libraries, which have no built in means of securing the packets that I know of. However, it is possible to use ssh to do all of the work for you. It is really quite straightforward. I saw this first published on the Sybase-L list by Tim Ellis, so all of the credit gos to him.

1. You will need a server running sshd that you have access to, which also has access to the ASE server.
2. Choose a port that you are going to make your secure connection from. Just like all ASE port selections it is totally arbitrary, but you if you were setting up a number of these, then you might want to think about a strategy. Regular server + 100 or something. Just make sure that it does not, and will not, clash with any of your regular servers.
3. Edit the interfaces file on the client side and set up a new server with an IP address of localhost and the port number you chose in the previous point. You might want to call it SERVER_SSH just to make sure that you know that it is the secure one. 4. Run the following ssh command:
ssh -2 -N -f -L port_chosen_above:remote_server:remote_port user@ssh.server.com
5. Connect to the server using isql -Uuser -SSERVER_SSH

sqsh
In my opinion, and that of quite a lot of others, this is the most useful (direct) replacement for isql that exists. It combines the usefulness of a good shell with database interaction. Looking for the ability to page the output of a long command? Look no further. Need to search a result set using a regular expression? This is the tool for you.

Like isql, sqsh is a command line tool. It supports all of the features and switches of isql with myriad of its own. There is one feature that isql has the sqsh does not, and that is the ability to read the password as the first line of an input file. If you look at a lot of the examples above, the password is piped in, sqsh does not support this with the latest release. I am not sure if this is a deliberate feature or not.

A quick summary of its features:
1. command line editing;
2. command history;
3. ability to pipe to standard filters;
4. ability to redirect output to X window;
5. shell variables
6. background execution;

The following example queries sysdatabases and takes each database name and creates a string of the sort sp_helpdb dbname and sends the results to another isql. This is accomplished using bourne shell sh(1) and sed(1) to strip unwanted output:

#!/bin/sh

PASSWD=yuk
DSQUERY=GNARLY_HAIRBALL

echo "$PASSWD print "$PASSWD"
go
select 'sp_helpdb ' + name + char(10) + 'go'
from sysdatabases
go" | isql -U sa -S $DSQUERY -w 1000 |
sed -e '/affected/d' -e '/---/d' -e '/Password:/d' |
isql -U sa -S $DSQUERY -w 1000

If you pipe the output of isql then you can use sed(1) to remove this extraneous output:

echo "$PASSWD
sp_who
go" | isql -U sa -S MY_SERVER | sed -e '/affected/d'
-e '/---/d'


If you simply wish to eliminate the row affected line use the set nocount on switch.