Creating Classic Replication Schemes With Scripts
Creating your classic replication schemes with scripts can save you time and help you avoid mistakes. This section provides some suggestions for automating the creation of replication schemes using Perl.
For example, consider a general workload bidirectional scheme. Entering the element description for the five tables, ttuser.accounts
, ttuser.sales
, ttuser.orders
, ttuser.inventory
, and ttuser.customers
, would be tedious and error-prone if done manually.
CREATE REPLICATION bigscheme ELEMENT elem_accounts_1 TABLE ttuser.accounts MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_accounts_2 TABLE ttuser.accounts MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast" ELEMENT elem_sales_1 TABLE ttuser.sales MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_sales_2 TABLE ttuser.sales MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast" ELEMENT elem_orders_1 TABLE ttuser.orders MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_orders_2 TABLE ttuser.orders MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast" ELEMENT elem_inventory_1 TABLE ttuser.inventory MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_inventory_2 TABLE ttuser.inventory MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast" ELEMENT elem_customers_1 TABLE ttuser.customers MASTER westds ON "westcoast" SUBSCRIBER eastds ON "eastcoast" ELEMENT elem_customers_2 TABLE ttuser.customers MASTER eastds ON "eastcoast" SUBSCRIBER westds ON "westcoast";
It is often more convenient to automate the process of writing a classic replication scheme with scripting. For example, the following perl script can be used to create the replication scheme shown in the previous example.
@tables = qw( ttuser.accounts ttuser.sales ttuser.orders ttuser.inventory ttuser.customers ); print "CREATE REPLICATION bigscheme"; foreach $table (@tables) { $element = $table; $element =~ s/repl\./elem\_/; print "\n"; print " ELEMENT $element\_1 TABLE $table\n"; print " MASTER westds ON \"westcoast\"\n"; print " SUBSCRIBER eastds ON \"eastcoast\"\n"; print " ELEMENT $element\_2 TABLE $table\n"; print " MASTER eastds ON \"eastcoast\"\n"; print " SUBSCRIBER westds ON \"westcoast\""; } print ";\n";
The @tables
array can be obtained from some other source, such as a database. For example, you can use ttIsql
and f
in a Perl statement to generate a @tables
array for all of the tables in the WestDSN
database with the owner name repl
:
@tables = 'ttIsql -e "tables; quit" WestDSN | grep " REPL\."';
This Perl script example creates a classic replication scheme for all of the repl
tables in the WestDSN
database. (Note that some substitution may be necessary to remove extra spaces and line feeds from the grep
output.)
@tables = 'ttIsql -e "tables; quit" WestDSN | grep " REPL\."'; print "CREATE REPLICATION bigscheme"; foreach $table (@tables) { $table =~ s/^\s*//; # Remove extra spaces $table =~ s/\n//; # Remove line feeds $element = $table; $element =~ s/repl\./elem\_/; print "\n"; print " ELEMENT $element\_1 TABLE $table\n"; print " MASTER westds ON \"westcoast\"\n"; print " SUBSCRIBER eastds ON \"eastcoast\"\n"; print " ELEMENT $element\_2 TABLE $table\n"; print " MASTER eastds ON \"eastcoast\"\n"; print " SUBSCRIBER westds ON \"westcoast\""; } print ";\n";