SAP ASE configuration allows page sizes of 2K, 4K, 8K, or 16K bytes.
SAP ASE supports extended sizes of char, varchar, univarchar, unichar, binary, and varbinary datatypes. The limit depends on the page size of the server:
These sizes are approximate. The basic rule specifies that the limit is the maximum size that still allows a single row to fit on a page. These limits also vary depending on the locking scheme specified when the table is created. It is assumed that the bulk of proxy tables are created with the default locking scheme, which is allpages locking.
Limits on length of Transact-SQL variables and parameters – the size of char, varchar, binary, and varbinary variables are extended to equal the maximum size of columns of the same datatype for a given server. This allows variables to be passed to stored procedures (or RPCs) whose length exceeds the current limit of 255 bytes.
Limits on number of columns per table – as many as 1024 columns per table are allowed, as long as the columns can still fit on a page. There is a limit of 254 variable-length columns (null columns are also considered variable length).
Limits on the width of an index – the total width of an index within SAP ASE can be larger than in earlier versions, depending on server page size.
Earlier versions of SAP ASE had a limit of 255 bytes for char/binary columns.
Limits on the number of columns per index – 31 columns per index.
Table names, column names and index names can be up to 255 bytes.
Identifier names can now be up to 255 bytes.
the create table command allows columns of datatype char, varchar, binary, and varbinary to be specified with extended lengths, as described above. These datatypes and lengths are forwarded to the remote server on which the table is to be created.
the create existing table command also allows columns to be specified with a length of greater than 255 bytes. This allows Component Integration Services to treat columns in remote databases as char, varchar, binary, or varbinary that previously had to be treated as text or image columns.
There is still an opportunity for column size mismatch errors. For example, in the case where the remote database contains a table with a column length of 5000 bytes, and the SAP ASE processing create existing table supports columns only up to 1900 bytes, a size mismatch error occurs. In this case, it is necessary to respecify the column as a text or image column.
When the proxy table column size exceeds that of the corresponding column in the remote table, a size mismatch error is detected and the command is aborted.
If alter table operates on a proxy table, it is first processed locally, then forwarded to the remote server for execution. If the remote execution fails, the local changes are backed out and the command is aborted.
The remote server must process the command appropriately, or raise an error. If an error is produced, the Component Integration Services side of the command is aborted and rolled back.
Component Integration Services handles large column values when proxy tables are involved in data manipulation language (DML) operations. Component Integration Services handles DML using one of several strategies:
Tabular data stream (TDS)™ language commands – if the entire SQL statement can be forwarded to a remote server, then Component Integration Services does so using TDS Language commands generated by CT-Library ct_command (CS_LANG_CMD).
The text of the language buffer may contain data for long char or binary values that exceeds 255 bytes, and remote servers must handle parsing of these command buffers.
TDS dynamic commands – if Component Integration Services cannot forward the entire SQL statement to a remote server (for example, Component Integration Services is forced to provide functional compensation for the statement), then an insert, update, or delete may be handled by using TDS dynamic commands, with parameters as needed, using the CT-Library function ct_dynamic (CS_PREPARE_CMD, CS_EXECUTE_CMD, CS_DEALLOC_CMD).
The parameters for the dynamic command may be CS_LONGCHAR_TYPE or CS_LONGBINARY_TYPE.
TDS cursor commands – CT-Library cursor operations can be used to handle proxy table operations for select, update, and delete if functional compensation has to be performed. For example, if you are updating a proxy table and there are multiple tables in the from clause, Component Integration Services may have to fetch rows from multiple data sources, and for each qualifying row, apply the update to the target table. In this case, Component Integration Services uses ct_cursor ({CS_DECLARE_CMD, CS_OPEN_CMD, CS_CURSOR_UPDATE_CMD, CS_CLOSE_CMD, CS_DEALLOC_CMD}).
After a cursor is prepared, parameters are specified. These parameters may include those of type CS_LONGCHAR or CS_LONGBINARY.
Bulk insert commands – when performing a select/into operation, if the target server supports the bulk interface (only true of remote SAP ASE servers and DirectConnect for Oracle), then the remote server must be prepared to handle char and binary values greater than 255 (via CS_LONGCHAR, CS_LONGBINARY values).
Columns from remote servers may be returned to Component Integration Services as type CS_LONGCHAR_TYPE or CS_LONGBINARY_TYPE.
Columns from remote servers may be returned to Component Integration Services as type CS_LONGCHAR_TYPE or CS_LONGBINARY_TYPE.
RPCs sent to remote servers can contain parameters of types CS_LONGCHAR and CS_LONGBINARY. The Component Integration Services command cis_rpc_handling supports these types.
Sending long parameters to SAP ASE servers older than version 12.5 is not allowed, as earlier versions of SAP ASE do not support CS_LONGCHAR or CS_LONGBINARY data. Component Integration Services examines TDS capabilities for the remote server prior to sending the RPC, and if the remote server cannot accept these datatypes, an error results.
The SQL Anywhere or ASIQ stored procedure sp_tables only returns user tables.