Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Setting up the CustDB consolidated database

The CustDB consolidated database may be SQL Anywhere, Sybase Adaptive Server Enterprise, Microsoft SQL Server, Oracle, or IBM DB2.

A SQL Anywhere CustDB consolidated database is provided in samples-dir\UltraLite\CustDB\custdb.db. A DSN called SQL Anywhere 10 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.

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

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, Oracle, or SQL Server)

1. Create a database in your RDBMS.

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

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

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

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

3. Add sample user tables to the CustDB database by running one of the following SQL scripts, located in the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere 10 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 > SQL Anywhere 10 > SQL 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.

To set up a consolidated database (IBM DB2)

1. Create a DB2 database on the DB2 server. For the purposes of this tutorial, call it CustDB.

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

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

   • Change the connect command at the top of the file syncdb2long.sql. Replace DB2Database with the name of your DB2 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 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

4. In order for DB2 to use the stored procedures defined in syncdb2long.sql, you must copy the syncdb2long_version Java and class files located in the MobiLink\setup subdirectory of your SQL Anywhere installation to the FUNCTION subdirectory of your DB2 installation.

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

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

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

   • Start the ODBC Administrator:

     From the Start menu, choose Programs > SQL Anywhere 10 > SQL Anywhere > ODBC Administrator.

     The ODBC Data Source Administrator appears.

   • On the User DSN tab, click Add.

     The Create New Data Source dialog appears.

   • Select the ODBC driver for your DB2 database. For example, choose IBM DB2 UDB ODBC Driver. Click Finish.

     For information about how to configure your DB2 ODBC driver, see:

     • Your DB2 documentation
     •  http://www.ianywhere.com/developer/technotes/odbc_mobilink.html

8. Run the custdb2setuplong Java application on the DB2 client machine as follows. 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

• IBM DB2 UDB consolidated database