Returns stored procedure replication marking status; unmarks all marked stored procedures or a specified stored procedure; enables or disables replication for all marked stored procedures or a specified stored procedure; marks a specified stored procedure for replication.
Sybase Replication Agent does not support procedure replication for 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 the stored procedure in the primary database.
This parameter can be delimited with quote characters to specify the character case.
If mixed case (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 name of the stored procedure specified in the function replication definition for a primary stored procedure. This parameter can be delimited with quote characters to specify character case. See the previous description of the procname parameter for specifics.
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 a stored procedure name specified by a deliver
as proc_name clause in a function replication
definition for the primary database connection. The Replication
Agent pdb_setrepproc command cannot
verify the existence of a valid function replication definition,
but if it does not exist, function replication from the primary
table will fail.
A keyword that refers to all marked stored procedures in the primary database. By using the all keyword, you can apply an unmark, enable, or disable operation to all marked stored procedures.
A keyword that refers to marking stored procedures for replication.
A keyword that refers to unmarking marked stored procedures.
A keyword that refers to the unmark operation.
When the force keyword follows the unmark keyword, the pdb_setrepproc command immediately removes replication marking for 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 any 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 stored procedure named authors in the primary database.
pdb_setrepproc authors, mark
This command marks the stored procedure named authors in the primary database.
pdb_setrepproc authors, enable
This command enables replication for the stored procedure named authors in the primary database.
pdb_setrepproc all, unmark
This command unmarks all marked stored procedures in the primary database.
When pdb_setrepproc is invoked, its function is determined by the keywords and options you specify.
When multiple keywords and/or options are specified, each must be separated by a comma. Blank space before or after the comma(s) 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 stored procedure.
You cannot specify the following items as a stored procedure name in the pdb_setrepproc command:
Primary database system procedures
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 Replication Agent drop then re-create the procedure. Ownership of the re-created procedure belongs to the user specified in the Replication Agent pds_username parameter. However, Replication Agent sets all the same privileges on the re-created procedure as were defined on the original procedure.
Do not remove or alter the Replication Agent comments
in a marked stored procedure.
When pdb_setrepproc is invoked to mark a procedure for replication, Replication Agent does the following:
Modifies the user procedure to add code that captures input parameter values and generates Replication Agent transaction log records.
Generates a SQL script that creates the tables and procedures required for the Replication Agent transaction log in the primary database.
Saves the generated script in a file called mark.sql in the rax-12_5\inst_name\scripts\procname directory, where inst_name is the name of the 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. When the value of
the pdb_auto_run_scripts configuration
parameter is false, you must manually execute
the procedure-marking scripts.
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 mark.sql file to the rax-12_5\inst_name\scripts\procname\installed directory.
If the mark script fails, it is stored in a file (mark.sql) in the rax-12_5\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, Replication Agent does the following:
Modifies the user procedure to remove 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-12_5\inst_name\scripts\procname directory, where inst_name is the name of the Replication Agent instance and procname is the name of the stored procedure being unmarked.
If the value of the pdb_auto_run_scripts configuration
parameter is false, the unmark.sql script
will be saved but not executed automatically. When the value of
the pdb_auto_run_scripts configuration
parameter is false, you must manually execute
the procedure-unmarking scripts.
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-12_5\inst_name\scripts\procname\installed directory.
If the unmark script fails, it is stored in a file (unmark.sql) in the rax-12_5\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 pdb_setrepproc command verifies that replication is disabled for that stored procedure and checks to make sure that there are no pending (unprocessed) operations for that stored procedure in the transaction log. If replication is not disabled or there is a pending operation for that stored procedure in the transaction log, pdb_setrepproc returns an error.
When pdb_setrepproc is invoked with either no argument or a single argument, it returns marking information about the stored procedures in the primary database.
If pdb_setrepproc is invoked with no argument, it returns a list of all marked procedures in the primary database.
Invoking the pdb_setrepproc command
with no argument produces the same result as invoking the pdb_setrepproc command with
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 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 disabled.
Stored procedures marked for replication are listed in the marked objects table. All other user procedures are considered unmarked.
The 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, the operation specified by the following keyword (unmark, enable, or disable) is applied to all marked procedures in the primary database.
If pdb_setrepproc is invoked with the all keyword and the unmark keyword, it removes replication marking from all marked procedures in the primary database. You can also specify the force keyword after the unmark keyword to force immediate unmarking of all marked procedures, or to unmark procedures for which replication is still enabled or pending operations remain in the transaction log.
If pdb_setrepproc is invoked with the all keyword and the enable keyword, it enables replication for all marked procedures in the primary database.
If pdb_setrepproc is invoked with the all keyword and the disable keyword, it disables replication for all marked procedures in the primary database.
When pdb_setrepproc is invoked with a valid procedure name followed by one or more keywords, the operation specified by the keyword (mark, unmark, enable, or disable) 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 also specify the force keyword after the unmark keyword to force immediate unmarking of the specified procedure, to unmark a procedure for which replication is still enabled or pending operations remain in the transaction log, or to 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 the procedure name you specify 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 a procedure name specified by a deliver as clause
in a function replication definition for the primary database connection.
The Replication Agent pdb_setrepproc command
cannot verify the existence of a valid replication definition, but
if it does not exist, replication of the primary procedure invocation
will fail.
If the Replication Agent transaction log does not exist in the primary database, the pdb_setrepproc command returns an error.
pdb_xlog, pdb_setrepcol, pdb_setreptable, ra_config
