Returns stored procedure replication marking status; unmarks all marked procedures or a specified procedure; enables or disables replication for all marked procedures or a specified procedure.
Sybase Replication Agent does not support procedure replication from DB2
Universal Database.
To return stored procedure replication marking status:
pdb_setrepproc [procname|mark|unmark|enable|disable]
To unmark, enable, or disable all marked stored procedures:
pdb_setrepproc all, {unmark[, force]|enable|disable}
To mark, unmark, enable, or disable a specified stored procedure:
pdb_setrepproc procname, {mark|unmark[, force]|enable|disable}
The name of a user stored procedure in the primary database.
The procname option 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:
"Proc"
If you must use an object name case that does not match
the setting of the ltl_character_case parameter,
the object name must be delimited.
If an object name contains any non-alphanumeric characters, such as spaces, periods, and so forth, it must be delimited with quote characters. For example:
"proc name"
"proc.name"
If an object name contains a period, it must be both owner-qualified and delimited with quote characters. For example:
owner."proc.name"
"proc.owner"."proc.name"
The replicated name of the stored procedure specified in a function replication definition for the primary stored procedure.
The repname option can be delimited with quote characters to specify character case. See the previous description of the procname option for details.
By specifying a replicated name, stored procedure invocations can be replicated to a stored procedure invocation in the replicate database that has a different stored procedure name from the primary database.
The replicated name you specify with the pdb_setrepproc command
must match the name of a Replication Server function replication
definition for the primary database connection. The Sybase Replication Agent cannot
validate the function replication definition, but if it does not
exist, function replication from the primary database will fail.
A keyword that refers to all user stored procedures in the primary database. By using the all keyword, you can mark all user stored procedures, or apply an unmark, enable, or disable operation to all marked stored procedures.
A keyword that refers to marking user stored procedures for replication.
A keyword that refers to unmarking marked stored procedures.
For trigger-based instances for Microsoft SQL, Sybase Replication Agent must
be in Admin state in order to unmark.
A keyword that refers to the unmark operation.
When the force keyword follows the unmark keyword, the pdb_setrepproc command immediately unmarks the specified stored procedure in the primary database, without first checking the enable status of the stored procedure or checking for pending operations in the transaction log. When the force keyword follows the unmark keyword and the all keyword, the pdb_setrepproc command immediately removes replication marking from all marked stored procedures in the primary database, regardless of their enable status or pending operations in the transaction log.
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_setrepproc command returns the following message:
Errors were encountered and ignored during FORCED script execution. See error log for details.
A keyword that refers to enabling replication for marked stored procedures.
A keyword that refers to disabling replication for marked stored procedures.
pdb_setrepproc
This command returns replication marking information for all marked stored procedures in the primary database.
pdb_setrepproc authors
This command returns replication marking information for the user stored procedure named “authors” in the primary database.
pdb_setrepproc authors, mark
This command marks the user stored procedure named “authors” in the primary database.
pdb_setrepproc authors, enable
This command enables replication for the marked stored procedure named “authors” in the primary database.
pdb_setrepproc all, unmark
This command unmarks all marked stored procedures in the primary database.
For Oracle, you must disable DDL replication before marking or unmarking a procedure, and re-enable it after marking or unmarking.
When pdb_setrepproc is invoked, its function is determined by the keywords and options you specify.
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_setrepproc all, unmark, force
When you specify a stored procedure name in the pdb_setrepproc command, you must use the name of a valid user stored procedure.
You cannot specify the following items as a stored procedure name in the pdb_setrepproc command:
System procedures
Sybase Replication Agent transaction log procedures
If a stored procedure name in the primary database
is the same as a keyword, it can be identified by adding the string proc= to
the beginning of the stored procedure name. For example:
pdb_setrepproc proc=unmark, mark
Marking and unmarking a stored procedure for replication requires that the Sybase Replication Agent drop, and then re-create the procedure. However, Sybase Replication Agent sets all the same privileges on the re-created procedure as those defined on the original procedure.
Do not remove or alter the Sybase Replication Agent comments
in a marked stored procedure.
When pdb_setrepproc is invoked to mark a procedure for replication, Sybase Replication Agent does the following:
Modifies the user procedure to add code that captures input parameter values and generates Sybase Replication Agent transaction log records.
Generates a SQL script that creates the procedures required for the Sybase Replication Agent transaction log in the primary database.
Saves the generated script in a file called mark.sql in the RAX-15_0\inst_name\scripts\procname directory, where inst_name is the name of the Sybase Replication Agent instance, and procname is the name of the stored procedure being marked.
If the value of the pdb_auto_run_scripts configuration
parameter is false, the mark.sql script
will be saved but not executed automatically. To manually re-run
the script, you must first set pdb_auto_run_scripts to true and
then re-run the command.
Executes the script to mark the stored procedure and create the transaction log objects in the primary database (if the value of the pdb_auto_run_scripts configuration parameter is true).
After the script completes successfully, moves the partmark.sql file to the RAX-15_0\inst_name\scripts\procname\installed directory.
If the mark script fails, it is stored in a file (partmark.sql) in the RAX-15_0\inst_name\scripts\procname directory, the stored procedure is not marked, and transaction log objects are not created. You can examine the script by viewing the mark.sql file.
When pdb_setrepproc is invoked to unmark a marked stored procedure, Sybase Replication Agent does the following:
Modifies the user procedure to remove Sybase Replication Agent code that captures input parameter values and generates transaction log records.
Generates a SQL script that removes the tables and procedures required for the transaction log in the primary database.
Saves the generated script in a file called unmark.sql in the RAX-15_0\inst_name\scripts\procname directory, where inst_name is the name of the Sybase Replication Agent instance and procname is the name of the stored procedure being unmarked. For Oracle, the script is named partmark.sql because it can not be manually executed—it is for informational purposes only.
If the value of the pdb_auto_run_scripts configuration
parameter is false, the partmark.sql script
will be saved but not executed automatically. To manually re-run
the script, you must first set pdb_auto_run_scripts to true and
then re-run the command.
Executes the script to unmark the stored procedure and remove the transaction log objects in the primary database (if the value of the pdb_auto_run_scripts configuration parameter is true).
After the script completes successfully, moves the unmark.sql file to the RAX-15_0\inst_name\scripts\procname\installed directory.
For Oracle, the file is called partunmark.sql.
If the unmark script fails, it is stored in a file (unmark.sql) in the RAX-15_0\inst_name\procname\scripts directory and the stored procedure 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_setrepproc command returns the following message:
Could not unmark the following objects: ... See error log for details.
When you use the unmark keyword to remove replication marking from a stored procedure, the Sybase Replication Agent verifies that replication is disabled for that stored procedure and there are no pending (unprocessed) operations for that stored procedure in the transaction log. If replication is not disabled for that procedure, or if there is a pending operation for that procedure in the transaction log, pdb_setrepproc returns an error.
When pdb_setrepproc is invoked with either no option or a single option, it returns marking information about the stored procedures in the primary database.
If pdb_setrepproc is invoked with no option, it returns a list of all marked procedures in the primary database.
Invoking the pdb_setrepproc command
with no option produces the same result as invoking the pdb_setrepproc command
with only the mark keyword.
If pdb_setrepproc is invoked with a procedure name, it returns complete marking information about the specified procedure.
If pdb_setrepproc is invoked with the mark keyword, it returns a list of all marked procedures in the primary database.
If pdb_setrepproc is invoked with the unmark keyword, it returns a list of all unmarked procedures in the primary database.
If pdb_setrepproc is invoked with the enable keyword, it returns a list of all marked procedures in the primary database, for which replication is currently enabled.
If pdb_setrepproc is invoked with the disable keyword, it returns a list of all marked procedures in the primary database, for which replication is currently disabled.
Stored procedures marked for replication are listed in the marked objects table. For Oracle, the marking information is recorded in the RASD. All other user procedures are considered unmarked.
The Sybase Replication Agent system procedures are not included in
the list of unmarked procedures. Also not included are any synonyms
or aliases of these procedures.
For procedures listed as unmarked or disabled, their invocations will not be captured for replication.
When pdb_setrepproc is invoked with the all keyword and an action keyword (unmark, enable, or disable), the action specified is applied to either all user stored procedures in the primary database, or to all marked procedures in the primary database.
If pdb_setrepproc is invoked with the all and unmark keywords, it removes replication marking from all marked procedures in the primary database.
You can specify the force keyword after the unmark keyword to force immediate unmarking of all marked procedures, including procedures for which replication is still enabled or pending operations remain in the transaction log.
If pdb_setrepproc is invoked with the all and enable keywords, it enables replication for all marked procedures in the primary database.
If pdb_setrepproc is invoked with the all and disable keywords, it disables replication for all marked procedures in the primary database.
When pdb_setrepproc is invoked with a valid user stored procedure name and followed by an action keyword (mark, unmark, enable, or disable), the action specified is applied to the specified procedure.
If pdb_setrepproc is invoked with a procedure name and the mark keyword, it marks the specified procedure in the primary database for replication.
If pdb_setrepproc is invoked with a procedure name and the unmark keyword, it removes replication marking from the specified procedure in the primary database.
You can specify the force keyword after the unmark keyword to force immediate unmarking of the specified procedure, unmark a procedure for which replication is still enabled or pending operations remain in the transaction log, or force the script execution to ignore errors and continue an unmarking operation that failed previously.
If the unmark script execution encounters a fatal error on any database object, the pdb_setrepproc command returns the following message:
Could not unmark the following objects: ... See error log for details.
If pdb_setrepproc is invoked with a procedure name and the enable keyword, it enables replication for the specified marked procedure in the primary database.
If the enable script execution encounters a fatal error on any database object, the pdb_setrepproc command returns the following message:
Could not enable the following objects: ... See error log for details.
If pdb_setrepproc is invoked with a procedure name and the disable keyword, it disables replication for the specified marked procedure in the primary database.
If the disable script execution encounters a fatal error on any database object, the pdb_setrepproc command returns the following message:
Could not disable the following objects: ... See error log for details.
If you specify a stored procedure name that does not exist in the primary database, the pdb_setrepproc command returns an error.
When pdb_setrepproc is invoked with a procedure name and a replicated name, followed by the mark keyword, the primary procedure is marked for replication with the specified replicated name.
If the primary procedure name you specify does not exist in the primary database, the pdb_setrepproc command returns an error.
By specifying a replicated name, procedure invocations can be replicated to a procedure in the replicate database that has a different name from the primary procedure.
The replicated name you specify with the pdb_setrepproc command must
match the name of a Replication Server function replication definition
for the primary database connection. The Sybase Replication Agent cannot validate
the function replication definition, but if it does not exist, function
replication from the primary database will fail.
If the Sybase Replication Agent transaction log does not exist in the primary database (DB2 UDB, or Microsoft SQL Server) or the RASD is not initialized (Oracle), the pdb_setrepproc command returns an error.
