Chapter 8 Issuing
Remote Procedure Calls

Access Service User's Guide DirectConnect

Chapter 7 Using Sybase Mode commands

Chapter 7

Using Sybase Mode commands

This chapter describes the Transact-SQL (T-SQL) syntax subset recognized in the Sybase SQL transformation mode. SQL commands, clauses, and other syntactical elements are presented in alphabetical order. Many of the commands use up to three-part table names. DB2 supports the following three-part naming convention:

location_name (database system name, current server)
authorization_ID (owner)
table_nam or view_name

All Sybase mode commands are issued as language commands, unless otherwise noted.

The following Transact-SQL commands are presented in this chapter. Each command section contains a description, syntax, examples, and comments.

Transact-SQL commands

SQL Command

Description

Location (page)

alter table

Adds new columns to an existing table

begin transaction Marks the starting point of a user-specified transaction

commit transaction

Commits all work performed for this transaction

create index Creates a new index on a table

create table

Creates new tables

create view Creates a new view

delete (cursor command)

Removes rows from a table using a cursor command

delete (dynamic command) Removes rows from a table using a dynamic command

delete (language command)

Removes rows from a table using a language command

drop index Removes an index from a table

drop table

Removes a table

drop view Removes a view

execute

Runs a system procedure or user-defined storage procedure

grant Assigns authorization to users

insert (dynamic command)

Adds new rows to a table or view using a dynamic command

insert (language command) Adds new rows to a table or view using a language command

prepare transaction

Checks to see if connections to databases are active

revoke Revokes authorization of users

rollback transaction

Rolls back or aborts the current transaction

select Retrieves rows from the database objects

truncate table

Truncates the table by removing all rows. This statement is not logged and is not part of any transaction

update (cursor command) Changes data in row made current by a read cursor (positional update)

update (dynamic command)

Changes data in existing rows using a dynamic command

update (language command) Changes data in existing rows using a language command

use

Accesses an existing database

For more information about the Sybase SQL transformation mode commands, see the Sybase SQL Server Reference Manual. alter table Using a language command, alter table adds new columns to an existing table. alter table [database.[owner].]table_nameadd column_name datatype {null | not null}[{, next_column}...] table_name is the name of the table to be changed. column_name is the name of a column to be added. datatype is the datatype of the column. Use only system datatypes, except bit. Certain datatypes expect a length, n, in parentheses: datatype(n) null specifies that you should assign a null value when a value is not provided during an insertion. next_column indicates that you can include additional column definitions separated by commas, using the same syntax described for a column definition. The following example adds the manager_name column to the publishers table. For each existing row in the table, a null value is assigned to the new column. alter table publishersadd manager_name varchar(40) null the CIS functionality in ASE (formerly OmniConnect) sends the alter table command to DirectConnect as a language event. The following are not supported: add constraint drop constraint replace column name partition | unpartition Transformation adds parentheses when the add column option includes more than one column. begin transactionA DB2 equivalent of this command does not exist. The access service sends both begin transaction and transaction_name to the target as begin transaction. none Not defined commit transaction Commits the work resulting from the current transaction. commit transaction {transaction_name} transaction_nameis the name assigned to the transaction. It must conform to the rules for identifiers that are described in the Sybase SQL Server Reference Manual. commit transaction The access service accepts this statement and translates it to the DB2 commit statement. If a transaction is not currently active, this statement has no effect. In Sybase mode, transaction_name is stripped from the statement before it is passed on the target. The access service converts both commit transaction transaction_name and commit transaction without a transaction name to a commit statement. create index Using a language command, create index adds a new index to an existing table. create [unique] index index_name on [[database.]owner.]table_name (column_name [, column_name]...) unique is an optional keyword that prohibits duplicate index values (also called key values). index_name is the name of the index. Index names must be unique within a table but need not be unique within a database. table_name is the name of the table that contains the indexed column or columns. column_name is the column or columns to be included in the index. Composite indexes are based on the combined values of up to 16 columns. The sum of the maximum lengths of all the columns used in a composite index cannot exceed 256 bytes. create index au_id_ind on authors (au_id) create index ind1 on titleauthor (au_id, title_id) Columns of type bit, text, and image cannot be indexed. You cannot create an index on a view. The access service initially accepts the following T-SQL statement components but then strips them out of the statement: clustered and nonclustered with { fillfactor = x, ignore_dup_key, sorted_data } on segment_name, which specifies the segment where the index is to be created. create table Creates a new table. create table [database.[owner].]table_name (column_name datatype {null | not null} [{, next_column }...]) [on segment_name] table_name is the name of the new table. It conforms to the rules for identifiers and is unique within the database and to the owner. column_name is the name of the column in the table. It conforms to the rules for identifiers and is unique in the table. datatype is the datatype of the column. Only system datatypes are used. As shown in Table 7-3 , several datatypes expect a length, n, in parentheses:

     datatype(n)

null | not null

specifies a null value if a user does not provide a value during an insertion and no default exists (for null), or that a user must provide a non-null value if no default exists (for not null).

next_column

indicates that you can include additional column definitions (separated by commas) using the same syntax described for a column definition.


	Expand Search