M413 MobiLink Design Best Practices

M413MobiLink Design Best Practices • David Fishburn • Principal Consultant • iAnywhere Solutions • David.Fishburn@ianywhere.com

Objective • Provide technical examples showing how to implement real-world business requirements using MobiLink • Develop an understanding of these examples which will enable their extension and customization

MobiLink ASA, ASE, Microsoft, Oracle, IBM Serial HTTP, TCPIP HotSync, Wireless ASA, PalmOS, PocketPC, UltraLite

Topics • Unique identifiers • Request Queue • Minor Schema Upgrades • Managing Deletes • Remote Deployment

Distribute Database Problems • Consider a consolidated database and 2 remote databases • 999 Customers in the consolidated database • Remote 1 & 2 download the customer list • Remote 1 inserts a new Customer (id = 1000) (max + 1) • Synchronizes • Remote 2 inserts a different new Customer (id = 1000) • Synchronizes • Synchronization fails due to duplicate primary key • Your design must PREVENT this from occurring

Uniqueness • SQL Anywhere Studio uses three techniques • GLOBAL AUTOINCREMENT • Built in feature (7.x and above) • UUID/GUIDs • Built in feature (8.0.2 and above) • Primary Key Pools • Works with any database

Global Autoincrement • A special feature of ASA • Can be used by the remote databases • Ensures each table uses a unique value for each row • Does not require • Triggers • Procedures • Primary Key Pools • Greatly simplifies development

Ensuring Uniqueness • If your consolidated already has a mechanism in place • Any of the techniques I have outlined can still be used • Global Autoincrement is an option • For new installations • Or conversions • UUID/GUID • Universally/Globally Unique Identifier • Similar to the Microsoft SQL Server feature

Create Table • Global autoincrement is used in the same manner as autoincrement CREATE TABLE ULCustomer ( cust_id unsigned integer not null default global autoincrement (100000), cust_name varchar(30), primary key( cust_id ) ) • This assigns a range of 100,000 values to EACH remote user

Remote Setup • Global Autoincrement support • ASA (7.x and 8.x) • SET OPTION PUBLIC.Global_Database_id = 12; • UltraLite (8.x) • ULSetDatabaseID( &sqlca, 12 ); • New to 8.0 • Range • Global Database ID = 12 • 12 * 100000 + 1 • 1,200,001 – 1,300,000

Global_database_id • Consolidated must assign unique keys to each remote user CREATE TABLE global_id ( next_id INTEGER NOT NULL); • When a remote first synchronizes SELECT next_id INTO next_global_id FROM global_id; UPDATE global_id SET next_id = next_id + 1; • The remote database uses the value of “next_id” for its value of Global_database_id SET OPTION "PUBLIC"."Global_database_id" = ‘100’

GUIDs • Globally Unique Identifiers CREATE TABLE ULCustomer ( cust_id binary(16) not null default newid() cust_name varchar(30), primary key( cust_id ) ) • Requires more storage space than GLOBAL AUTOINCREMENT • Index lookups are slower than GLOBAL AUTOINCREMENT • No ordering to values, complete random set of bytes • Simpler to use than GLOBAL AUTOINCREMENT

Primary Key Pools • Much more work to implement than either GLOBAL AUTOINCREMENT or UUIDs • Will work with any database • You have complete control over values • Examples in the Help file

Request Queue • Business Requirement • From an application standpoint, some operations may have to be performed at a central location. • Approval of a loan application • Assignment of an e-mail id • For Administrative reasons you may need to send out commands to be executed at remote sites. • Correct a stored procedure logic error • For performance reasons you may want to perform a batch operation • Archive records older than a certain date

Request Queue Design Considerations • The only operations that synchronize automatically are Inserts, Updates and Deletes. • It is not possible to “request” that a stored procedure be called. • It is not possible to know the result of a statement.

Request_Queue Sync_Audit id location_id op_code stmt status last_modified id site_name table_name error_msg timestamp Request Queue Implementation Table Definitions

Request_Queue Head Office Regional Office 1 ‘Dave’ 2 ‘DELETE FROM Expense’ NEW Expense 1 Mileage $40.00 Request Queue Implementation PROCESSED Request_Queue 1 ‘Dave’ 2 ‘DELETE FROM Expense’ NEW PROCESSED

Request_Queue Head Office Regional Office 1 ‘Dave’ 2 ‘DELETE FROM wrong’ NEW Sync_Audit Sync_Audit Request Queue Implementation ERROR 6 ‘Dave’ RQ: 1 SQLCODE –141 ‘wrong’ not found Request_Queue 1 ‘Dave’ 2 ‘DELETE FROM wrong’ NEW ERROR 6 ‘Dave’ RQ: 1 SQLCODE –141 ‘wrong’ not found

Request Queue Implementation • MobiLink Synchronization Scripts • Upload_insert (8.x) INSERT INTO request_queue( id, location_id, op_code, stmt, status, last_modified ) VALUES( ?, ?, ?, ?, ?, ? ) • Upload_delete (8.x) DELETE FROM request_queue WHERE id = ? • Upload_update (8.x) UPDATE request_queue SET location_id = ?, op_code = ?, stmt = ?, status = ?, last_modified = ? WHERE id = ?

Request Queue Implementation • MobiLink Synchronization Scripts • Download_cursor SELECT id,location_id,op_code, stmt,status,last_modified FROM request_queue WHERE location_id = @AgentName AND status = ‘NEW’ AND last_modified >= @LastDownload

Request Queue Implementation • DBMLSync (For the remote database) • Publication (8.x) – What to synchronize CREATE PUBLICATION routing( TABLE proposal( prop_id, prop_text, last_modified ), TABLE sync_audit, TABLE request_queue ); • Subscription (8.x) – Where to synchronize it CREATE SYNCHRONIZATION USER "sales"; CREATE SYNCHRONIZATION SUBSCRIPTION TO "routing" FOR "sales" TYPE 'tcpip' ADDRESS 'host=localhost;port=2439' OPTION ScriptVersion='v1.0'

Request_Queue id location_id op_code stmt status last_modified Request Queue Implementation • Stored Procedure • Needed to process the commands after synchronizing • Loop on all request_queue rows with a status = ‘NEW’ CASE op_code WHEN 1 THEN Display message for the remote WHEN 2 THEN Execute the command

RQ: 1 SQLCODE –141 ‘wrong’ not found Request Queue Implementation • Stored Procedure • Used to execute commands in ASA EXECUTE IMMEDIATE cmd_stmt; SET cmd_status = 'PROCESSED'; EXCEPTION WHEN OTHERS THEN SET cmd_error = 'RQ: ' + string( cmd_id ) + ' Failed with SQLCODE: ' + string( SQLCODE ) + ': ' + errormsg(); SET cmd_status = 'ERROR'; INSERT INTO Sync_Audit … Errormsg(…) New function for 8.x Returns current English error message with substitutions! Optionally takes parameters

Request Queue Implementation • How to automatically process the request queue • Use DBMLSync hooks (7.x and 8.x) • If a defined stored procedure exists, DBMLSync calls it • There are over 15 hook procedures • Each are optional • For this case, we need to use 3: • sp_hook_dbmlsync_begin() • sp_hook_dbmlsync_upload_end() • sp_hook_dbmlsync_end()

Request Queue Implementation • Passing parameters to the hook procedures • DBMLSync automatically creates a temporary table called #hook_dict • Two columns: name, value • Each hook procedure populates this table with different rows ( parameters ) • Run DBMLSync with –v (verbose) • The DBMLSync log will contain useful information

Request Queue Implementation • DBMLSync verbose output (when using hooks) • Uploading table operations • Waiting for MobiLink to apply upload insert into #hook_dict values( 'MobiLink user','sales' ) insert into #hook_dict values( 'upload status','committed' ) insert into #hook_dict values( 'publication_0','routing' ) execute "DBA".sp_hook_dbmlsync_upload_end

Request Queue Implementation • sp_hook_dbmlsync_end() • Used to call the procedure to process the request_queue CALL sp_process_request_queue( @sync_user ); • This procedure will always be called • Even if there is an error during synchronization

Request Queue Implementation • sync_user – can be retrieved from the #hook_dict table DECLARE @sync_user VARCHAR(255); SELECT value INTO @sync_user FROM #hook_dict WHERE name = 'MobiLink user';

Request Queue Implementation • We only want to process the queue • If the upload was successful IF( @upload_successful = 'Y' ) THEN CALL sp_process_request_queue( @sync_user ); END IF; • @upload_successful is a database variable that MUST be created on the connection DBMLSync uses • sp_hook_dbmlsync_end()

Request Queue Implementation • sp_hook_dbmlsync_begin() • Used to create the @upload_successful variable CREATE VARIABLE @upload_successful char(1); SET @upload_successful = 'N'; • During synchronization • sp_hook_dbmlsync_upload_end • Only point where we can determine if the upload was successful • It is always called if an upload was started

Request Queue Implementation • sp_hook_dbmlsync_upload_end() • Used to set the @upload_successful variable IF EXISTS( SELECT 1 FROM #hook_dict WHERE name = 'upload status' AND value = 'committed' ) THEN SET @upload_successful = 'Y'; END IF;

Request Queue Test • Now lets test the request queue • Insert the following at the consolidated • A message that should be displayed at the remote • A row that should be inserted • A row that will cause a duplicate primary key • Ensure the error is correctly reported

Minor Schema Upgrades • There are times when schema changes are required to solve a problem • Schema changes are difficult in a distributed environment • There are many different ways to deploy schema changes • This one is based on the request queue • This session focuses on deployment and upgrades • M405 - Upgrading your SQL Anywhere Applications

Schema Change Challenges • Concurrency • Schema changes cannot be deployed to all users at the exact same time • Fact of life, not a software limitation • MobiLink must be able to synchronize users that use both the new and old schema • Failed schema upgrades • If the schema upgrade fails half way through, manual intervention may be required

MobiLink Schema Challenges • The remote sends changes to MobiLink based on the transaction log • You cannot modify the schema in a REMOTE database if there are any outstanding changes to be uploaded • Must ensure, a successful upload has happened before attempting any schema upgrades • A new MobiLink script version should be added • V1.0 scripts • V1.1 scripts • Allows for concurrent versions

Schema Changes • Create the schema upgrade requests • Alter the table, OP_CODE - 2 means execute this statement CALL sp_create_request( 'sales', 2, 'ALTER TABLE proposal ADD v2_col CHAR(30) ' ); • Now, alter the subscription for the new script version CALL sp_create_request( 'sales', 2, 'ALTER SYNCHRONIZATION SUBSCRIPTION TO "routing" FOR "sales" MODIFY OPTION sv=''v2.0'' ' );

Wait for a Synchronization • It is possible other changes are occurring in the request queue to prepare for a schema upgrade • Remember, there can be no operations against any of the tables we are altering after a synchronization • Need a way to stop processing the request queue until a synchronization has occurred • Op_code = 4 • Force a synchronization

Forced Synchronization • New request and op_code • CALL sp_create_request( 'sales', 4, 'Force a synchronization' ); • This op_code will instruct the stored procedure which processes the request queue to stop processing any requests until a synchronization has completed • It will LOCK all tables and drop all current connections to the database

sp_process_request_queue() • Needs to handle the new op_code WHEN 4 THEN SET cmd_status = 'WAITING'; UPDATE request_queue SET status = cmd_status WHERE CURRENT OF process_cmd; LEAVE cmd_loop; • Sets the status to “WAITING” and exits

sp_remove_wait_request() • New procedure to remove any forced synchronization request • Must be processed AFTER a successful upload • We can use the existing sp_hook_dbmlsync_end() to call the stored procedure • It is already ensuring the upload was successful before processing the request queue • Only check the FIRST row in the request_queue • If it is an op_code of 4, mark it as PROCESSED

sp_dbmlsync_hook_begin/end() • sp_hook_dbmlsync_begin() CALL sp_check_to_lock_tables_for_upgrade(); • sp_hook_dbmlsync_end() • Modified to remove any wait requests IF( @upload_successful = 'Y' ) THEN CALL sp_remove_wait_request(); CALL sp_process_request_queue(); END IF;

Request Queue Test • Now lets test the request queue • Insert the following at the consolidated • A message that should be displayed • A message to “force” a synchronization • Add a new column to the Proposal table • Modify the Subscription to change the script version • Insert a new record that uses the new column • Synchronize • Make sure it all works and the consolidated gets the change

Managing Deletes • Removing rows from remotes is one of the last things you consider • Often confuses people • Rows can be deleted from the remote two different ways • The remote can delete the row directly • MobiLink can tell the remote to delete a row

Deleting Rows Directly • If a remote database uses a DELETE statement • The information is logged • Therefore the next time the remote synchronizes a DELETE will be uploaded to MobiLink • MobiLink will delete the row from the consolidated database

Removing Unwanted Rows • In many cases you will want the rows removed from the remote, but NOT from the consolidated • This helps control the size of the remote database • Only keep a months worth of data • No longer interested in a certain customer’s orders • In these cases, the data is still valid on the consolidated, but not on the remote • So we must delete the rows in a different way

STOP SYNC DELETE • You can still have the remote database delete the unwanted rows • You must use a special command STOP SYNCHRONIZATION DELETE; DELETE FROM orders WHERE customer_name = ‘No longer needed’; START SYNCHRONIZATION DELETE; COMMIT; • This allows the remote to delete the rows, but will NOT upload them to MobiLink

Download_delete_cursor • MobiLink has a special table level event to remove rows from the remote • Download_delete_cursor SELECT order_id FROM ULOrder WHERE status = ‘Approved’ • You only select the primary key of the table • Since the event ends in “_cursor” you know it is a SELECT statement

M413 MobiLink Design Best Practices