Setting up DB2 mainframe as a consolidated database

To set up DB2 mainframe 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. You can perform this task using SQL or JCL methods.

Common steps to set up the DB2 mainframe environment

1. Create a buffer pool with a page size of 8K or greater and row-level locking for MobiLink schema. Row-level locking is required to handle concurrent synchronizations to the same tables. For this example, name the buffer pool BP8K.

2. Create a tablespace named MLTB8K with an 8K page size buffer pool for MobiLink schema. For example:

   create tablespace MLTB8K in IANY bufferpool BP8K locksize row grant use of tablespace IANY.MLTB8K to public

3. If you do not already have one, create a Workload Manager environment for MobiLink schema procedures and name is something like MLWLM.

4. Set up an ODBC DSN for your DB2 mainframe consolidated database using the ODBC driver that is provided with your DB2 database. See:

   •  Recommended ODBC Drivers for MobiLink
   • IBM DB2 mainframe documentation

To create MobiLink system tables using SQL

1. Set up the DB2 mainframe environment using the common steps listed in Setting up DB2 mainframe as a consolidated database.

2. Modify the syncd2m.sql setup script, located in install-dir\MobiLink\setup.

   Note

   Be sure to make a backup copy of the original syncd2m.sql file before you proceed.

   In the syncd2m.sql file, replace all occurrences of

   • {MLTABLESPACE} with MLTB8K, the name of the tablespace.

   • {WLMENV} with MLWLM, the Workload Manager.

3. Run the syncd2m.sql setup script using the following command line:

   dbisql -c "uid=user-id;pwd=password;DSN=dsn-name" -nogui syncd2m.sql

   The message log file, syncd2m.txt, gets generated.

4. Open syncd2m.txt to verify that the DSNTPSMP calls succeeded.

To create the MobiLink system tables using JCL

1. Set up the DB2 mainframe environment using the common steps listed in Setting up DB2 mainframe as a consolidated database.

2. Modify the syncd2m_jcl.sql script, located in install-dir\MobiLink\setup.

   Note

   Be sure to make a backup copy of the original syncd2m_jcl.sql file before you proceed.

   In the syncd2m_jcl.sql file, replace all occurrences of

   • {MLTABLESPACE} with your qualified tablespace, for example MYDB.MYTS.

   • {WLMENV} with the name of a Workload Manager associated with your DB2 instance.

3. Start DBISQL and connect to DB2 mainframe.

4. Run the edited copy of the syncd2m_jcl.sql setup script, located in install-dir\MobiLink\setup, to create Mobilink tables and define Mobilink procedures in the DB2 mainframe.

5. From the %SQLANY%\MobiLink\setup directory, FTP to your mainframe and run the following commands:

   bin hash cd xmit quote site recfm=fb lrecl=80 quote site cyl put d2mload.xmit put d2mdbrm.xmit quit

6. The two xmit files on the mainframe are as follows:

   • USERID.XMIT.D2MLOAD.XMIT

   • USERID.XMIT.D2MDBRM.XMIT

   USERID is the username you gave when connecting via FTP.

7. Open a terminal session and run the following commands from the ISPF Command Shell:

   RECEIVE INDATASET('USERID.XMIT.D2MLOAD.XMIT') RECEIVE INDATASET('USERID.XMIT.D2MDBRM.XMIT')

   The output is as follows:

   • USERID.ML.LOADLIB

   • USERID.ML.DBRMLIB

8. Copy the d2mrelod.jcl file and modify it as follows:

   • Change USERID to your mainframe userid.

   • Change DSNDB0T to your DB2 DSN.

9. Run the edited copy of the d2mrelod.jcl script, located in install-dir\MobiLink\setup.

10. Copy the d2mbdpk.jcl file and modify it as follows:

    • Change USERID to your mainframe userid.

    • Change DB0T to your DB2 SSID.

11. Bind all SQL procedures by running the edited copy of d2mbdpk.jcl. The following is a reference of SQL procedure mappings to load module names.

    Procedure name	Load module name
    ml_add_user	mlaub
    ml_delete_user	mldub
    ml_del_sstate	mldssb
    ml_reset_sstate	mlrssb
    ml_del_sstate_b4	mldssbb
    ml_add_lcs_chk	mlalcscb
    ml_add_lcs	mlalcsb
    ml_add_cs	mlacsb
    ml_add_jcs	mlajcsb
    ml_add_dcs	mladcsb
    ml_add_lts_chk	mlaltscb
    ml_add_lts	mlaltsb
    ml_add_ts	mlatsb
    ml_add_jts	mlajtsb
    ml_add_dts	mladtsb
    ml_add_property	mlapb
    ml_add_column	mlacb
    ml_set_device	mlsdb
    ml_set_device_nt	mlsdnb
    ml_set_dev_addr	mlsdab
    ml_set_dev_addr_int	mlsdanb
    ml_set_listening	mlslb
    ml_set_listen_nt	mlslnb
    ml_set_sis_sstate	mlssssb
    ml_del_dev_addr	mlddab
    ml_del_listen	mldlb
    ml_delete_device	mlddb

DB2 mainframe known issues

Isolation level

See MobiLink isolation levels.