IBM DB2 LUW consolidated database

Before running the setup script, you should be aware of the following requirements:

The database user who runs the setup script is expected to be the same one used to update the MobiLink system tables during synchronization. This user must be used to start the MobiLink server and to configure MobiLink applications. See Required permissions.
The RDBMS user that the MobiLink server uses to connect to the consolidated database must be 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 set up IBM 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 %SQLANY12%\MobiLink\Setup. Before running the file, you must copy it to another location and modify it. Instructions follow.
Check and update the MobiLink system setup from Sybase Central. See MobiLink system setup.

Run the IBM DB2 setup script

To install MobiLink system tables using the setup script, the targeted 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 IBM DB2 LUW documentation.

Customize syncdb2.sql with your connection information:

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

The syncdb2.sql script contains a default connection statement, connect to DB2Database. Alter this line to connect to your IBM 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.)

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

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

http://www.sybase.com/detail?id=1011880
IBM DB2 LUW documentation

Lock escalation In order to maintain data consistency between the consolidated and remote databases, the MobiLink server issues the following query through the ml_lock_rid stored procedure to lock the remote ID in every synchronization phase.

SELECT sync_key into p_sync_key FROM ml_database WHERE rid = a_given_remote_id WITH RR USE AND KEEP EXCLUSIVE LOCKS;

This query exclusively locks the remote ID to prevent any concurrent synchronizations using the same remote ID.

If you experience any MobiLink remote ID locking errors when there are no concurrent synchronizations for the same remote ID, for example, if MobiLink error code -10341 is in the MobiLink server log, you may need to adjust the DB2 maxlocks and locklist configuration parameters to prevent lock escalation. Consult the DB2 documentation for full details.

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.
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 mlsrv12 -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 mlsrv12 option.

Tablespace capacity A tablespace and temporary tablespace of any IBM DB2 LUW database that you want to use as a consolidated database must use at least 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 CustDB sample for MobiLink.

Session-wide variables IBM DB2 LUW before 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 IBM DB2 LUW before 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:
- Synchronization script writing in Java
- Synchronization script writing in .NET
Double up the quotation marks in system procedure calls When you use a MobiLink system procedure to add scripts to your IBM 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 IBM DB2 you would have to write this as SET "DELETED" = ''''Y''''.

See MobiLink isolation levels.

IBM DB2 LUW consolidated database

Permissions

Setting up IBM DB2 LUW as a consolidated database

Run the IBM DB2 setup script

ODBC driver

IBM DB2 LUW considerations

Isolation level