MobiLink Synchronization User's Guide
  The CustDB Sample Application
    Setup
      Setting up the CustDB consolidated database

Creating a consolidated database

The following procedures create a consolidated database for CustDB for each of the supported types of consolidated database.

For databases other than Adaptive Server Anywhere databases, you will first need to run a script to add the MobiLink system tables. This platform-specific script is located in the MobiLink\setup subdirectory of your SQL Anywhere 9 installation.

For more information about preparing a database for use as a consolidated database, see Setting up a consolidated database.

To set up a consolidated database (Adaptive Server Enterprise, Oracle or SQL Server)

1. Create the consolidated database.

2. Add the MobiLink system tables by running one of the following SQL scripts, located in the MobiLink\setup subdirectory of your SQL Anywhere 9 installation:

   • For an Adaptive Server Enterprise consolidated database prior to version 12.5, run syncase.sql. Otherwise, run syncase125.sql.

   • For an Oracle consolidated database, run syncora.sql.

   • For a SQL Server consolidated database, run syncmss.sql.

3. Add tables to the CustDB database by running one of the following SQL scripts, located in the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere 9 installation:

   • For an Adaptive Server Enterprise consolidated database, run custase.sql.

   • For an Oracle consolidated database, run custora.sql.

   • For a SQL Server consolidated database, run custmss.sql.

4. Create an ODBC data source called CustDB that references your database on the client machine.

   • Choose Start > Programs > Sybase SQL Anywhere 9 > Adaptive Server Anywhere > ODBC Administrator.

   • Click Add.

   • Select the appropriate driver from the list.

     Click Finish.

   • Name the ODBC data source CustDB.

   • Click the Login tab. Enter the user ID and password for your database. The default values are DBA and SQL.

   • Click the Database tab. Browse to the location of your database file.

To set up a consolidated database (Adaptive Server Anywhere)

1. Create the consolidated database:

   Navigate to the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere 9 installation and run the following command line:

   dbinit consol.db

2. Add tables to the CustDB database by running custdb.sql, located in the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere 9 installation.

   • Choose Start > Programs > Sybase SQL Anywhere 9 > Sybase Central.

   • In the right pane of Sybase Central, right-click Adaptive Server Anywhere 9 and connect to the consolidated database you have created. The default user ID and password are DBA and SQL.

   • In the right pane, right-click the consolidated database and select Open Interactive SQL from the popup menu.

   • In Interactive SQL, select File > Run Script. Browse to custdb.sql. Click Open.

   • Close Interactive SQL.

3. Create an ODBC data source called CustDB that references your database on the client machine.

   • In Sybase Central, select Tools > Adaptive Server Anywhere 9 > Open ODBC Administrator.

   • Click Add.

   • Select Adaptive Server Anywhere 9.0 Driver. Click Finish.

   • Name the ODBC data source CustDB.

   • Click the Login tab. Enter the user ID and password for your database. The default values are DBA and SQL.

   • Click the Database tab. Browse to the location of your database file.

To set up a consolidated database (IBM DB2)

1. Create a DB2 database on the DB2 server. Ensure that the default table space (usually called USERSPACE1) uses 9 Kb pages.

   If the default table space does not use 9 Kb pages, complete the following steps:

   • Delete the default table space, USERSPACE1.

   • Verify that at least one of your buffer pools has 9 Kb pages. If not, create a buffer pool with 9 Kb pages and restart the database to activate it.

   • Create a new table space with 9 Kb pages and name it USERSPACE1. For more information, consult your DB2 documentation.

2. Add the MobiLink system tables using the file MobiLink\setup\syncdb2long.sql. (If you are using a version of DB2 prior to 6.5, use syncdb2.sql.)

   • Change the connect command in syncdb2long.sql. Replace DB2Database with the name of your ODBC data source. In this example, the ODBC data source is CustDB. You could also add the user name and password as follows. Replace userid and password with your user name and password.

        connect to DB2Database user userid using password

   • Open a DB2 Command Window on either the server or client computer. Run syncdb2long.sql by typing the following command:

        db2 -c -ec -td~ +s -v -f syncdb2long.sql

3. Copy custdb2.class to the SQLLIB\FUNCTION directory on your DB2 server machine.

4. Add tables to the CustDB database:

   • If necessary, change the connect command in custdb2.sql. For example, you could add the user name and password as follows. Replace userid and password with your user name and password.

        connect to CustDB user userid using password

   • Open a DB2 Command Window on either the server or client computer. Run custdb2.sql by typing the following command:

        db2 -c -ec -td~ +s -v -f custdb2.sql

   • When processing is complete, enter the following command to close the command window:

        exit

5. Create an ODBC data source called CustDB that references the DB2 database on the DB2 client machine.

   • Choose Start > Programs > Sybase SQL Anywhere 9 > Adaptive Server Anywhere > ODBC Administrator.

   • Click Add.

   • Select IBM DB2 ODBC Driver. Click Finish.

   • Name the ODBC data source CustDB.

   • Click the Login tab. Enter the user ID and password for your database. The default values are DBA and SQL.

   • Click the Database tab. Browse to the location of your database file.

6. Run the custdb2setuplong Java application on the DB2 client machine as follows. If you are using a version of DB2 prior to 6.5, use custdb2setup. This application resets the CustDB example in the DB2 database. After the initial setup, you can run this application at any time to reset the DB2 CustDB database by typing the same command line.

   • If you use a name other than CustDB for the data source, you must modify the connection code in custdb2setuplong.java and recompile it as follows. If the path specified by the system variable %db2tempdir% contains spaces, you must enclose the path in quotation marks.

     javac -g -classpath %db2tempdir%\java\jdk\lib\classes.zip;
     %db2tempdir%\java\db2java.zip;
     %db2tempdir%\java\runtime.zip custdb2setuplong.java

   • Type the following, where userid and password are the user name and password for connecting to the CustDB ODBC data source.

     java custdb2setuplong userid password