EM415 – Custom Extraction Techniques
This presentation is the property of its rightful owner.
Sponsored Links
1 / 87

EM415 – Custom Extraction Techniques PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on
  • Presentation posted in: General

EM415 – Custom Extraction Techniques. Robert Waywell Senior Product Support Engineer iAnywhere Solutions [email protected] Understand the reasons to customize the extraction process for remote databases. Be able to plan and implement an appropriate extraction process for your environment.

Download Presentation

EM415 – Custom Extraction Techniques

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Em415 custom extraction techniques

EM415 – Custom Extraction Techniques

  • Robert Waywell

  • Senior Product Support Engineer

  • iAnywhere Solutions

  • [email protected]


Objectives

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


Topics

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Dbxtract benefits

Generates schema and data.

Easy to use.

One-stop shopping.

DBXtract - Benefits


Dbxtract limitations

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


Mlxtract

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


Topics1

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Performance

Database Initialization

Schema Creation

Common Data

Node-Specific Data

Performance


Database initialization

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 initialization1

Database Initialization


Schema creation

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


Schema creation cont

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

Initialization & Schema Creation


Common data

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


Node specific 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


Topics2

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Template database

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


Template database costs

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


Topics3

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Sql remote

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Sample databases

Head Office

Regional Office

Manager1

SalesRep1

SalesRep2

Sample Databases


Database initialization2

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


Database initialization script

Create_template.bat

%asany7%\win32\dbinit template.db

Database Initialization – Script


Sql remote1

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Schema creation1

Replicated Schema

Non-replicated Schema

Common Schema

Tier-Specific Schema

Schema Creation


Replicated schema

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


Reciprocal publications and subscriptions

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

)


Replicated schema script

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


Replicated schema script cont

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)


Replicated schema script cont1

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)


Replicated schema script cont2

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)


Replicated schema script cont3

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)


Replicated schema script cont4

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)


Replicated schema script cont5

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)


Replicated schema script cont6

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)


Non replicated schema

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


Non replicated schema cont

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)


Non replicated schema cont1

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)


Non replicated schema script

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


Non replicated schema script cont

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)


Common schema

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


Tier specific 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


Tier specific schema cont

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)


Tier specific schema cont1

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)


Tier specific schema cont2

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)


Tier specific schema script

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


Tier specific schema script cont

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)


Tier specific schema script cont1

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)


Tier specific schema script cont2

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)


Sql remote2

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Load the common data

What tables are replicated in their entirety?

Can use UNLOAD TABLE to extract this data from the consolidated database

Examples:

Product

Region

Order_Status

At the Consolidated:

Extract_common_data.sql

UNLOAD TABLE product TO ‘product.dat’;

UNLOAD TABLE region TO ‘region.dat’;

UNLOAD TABLE order_status TO ‘order_status.dat’:

Load the Common Data


Load the common data cont

At the Template:

Load_common_data.sql

LOAD TABLE product FROM ‘product.dat’;

LOAD TABLE region FROM ‘region.dat’;

LOAD TABLE order_status FROM ‘order_status.dat’;

Notes:

The path of the file created UNLOAD TABLE and read by LOAD TABLE is relative to the location of the database server

These tables are published one-way down to the remotes

Load the Common Data (cont)


Load the common data script

Create_template.bat (cont)

start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=regionaloffice;dbn=regionaloffice;dbf=regionalofficedb\regionaloffice.db" read extract_common_data.sql

start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;dbn=template;eng=template;dbf=template.db" read load_common_data.sql

Load the Common Data – Script


Load the common data cont1

If not the whole table, do all the users still get all the rows that are replicated?

Can use “SELECT …; OUTPUT TO …” to extract this data from the consolidated database

i.e. Product table may contain discontinued products in addition to current products

At the Consolidated:

SELECT product_id, description, price, quantity FROM product WHERE status = ‘current’;

OUTPUT TO ‘product.dat’;

Load the Common Data (cont)


Load the common data cont2

At the Template:

INPUT INTO product FROM ‘product.dat’;

Note: OUTPUT and INPUT are ISQL functions, not database engine functions.

The path used by OUTPUT and INPUT is relative to the working directory of ISQL

Load the Common Data (cont)


Sql remote3

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Minimizing down time

Ideally, remote databases should be extracted from a quiescent consolidated database.

If a remote database is extracted from an active consolidated database, then the extraction must be done at isolation level 3 to guarantee consistency.

This may not be practical:

7X24

The more remotes, the longer the database will be impacted

Solution is to work with a copy of the consolidated database.

Minimizing Down Time


Minimizing down time cont

Process:

Shut down the consolidated database server and restart the consolidated database on a standalone engine.

This is to ensure that no other users are able to connect to the consolidated database during the process

Run a script to add the new remote user(s) to the consolidated database.

This is the Create_new_remote.sql script

Shut down the consolidated database and make a copy of it.

This must be an off-line backup to ensure that the database was shut down cleanly.

We need to ensure that none of the data changes on the consolidated between the time the remote users are created and the time that the copy of the database is made

Minimizing Down Time (cont)


Minimizing down time cont1

Process (cont):

At this point you can re-start the consolidated database on the production server.

Changes made after this point will be replicated out, as appropriate, to the new remote users

The copy of the consolidated database has captured the common initial state to be used in extracting the new remote databases.

Minimizing Down Time (cont)


Minimizing down time script

Use_template.bat

if exist copy_of_cons.db %asany7%\win32\dberase -y copy_of_cons.db

copy regionalofficedb\regionaloffice.db copy_of_cons.db

%asany7%\win32\dblog -t copy_of_cons.log copy_of_cons.db

Minimizing Down Time – Script


Sql remote4

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Node specific data1

At this point, we have a template database that contains all of the schema that we need and some of the data that we need.

If you run DBXtract –d –v … you can see how DBXtract is selecting the data for a given remote user.

-d is “data only”

-v is “verbose”

Since we are working with a copy of the consolidated database at this point, we can modify the publications to not include the common data that has already been extracted.

Node Specific Data


Node specific data scripts

Use_template.bat (cont)

start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=copy_of_cons;dbn=copy_of_cons;dbf=copy_of_cons.db" read drop_common_data_publications.sql

Drop_common_data_publications.sql

DROP TABLE "DBA".latency_calculation;

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";

Node Specific Data – Scripts


Node specific data scripts cont

Use_template.bat

rem %1 is the user id for the user that the remote database is being created for

rem %2 is the operation(s) to be performed

rem clean cleans up the files and directories from a previous run

remfull cleans up and then creates a new remote database

rem refresh take a new copy of the consolidated database

remwhen specifying "refresh", specify "none" for the user id

rem the default is "full“

if not exist %1db md %1db

copy template.db %1db\%1.db

cd %1db

%asany7%\win32\dblog -t %1.log %1.db

cd ..

Node Specific Data – Scripts (cont)


Node specific data scripts cont1

Use_template.bat (cont)

start /wait %asany7%\win32\dbxtract -d -c "uid=dbxtract_user;pwd=sql;dbn=copy_of_cons;eng=copy_of_cons;dbf=copy_of_cons.db" -o %1_extract.out -r %1_reload.sql unload_dir %1

start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=%1;dbn=%1;dbf=%1db\%1.db" read %1_reload.sql

start /wait %asany7%\win32\dbisqlc -c "uid=dba;pwd=sql;eng=%1;dbn=%1;dbf=%1db\%1.db" read finish_new_remote.sql [%1]

Node Specific Data – Scripts (cont)


Node specific data scripts cont2

Finish_new_remote.sql

PARAMETERS user_id;

GRANT CONNECT TO {user_id} IDENTIFIED BY sql;

GRANT PUBLISH TO {user_id};

CREATE REMOTE TYPE "FILE" ADDRESS '{user_id}';

// Since all my database objects are owned by DBA, granting membership in that group

// simplifies SQL statements by not requiring objects to be qualified with DBA.

// DBA permissions are not inherited by groups or members of a group

GRANT MEMBERSHIP IN GROUP DBA TO {user_id};

REMOTE RESET RegionalOffice;

Node Specific Data – Scripts (cont)


Sql remote template database

At this point we have a working template database and customized extraction process.

To add a given user to the list of users to be extracted requires only 2 lines to be added to a single batch file.

Have saved time on:

Initializing the database

Creating the schema

Extracting & loading common data

Have reduced impact on the production consolidated database by working on an offline copy for the resource intensive extraction of node-specific data.

SQL Remote Template Database


Sql remote template database script

Extract_salesreps.bat

rem This batch file will extract one remote user for each call to “subscribe_new_remote.bat“ and “use_template.bat”

rem Each time this file is run, we will create a new template database and refresh the off-line copy

rem of the consolidated database.

if %1. == . goto :full

if %1 == clean goto :clean

:full

call create_template

SQL Remote Template Database – Script


Sql remote template database script cont

Extract_salesreps.bat (cont)

call subscribe_new_remote.bat salesrep1 1

call subscribe_new_remote.bat salesrep2 2

call subscribe_new_remote.bat salesrep3 3

call use_template none refresh

call use_template salesrep1 full

call use_template salesrep2 full

call use_template salesrep3 full

SQL Remote Template Database – Script (cont)


Sql remote template database script cont1

Extract_salesreps.bat (cont)

call repall.bat

call repall.bat

call repall.bat

goto :end

:clean

call create_template clean

call drop_existing_remote salesrep1 1

call drop_existing_remote salesrep2 2

call drop_existing_remote salesrep3 3

:end

SQL Remote Template Database – Script (cont)


Sql remote template database maintenance

SQL Scripts:

Complete_template.sqlCreate_new_remote.sql

Drop_common_data_publications.sqlDrop_existing_remote.sql

Extract_common_data.sqlLoad_common_data.sql

Finish_new_remote.sql

Batch Files:

Create_template.batDrop_existing_remote.bat

Subscribe_new_remote.batUse_template.bat

Extract_salesreps.bat

SQL Remote Template Database – Maintenance


Sql remote5

Database Initialization

Schema Creation

Load the Common Data

Minimizing Down Time

Node Specific Data

Customized scripts

SQL Remote


Customized scripts

In a typical schema, many tables will have common pieces to their SUBSCRIBE BY clauses

TABLE Customer SUBSCRIBE BY

( SELECT salesrep_id FROM Link

WHERE Customer.customer_id = Link.customer_id

),

TABLE Contact SUBSCRIBE BY

( SELECT salesrep_id FROM Link

WHERE Contact.customer_id = Link.customer_id

),

TABLE "Order" SUBSCRIBE BY

( SELECT salesrep_id FROM Link

WHERE "Order".customer_id = Link.customer_id

),

Customized Scripts


Customized scripts cont

DBXtract uses the following queries to extract the data for these tables:

Unloading "DBA"."Customer" into unload_dir\205.dat (relative to server)

SELECT "customer_id", "company_name", "address" FROM "DBA"."Customer" WHERE (( '1' IN (select salesrep_id from DBA.Link where

Customer.customer_id = Link.customer_id) ))

Unloading "DBA"."Contact"

Unloading "DBA"."Contact" into unload_dir\206.dat (relative to server)

SELECT "contact_id", "contact_name", "phone", "email", "customer_id" FROM "DBA"."Contact" WHERE (( '1' IN (select salesrep_id from DBA.Link where

Contact.customer_id = Link.customer_id) ))

Customized Scripts (cont)


Customized scripts cont1

DBXtract queries (cont)

Unloading "DBA"."Order"

Unloading "DBA"."Order" into unload_dir\207.dat (relative to server)

SELECT "order_id", "customer_id", "product_id", "quantity", "status_id", "order_date", "created_log_offset", "created_location" FROM "DBA"."Order" WHERE (( '1' IN (select salesrep_id from DBA.Link where

"Order".customer_id = Link.customer_id) ))

Customized Scripts (cont)


Customized scripts cont2

The data from these tables could be extracted more efficiently if a temporary table was used to hold the list of customer id’s assigned to this sales rep.

CREATE GLOBAL TEMPORARY TABLE customer_ids_for_salesrep

(customer_idINT PRIMARY KEY)

INSERT INTO customer_ids_for_salesrep

SELECT Link.customer_id FROM Link

WHERE Link.salesrep_id = 1;

SELECT Customer."customer_id", "company_name", "address"

FROM "DBA"."Customer“, “DBA”.”customer_ids_for_salesrep”

WHERE Customer.customer_id = customer_ids_for_salesrep.customer_id;

Customized Scripts (cont)


Customized scripts cont3

SELECT "contact_id", "contact_name", "phone", "email", Contact."customer_id" FROM "DBA"."Contact", “DBA”.”customer_ids_for_salesrep”

WHERE Contact.customer_id = customer_ids_for_salesrep.customer_id;

SELECT "order_id", Order."customer_id", "product_id", "quantity", "status_id", "order_date", "created_log_offset", "created_location“

FROM "DBA"."Order", “DBA”.”customer_ids_for_salesrep”

WHERE Contact.customer_id = customer_ids_for_salesrep.customer_id;

Customized Scripts (cont)


Customized scripts cont4

More development cost, greater performance optimization.

May not be reasonable for relatively flat schemas.

The longer the chain of table dependencies, the more beneficial it will be to customize the SQL queries used to extract the node specific data.

Customized Scripts (cont)


Topics4

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Mobilink

Reference database already defines the schema for the remotes.

Proxy tables can be used to populate the common data in the template before copying the database to create a new remote.

First time synchronization logic will have to handle both:

Not downloading the common data that already exists on the remote.

Downloading the node-specific data that does not yet exist on the remote.

Special case handling of the first synchronization would be required even if not using a template database.

MobiLink


Mobilink cont

Could handle the first time synchronization with a separate script version, but this would require maintaining two script versions.

Easier to maintain a single script version

First time synchronization is usually handled by setting a last synched time value to “pre-history”

In 8.0 last synch value automatically included

Have the option of using Proxy tables to extract the node specific data but:

Already have synchronization scripts to extract the data.

DBMLSync already uses a direct ODBC connection to the consolidated database.

MobiLink (cont)


Mobilink cont1

Will want to run DBMLSync from a LAN connection (desktop remotes) or when docked (CE remotes) for the first synchronization.

MobiLink (cont)


Topics5

DBXtract and MLXtract

Performance

Template Database

SQL Remote

MobiLink

Summary

Topics


Summary

Using a template database can have an orders-of-magnitude impact on the time required to extract the remote databases for a new installation.

There will be some minimum number of remotes, below which it is not worth the effort to customize the extraction process

Customizing the extraction process may require only a few days development effort.

Summary


Recommended sessions

Morning Sessions

AM33 - Adaptive Server Anywhere (ASA) Internals Performance and Tuning

AM38 – Data Replication with ASA

AM36 – MobiLink Synchronization

Afternoon Sessions

EM414 – SQL Remote Tips & Techniques

EM417 - Setting Up a SQL Remote System for Performance

EM418 - SQL Remote for Adaptive Server Anywhere Internals

EM408 - Back-up and Recovery of SQL Anywhere: Tips & Techniques

Recommended Sessions


Newsgroups

forums.sybase.com

\sybase.public.sqlanywhere.general

\sybase.public.sqlanywhere.linux

\sybase.public.sqlanywhere.mobilink

\sybase.public.sqlanywhere.product_futures_discussion

\sybase.public.sqlanywhere.replication

\sybase.public.sqlanywhere.ultralite

Newsgroups


Ianywhere solutions highlights

Ask the Experts - about Mobile & Wireless Solutions

-Mezzanine Level Room 15B

Mon./Tues. 11:30 am - 3:30 pm; Wed. 11:30 - 1:30;

Thurs. 9 am - 12 noon

-Exhibit Hall - Demo Center(truck) exhibit hall hours

SIG (Special Interest Group)

- Tuesday 5:30pm Mobile & Wireless SDCC, Upper level, Room 11

Keynote - Enabling m-Business Solutions

Wednesday 1:30 pm - 3:00 pm

iAnywhere Solutions Developer Community

-Excellent resource for commonly asked questions, newsgroups, bug

fixes, newsletters, event listings - visit www.ianywhere.com/developer

iAnywhere Solutions Highlights


  • Login