/ 
Sybase-RepAgent-0.03
/ Sybase::RepAgent

NAME

Sybase::RepAgent - Perl extension for building a Sybase Replication Agent which talks to a Sybase Replication Server

SYNOPSIS

use Sybase::RepAgent;
my $ra = Sybase::RepAgent->new($repserver, 
                               $user, 
                               password, 
                               $dataserver, 
                               $database, 
                               $ltl_version);

$ra->distribute(\%command_tags, $subcommand);
$ra->begin_tran();
$ra->commit_tran();
$ra->rollback_tran();
$ra->insert();
$ra->update();
$ra->delete();

my $mu = $ra->maintenance_user;
my $tp = $ra->truncation_pointer;
my $lv = $ra->ltl_version;
my $sv = $ra->system_version;
my $ul = $ra->upgrade_locator;
my $last_oqid = $ra->last_oqid;
my $last_tran_id = $ra->last_tran_id;

DESCRIPTION

Sybase Replication Server is a mighty tool for data distribution, mirroring, warm stand by and a lot more. RepServer gets the data to distribut from a Replication Agent, which is built into the Sybase database server. RepAgents exit for all major databases and the language which is used by RepAgent to RepServer is described in the Replication Server Design Guide at the Sybase web site.

This module is just a wrapper around this language which allows you to roll your own RepAgent. You can use it to enable replication in a database which is not supported by Sybase (e.g. MySQL and PostgreSQL, which both support Perl-Procedures by now). Or you can use it to feed data into RepServer, which will do the distribution, error handling and all that stuff.

For setting up and using a replication with Sybase::RepAgent see the RepAgent cookbook (cookbook.pm).

This is my own work. Sybase Inc. is in no way involved and does NOT support this module.

METHODS

new() - The Constructor

Parameters:

Replicationserver

Name of the Replication Server to which the RepAgent shall connect.

User

Login used in the connection.

Password

Password used to connect.

Source Dataserver

RepServer expects a source from which the data comes. This is specified in the Replication Definitions as DATASERVER.DATABASE. This parameter is the DATASERVER part.

Source Database

The DATABASE part

LTL Version (optional, default 200)

The Version of the Log Transfer Language to use. Use 100-103 to communicate with RepServer Version 10.0.x - 11.0. Those shouldn't be running anyway. Use 200 for version 11.5 and later.

Returnvalue:

The constructor returns a RepAgent-object if the connect to the RepServer succeeds, otherwise it returns undef.

Example:

my $ra = Sybase::RepAgent->new($repserver,
                               $user,
                               $password,
                               $dataserver,
                               $database,
                               $ltl_version);

distribute() - send a command to the repserver

Parameters:

\%command_tags

A reference to a hash containing the command_tags for the subcommand.

Keys in the hash can be:

origin_time (date_time value, optional)

The origin_time parameter is a datetime value that specifies the time when the transaction or data manipulation operation occurred. It is used to report errors. origin_time is used only with the transaction control subcommands: begin transaction, commit transaction, and rollback transaction.

origin_qid (32-byte binary)

The origin_qid parameter is a 32-byte binary value that uniquely identifies the command in the log. It is a sequence number used by Replication Server to reject duplicate commands after a RepAgent connection has been reestablished.

tran_id (120-byte binary)

The tran_id parameter is a 120-byte binary value that identifies the transaction the command belongs to. The transaction ID must be globally unique. One way to guarantee this is to first construct a unique transaction ID for the database log, and then attach the data server name and database name to it.

mode (binary 0x08, optional)

The mode parameter is set if the owner name is to be used when Replication Server looks up replication definitions. This parameter is optional for applied commands. It should not be set if the owner name is unavailable.

mode is an LTL version 200 parameter; it is available with Replication Server version 11.5 or later.

standby_only (1 or 0, optional)

The standby_only parameter determines whether the command is sent to the standby and/or replicate databases. If standby_only is set to 1, the command is sent to the standby database and not to the replicate database. If standby_only is set to 0, the command is sent to the standby and replicate databases.

standby_only is an LTL version 200 parameter and is available with Replication Server version 11.5 or later. It is optional for applied commands.

$subcommand

The command that the repserver will execute. (for a more detailed description see Repserver Design Guide)

One of the following:

begin transaction
commit transaction
rollback transaction
rollback
applied
execute
begin transaction

Starts a transaction.

commit transaction

Commits the transaction.

rollback transaction

Rolls back the transaction.

rollback [from oqid] to] oqid

The rollback subcommand, without the transaction keyword, requires specification of origin queue ID (oqid) values. The three possible forms of this subcommand are:

rollback oqid

rolls back a single log record corresponding to the specified origin queue ID. This option supports the mini-rollback capability in DB2.

rollback to oqid

rolls back all log records between the specified origin queue ID and the current log record.

rollback from oqid1 to oqid2

rolls back a sequence of log records whose origin queue IDs fall in the specified range.

applied

The applied subcommand describes operations recorded in the database, including:

row inserts
row updates
row deletes
execution of applied stored procedures
manipulation of text and image columns

Syntax:

distribute command_tags applied [owner=owner_name]
 {'table'.rs_update
      yielding before param_list after param_list |
 'table'rs_insert  yielding after param_list |
 'table'.rs_delete  yielding before param_list |
 'table'.function_name [param_list]
      yielding after param_list before param_list |
 'table'.rs_datarow_for_writetext
      yielding datarow column_list |
 'table'.rs_writetext
      append [first] [last] [changed] [with  log]
     [textlen=100] column_list}

table is the name of the database table to which the operation was applied. It must be enclosed in quotation marks.

Replication Server uses table to associate the command with a replication definition. Beginning with Replication Server version 11.5 and version 200 LTL, if the tag @mode=0x08 is set, Replication Server also associates the owner name with the replication definition. The create replication definition command's with all tables named table_identifier clause determines how table is mapped to a replication definition:

If the replication definition has a with all tables named table_identifier or with primary table named table_identifier clause, table above is matched to the table_identifier or with the primary table named.

If the with all tables named table_identifier clause and the with primary table named table_identifier clauses were omitted, then table above is the name of the replication definition.

RepAgent does not need to be aware of replication definitions. It can use the table name on the data source.

yielding clause

For rs_update, rs_insert, and rs_delete, the yielding clause introduces before and after images of the row affected by the operation. Depending on the operation, the before image, the after image, or both, must be provided. Applied subcommand before and after images:

Operation    Before Image    After Image

rs_update         Yes            Yes

rs_insert         ---            Yes

rs_delete         Yes            ---

The table.function_name form of the applied subcommand is used to distribute replicated stored procedures when you use the method associated with table replication definitions.

Before and after images are specified by a param_list, which is a list of column or parameter values. The syntax for param_list is:

[@param_name=]literal[, [@param_name=]literal]...

param_name is a column name or, 
     for replicated stored procedures, a parameter name.

literal is the value of the column or parameter.

All column names in the replication definition must appear in the list. Replication Server ignores any additional columns. Column or parameter names can be omitted if the values are supplied in the same sequence as they are defined in the replication definition. If the column names are included, you can list them in any order, although there is a performance advantage if the columns are supplied in replication definition order.

Replication Server version 10.1 and later supports an optimized yielding clause. An after image value can be omitted if it is the same as the before image value. For example, if a table has three columns a, b, and c, for an update where only column b changes, the yielding clause could be:

yielding before @a=5, @b=10, @c=15 after @b=12

If the minimal columns feature is used, a RepAgent using LTL version 101 or later must omit identical after images.

execute

The execute subcommand is used to send a replicated function or stored procedure call to another Replication Server. This subcommand is used with the preferred method for distributing stored procedures -- applied and request functions -- and with the older method--request stored procedures.

This is the syntax for the execute subcommand:

distribute command_tags execute
 {[repfunc] function | [replication_definition.]function | 
 sys_sp stored_procedure} [param_list]

   * The repfunc keyword (available only with LTL version 103 or later) 
     indicates that the function name that follows is a user-defined 
     function associated with a function replication definition. When 
     you create a function replication definition for a replicated 
     stored procedure, a user-defined function with the same name is 
     created for you. In this case, the execute subcommand does not 
     include the function replication definition name.

     For applied functions, Replication Server distributes the execute 
     repfunc subcommand from a primary Replication Server to any 
     replicate Replication Servers with subscriptions for the associated 
     function replication definition.

     For request functions, Replication Server distributes the execute 
     repfunc subcommand from a replicate Replication Server to the 
     primary Replication Server for the function replication definition.

   * When the repfunc keyword is omitted, the function name that follows 
     is a user-defined function associated with a table replication 
     definition, and replication_definition is the name of the replication 
     definition.

     Without the repfunc keyword, the execute subcommand is used only for 
     request stored procedures associated with table replication definitions. 
     (Applied stored procedures associated with table replication definitions 
     use the applied subcommand.) Replication Server distributes the execute 
     subcommand from a replicate Replication Server to the primary 
     Replication Server for the table replication definition.

     If the execute subcommand does not specify a replication definition, 
     Replication Server searches its system tables for the function name 
     and then finds the associated table replication definition. If the 
     function name is not unique, and the replication definition is not 
     specified, an error message reports that the function name is valid 
     for more than one replication definition.

   * function is the name of both the user-defined function and the 
     replicated stored procedure. When Replication Server receives the 
     execute command, it maps the function name to a user-defined function 
     previously created by either the create function replication definition 
     command or the create function command.

   * With LTL version 200 or later, RepAgent uses sys_sp to send system 
     stored procedures to the standby database.

   * param_list is a list of the data values supplied when the procedure 
     was executed. You must enclose parameter values in parentheses.

examples

$ra->distribute({origin_time => 'Dec  10 1992  8:48:12:750AM',
                 origin_qid => '0x00000000000000000000000000000001',
                 tran_id => '0x000000111111'}, 
                 "begin transaction 'T1' for 'user'/'password'");

$ra->distribute({origin_time => 'Dec  10 1992  8:48:13:750AM',
                 origin_qid => '0x00000000000000000000000000000002',
                 tran_id => '0x000000111111'},
                 "applied 'mytable'.rs_insert 
                  yielding after @name='foo', @city='bar'");

$ra->distribute({origin_time => 'Dec  10 1992  8:48:13:750AM',
                 origin_qid => '0x00000000000000000000000000000003',
                 tran_id => '0x000000111111'},
                 "applied 'mytable'.rs_update 
                  yielding before @name='bar', @city='baz'
                           after  @name='bar', @city='qwert'");

$ra->distribute({origin_time => 'Dec  10 1992  8:48:13:750AM',
                 origin_qid => '0x00000000000000000000000000000004',
                 tran_id => '0x000000111111'},
                 "commit transaction");

begin_tran()

Starts a transaction. Shortcut for distribute(\%tags,"begin transaction");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

Returns

begin_tran returns the result of the dbi command and the tran_id valid for the transaction just started.

examples

$ra->begin_tran({origin_time => 'Dec  10 1992  8:48:12:750AM',
                 origin_qid => '0x00000000000000000000000000000001',
                 tran_id => '0x000000111111'});

commit_tran()

Commits a transaction. Shortcut for distribute(\%tags,"commit transaction");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

Returns

commit_tran returns the result of the dbi command and the tran_id valid for the transaction just comitted.

examples

$ra->commit_tran({origin_time => 'Dec  10 1992  8:48:12:750AM',
                 origin_qid => '0x00000000000000000000000000000001',
                 tran_id => '0x000000111111'});

rollback_tran()

Rolls a transaction back. Shortcut for distribute(\%tags,"rollback transaction");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

Returns

rollback_tran returns the result of the dbi command and the tran_id valid for the transaction just rolled back.

examples

$ra->rollback_tran({origin_time => 'Dec  10 1992  8:48:12:750AM',
                 origin_qid => '0x00000000000000000000000000000001',
                 tran_id => '0x000000111111'});

insert()

Inserts a record into a table. Shortcut for distribute(\%tags,"applied 'mytable'.rs_insert ...");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

$table

Name of the table into which the data will be inserted.

$param_list

'after' parameter list as described in 'distribute'.

Returns

insert returns the result of the dbi command.

examples

$ra->insert({origin_time => 'Dec  10 1992  8:48:12:750AM',
             origin_qid => '0x00000000000000000000000000000001',
             tran_id => '0x000000111111'},
             'mytable', 
             q{@name='Joe Looser', @phone='123-456'}
           );

update()

Changes a record in a table. Shortcut for distribute(\%tags,"applied 'mytable'.rs_update ...");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

$table

Name of the table into which the data will be inserted.

$before_param_list

'before' parameter list as described in 'distribute'.

$after_param_list

'after' parameter list as described in 'distribute'.

Returns

insert returns the result of the dbi command.

examples

$ra->update({origin_time => 'Dec  10 1992  8:48:12:750AM',
             origin_qid => '0x00000000000000000000000000000001',
             tran_id => '0x000000111111'},
             'mytable', 
             q{@name='Joe Looser', @phone='123-456'}, 
             q{@name='Joe Random', @phone='987-654'}
           );

delete()

Deletes a record in a table. Shortcut for distribute(\%tags,"applied 'mytable'.rs_delete ...");

Parameters:

\%cmd_tags

Look at cmd_tags at the distribute command.

$table

Name of the table into which the data will be inserted.

$before_param_list

'before' parameter list as described in 'distribute'.

Returns

insert returns the result of the dbi command.

examples

$ra->delete({origin_time => 'Dec  10 1992  8:48:12:750AM',
             origin_qid => '0x00000000000000000000000000000001',
             tran_id => '0x000000111111'},
             'mytable', 
             q{@name='Joe Random', @phone='987-654'}
           );

Accessor Methods

maintenance_user()

Returns the name of the maintenance user given by the repserver

my $mu = $ra->maintenance_user;

truncation_pointer()

Returns the log truncation pointer given by the repserver

my $tp = $ra->truncation_point;

ltl_version()

Returns the ltl version that was agreed upon between repagent and repserver

my $lv = $ra->ltl_version;

system_version()

Returns the system version of the repserver

my $sv = $ra->system_version;

upgrade_locator()

Returns the upgrade locator given by the repserver

my $ul = $ra->upgrade_locator;

last_oqid()

Fetches the last origin queue id from the repserver.

my $last_oqid = $ra->last_oqid;

last_tran_id()

Fetches the last transaction id seen or generated by the repagent.

my $last_tran_id = $ra->last_tran_id;

AUTHOR

Bernd Dulfer <bdulfer@cpan.org>

SEE ALSO

Perl
DBI
DBD::Sybase
Replication Server Design Guide (Sybase web site)