Lesson 1: Set up the SQL Anywhere consolidated database

This lesson guides you through the following steps to set up your SQL Anywhere consolidated database:

Create the consolidated database and schema.
Run the MobiLink setup script.
Define an ODBC data source for the consolidated database.

Create the consolidated database

In this procedure, you create the consolidated database using the Sybase Central Create Database Wizard.

To create your SQL Anywhere database

Choose Start » Programs » SQL Anywhere 11 » Sybase Central.
Choose Tools » SQL Anywhere 11 » Create Database.
On the Welcome page, click Next.
Click Next.
In the Save The Main Database File To The Following File field, type C:\MLmon\cons.db. Click Next.
Follow the instructions in the Create Database Wizard and accept the default values.
Click Finish.

Generate the consolidated database schema

The consolidated database schema includes the Product table, storing the name and quantity of hardware products.

To add the Product table to the consolidated schema

In the left pane of Sybase Central, right-click cons - DBA and choose File » Open Interactive SQL.

Install the Product table.

Execute the following commands in Interactive SQL.

/* the Product table */
create table Product (
    name    varchar(128) not null primary key,
    quantity      integer,
    last_modified timestamp default timestamp
)
go

insert into Product(name, quantity)
  values ( 'Screwmaster Drill', 10);

insert into Product(name, quantity)
  values ( 'Drywall Screws 10lb', 30);

insert into Product(name, quantity)
  values ( 'Putty Knife x25', 12);

go

Install temporary tables used for conflict resolution.

You write scripts that insert values into these tables when a conflict occurs in Lesson 6: Creating scripts for conflict detection and resolution.

/* the Product_old table */
create table Product_old (
    name    varchar(128) not null primary key,
    quantity      integer,
    last_modified timestamp default timestamp
)
go

/* the Product_new table */
create table Product_new (
    name    varchar(128) not null primary key,
    quantity      integer,
    last_modified timestamp default timestamp
)
go

Verify the successful creation of each table.
- For example, execute the following command in Interactive SQL to verify the contents of the Product table.
  select * from Product
Add the system objects that MobiLink requires by running the MobiLink setup script.
- In the SQL Statements pane, type:
read "C:\Program Files\SQL Anywhere 11\MobiLink\setup\syncsa.sql"

Define an ODBC data source for the consolidated database.

Use the SQL Anywhere 11 driver to define an ODBC data source for the cons database.

To define an ODBC data source for the consolidated database

Choose Start » Programs » SQL Anywhere 11 » ODBC Administrator.
Click the User DSN tab, and click Add.
On the Name list, click SQL Anywhere 11. Click Finish.
In the ODBC Configuration For SQL Anywhere 11 window, do the following:
1. Click the ODBC tab.
2. In the Data Source Name field, type sa_cons.
3. Click the Login tab.
4. In the User ID field, type DBA.
5. In the Password field, type sql.
6. Click the Database tab.
7. In the Server Name field, type cons.
8. In the Database File field, type C:\MLmon\cons.db.
9. Click OK.
Click OK.