Switch to standard view 
  Sybase logo
 
 
 



Using Adaptive Server Enterprise version 12.5 with Replication Server version 12.1 or earlier: Schema-length and compatibility issues

Version 0.7 DRAFT

1/7/2002

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.

 

Summary

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.

 

Terminology

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:

  • Column count
  • Column length
  • Row length
  • Parameter count
  • Parameter length
  • Parameter list length

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:

  • Running a database created with an ASE pre-12.5 on an ASE 12.5, or
  • Moving a database from ASE pre-12.5 to ASE 12.5 with a database page size equal to 2K.

 

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:

  • Column count greater than 250 bytes
  • Column length greater than 255 bytes
  • Parameter length greater than 255 bytes

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.

 

RepAgent options

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:

  • stop
  • skip
  • truncate

To configure these options, log in to Adaptive Server and enter:

sp_config_rep_agent database_name, 'data limits filter mode',
'option_name'

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:

  • Database objects containing more than 250 columns are not sent.
  • Columns wider than 255 bytes are truncated to 255 bytes.
  • Parameters containing more than 250 parameters are not sent.
  • Parameters wider than 255 bytes are truncated to 255 bytes.

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:

  1. Stop all transaction activity on the database.
  2. Send all replicated transaction log information to the Replication Server.
  3. Shut down the Replication Server.

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.

  1. To retrieve a list of databases configured to use a RepAgent, execute these commands:
  2. use master

    sp_config_rep_agent

  3. To retrieve RepAgent configuration settings, execute this command for each database configured to use a RepAgent:
  4. sp_config_rep_agent database_name

  5. Apply this information to the migrated database on ASE 12.5.

 

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:

sp_setreptable

Apply this information to the migrated database on the ASE 12.5 database.

text and 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:

sp_setrepcol table_name

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:

sp_setrepproc

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:

sp_reptostandby database_name

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:

dbcc gettrunc

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.

  1. If the RSSD uses a RepAgent, reset the RSSD's locator value to zero using rs_zeroltm. In the RSSD of the Replication Server that the migrated replication database's RepAgent thread contacts, enter:
  2. rs_zeroltm Adaptive_Server_version_12,5_name,
    migrated_replication_database

  3. If the migrated replication database is configured to use RepAgent, the RepAgent must be restarted. Enter:
  4. use migrated_replication_database
    sp_start_rep_agent migrated_replication_database

  5. Notify Replication Server that the RepAgent has been started. Enter:

resume log transfer from Adaptive_Server_version_12,5_name.
migrated_replication_database

 

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):

rs_threads

rs_lastcommit

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, or
  • Increasing pad column lengths.

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
alter table rs_threads 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
char(padsize), pad3 char(padsize), pad4 char(padsize), pad5
char(padsize), pad6 char(padsize), pad7 char(padsize), pad8
char(padsize)

For the rs_threadsize table:

 

alter table rs_threads modify pad1 char(padsize), pad2 char(padsize),

pad3 char(padsize), pad4 char(padsize)

 

 

Virtual-width tables

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:

Page sizeMaximum row length
2K2,048 bytes
4K4,096 bytes
8K8,192 bytes
16K10,200 bytes

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:

Page sizeMaximum length of insert command
2K3,640 bytes
4K5,680 bytes
8K9,760 bytes
16K11,800 bytes

The calculation is based on this formula:

(IBQMetaDataLen + min((PageSize / ColumnCount), ColumnLength)) *

ColumnCount

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

doubled.

 

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.

 

 

Replication database characteristics

Schema length compatibility issue applicable?

ASE 12.5 installation

RS
database type

Database object schema limits

Page size

RepAgent thread

Inbound queue

Stored procedure

Replication database migration

Replication system database tables

Virtual-width tables

new

primary

<12.5

2KB

yes

yes

yes

     

new

primary

<12.5

>2KB

yes

yes

yes

 

yes

yes

new

primary

12.5

2KB

yes

yes

yes

     

new

primary

12.5

>2KB

yes

yes

yes

 

yes

yes

                   

new

replicate

<12.5

2KB

           

new

replicate

<12.5

>2KB

       

yes

 

new

replicate

12.5

2KB

           

new

replicate

12.5

>2KB

       

yes

 
                   

new

RSSD

<12.5

2KB

           

new

RSSD

<12.5

>2KB

       

yes

 

new

RSSD

12.5

2KB

           

new

RSSD

12.5

>2KB

       

yes

 
                   

upgrade

primary

<12.5

2KB

yes

yes

yes

     

upgrade

primary

12.5

2KB

yes

yes

yes

     
                   

upgrade

replicate

<12.5

2KB

           

upgrade

replicate

12.5

2KB

           
                   

upgrade

RSSD

<12.5

2KB

           

upgrade

RSSD

12.5

2KB

           
                   

migration

primary

<12.5

>2KB

yes

yes

yes

yes

yes

yes

migration

primary

12.5

>2KB

yes

yes

yes

yes

yes

yes

                   

migration

replicate

<12.5

>2KB

     

yes

yes

 

migration

replicate

12.5

>2KB

     

yes

yes

 
                   

migration

RSSD

<12.5

>2KB

     

yes

yes

 

migration

RSSD

12.5

>2KB

     

yes

yes

 

 

Appendix B: related problems

1. CR # 264199 Problem: 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.

For example:

sp_setrepproc rs_cmd_marker, 'function'

 

3. CR# 242859 Problem: 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:
Text pointer value conflicts with the column name specified.

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# 261930 Problem: 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.

 

 



Back to Top
© Copyright 2010, Sybase Inc.