EM415 – Custom Extraction Techniques

EM415 – Custom Extraction Techniques • Robert Waywell • Senior Product Support Engineer • iAnywhere Solutions • Rwaywell@sybase.com

Understand the reasons to customize the extraction process for remote databases. Be able to plan and implement an appropriate extraction process for your environment. Objectives

DBXtract and MLXtract Performance Template Database SQL Remote MobiLink Summary Topics

Generates schema and data. Easy to use. One-stop shopping. DBXtract - Benefits

Schema consists of all-or-none of various object types: Stored Procedures Views Triggers Doesn’t handle non-replicated tables. May want different trigger logic on remotes. Avoid “IF CURRENT REMOTE USER IS NULL …” logic. For performance reasons, may want different indexes on consolidated and remotes. Doesn’t handle multiple tiers. DBXtract – Limitations

Run against the reference database. Generates the schema. If using an ASA consolidated database, can extract the data as well. Otherwise, the only difference between remote databases is the SYNCHRONIZATION DEFINITION. MLXtract

Database Initialization Schema Creation Common Data Node-Specific Data Performance

Initializing a blank, default database takes approx 24 seconds on average. dbinit db1.db Initializing 1 database and copying it 9 times took 31-33 seconds total. dbinit db1.db copy db1.db db2.db dblog -t db2.log db2.db … Database Initialization

Database Initialization

Using a 10 table schema and creating the schema individually in each database took approx. 15 seconds per database. start /wait dbisql -c "uid=dba;pwd=sql;eng=schema;dbf=db1.db" read regionaloffice.sql Notes: Approx. 7 seconds to load the database and connect from ISQL Leaves approx. 8 seconds to load the schema. Schema used was the Regional Office database from EM414 SQL Remote Techniques Includes supporting triggers, RI, etc. Rough estimate for a 200 table schema plus supporting triggers, RI, etc. is 160 seconds. Schema Creation

Loading the schema once before copying the databases took approx. 48 seconds total for 10 databases. dbinit db1.db start /wait dbisql -c "uid=dba;pwd=sql;eng=schema;dbf=db1.db" read regionaloffice.sql copy db1.db db2.db dblog -t db2.log db2.db … Notes: 24 seconds to initialize 1st database 15 seconds to load schema in 1st database 9 seconds to copy 9 databases Schema Creation (cont.)

Initialization & Schema Creation

Data shared by all, or a large group of, nodes in the system. Examples: Product Lists Look-Up Tables Employee Information Can be extracted once, included in a template database and copied many times. Common Data

Data that is unique to a given node or a small group of nodes. Examples: Customers Projects Sales Leads Needs to be extracted separately for each individual user. Node-Specific Data

Concept: Do as much work as possible once. Can Include: Standard Schema Including non-replicated tables Views and procedures specific to the remote May customize triggers and indexes to optimize performance Administrative tables and publications Common Data Template Database

Takes time to create. Maintenance Differs from either a remote or a consolidated database Changes made to either the remote or consolidated schema need to be mirrored in the template database Cost of identifying and extracting node specific data. Can’t use DBXtract to extract data for only some of the tables For MobiLink, need first-time-synch logic that only synchronizes the node specific data and not the common data Template Database – Costs

Database Initialization Schema Creation Load the Common Data Minimizing Down Time Node Specific Data Customized scripts SQL Remote

Head Office Regional Office Manager1 SalesRep1 SalesRep2 Sample Databases

Can use DBInit or Sybase Central Recommend DBInit, then the process can be scripted which facilitates testing and re-use Ensure proper initialization settings such as: Case sensitivity Blank padding Collation sequence Database Initialization

Create_template.bat %asany7%\win32\dbinit template.db Database Initialization – Script

Replicated Schema Non-replicated Schema Common Schema Tier-Specific Schema Schema Creation

DBXtract –n The –n switch extracts schema only Useful to determine what schema DBXtract would have created on its own GLOBAL TEMPORARY tables and database events are always extracted Switches to control what objects are extracted: Views (-xp) Triggers (-xt) Stored Procedures (-xp) Foreign Keys (-xf) Remember that reciprocal publications and subscriptions are created on the remote nodes Replicated Schema

RegionalOffice SalesRep1 DBXtract/ SSXtract Reciprocal Publications and Subscriptions CREATE PUBLICATION Customer_Pub ( TABLE Customer SUBSCRIBE BY ( SELECT salesrep_id FROM Link WHERE Customer.customer_id = Link.customer_id ) ) CREATE PUBLICATION Customer_Pub ( TABLE Customer )

Create_template.bat rem need to create a template remote user to be used by dbxtract in generating the schema start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=regionaloffice;dbn=regionaloffice;dbf=regionalofficedb\regionaloffice.db" read create_new_remote.sql [template_user] [0] start /wait %asany7%\win32\dbxtract -c "uid=dbxtract_user;pwd=sql;eng=regionaloffice;dbn=regionaloffice;dbf=regionalofficedb\regionaloffice.db" -n -r pre_template.sql unload_dir template_user Replicated Schema – Script

rem Once the template schema has been extracted, we need to rem remove the template_user from the production database start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=regionaloffice;dbn=regionaloffice;dbf=regionalofficedb\regionaloffice.db" read drop_existing_remote.sql [template_user] [0] start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;dbn=template;eng=template;dbf=template.db" read pre_template.sql Replicated Schema – Script (cont)

Create_new_remote.sql PARAMETERS user_id, subscribe_by; GRANT CONNECT TO {user_id} IDENTIFIED BY sql; GRANT REMOTE TO {user_id} TYPE FILE ADDRESS '{user_id}'; CREATE SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR {user_id}; Replicated Schema – Script (cont)

Create_new_remote.sql (cont) CREATE SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR Manager1; START SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR Manager1; CREATE SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR HeadOffice; START SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR HeadOffice; Replicated Schema – Script (cont)

Create_new_remote.sql (cont) CREATE SUBSCRIPTION TO Manager_Expense_Pub('{subscribe_by}') FOR Manager1; START SUBSCRIPTION TO Manager_Expense_Pub('{subscribe_by}') FOR Manager1; CREATE SUBSCRIPTION TO Admin_Pub('{user_id}') FOR {user_id}; CREATE SUBSCRIPTION TO Admin_Pub('ALL') FOR {user_id}; CREATE SUBSCRIPTION TO Clean_Up_Pub('NotMe') FOR {user_id}; Replicated Schema – Script (cont)

Create_new_remote.sql (cont) CALL Prime_Key_Pool( '{user_id}', 'Expense'); CALL Prime_Key_Pool( '{user_id}', 'Order'); COMMIT; REMOTE RESET {user_id}; COMMIT; Replicated Schema – Script (cont)

Drop_Existing_Remote.sql PARAMETERS user_id, subscribe_by; DROP SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR {user_id}; DROP SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR Manager1; DROP SUBSCRIPTION TO Customer_Pub('{subscribe_by}') FOR HeadOffice; DROP SUBSCRIPTION TO Manager_Expense_Pub('{subscribe_by}') FOR Manager1; Replicated Schema – Script (cont)

Drop_Existing_Remote.sql DROP SUBSCRIPTION TO Admin_Pub('{user_id}') FOR {user_id}; DROP SUBSCRIPTION TO Admin_Pub('ALL') FOR {user_id}; DROP SUBSCRIPTION TO Clean_Up_Pub('NotMe') FOR {user_id}; REVOKE REMOTE FROM {user_id}; REVOKE CONNECT FROM {user_id}; DELETE FROM keypool WHERE remote_location = '{user_id}'; COMMIT; Replicated Schema – Script (cont)

Compare physical data model to the schema created by DBXtract Refer to the EM414 & EM415 Schema data model EM414 describes a Message System Control Chart technique for detecting unexpected latency in the SQL Remote message system This technique uses 3 tables and 1 database event Base Tables: Latency_exception_report Control_chart GLOBAL TEMPORARY table: Latency_calculation Database Event: Populate_control_chart Non-replicated Schema

In this example, the Latency_calculation table and the Populate_control_chart event will have been included in the reload.sql script generated by DBXtract The Latency_exception_report and Control_chart tables will not have been included in the reload.sql script since they are not part of a publication. Options are to either add the missing tables or remove the GLOBAL TEMPORARY table and the database event. If there is unexpected latency, then I probably won’t be able to replicate an exception report up to the consolidated database Non-Replicated Schema (cont)

Any tables that are replicated one-way up from a remote database will not be included by DBXtract in the reload.sql. Examples: Replication_Error table Acknowledgement tables Expense table Non-Replicated Schema (cont)

Complete_Template.sql CREATE TABLE "DBA"."Expense" ( "expense_id" integer NOT NULL, "salesrep_id" integer NOT NULL, "description" char(40) NOT NULL, "amount" money NOT NULL, PRIMARY KEY ("expense_id") ) go Non-Replicated Schema - Script

Complete_Template.sql (cont) CREATE TRIGGER BI_Expense BEFORE INSERT ORDER 1 ON Expense REFERENCING NEW AS newrow FOR EACH ROW BEGIN … END; Non-Replicated Schema – Script (cont)

The pieces of the schema that are shared between tiers Most of the table definitions and RI constraints will be common between tiers Key Pool triggers will be required at all tiers Stored procedures used by the front-end application Database Events for backup & recovery … As long as these pieces are replicated, then they will have been included when you ran DBXtract If they are not replicated, then you will have picked them up when preparing the scripts for the non-replicate schema Common Schema

Most of the UPDATE … PUBLICATION triggers will not be required at the remote nodes Some publications at the remote nodes will require SUBSCRIBE BY clauses in order to force data up to the consolidated database i.e. request_queue_return_codes Request queue implementation handles some requests via a trigger In order to force the result codes up to the consolidated database, need a SUBSCRIBE BY clause on the article Did you have indexes created at the consolidated node to support reporting functionality? If so, are these indexes worthwhile at the remote nodes? Tier-Specific Schema

Are there tables used at the remote nodes that are never replicated up? Are additional columns included in a given table at different tiers? i.e. Expense table has an approval_status column at the Regional Office tier that does not exist at the Sales Rep tier Key Pool Trigger logic Do you write a more complex trigger that contains all the logic necessary to execute at any tier or do you use separate, less complex triggers for each tier Tier-Specific Schema (cont)

Administrative publications may differ between tiers to facilitate control of data flow Regional Office: CREATE PUBLICATION Clean_Up_Pub ( TABLE Request_Queue_Return_Codes SUBSCRIBE BY remote_location, TABLE Rep_Errors SUBSCRIBE BY remote_location ); CREATE SUBSCRIPTION TO Clean_Up_Pub('NotMe') FOR SalesRep1; CREATE SUBSCRIPTION TO Clean_Up_Pub('NotMe') FOR SalesRep2; CREATE SUBSCRIPTION TO Clean_Up_Pub('NotMe') FOR Manager1; Tier-Specific Schema (cont)

Administrative publications may differ between tiers to facilitate control of data flow Sales Rep: CREATE PUBLICATION Response_Pub ( TABLE Request_Queue_Return_Codes SUBSCRIBE BY subscribe_by, TABLE Rep_Errors ); CREATE SUBSCRIPTION TO Response_Pub('RegionalOffice') FOR RegionalOffice; CREATE SUBSCRIPTION TO Response_Pub('HeadOffice') FOR RegionalOffice; Tier-Specific Schema (cont)

Create_template.bat (cont) start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;dbn=template;eng=template;dbf=template.db" read complete_template.sql Complete_template.sql (cont) DROP TRIGGER "DBA"."RU_Order"; DROP TABLE latency_calculation; DROP EVENT populate_control_chart; Tier-Specific Schema – Script

Complete_template.sql (cont) ALTER PUBLICATION "DBA"."Customer_Pub" DROP TABLE "DBA"."Region"; ALTER PUBLICATION "DBA"."Customer_Pub" DROP TABLE "DBA"."Product"; ALTER PUBLICATION "DBA"."Customer_Pub" DROP TABLE "DBA"."Order_Status"; Tier-Specific Schema – Script (cont)

Complete_template.sql (cont) CREATE PUBLICATION Expense_Pub ( TABLE Expense ); CREATE PUBLICATION Response_Pub ( TABLE Request_Queue_Return_Codes SUBSCRIBE BY subscribe_by, TABLE Rep_Errors ); Tier-Specific Schema – Script (cont)

Complete_template.sql (cont) DROP PUBLICATION "DBA"."Admin_Pub"; CREATE SUBSCRIPTION TO Expense_Pub FOR RegionalOffice; CREATE SUBSCRIPTION TO Response_Pub('RegionalOffice') FOR RegionalOffice; CREATE SUBSCRIPTION TO Response_Pub('HeadOffice') FOR RegionalOffice; REVOKE CONNECT FROM template_user; Tier-Specific Schema – Script (cont)

EM415 – Custom Extraction Techniques