|
Schema
> Products
> SRTransport
> Documentation
> Modules
> Sybase
The Sybase Module
The Sybase transport module allows you to read
records from and write records to Sybase tables, views and stored procedures.
Table Detail
SRTransport reads the detail about the chosen table directly from the Sybase data dictionary. The following picture presents the detail for our example table invoice.
 
The window can be resized. The individual columns in the above picture can also be resized to view long names.
You can not change the table itself in this window. Use SQL commands to alter the structure of the table. SRTransport will display those changes appropriately.
The columns have the following definitions.
- P: Indicates that the column is the (or part of the) primary key (see sp_primarykey in the Sybase Commands Reference Guide).
- I: Identifies - When checked SRTransport uses this column to build WHERE clauses for updates and deletes.
- Column: The column name.
- U: Update - When checked SRTransport will never update this column.
- Type: The user defined datatype (see sp_addtype in the Sybase Commands Reference Guide).
- Sys Type: The Sybase primitive datatype (see Dataypes in the Sybase Commands Reference Guide).
- Size: The number of bytes of storage each element of that datatype will consume. For variable length datatypes, size is the maximum size.
- Null: ?????Indicates that the column allows NULL's (missing values). (See CREATE TABLE in the Sybase Commands Reference Guide).
Nulls overwrite:
If a record from the data source contains a NULL value, and "Nulls overwrite" is checked, the update statement will set the column to null:
UPDATE ... SET columnA = NULL
If Nulls overwrite is not checked, the set columnA = NULL clause will be omitted, and the value in the table will not be overwritten.
This option is only appropriate for update statements (i.e. modes Update Only, Insert or Update), and only if the table is the destination.
The delete-and-insert mode behaves similarly to 'update with null values overwite' with two exceptions.
- Different triggers fire
- In update mode, columns in the table that are not supplied with data from the source (or from any expression) keep their value. In delete-and-insert mode, the record is deleted and reinserted, effectively setting those same fields to NULL.
To avoid loss of data, leave this option un-checked.
Dynamic Procedures
If this option is specified, then a stored procedure is created at the start of the transport. Each row is passed as parameters to the stored procedure.
The benefit is that the database server precompiles the stored procedure, and doesn't need to parse each new SQL statement. Database server performance is increased. There is no additional cost within SRTransport.
The dynamic stored procedure is dropped (removed) at the end of the transport.
Use SQL Only
Sybase defines two methods for inserting or updating columns with datatypes text and image.
- Via standard SQL insert/update statements
- Via optimized API calls
Method 1 above is limited in size to 128kb. Method 2 has no size limit, but requires more SQL statements. Use method 2 if your data (text or image) is very large.
Likewise, calls to stored procedures (dynamic or pre-existing) can be performed either in SQL or via an optimized RPC mechanism.
Transaction size
The transaction size indicates the number of rows that are committed to the database at one time. It is only appropriate if the table is the data destination.
Transaction sizes greater than 1 are supported only for the Bulk Copy mode  .
Transport Modes
The transport mode determines how each row of data is transported to the table. This mode is only appropriate if the table is the data destination.
There are seven transport modes to choose from. Each mode is explained in the immediately following sections.
Sybase may reject SQL (insert, update and delete) statements for many reasons. Here are some of them.
- Violation of a unique index.
- NULL values are not allowed in a column.
- Datatypes do not convert.
- Violation of a column rule.
- A trigger raised an error.
- Insufficient disk space (data or log).
If Sybase rejects any insert, update or delete statement, SRTransport rejects the corresponding row. The row is written to the reject file and to the log with a descriptive message.
Update Insert
'Update Insert' is one of seven Transport Modes.
In this mode, SRTransport attempts to update the row. If the SQL update statement effects now rows, then SRTransport attempts to insert the row.
This mode is most efficient if you expect most transported rows to update existing rows in the target table.
To update the row, SRTransport uses the primary key of the table. If the table doesn't have a primary key, this mode will fail.
A NULL value in the transported row will overwrite (erase) data in the corresponding column of the table, if the option Nulls Overwrite is enabled.
Insert Update
'Insert Update' is one of seven Transport Modes.
In this mode, SRTransport determines whether or not the row exists in the table (using an SQL if exists statement). If it does exist in the table, the row is updated with values from the transported row. If the row doesn't exist in the table, a new row is inserted.
This mode is the least efficient because it searches for the row before deciding to update or insert.
To determine whether or not a row currently exists, and to update the row, SRTransport uses the primary key of the table. If the table doesn't have a primary key, this mode will fail.
A NULL value in the transported row will overwrite (erase) data in the corresponding column of the table, if the option Nulls Overwrite is enabled.
See 'Delete Insert' for a comparison of these two modes.
Delete Insert
'Delete Insert' is one of seven Transport Modes.
In this mode, SRTransport deletes the row from the table before it inserts the new row. SRTransport determines which row to delete by the primary key of the table.
It is not an error if the row doesn't exist prior to inserting it into the table.
To determine which row to delete, SRTransport uses the primary key of the table. If the table doesn't have a primary key, this mode will fail.
The end result of this mode is similar to, but different from "Update or Insert" mode or "Insert or Update" mode . In "Delete and Insert" mode, columns in the table that do not have a corresponding value in the transport row will effectively be set to NULL (erased). Values in the transport row which are NULL will effectively overwrite (erase) values in the table.
In "Delete and Insert" mode, the delete and insert triggers are fired. With "Update or Insert" mode, the update or insert trigger is fired (depending upon whether or not the previous row existed).
Insert
'Insert' is one of seven Transport Modes.
The transport row is inserted into the table. Sybase may reject the row if it violates a unique index (or for other reasons).
This mode requires that the table have a primary key, only when text and image datatypes are inserted.
Update
'Update' is one of seven different Transport Modes.
The row in the table is updated with new values from the transport row. If the row does not exist in the table before the update, it is an error and the transport row is rejected.
A NULL value in the transport row will not overwrite (erase) data in the corresponding column of the table.
To determine which row to update, SRTransport uses the primary key of the table. If the table doesn't have a primary key, this mode will fail.
Delete
'Delete' is one of seven Transport Modes.
The transport row is deleted the table. Sybase may reject the row if it violates a unique index (or for other reasons).
This mode requires that the table have a primary key, only when text and image datatypes are inserted.
Bulk Copy
'Bulk Copy' is one of seven Transport Modes .
Rows are inserted into the table using Sybase's bulk copy method (which is not SQL -- and isn't traced). To use the bulk copy method, Sybase requires that the database option "select into/bulk copy" be turned on (see sp_dboption in the Sybase Sql Server reference manual).
The Bulk Copy method is much faster than insert statements, but be aware that Bulk Copy mode bypasses the tables' triggers and rules (default values are substituted). In general, the Bulk Copy mode gives increased speed and lower integrity and flexibility.
Bulk Copy mode uses the Transaction size field to determine how many rows to insert at one time.
The best performance is attained when you Bulk Copy data into a table without any indexes, and then create the indexes after the data is copied. There are two exceptions to this rule. One, when the table already contains records, you may not want to drop the indexes, especially if the number of copied records are far fewer than the number of existing records. Two, if the table is initially empty, and the data to be copied is presorted in the order of the tables' clustered index, then you want to create the clustered index before copying the data, and create other the non-clustered indexes after.
WARNING:
Rows are transported differently when the transaction size is not 1, as you would normally want with the Bulk Copy mode.
When the transaction size is one, each row is processed in turn as follows:
execute your row preprocessing 4gl (if any)
transport the row
count the row as transported or rejected
execute your row postprocessing 4gl
When the transaction size is greater than one, rows are processed as follows (A batch is a set of n rows, where n is the transaction size):
for each row in the batch
execute your row preprocessing (if any)
transport the row (but within a transaction, which
might be undone/rolled-back)
if the row fails, it's counted as rejected
otherwise it's not counted as transported until later
commit the entire batch of rows
if the commit succeeds
for each row in the batch
count the row as transported
execute your row postprocessing 4gl
(else) if the commit fails
retry each row with the transaction size equal to 1
The purpose of this added complexity is to gain the performance provided by transactions, while maintaining these consistent behaviors.
| 1 | The count of transported and rejected rows is accurate.
| | 2 | Your row postprocessing code knows for sure whether the
row was transported or not.
|
Primary key - Identifies -
The primary key is reverse engineered from the chosen Sybase table. This is done every time the database is opened. Therefore, be aware that if your primary key changes in the database, your transports will automatically adapt to those changes.
The primary key is used to form a where clause in each of the following sql statements:
| if exists | - Insert or Update mode
| | delete | - Delete and Insert mode
| | update | - Update mode and Insert or Update mode
| | select | - All insert and update statements that involve TEXT and IMAGE data
|
The primary key is not used for Bulk Copy mode or in Insert mode when there is no text or image data.
The where clause references every column of the primary key that has data provided by the data source. Be aware that this does not necessarily mean all columns of the primary key. Therefore, if there are columns in the primary key that are not in the data source, it's possible that the where clause generated will not uniquely identify a row, and therefore, it's possible for an imported row to update many rows in the table. SRTransport does not consider this an error.
Options Pop-up Menu
Truncate Table
Truncate all rows of data from the table. The delete trigger in Sybase is not activated by the Sql command TRUNCATE-TABLE.
This command is only available in the table window, and only if the table is the data destination.
Delete All From Table
Delete all rows of data from the table. The delete trigger in Sybase is activated by the Sql command DELETE FROM.
This command is only available in the table window, and only if the table is the data destination.
Dump Tran No Log
'Dump Tran No Log' dumps the transaction log in the Sybase database. This command tells Sybase to throw away the log file for this database. It can run for a long time, depending upon the size of the log.
Sybase keeps a log of all inserts, updates and deletes. As you transport data, you are filling up this log., and when the log is full, everything stops. Therefore, you may want to throw the log away.
This command does not effect the dataserver's ability to recover from a system crash. But it does effect recovery from a disk crash.
For disk crashes, this command does not effect your ability to recover from media with full backups (full dumps).
For disk crashes, this does effect your ability to recover from media with partial backups (transaction dumps). You can't.
See the Sybase command dump transaction and dump database for further information.
Enable Bulk Copy
To use the Bulk Copy transfer mode, You must enable bulk copy operations in your Sybase database. This command performs the following two SQL statements.
EXEC sp_dboption your_database, 'bulkcopy', true
CHECKPOINT
This also enables SELECT INTO operations in your database.
WARNING: Performing a bulk-copy or select-into operation will prevent you from doing incremental
backups (a.k.a. transaction dumps or partials). See the Sybase documentation for more information.
Disable Bulk Copy
Use this command to disable bulk-copy operations in your database. This also disables select-into
operations. This command performs the following two SQL statements.
EXEC sp_dboption your_database, 'bulkcopy', false
CHECKPOINT
You can do incremental backups of this database after you've disabled bulk-copy operations, and taken a full backup of the database (Sybase dump database command).
Interactive SQL
This brings up a window into which you can type and send SQL to the database.
| 
