IBM DB2 LUW consolidated database

MobiLink supports IBM DB2 LUW for Linux, Unix, and Windows. MobiLink does not support IBM DB2 for AS/400.

Setting up DB2 LUW as a consolidated database

To set up DB2 to work as a MobiLink consolidated database, you must run a setup procedure that adds MobiLink system tables, stored procedures, triggers, and views that are required for MobiLink synchronization. There are multiple ways you can do this:

  • Run the syncdb2.sql setup script, located in install-dir\MobiLink\setup. Before running the file, you must copy it to another location and modify it. Instructions follow.

  • In the MobiLink plug-in for Sybase Central, choose Mode » Admin and connect to your server database. Right-click the database name and choose Check MobiLink System Setup. If your database requires setup, you are prompted to continue. Note that you are required to perform step 1 of the following procedure.

  • When you use the Create Synchronization Model Wizard or Deploy Synchronization Model Wizard, system setup is checked when you connect to your server database. If your database requires setup, you are prompted to continue. Note that you are required to perform step 1 of the following procedure.

Note

The database user who runs the setup script is the only user who has permission to change the MobiLink system tables, which is required for configuring MobiLink applications. See Required permissions.

The RDBMS user that the MobiLink server uses to connect to the consolidated database must be able to able to use the MobiLink system tables, procedures, and so on, without any qualifiers (for example, SELECT * from ml_user). See MobiLink server system tables.

To run the DB2 setup script

  1. To install MobiLink system tables using the setup script, an IBM DB2 LUW tablespace must use a minimum of 8 KB pages. If a tablespace 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 tablespace and temporary tablespace that use the buffer pool with 8 KB pages.

      For more information, consult your DB2 LUW documentation.

  2. Customize syncdb2.sql with your connection information:

    1. Copy syncdb2.sql to a new location where it can be modified and stored.

    2. The syncdb2.sql script contains a default connection statement, connect to DB2Database. Alter this line to connect to your DB2 database. Use the following syntax: 

      connect to DB2Database user userid using password ~

      where DB2Database, userid, and password are names you provide. (The syncdb2.sql script uses the tilde character (~) as a command delimiter.)

  3. Run syncdb2.sql: 

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

You must set up an ODBC DSN for your DB2 consolidated database using the ODBC driver that is provided with your DB2 database. See:

DB2 LUW issues

  • Tablespace capacity   A tablespace and temporary tablespace of any DB2 LUW database that you want to use as a consolidated database must use 8 KB pages.

    In addition, there are columns that require a LONG tablespace. If there is no default LONG tablespace, the creation statements for the tables containing these columns must be qualified appropriately, as in the following example: 

    CREATE TABLE ... ( ... )
IN tablespace
LONG IN long-tablespace

    For an example using the sample application, see Exploring the CustDB sample for MobiLink.

  • Session-wide variables   DB2 LUW prior to version 8 does not support session-wide variables. A convenient solution is to use a base table with columns for the MobiLink user name and other session data. The base table has rows representing concurrent synchronizations.

  • User-defined procedures   DB2 LUW prior to version 8.2 requires that you compile SQL procedures into an executable library (such as a DLL). The resulting DLL/shared library must be copied to a special directory on the server. Note that you can write procedures using several different languages, including C/C++ and Java, among others.

    For more information about Java and .NET synchronization scripts, see:

  • CHAR columns   In IBM DB2 LUW, CHAR data types are fixed length and blank-padded to the full length of the string. In MobiLink remote databases (SQL Anywhere or UltraLite) CHAR is the same as VARCHAR: values are not blank-padded to a fixed width. It is strongly recommended that you use VARCHAR in the consolidated database rather than CHAR. If you must use CHAR, the mlsrv11 -b command line option can be used to remove trailing blanks from strings during synchronization. This option is important for string comparisons used to detect conflicts.

    See -b option.

  • Data type mapping   The data types of columns must map correctly between your consolidated and remote database. For details, see IBM DB2 LUW data mapping.

  • Double up the quotation marks in system procedure calls   When you use a MobiLink system procedure to add scripts to your DB2 consolidated database, you need to double up the quotation marks. For example, if the script you are adding with ml_add_table_script includes the line SET "DELETED"=''Y'' for any other consolidated database, for DB2 you would have to write this as SET "DELETED" = ''''Y''''.

  • Special considerations for version 5 and earlier   If you are using IBM DB2 LUW prior to version 6, column names and other identifiers are only supported up to 18 characters. This means that you must truncate the names of MobiLink system procedures. For example, to call ml_add_connection_script, use the name ml_add_connection_.

Isolation level

See MobiLink isolation levels.

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1