slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
M413 MobiLink Design Best Practices PowerPoint Presentation
Download Presentation
M413 MobiLink Design Best Practices

M413 MobiLink Design Best Practices

220 Views Download Presentation
Download Presentation

M413 MobiLink Design Best Practices

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. M413MobiLink Design Best Practices • David Fishburn • Principal Consultant • iAnywhere Solutions •

  2. 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

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

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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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’

  12. 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

  13. 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

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

  15. 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

  16. 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.

  17. 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

  18. 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

  19. 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

  20. 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 = ?

  21. 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

  22. 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'

  23. 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

  24. 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

  25. 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()

  26. 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

  27. 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

  28. 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

  29. 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';

  30. 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()

  31. 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

  32. 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;

  33. 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

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

  35. 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

  36. 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

  37. 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

  38. 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'' ' );

  39. 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

  40. 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

  41. 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

  42. 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

  43. 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;

  44. 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

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

  46. 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

  47. 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

  48. 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

  49. 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

  50. 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