Help-Site Computer Manuals
Software
Hardware
Programming
Networking
  Algorithms & Data Structures   Programming Languages   Revision Control
  Protocols
  Cameras   Computers   Displays   Keyboards & Mice   Motherboards   Networking   Printers & Scanners   Storage
  Windows   Linux & Unix   Mac

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

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


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

 

=over

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)
Programminig
Wy
Wy
yW
Wy
Programming
Wy
Wy
Wy
Wy