MDBitz Development Resources

In most instances you will be using a User Interface of one sort or another to execute Transact-SQL commands against your Sybase Database Servers. In rare occasions however I find that I execute commands through the dos prompt via the Interactive SQL Utility or iSQL as it is more commonly referred.

Opening a Connection

BCP and iSQL both use the same parameter flags when specifying the database server and user credentials for connecting to a server.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)

Using these variables you would connect to the Sybase Database Server with the following command.

isql -S server -U userName -P password

Issuing Transact-SQL Commands

A successful connection will present you with the > carat letting you know you can now enter your T-SQL commands. To do so you simply type the command line by line and when finished type the GO command to issue the SQL.

> USE db2
> GO
> SELECT *
> FROM books b
> WHERE b.pub_date >= '09/01/2009'
> GO

Understanding the output from iSQL

iSQL will output the results of the command to standard output. However you have the option of formatting the output to your own preferences.

• Headers Flag -h to modify the number of lines between the column headers and the data rows use the -h flag followed by the number of desired lines.
• Column Seperator -s by default a space is used to separate the columns in a row you can use the -s flag to change this to your desired delimiter.
• Column Width -w by default the output is limited to 80 characters per line use the -w flag to modify this to the length you want.

Interactive SQL is a useful tool when you need to quickly execute commands against your database. For that reason alone it is worth knowing about the utility, but in most cases you will find yourself using other UI based applications to issue commands against your Sybase Server.

References

• Sybase Online Manual – Using the iSQL Utility

When performing database maintenance you will occasionally find the need to export data out of your database tables to an operating system for storage, or conversely import data to a table from a file. You may find yourself needing to do these tasks for data backup or for inserting data that comes from a 3rd party export. Sybase makes this process simple by the Bulk Copy Utility (BCP).

Basic BCP Usage

BCP as it’s name suggest is used for 1 purpose the bulk copy of data to and from Sybase Tables. To utilize the utility the basic command format is:

bcp DATABASE..TABLE out C:\DB\temp.txt

If you wanted to perform an import you simply switch the out keyword for in.

BCP Option Flags

In addition to the command there are a few options you may also find yourself using.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)
• Native Format -n To export data in native format which is not readable by looking directly at the file you can use the -n flag. This mode will make it so you don’t have to specify the column format during export.
• Char Format -c To export all data in basic char format then you can use the -c flag. This mode will also make it so you don’t have to specify the column format during export.
• Tab Format -t The default format for delimiting data columns per row is the tab character to specify a different delimiter -t can be used followed by the new delimiter.
• Row Format -r If you want to have a different delimiter then the new line character in your export you can modify it by using the -r flag followed by the new delimiter.

Basic BCP Output Example

bcp DATABASE..TABLE out C:\DB\temp.txt -c -S server -U userName -P password

Basic BCP Input Example

bcp DATABASE..TABLE inC:\DB\temp.txt -c -S server -U userName -P password

The Bulk Copy Utility (BCP) is a very simple and versatile tool. If you find yourself doing a lot of database management you may want to familiarize yourself with it as it can save you a lot of time, effort, and headaches.

Resources

• Sybase Online Manual – BCP Utility Guide

Today I was running some tests on my T-SQL Procedures and noticed that for some reason my Select statement was performing in seconds compared to my Procedure which would take minutes to complete. After a few hours I stumbled upon my mistake I had used the date datatype in the procedure parameters when my database table was of type datetime, by switching the variable to a varchar(30) datatype the procedures performance had increased and functioned with the same IO count and time as the select statement.

Incorrect Procedure Statement

CREATE PROCEDURE P_RULE_MULTI_LOCATION
@startDate DATE,
@endDate DATE
AS
BEGIN
 
SELECT * FROM ACTIVITY_LOG WHERE ACTION_TS >= @startDate AND ACTION_TS < @endDate
 
END

Correct Procedure Statement

CREATE PROCEDURE P_RULE_MULTI_LOCATION
@startDate VARCHAR(30),
@endDate VARCHAR(30)
AS
BEGIN
 
SELECT * FROM ACTIVITY_LOG WHERE ACTION_TS >= @startDate AND ACTION_TS < @endDate
 
END

The simple above change enhances the queries performance where it can use both an index and its position when performing the select statement. While the date to datetime comparison does not allow for the index position to be used due to the conversion. It may not make the most sense but if you find your procedure functioning slower than your direct sql query you may want to check your datatypes to make sure that they are the same.

It is often the case that developers new to databases will try to perform large data modifications without batching. When this happens in most cases the modification will fail with the error of the transaction log being full after you are presented with the message The transaction log in the database is almost full. Your transaction is suspended until space is made available in the log. What is happening is that the update/insert modification is trying to change too many rows without committing so the transaction log is getting filled up and once it is full the modification fails and no changes get saved to the database.

To correct this it is good practice to perform your database modifications in batches, and upon each iteration of the batch to clear your log transaction file. The dump tran command notifies Sybase that you want to clear the transaction log of a database and with which options. In these cases you simply want to clear it without backing up the log to a file so you would use the with turncate_only option.

DUMP TRAN MY_TABLE WITH truncate_only

Resources

• Sybase 12.5 Manual: dump transaction command

Transferring data between tables is a common task of database maintenance. It is used when updating database schemas or when you need to migrate data to a log or history table. There are 2 common methods for performing the transfer the first is done by use of a WHERE NOT EXISTS sub query, while the less common known method is by setting up and utilizing a Trigger, which removes rows from the old table as soon as they get inserted into the new table.

WHERE NOT EXISTS Method

The most common method of transferring data between tables in a database that i have seen is the combination of ROWCOUNT and a WHERE NOT EXISTS clause. How this method works is that you set your ROWCOUNT to the batch size then you insert into your table to be transferred to from a select of your old table where the current record does not exist in your transfer to table. A sample code snippet showing this method is:

DECLARE @ErrorId INTEGER,
    @RowCtrInserted INTEGER
 
SET ROWCOUNT 10000
 
SELECT @RowCtrInserted = 1, @ErrorId = @@ERROR
 
WHILE (@ErrorId = 0 AND @RowCtrInserted > 0)
BEGIN			
    INSERT INTO LOG_HIST	
        (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
        )
    SELECT 
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG LOG
    WHERE NOT EXISTS (
        SELECT * 
        FROM LOG_HIST hist
        WHERE LOG.ID = hist.ID 
     )
 
    SELECT @RowCtrInserted = @@ROWCOUNT, @ErrorId = @@ERROR
END
 
IF @ErrorId=0 
BEGIN
    TRUNCATE TABLE LOG
END

As can be seen in the code this method loops while a record exists in the log table that is not present in the log_hist table. This requires an additional query every time the batch size is met, and can be a performance problem on large database tables.

Temporary Trigger Method

The second method that can be used to transfer data between two tables is the use of a temporary insert trigger on the table being transferred to. How it works is that the trigger is defined to delete the newly inserted record from the old table. What this means is that for every iteration of the insert loop as determined by the batch size a sub query to determine if a record already exists in the new table is not necessary as records are deleted as soon as they are transferred. Below is a sample sql script setting up the trigger and performing the transfer, in addition it contains error checking and print statements.

CREATE TRIGGER LOG_HIST_TR ON LOG_HIST FOR INSERT
AS
DELETE LOG FROM LOG, inserted
  WHERE 1=1
    AND LOG.ID = inserted.ID
go
 
PRINT "Transferring data from LOG to LOG_HIST"
go
 
DECLARE @err INT, @n INT
SELECT @n=0
PRINT 'Will use batch size of 10000 rows'
WHILE exists (SELECT * FROM LOG)
BEGIN
    INSERT LOG_HIST (
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    )
    SELECT TOP 10000
        ID,
        USER_EMP_ID,
        APP_NM,
        ACTION_NM,
        ACTION_TS,
        IP_AD
    FROM LOG
 
    SELECT @err=@@ERROR, @n=@n+@@ROWCOUNT
 
    IF @err=0
    BEGIN
        DUMP TRAN USERACTIVITY WITH truncate_only
        PRINT '%1! rows transferred', @n
    END
    ELSE
    BEGIN
        RAISERROR 20001 'Failed to transfer data from table LOG to table LOG_HIST. Please recover data from these two tables'
        BREAK
    END
END
 
go
PRINT "Checking data transfer completion"
go
IF not exists (SELECT * FROM LOG)
BEGIN
    DROP TRIGGER LOG_HIST_TR
END
ELSE
BEGIN
    RAISERROR 20001 'Failed to transfer data from LOG into LOG_HIST'
END

In the above example after each batch iteration we output the total number of records transferred, as well as cleaning up the database log so that we don’t max out the allocated space. Once an error is thrown or no more records exists we drop our temporary trigger. You will notice that no truncating of the table we transferred data from is needed as the trigger was responsible for removing them. The additional benefit to using this method is that if a transfer is interrupted no time or data is loss and the transfer can pick up right were it left off without performing an additional WHERE NOT EXISTS sub query.