alter database

Description Increases the amount of space allocated to a database. Synchronizes proxy table metadata with tables at remote location.

Syntax alter database database_name [on {default | database_device } [= size] [, database_device [= size]]...]

[log on { default | database_device } [ = size ] [ , database_device [= size]]...]

[with override] [for load]

[for proxy_update]]

Usage Usage

• If a database has been created with the optional clause with

default_location = pathname, then the alter database command, with the for proxy_update clause, will re-synchronize the proxy tables in the named database with tables and views found in the pathname to the remote location.

• The default location may also have been specified with the system stored procedure sp_defaultloc. The for proxy_update clause of alter database works the same way in this case.

• This is a convenient, one-step procedure for keeping the proxy table definition in sync with the definition of actual tables and views in a remote database.

• If for proxy_update is specified with no size or device name, then the size is not altered; only proxy table synchronization is performed.

• In some cases, a database may not be large enough to contain all proxy table definitions; therefore, it may be necessary to change the size as well when the for proxy_update clause is used.

• When for proxy_update is used, the names of remote tables and views are obtained from the server specified in the default location for the database (master.dbo.sysdatabases.default_loc) using the RPC named sp_tables. For each user table and view, column attributes are then obtained, using the RPC named sp_columns. Once all metadata has been obtained for a table (or view), an internal command is executed which is equivalent to create existing table, causing the proxy table to be created within the named database.

• If the proxy table already exists, it is automatically dropped before the internal create existing table command is executed.

• After the proxy table is created, index metadata is obtained from the remote location so that indexes on the proxy table can also be created. Index metadata is obtained from the remote server using the RPC sp_statistics.

• This command behaves the same way for all server classes; interaction with the remote server associated with the database default location is limited to the RPCs sp_tables, sp_columns and sp_statistics (to import index information).

alter table

Description Adds, changes or drops columns; adds, changes, or drops constraints; partitions or unpartitions an existing table; changes the locking scheme for an existing table; specifies ascending or descending index order when alter table is used to create referential integrity constraints that are based on indexes; specifies the ratio of filled pages to empty pages, to reduce storage fragmentation.

Syntax alter table [database.[owner].]table_name {add column_name datatype

[default {constant_expression | user | null}] {identity | null | not null}

| [[constraint constraint_name] {{unique | primary key}

[clustered | nonclustered] [asc | desc] [with { { fillfactor = pct | max_rows_per_page = num_rows } , reservepagegap = num_pages }] [on segment_name] | references [[database.]owner.]ref_table [(ref_column)] | check (search_condition) ] ... } [, next_column]...

| add { [constraint constraint_name] { {unique | primary key} [clustered | nonclustered] (column_name [asc | desc] [, column_name [asc | desc]...]) [with { { fillfactor = pct

| max_rows_per_page = num_rows} , reservepagegap = num_pages}] [on segment_name]

| foreign key (column_name [{, column_name}...]) references [[database.]owner.]ref_table [(ref_column [{, ref_column}...])] | check (search_condition)}

| drop {[column_name [, column_name]] | [constraint constraint_name]} | modify column_name {[data_type] [null] |

[not null]] [, column_name] | replace column_name

default {constant_expression | user | null} | partition number_of_partitions

| lock {allpages | datarows | datapages } } | with exp_row_size = num_bytes

Usage Usage

• Component Integration Services processes the alter table command when the table on which it operates has been created as a proxy table.

Component Integration Services forwards the request (or part of it) to the server that owns the actual object.

• When Component Integration Services forwards the alter table command to a remote server, it is assumed that the column names on the proxy table and on the remote server are the same.

• The only portions of the alter table command that are forwarded to a remote server are add, modify,drop column, partition, and unpartition. The rest of the syntax is processed internally, and not forwarded to a remote server. The only exception to this is the lock clause, and then only for ASEnterprise-class servers.

Server Class ASEnterprise

Component Integration Services forwards the following syntax to a server configured as class sql_server:

alter table [database.[owner].]table_name {add column_name datatype [{identity | null}] {[, next_column]}...}

| [drop column_name [, column_name]} | modify column_name [data_type] [NULL] |

[not null]] [, column_name]}

• When a user adds a column with the alter table command, Component Integration Services passes the datatype of each column to the remote

alter table

• text and image datatypes are not supported by server class ASIQ. If text and image datatypes are used, Component Integration Services raises Error 11205:

Datatype <typename> is unsupported for server <servername>. Server Class sql_server

• Handling of the alter table command by servers in this class is the same as for ASEnterprise servers.

Server Class direct_connect

• Component Integration Services forwards the following syntax to a remote server configured as class direct_connect:

alter table [database.[owner].]table_name add column_name datatype [{identity | null}] {[, next_column]}...

• Although Component Integration Services requests a capabilities response from a server with class direct_connect, support for alter table is not optional. Component Integration Services forwards the alter table command to the remote server regardless of the capabilities response. • The behavior of the server with class direct_connect is database

dependent.alter table [database .[owner ].]table_name {add column_name datatype [{identity | null}]

{[, next_column ]}... The Transact-SQL syntax is forwarded, and errors may or may not be raised, depending on the ability of the remote database to handle this syntax.

• If the syntax capability of the remote server indicates Sybase Transact- SQL, Adaptive Server datatypes are sent to the remote server. If the syntax capability indicates DB2 SQL, DB2 datatypes are sent. The mapping for these datatypes is shown in Table 3-2

Table 3-2: DirectConnect datatype conversions for alter table Adaptive Server Datatype DirectConnect Default Datatype DirectConnect DB2 Syntax Mode Datatype

binary(n) binary(n) char(n) for bit data bit bit char(1)

char char char datetime datetime timestamp decimal(p, s) decimal(p, s) decimal(p, s) float float float

Server Class db2 •Component Integration Services forwards the following syntax to a remote server configured as class db2:

alter table [database.[owner].]table_name

image image varchar(n)

for bit data; the value of n is determined by the global variable @@textsize

int int int

money money float numeric(p, s) numeric(p, s) decimal(p, s) nchar(n) nchar(n) graphic(n) nvarchar(n) nvarchar(n) vargraphic(n) real real real

smalldatetime smalldatetime timestamp smallint smallint smallint smallmoney smallmoney float timestamp timestamp varbinary(8) tinyint tinyint smallint

text text varchar(n); the value of n is determined by the global variable @@textsize unichar unichar varchar(n) for bit data univarchar univarchar varchar(n) for bit data varbinary(n) varbinary(n) varchar(n) for bit data varchar(n) varchar(n) varchar(n)

Adaptive Server Datatype DirectConnect Default Datatype DirectConnect DB2 Syntax Mode Datatype

alter table

Table 3-3: DB2 datatype conversions for alter table