Setting up the CustDB consolidated database

The CustDB consolidated database can be any MobiLink supported consolidated database.

SQL Anywhere CustDB

A SQL Anywhere CustDB consolidated database is provided in samples-dir\UltraLite\CustDB\custdb.db. A DSN called SQL Anywhere 11 CustDB is included with your installation.

You can rebuild this database using the file samples-dir\UltraLite\CustDB\newdb.bat.

If you want to explore the way the CustDB sample is created, you can view the file samples-dir\MobiLink\CustDB\syncsa.sql.

CustDB for other RDBMSs

The following SQL scripts are provided in samples-dir\MobiLink\CustDB to build the CustDB consolidated database as any one of these supported RDBMSs:

RDBMS	Custdb setup script
Adaptive Server Enterprise	custase.sql
SQL Server	custmss.sql
Oracle	custora.sql
DB2 LUW	custdb2.sql
MySQL	custmys.sql

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

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, DB2 LUW, MySQL, Oracle, SQL Server)

Create a database in your RDBMS.
Add the MobiLink system tables by running one of the following SQL scripts, located in the MobiLink\setup subdirectory of your SQL Anywhere 11 installation:
- For an Adaptive Server Enterprise consolidated database, run syncase.sql.
- For a MySQL consolidated database, run syncmys.sql.
- For an Oracle consolidated database, run syncora.sql.
- For a SQL Server consolidated database, run syncmss.sql.
Add sample user tables to the CustDB database by running one of the following SQL scripts, located in samples-dir\MobiLink\CustDB:
- For an Adaptive Server Enterprise consolidated database, run custase.sql.
- For a MySQL consolidated database, run custmys.sql.
- For an Oracle consolidated database, run custora.sql.
- For a SQL Server consolidated database, run custmss.sql.
Create an ODBC data source called CustDB that references your database on the client machine.
- Choose Start » Programs » SQL Anywhere 11 » 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.

To set up a consolidated database (DB2 LUW)

Create a consolidated database on the DB2 LUW server. For the purposes of this tutorial, call it CustDB.
Ensure that the default table space (usually called USERSPACE1) uses 8 KB pages.

If the default table space does not use 8 KB pages, complete the following steps:
- Verify that at least one of your buffer pools has 8 KB pages. If not, create a buffer pool with 8 KB pages.
- Create a new table space and temporary table space with 8 KB pages.
  For more information, consult your DB2 LUW documentation.

Add the MobiLink system tables to the DB2 LUW consolidated database using the file MobiLink\setup\syncdb2.sql:

Change the connect command at the top of the file syncdb2.sql. Replace DB2Database with the name of your database (or its alias). In this example, the database is called CustDB. You can also add your DB2 user name and password as follows:

connect to CustDB user userid using password ~

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

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

Copy custdb2.class, located in the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere installation, to the SQLLIB\FUNCTION directory on your DB2 LUW server. This class contains procedures used for the CustDB sample.

Add data 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

Create an ODBC data source called CustDB that references the DB2 LUW database on the DB2 LUW client.
- Start the ODBC Administrator:
  From the Start menu, choose Programs » SQL Anywhere 11 » ODBC Administrator.
  
  The ODBC Data Source Administrator appears.
- On the User DSN tab, click Add.
  The Create New Data Source window appears.
- Select the ODBC driver for your DB2 LUW database. For example, choose IBM DB2 UDB ODBC Driver. Click Finish.
  For information about how to configure your ODBC driver, see:
  - Your DB2 LUW documentation
  - Recommended ODBC Drivers for MobiLink

Run the custdb2setuplong Java application on the DB2 LUW client as follows. This application resets the CustDB example in the database. After the initial setup, you can run this application at any time to reset the 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