Sybase

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;

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

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

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)

The timestamp datatype is user-defined datatype supplied by Sybase, defined as:

varbinary(8) NULL

It has a special use when used to define a table column. A table may have at most one column of type timestamp, and whenever a row containing a timestamp column is inserted or updated the value in the timestamp column is automatically updated. This much is covered in the documentation.

What isn't covered is what the values placed in timestamp columns actually represent. It is a common misconception that timestamp values bear some relation to calendar date and/or clock time. They don't - the datatype is badly-named. SQL Server keeps a counter that is incremented for every write operation - you can see its current value via the global variable @@DBTS (though don't try and use this value to predict what will get inserted into a timestamp column as every connection shares the same counter.)

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

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

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.

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.

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!

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

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.

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.

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