pdb_setreptable

Returns replication marking status; marks all user tables or a specified table for replication; unmarks all marked tables or a specified table; or enables or disables replication for all marked tables or a specified table.

Syntax

For Oracle and Microsoft SQL Server:

pdb_setreptable
  [ { tablename[, [repname,] { mark [ { , immediate
                                        | , owner[, force] } ]
                               | unmark[, force]
                               | enable
                               | disable}]
    | all, { mark
           | unmark[, force]
           | enable
           | disable }
  } ]

For IBM DB2:

pdb_setreptable
  [ { tablename[, [repname,] { mark [ , owner[, force] ]
                               | unmark[, force]
                               | enable
                               | disable }]
    | all, { mark
           | unmark[, force]
           | enable
           | disable }
  } ]

To return replication marking status:

pdb_setreptable tablename

To mark all user tables (available only for Oracle and Microsoft SQL Server):

pdb_setreptable all, mark

To unmark, enable, or disable all marked tables:

pdb_setreptable all, {unmark[, force]|enable|disable}

To mark, unmark, enable, or disable a specified table:

pdb_setreptable tablename, {mark[, owner][, force] |
 unmark[, force] |enable|disable}

Note: Marking or unmarking all tables at once in the primary database using pdb_setreptable all, mark or pdb_setreptable all, unmark is not supported in Replication Agent for UDB. You must mark or unmark each table individually.

To mark a specified table for replication with a replicated name:

pdb_setreptable tablename, repname, mark[, owner][, force]

To mark a specified table for replication immediately for any occurrence, that may or may not be marked:

pdb_setreptable tablename, mark[, immediate]

To return a list of all marked tables:

pdb_setreptable

Parameters

tablename – The name of a valid user table in the primary database. Replication Agent returns complete marking information about the specified primary table. You cannot specify a system table, a view, or a Replication Agent transaction log table as a primary table.
The tablename parameter can be owner-qualified to include the primary table owner name, with each element separated by a period. For example:
```
owner.table
```
This parameter can be delimited with quote characters to specify the character case.
If mixed character case (both uppercase and lowercase) is required, the name must be delimited. For example:
```
"Owner".table
```
```
"Owner"."Table"
```
Each mixed case element of the tablename option must be delimited separately, as shown in the previous example.
If an object name contains any non-alphanumeric characters, such as spaces or periods, it must be delimited with quote characters. For example:
```
"table name"
```
```
owner."table name"
```
If an object name contains a period, it must be both owner-qualified and delimited with quote characters. For example:
```
owner."table.name"
```
```
"table.owner"."table.name"
```
repname – The name of the table specified in the replication definition for a primary table.
Note: The replicated name you specify with the pdb_setreptable command must match a table name specified by a with primary table named clause in a Replication Server replication definition for the primary database connection. Replication Agent cannot validate the replication definition, but if it does not exist, or if the with primary table named clause does not match the replicated name specified with pdb_setreptable, replication from the primary table will fail.
The repname option can be owner-qualified to include the replicate table owner name, with each element separated by a period. For example:
```
repowner.reptable
```
The repname option can also be delimited with quote characters to specify the character case. See the previous description of the tablename option for details.

Note: If the replicate table name contains a period (for example, table.name), without owner qualification, you must set the value of the Replication Agent use_rssd parameter to true.
all – A keyword that refers to all tables in the primary database. By using the all keyword, you can mark all user tables, or apply an unmark, enable, or disable operation to all marked tables.
mark – A keyword that refers to marking a table. Replication Agent returns a list of all marked tables in the primary database.
owner – A keyword that refers to the mark operation.
The owner keyword turns on the SEND OWNER mode. When you specify the owner of a table in a replication definition, you must always use the owner keyword if you want to enable the SEND OWNER mode.

owner mode sets a flag in the LTL telling Replication Server that any table level Replication definition must be owner qualified to match this table.

If the owner mode is set, the replication definition must be owner qualified. If the owner mode is not set, the replication definition must not be owner qualified.
unmark – A keyword that refers to unmarking a marked table. Replication Agent returns a list of all unmarked tables in the primary database.
force – A keyword used with the unmark operation or mark operation
Note: The combination of mark and force keywords with the pdb_setreptable command is only valid for UDB and Oracle.
- When the force keyword follows the unmark keyword, the pdb_setreptable command immediately removes replication marking for the specified table in the primary database, without first checking the enable status of the table. When the force keyword follows the unmark keyword and the all keyword, pdb_setreptable immediately removes replication marking from all marked tables in the primary database, regardless of their enable status.
  
  The force keyword also forces complete execution of the unmarking script, even if errors occur during the unmarking process. Normally, when errors occur during script execution, the script terminates immediately without completing. The force keyword can be useful when a previous script execution failed and left the unmarking operation incomplete.
  When errors occur during a forced script execution, the pdb_setreptable command returns this message:
```
Errors were encountered and ignored during FORCED script execution. See error log for details.
```
- For UDB and Oracle:
  
  When the force keyword follows the mark keyword, the pdb_setreptable command allows a table that contains one or more columns with unsupported datatypes to be marked for replication. No data for the unsupported columns is sent to Replication Server. As a result, any replicate table must have a suitable default value defined for the unsupported columns, since no data is received by the replicate database to be inserted into the unsupported columns.
  
  The force keyword cannot be used in combination with the all keyword. Tables with unsupported datatypes must be individually marked using the pdb_setreptable command and the force keyword (they will never be automatically marked, or marked by default if they have columns with unsupported datatypes).
  
  In addition, tables with unsupported datatypes are not automatically marked when the pdb_automark_tables configuration parameter is true. Tables with unsupported datatypes must be individually marked using the pdb_setreptable command and the mark and force keywords. For a list of supported and unsupported datatypes, see the Replication Agent Primary Database Guide.
  
  Note: If a replication definition is created using the command rs_create_repdef, for a table that was marked using the force keyword, only columns with supported datatypes are listed in the replication definition. Any column with an unsupported datatype is excluded from the replication definition.
enable – A keyword that refers to enabling replication for marked tables. Replication Agent returns a list of all marked tables in the primary database for which replication is enabled.
disable – A keyword that refers to disabling replication for marked tables. Replication Agent returns a list of all marked tables in the primary database for which replication is disabled.
immediate – A keyword that allows a table to be immediately marked for any occurrence. This keyword is for Microsoft SQL Server and Oracle only.

Examples

Example 1 –
```
pdb_setreptable authors
```
This command returns replication marking information for the table named “authors” in the primary database.
Example 2 –
```
pdb_setreptable mark
```
This command returns replication marking information for all marked tables in the primary database.
Example 3 –
```
pdb_setreptable disable
```
This command returns replication marking information for all marked tables for which replication has been disabled in the primary database.
Example 4 –
```
pdb_setreptable all, unmark, force
```
This command forces unmarking for all marked tables in the primary database.
Example 5 –
```
pdb_setreptable all, enable
```
This command enables replication for all marked tables in the primary database.
Example 6 –
```
pdb_setreptable authors, mark
```
This command marks for replication the table named “authors” in the primary database. The primary table name in the replication definition must be authors.
Example 7 –
```
pdb_setreptable authors, mark, owner
```
This command marks for replication the table named “authors” in the primary database so that the OWNER_MODE is enabled in the LTL. Therefore, any table replication definition created for this table must also be owner qualified.
Example 8 –
```
pdb_setreptable ptable, rtable, mark, owner
```
The primary table name in the replication definition must be:
```
powner.rtable
```
Example 9 –
```
pdb_setreptable ptable, rowner.rtable, mark,owner
```
The primary table name in the replication definition must be:
```
rowner.rtable
```
Example 10 –
```
pdb_setreptable ptable, rowner.rtable, mark,owner
```
The primary table name in the replication definition must be:
```
rowner.rtable
```
Example 11 –
```
pdb_setreptable authors, enable
```
This command enables replication for the marked table “authors” in the primary database.
Example 12 –
```
pdb_setreptable table=mark, enable
```
This command enables replication for the marked table named “mark” in the primary database.
Example 13 –
```
pdb_setreptable authors, unmark, force
```
This command forces unmarking for the marked table “authors” in the primary database.
Example 14 – (UDB and Oracle only)
```
pdb_setreptable authors, mark, force
```
This command forces table authors” to be marked, even if it contains columns with unsupported datatypes. The columns with unsupported datatypes will not be replicated.

Usage

Using No Options

When pdb_setreptable is invoked with no option, Replication Agent returns a list of all marked tables in the primary database.
Tables marked for replication are listed in the marked objects table. All other user tables are considered unmarked.

Note: The Replication Agent transaction log tables and shadow tables are not included in the list of unmarked tables. Also not included are any synonyms, views, or aliases of these database objects.

For tables listed as unmarked or disabled, transactions will not be captured for replication.

Marking Tables

When a table is marked for replication and the owner mode is set to on, the replication definition must contain the owner name in the “with primary table named” clause, or the with all tables named clause. If the owner mode setting and the existence of the owner name in the replication definition do not match, the replication definition is not used.
For example:
- Issuing pdb_setreptable with the owner mode set to on:
```
pdb_setreptable “mytable”, mark, owner
```
  causes the rs_create_repdef command to generate this replication definition for the primary and replicate database, which Replication Server expects to receive:
```
create replication definition ra$0xda_”mytable”
```
```
with primary at ora102.dco
with primary table named “qa4user”.”mytable”
with replicate table named “qa4user”.”mytable”
.
.
.
```
- Issuing pdb_setreptable with the owner mode set to off:
```
pdb_setreptable “mytable”, mark
```
  causes the rs_create_repdef command to generate this replication definition for the primary and replicate database, which Replication Server expects to receive:
```
create replication definition ra$0xda_”mytable”
```
```
with primary at ora102.dco
with primary table named ”mytable”
```
```
with replicate table named “qa4user”.”mytable”
.
.
.
```
When a marked table is renamed or dropped and a new table with the original name is created, you must explicitly mark the new table because the new table has no marking-related information from the original table. The marking information is maintained internally by object ID, not table name.
If you create a new table using a table name that was previously marked you must mark the new table by executing the pdb_setreptable command with the mark option, even if you did not unmark the previous table.
(UDB only) If the mark script fails, it is stored in a file (mark.sql) in the RAX-15_5\inst_name\scripts\tablename directory, the table is not marked, and transaction log objects are not updated. You can examine the script by viewing the mark.sql file.

Unmarking Tables

When you use the unmark keyword to remove replication marking from a primary table, Replication Agent verifies that replication is disabled for that table and checks to make sure that there are no pending (unprocessed) operations for that table in the transaction log. If replication is not disabled, or there is a pending operation for that table in the transaction log, pdb_setreptable returns an error.
When you use the unmark keyword to remove replication marking from primary tables, you can also specify the force keyword to immediately remove replication marking from primary tables, without regard to whether replication is disabled.
(UDB only) If the unmark script fails, it is stored in a file (unmark.sql) in the RAX-15_5\inst_name\tablename\scripts directory, the table is not unmarked, and the transaction log objects are not removed. You can examine the script by viewing the unmark.sql file. When the unmark script execution encounters a fatal error on any database object, the pdb_setreptable command returns this message:
```
Could not unmark the following objects: ... 
See error log for details.
```

Keywords

When multiple keywords and options are specified, each must be separated by a comma. Blank space before or after a comma is optional. For example:
```
pdb_setreptable all, unmark, force
```
If a table name in the primary database is the same as a keyword, it can be identified by adding the table=string to the beginning of the name. For example:
```
pdb_setreptable table=unmark, mark
```
This is true for both primary table names and replicated names.

Action Keywords

When pdb_setreptable is invoked with a valid user table name, followed by an action keyword (mark, unmark, enable, or disable), the action specified is applied to the specified table.
If pdb_setreptable is invoked with a table name and the mark keyword, it marks the specified table in the primary database for replication.

Note: When an individual table is marked, the owner filter list is not checked. This allows users to mark a table that has an owner in the owner filter list.
If pdb_setreptable is invoked with a table name and the unmark keyword, it removes replication marking from the specified table in the primary database.

You can specify the force keyword after the unmark keyword to force immediate unmarking of the specified table, to unmark a table for which replication is still enabled, or to force the script execution to ignore errors and continue an unmarking operation that failed previously.
If pdb_setreptable is invoked with a table name and the enable keyword, it enables replication for the specified marked table in the primary database.
If pdb_setreptable is invoked with a table name and the disable keyword, it disables replication for the specified marked table in the primary database.
If the disable script execution encounters a fatal error on any database object, the pdb_setreptable command returns this message:
```
Could not disable the following objects: ... 
See error log for details.
```

The all Keyword

When pdb_setreptable is invoked with the all keyword and an action keyword (mark, unmark, enable, or disable), the action specified is applied to either all tables in the primary database, or all marked tables in the primary database.
If pdb_setreptable is invoked with the all and mark keywords, all user tables in the primary database are marked for replication.

Note: Tables owned by users contained in the owner filter list will not be marked. However, you will be able to mark any individual table.
If pdb_setreptable is invoked with the all and unmark keywords, it removes replication marking from all marked tables in the primary database.

You can specify the force keyword after the unmark keyword to force immediate unmarking of all marked tables, or to unmark tables for which replication is still enabled, or to force the script execution to ignore errors and continue an unmarking operation that failed previously.
If pdb_setreptable is invoked with the all and enable keywords, it enables replication for all marked tables in the primary database.
If pdb_setreptable is invoked with the all and disable keywords, it disables replication for all marked tables in the primary database.

Replication Definition Types

How you use the pdb_setreptable command depends on the type of replication definition that you have created at Replication Server. If you have created a database replication definition with no table replication definition, then the replicate procedure in the pdb_setreptable command refers to the table in the replicate database. However, if you have created a table replication definition, then the replicate table in the pdb_setreptable command refers to the name of the table replication definition, and it is the table replication definition that must map to the table in the replicate database.
If no table replication definition exists and will not be added prior to replication, but only a database replication definition exists, use these commands to mark a table for replication.
- When the table in the replicate database has the same name as the table in the primary database, use:
```
pdb_setreptable pdb_table, mark
```
  where pdb_table is the name of the table in the primary database that you want to mark for replication.
- When the table in the replicate database has the different name than the table in the primary database, use:
```
pdb_setreptable pdb_table, rep_table, mark
```
  where rep_table is the name of the table in the replicate database.
If a table replication definition exists or will be added prior to replication, regardless of whether or not a database replication definition exists, use these commands to mark a table for replication:
- When the primary table in the table definition has the same name as the table in the primary database:
```
pdb_setreptable pdb_table, mark
```
  If the table in the replicate database also has the same name as the table replication definition, then you can use the with all tables named clause in the replication definition in the primary Replication Server. For example:
```
create replication definition my_table_repdef with primary at data_server.database
with all tables named pdb_table …
```
  If the table in the replicate database has a different name than the primary table in the table replication definition, then the table replication definition must map to the table in the replicate database. For example:
```
create replication definition my_table_repdef with primary at data_server.database
with primary table named pdb_table 
with replicate table name rep_table …
```
- When the name of the table replication definition is different than the table in the primary database, use:
```
pdb_setreptable pdb_table, rdpri_table, mark
```
  where rdpri_table is the name of the primary table in the replication definition.
  If the table in the replicate database also has the same name as the primary table in the table replication definition, then you can use the with all tables named clause in the replication definition in the primary Replication Server. For example:
```
create replication definition my_table_repdef 
with primary at data_server.database 
with all tables named rdpri_table …
```
  If the table in the replicate database has a different name from the primary table in the table replication definition, then the table replication definition must map to the table in the replicate database. For example:
```
create replication definition my_table_repdef 
with primary at data_server.database
```
```
with primary table named rdpri_table
with replicate table name rep_table …
```

Replicated Names and the owner Keyword

When pdb_setreptable is invoked with a primary table name and a replicated name, followed by the mark keyword, the primary table is marked for replication with the specified replicated name.

By specifying a replicated name, transactions can be replicated to a table in the replicate database that has a different name from the primary table.

Note: The replicated name you specify with the pdb_setreptable command must match a table name specified by a with all tables named clause in a Replication Server replication definition for the primary database connection. Replication Agent cannot validate the replication definition, but if it does not exist, or if the with all tables named clause does not match the replicated name specified with pdb_setreptable, replication from the primary table will fail.
You can also specify the owner keyword after the mark keyword so that when operations against the primary table are replicated, the primary table owner name will be attached to the replicate table name in the form owner.tablename.

Note: If you want to use an owner-qualified replicate table name with the replicate owner’s name, use the owner keyword with the pdb_setreptable command. If you specify an unqualified replicate table name, the primary table owner name is sent with the replicate table name in the LTL.

Unsupported Datatypes

(UDB and Oracle) If a table contains a column with a datatype that is not supported for replication, the pdb_setreptable command using the mark keyword may fail with an error similar to:
```
Command <pdb_setreptable> failed - Table <MYTABLE> could not be marked because:The table contains an 
unsupported data type.
```
To force the table to be marked, excluding the unsupported datatype columns from replication, add the force keyword to the pdb_setreptable command.

Errors

If the Replication Agent transaction log does not exist in the primary database (UDB) or the RASD is not initialized (Oracle and Microsoft SQL Server), the pdb_setreptable command returns an error.
If the table name you specify does not exist in the primary database, the pdb_setreptable command returns an error.
If the primary table name you specify does not exist in the primary database, the pdb_setreptable command returns an error.
If the enable script execution encounters a fatal error on any database object, the pdb_setreptable command returns this message:
```
Could not enable the following objects: ... 
See error log for details.
```

Spaces in column names

To replicate a table that contains column names that have spaces, you must set structured_tokens to true.

Aliases

If you specify an alias or synonym as a primary table in the pdb_setreptable command, the actual table that the alias or synonym refers to is acted upon. The actual table name is the table name sent to the primary Replication Server.