Lesson 1: Creating and configuring the consolidated database

In this lesson, you set up a consolidated database for synchronization.

 Create and configure a consolidated database
  1. Run the following commands to create and start a consolidated database.

    md c:\cons
    cd c:\cons
    dbinit consol.db
    dbeng12 consol.db
  2. Run the following command to define an ODBC data source for the consolidated database:

    dbdsn -w dsn_consol -y -c "UID=DBA;PWD=sql;DBF=consol.db;SERVER=consol"
  3. To use a database as a consolidated database, you must run a setup script that adds system tables, views, and stored procedures that are used by MobiLink. The following command sets up consol.db as a consolidated database.

    dbisql -c "DSN=dsn_consol" %SQLANY12%\MobiLink\setup\syncsa.sql
  4. Open Interactive SQL and connect to consol.db using the dsn_consol DSN.

    dbisql -c "DSN=dsn_consol"
  5. Execute the following SQL statements in Interactive SQL. They create the customer table on the consolidated database and create the required synchronization scripts.



    CREATE TABLE customer (
       id      unsigned integer primary key,
       name    varchar( 256),
       phone   varchar( 12 )
    );
    
    CALL ml_add_column('my_ver1', 'customer', 'id', null );
    CALL ml_add_column('my_ver1', 'customer', 'name', null );
    CALL ml_add_column('my_ver1', 'customer', 'phone', null );
    
    CALL ml_add_table_script( 'my_ver1', 'customer', 'upload_insert',
          'INSERT INTO customer ( id, name, phone ) ' 
          || 'VALUES ({ml r.id}, {ml r.name}, {ml r.phone} )' );
    
    CALL ml_add_table_script( 'my_ver1', 'customer', 'download_cursor',
           'SELECT id, name, phone from customer' );
    
    CALL ml_add_table_script( 'my_ver1', 'customer', 'download_delete_cursor', '--{ml_ignore}' );
    COMMIT;
    

    After you have executed the SQL statements, leave Interactive SQL running and connected to the database as you will be executing more SQL against the database as you work through the tutorial.

  6. Start the MobiLink server by running the following command:

    start mlsrv12 -c "DSN=dsn_consol" -v+ -ot mlsrv.txt -zu+
  7. Proceed to Lesson 2: Creating and configuring the remote database.