Using Adaptive Server Enterprise version 12.5 with Replication Server version 12.1 or earlier: Schema-length and compatibility issues
Version 0.7 DRAFT
New features in Adaptive Server Enterprise version 12.5 (ASE 12.5) support schema limits (table column count, table column length, stored procedure parameter count, and stored procedure parameter length) that exceed those supported by Replication Server versions 12.1 and earlier. Full Replication Server support of Adaptive Server's new schema limits is scheduled for Replication Server version 12.5.
ASE 12.5 and Replication Server versions 12.1 and earlier can work together as long as all replicated data adheres to schema limits supported by Adaptive Server versions 12.0 and earlier (ASE pre-12.5). With certain constraints, you can configure ASE 12.5 and Replication Server version 12.1 or earlier to work together even if Adaptive Server uses a database page size larger than 2K (the ASE pre-12.5 limit).
This white paper describes how to configure ASE 12.5 to work with Replication Server version 12.1 or earlier.
In this paper, Replication Server (without version numbers) refers to Replication Server version 12.1 or earlier.
You can configure the ASE 12.5 RepAgent thread so that replicated commands containing too many columns or columns that are too wide are not copied to Replication Server. You cannot, however, configure RepAgent so that commands containing wide rows are not replicated. Replication Server always discards these commands.
Replication Server notes discarded commands and procedures in the error log, but otherwise continues to replicate in-bounds commands and procedures without interruption. Discarded commands and procedures are not recorded in the exceptions log and cannot be recovered.
Stored procedures with wide parameters from ASE pre-12.5 are rolled back and not replicated. Stored procedures with wide parameters from ASE 12.5 commit, but Replication Server discards them.
You must transfer metadata settings separately when migrating a replication database to ASE 12.5 if you use a copying utility such as bcp.
You must modify the replication system database tables rs_lastcommit and rs_threads to maintain row-level locking support when they are created on or migrated to an ASE 12.5 with a page size larger than 2K.
Replicated commands will roll back if they are too wide for a table with variable-width columns on an ASE pre-12.5 database. The same replicated commands on the same table on an ASE 12.5 database with a larger than 2K page size may commit. If, however, the replicated command is too wide for Replication Server, the command is discarded.
replicated command - a data manipulation language (DML) command (for example, insert, update, or delete) or a stored procedure execution (exec) that can be retrieved from an Adaptive Server database transaction log and sent to another Adaptive Server database via Replication Server.
A replicated command can be defined in terms of database object schema limits.
Database object schema limits - if we limit our discussion of database objects to tables and stored procedures, then database object schema limits describe:
replication database - any database that participates in a replication environment, including the Replication Server System Database (RSSD).
database migration - the movement of a database from ASE pre-12.5 to ASE 12.5 configured for a page size larger than 2K.
database upgrade - occurs as either:
Adaptive Server 12.5 reserved words
In ASE 12.5 and later, "function" is a reserved word. Make sure that you edit your Replication Server scripts to ensure that all instances of the word "function" are now encased in single quotes.
The RepAgent thread
The ASE 12.5 RepAgent thread uses a data limits filter to halt replication of commands that exceed certain database object schema limits of Replication Server.
The filter targets replicated commands based on the number of columns, column length, and parameter length. It can stop, skip, or truncate replicated commands that exceed these Replication Server limits:
The data limit filter does not target replicated commands based on row or parameter list length. Thus, RepAgent always copies a replicated command that exceeds the Replication Server inbound queue block limit of 16K. See the section, "Replication Server inbound queue," for more information.
Use sp_config_rep_agent on Adaptive Server to specify how you want RepAgent to handle replicated commands that exceed Replication Server limits. The options are:
To configure these options, log in to Adaptive Server and enter:
sp_config_rep_agent database_name, 'data limits filter mode',
The default option is stop. When the data limit filter mode is set to stop, RepAgent shuts down when it encounters a replicated command that exceeds Replication Server limits. The System Administrator must then restart RepAgent. The replicated command is not sent to Replication Server.
If the data limit filter mode is set to skip, the replicated command is not sent to Replication Server. However, RepAgent continues to send replicated commands that are within limits to Replication Server.
If the data limit filter mode is set to truncate, the replicated command is sent to Replication Server, but with these possible modifications:
RepAgent continues to send modified replicated commands and replicated commands that are within limits to Replication Server.
The Replication Server inbound queue
For Replication Server, a replicated command must fit within an inbound queue block of 16K. The inbound queue allows any combination of column count and length as long as the entire replicated command is less than 16K. A replicated command that cannot fit is discarded.
A replicated command stored in a Replication Server inbound queue requires more bytes of storage than the same replicated command stored in the Adaptive Server transaction log. See Appendix C, "RS inbound queue storage representation."
The storage requirements are sufficiently different that some replicated commands that fit in an ASE pre-12.5 transaction log do not fit in a 16K Replication Server queue block. Further, replicated update commands, which may be stored in two Adaptive Server transaction log records, require even more storage space in Replication Server's inbound queue.
The larger limits and page sizes supported by ASE 12.5 make it even more difficult for replicated commands to fit in a Replication Server inbound queue block.
The RepAgent data limits filter only partially alleviates storage problems at Replication Server's inbound queue since the filter considers only the replicated command's column and parameter count and column and parameter width. It does not consider the overall length of the replicated command. See the section "The RepAgent thread."
For example, the filter does not prevent the replication of a command with
200 columns where each column is 80 bytes wide (an overall replicated command length of 16,000 bytes). However, a replicated command of this size does not fit in a 16K inbound queue block.
Stored procedure execution
In ASE pre-12.5
ASE pre-12.5 writes a stored procedure's execution information (the stored procedure name and associated parameter list) into a single 2K transaction log record. If the execution information does not fit within the 2K log record, the transaction is rolled back and the stored procedure is not replicated. Any stored procedure with execution information that exceeds the Replication Server inbound queue block limit of 16K also exceeds Adaptive Server's 2K transaction log record limit. Thus, any stored procedure larger than 2K is always be rolled back and never copied to Replication Server.
An exception occurs if a stored procedure from ASE pre-12.5 does not have an associated replication definition. Without the associated replication definition, the execution information that is written to the inbound queue must include the stored procedure's parameter names. Thus, execution information that fits in an ASE pre-12.5 transaction log record may not fit in a 16K Replication Server inbound queue block because of the addition of the parameter names. Such a stored procedure executes successfully at ASE pre-12.5 but is discarded by the Replication Server.
In ASE 12.5
If ASE 12.5 can successfully execute a stored procedure, it uses as many transaction log records as necessary to write the execution information into the transaction log.
Thus,a stored procedure with execution information that exceeds the 16K Replication Server inbound queue block limit executes successfully at the Adaptive Server, but is discarded by Replication Server.
Replication database migration
To change the page size of an ASE pre-12.5 database, you must migrate the database to ASE 12.5 using bcp or a similar utility. ASE pre-12.5 does not have dump database images that are compatible with those of version 12.5.
Database copy utilities such as bcp do not copy metadata (database settings, table settings, and so on). When you copy database with utilities like bcp, certain replication settings set by system stored procedures such as sp_setreptable and sp_reptostandby are lost. However, you can manually copy all replication-related metadata.
Before you migrate replication databases to ASE 12.5:
When you migrate the database, any replicated transaction log information that has not been sent to the Replication Server is lost.
RepAgent replication status
If the replication database is configured to use a RepAgent thread, you must migrate the RepAgent configuration information. Execute steps 1 and 2 below on ASE pre-12.5 before migration.
Table replication status
Tables marked for replication in the ASE pre-12.5 database must be re-marked for replication in the ASE 12.5 database. To generate a list of each table marked for replication and each table's replication status, execute this command for each ASE pre-12.5 database:
Apply this information to the migrated database on the ASE 12.5 database.
textand image column replication status
text and image columns marked for replication in the ASE pre-12.5 database must be re-marked for replication in the ASE 12.5 database. To generate a list of each text and image column marked for replication and the replication status of each column, execute this command for each table marked for replication in the ASE pre-12.5 database:
Apply this information to the migrated ASE 12.5 database.
Stored procedure replication status
Stored procedures marked for replication in the ASE pre-12.5 database must be re-marked for replication in the ASE 12.5 database. To generate a list of each stored procedure marked for replication and its replication status, execute this command for each ASE pre-12.5 database:
Apply this information to the migrated ASE 12.5 database.
Database replication status
Databases marked for warm standby replication in ASE pre-12.5 must be re-marked for replication in the ASE 12.5 database. To retrieve this information, execute this command for each ASE pre-12.5 database:
Apply this information to the migrated database on ASE 12.5.
Generation ID information
You must also migrate the ASE pre-12.5 database generation ID information to ASE 12.5. To retrieve this information, execute this command for each ASE pre-12.5 database:
Apply this information to the migrated database on ASE 12.5.
Restarting the RepAgent thread
After you have migrated the replication metadata to the ASE 12.5 database, you must reconfigure and restart its RepAgent.
resume log transfer from Adaptive_Server_version_12,5_name.
Replication system database tables
When rs_init configures a database for replication or creates a
database for a new RSSD, these tables are created in the database (this is also true for primary databases):
To avoid contention, these tables must support row-level locking. Thus, rs_init creates the tables with extra "pad" columns to ensure that only one table row will fit on a data page.
When sizing the pad columns of these tables, rs_init always assumes a data page is 2K in length.
The sum of the pad and nonpad column lengths need not equal 2K. The objective is to prevent more than one table row from fitting on a data page; therefore, the sum of all the columns need only exceed half the data page size.
Larger database page size
If rs_init creates replication system database tables on an
ASE 12.5 database with a page size larger than 2K, these tables will not support row-level locking since the pad columns are no longer large enough to ensure that only one table row will fit on a data page. Therefore, the tables must be modified to again support row-level locking.
You can modify replication system database tables to support row-level locking by either:
Enabling Adaptive Server datarows locking support
Each replication system database table can be configured to use
ASE 12.5 datarows locking support. Enter:
alter table rs_lastcommit lock datarows
Do not remove pad columns since certain replication database stored
procedures (for example, rs_update_lastcommit and rs_initialize_threads) expect the pad columns to be there.
Increasing pad column lengths
In this method, you alter rs_lastcommit and rs_threadsize so that the number of pad columns again ensures that only one table row fits on a table data page.
To calculate the pad size:
padsize = max_page_size/2(num_pad_columns)
The maximum page size can be 4096, 8192, or 16832 bytes (4K, 8K, or 16K page sizes). The number of pad columns is 8 for the rs_lastcommit table and 4 for the rs_threadsize table. We divide by two because we need pad only half the page size.
For example, if the maximum page size is 4096 bytes, the padsize for rs_lastcommit is 4096/2(8) = 256.
For the rs_lastcommit table:
alter table rs_lastcommit modify pad1 char(padsize), pad2
For the rs_threadsize table:
alter table rs_threads modify pad1 char(padsize), pad2 char(padsize),
pad3 char(padsize), pad4 char(padsize)
A virtual-width table is created with variable-length columns such that the sum of all the minimum column lengths does not exceed the row length limit, but the sum of all the maximum column lengths does exceed the row length limit.
For example, a table containing 40 columns of varchar(255) is a
virtual-width table in an Adaptive Server database that uses a 2K page size:
(40 cols * char(1)) < 2048 byte row limit < (40 cols * char(255))
Adaptive Server permits the creation of virtual-width tables. When Adaptive Server attempts to insert into or update a virtual-width table row, the server performs a runtime check to ensure that the new row does not exceed the row length limit. If it does, the new row is not added to the table, and the associated transaction is rolled back.
The maximum row length of a virtual-width table on an ASE 12.5 database depends on the page size. For example, given a virtual-width table containing 40 columns of varchar(255), the maximum row length for the table is:
The calculation is based on this formula:
min((PageSize / ColumnCount), ColumnLength) * ColumnCount
Maximum replicated command length
The maximum replicated command length (when formatted for the Replication Server inbound queue) for a virtual-width table on an ASE 12.5 database also depends on the page size.
For example, given the same virtual-width table, the maximum length of a
replicated insert command (when formatted for the Replication Server inbound queue) for the virtual table is:
The calculation is based on this formula:
(IBQMetaDataLen + min((PageSize / ColumnCount), ColumnLength)) *
This calculation assumes a replication definition exists for the
virtual-width table and that IBQMetaDataLen is 40 bytes. For more
information, see "RS inbound queue" and Appendix C, "Replication Server inbound queue storage representation."
If the replicated command is an update command, the maximum column count is
Impact on the Replication Server inbound queue
If the same virtual-width table is created on or migrated to an ASE 12.5 database with a 2K or 4K page size, replicated update commands to the virtual-width table will always fit in a 16K Replication Server inbound queue block (assuming that a replication definition exists for the virtual-width table). However, if the same virtual-width table is created on or migrated to an ASE 12.5 database with a 8K or 16K page size, replicated update commands to the virtual-width table may not fit in a 16K Replication Server inbound queue block if the length of the replicated update command increases.
The likelihood of a replicated command that an ASE 12.5 virtual-width table will not fit in a 16K Replication Server inbound queue block increases as the ASE 12.5 database page size increases.
Appendix A: scenarios
Use this table to determine when an issue presented in this paper is applicable to a particular replication database.
Appendix B: related problems
1. CR # 264199Problem: Studio Installer may create a corrupt Open Client installation.
If Replication Server and Adaptive Server version 12.5 are installed in the same release directory, the Open Client installation may become corrupt, and rs_init cannot start.
Resolution: If you are installing Replication Server in the same release directory as Adaptive Server version 12.5, enter "no" when Studio Installer asks if existing language modules should be overwritten.
Optionally, you can install Replication Server and Adaptive Server version 12.5 in separate release directories.
2. CR# 233198 Problem: Adaptive Server version 12.5 includes the new reserved word "function."
Resolution: Edit existing Adaptive Server scripts so that all instances of the string "function" are enclosed in quotes.
sp_setrepproc rs_cmd_marker, 'function'
3. CR# 242859Problem: In Adaptive Server version 12.5, writetext operations on a table fail after a column is dropped using the alter table command. This error message displays:
Msg 7125, Level 16, State 3: Line 4:
Replication Server applies all text and image data using the writetext operation. writetext failure normally does not affect replicate databases because the alter table command is not copied to replicate databases. However, alter table is copied to warm standby databases.
A writetext failure can affect replicate databases if a column is dropped when alter table is executed manually.
Resolution: Drop and recreate both the primary and replicate tables.
4. CR# 264401 Problem: rs_init incorrectly creates the rs_lastcommit and rs_threads system tables when the page size is greater than 2KB.
rs_lastcommit and rs_threads must support row-level locking; they implement this support using pad columns. rs_init creates these tables with pad column widths that only support row-level locking when the page size is 2KB.
Resolution: After rs_init completes, manually alter all rs_lastcommit and rs_threads tables using this command:
alter table system_table_name lock datarows
After this command is entered, rs_lastcommit and rs_threads support row-level locking for all page sizes. Make sure that you enter this command for all rs_lastcommit and rs_threads tables in the replication environment, including primary databases, replicate databases, warm standby databases, and RSSDs.
5. CR# 261930Problem: rs_init incorrectly calculates the size of the RSSD if the RSSD is created on a master device with a page size larger than 2KB.
Resolution:Create the RSSD before running rs_init . Do not create an RSSD in rs_init ; when rs_init is invoked, check "no" when rs_init asks if you want to create the RSSD.
6. #CR 264696 Problem: Replication Server cannot properly replicate text and image data to tables that have the same name but different owners.
Resolution: Make sure that all replicate tables have unique names.
NOTE: In the following CRs, "oversized" refers to tables with columns that are too wide, with too many columns, and so on for Replication Server version 12.1 and earlier to handle.
7. #CR 258965 Problem: RepAgent thread generates incorrect LTL when it skips oversized log records.
When RepAgent is configured to skip transactions that are too large for Replication Server, RepAgent generates incomplete LTL for the skipped transaction log record. This causes a syntax error in Replication Server.
8. #CR 263211 Problem: RepAgent mishandles oversized log records after an LTL version change.
Changing the Replication Server site version also changes the LTL version. When Replication Server starts after a change of site version, RepAgent fails to change the default action for oversized transaction records to the new default appropriate for the new LTL version.
9. #CR 264489 Problem: RepAgent does not replicate the TRUNCATE TABLE command if the table is oversized.
RepAgent treats the TRUNCATE TABLE transaction log record as an oversized transaction log record when the associated table is oversized. RepAgent treats the TRUNCATE TABLE transaction log in the same way it treats oversized transaction log records.
10. #CR 264696 Problem: The ct_send_data routine fails due to incomplete CT_IODESC TEXT pointer information.
When used with Adaptive Server version 12.5, ct_send_data fails if the CT_IODESC TEXT pointer information returned from a prior call to ct_send_data() is incomplete. Incomplete information is returned if the named table is not in the current database or if multiple tables exist with the same name. In such a case, the name field of the CT_IODESC structure is not complete, and ct_send_data fails because the named table cannot be properly identified.