IBM DB2 mainframe consolidated database

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 named BP8K with an 8K page size and row-level locking for MobiLink schema. Row-level locking is required to handle concurrent synchronizations to the same tables.

  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. Create a Workload Manager environment for MobiLink schema procedures named 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:

To create MobiLink system tables using SQL

Note

The SQL method requires the ability to create stored procedures using DSNTPSMP. If you do not have SQL stored procedures enabled, use the JCL technique.

  1. Set up the DB2 mainframe environment using the common steps listed above.

  2. Modify the syncd2m.sql setup script, located in the MobiLink\setup subdirectory of your SQL Anywhere installation.

    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 output 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 above.

  2. Run the syncd2m_jcl.sql setup script, located in MobiLink\setup subdirectory of your SQL Anywhere installation, to create Mobilink tables and define Mobilink procedures in the DB2 mainframe.

  3. Move the compiled SQL load modules to the correct DB2 subsystem load library. The compiled SQL load module files are packaged into a tso xmit file called d2mload.xmit. The file must have RECFM=FB and LRECL=80 defined.

    The transmit function converts the load module to a FB 80 bytes recodes. Once on the mainframe, a receive function must be performed to convert the xmit file back to a load module.

  4. Move the transmit DBRM library, d2mdbrm.xmit, to the mainframe.

  5. Bind all SQL procedures using d2mbdpk.jcl. 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

  • DB2 mainframe does not work with Model mode   You can not use DB2 mainframe as your consolidated database when you use the Create Synchronization Model Wizard.

  • SELECT statements require the FOR READ ONLY clause   SELECT statements in DB2 mainframe are opened for update by default, meaning that the database acquires write locks with the anticipation of an UPDATE statement after the SELECT statement.

    To avoid the write locks and enhance concurrency, append FOR READ ONLY on all SELECT statements that do not precede UPDATE statements. Use FOR READ ONLY in SELECT statements as often as possible, specifically in the download_cursor and download_delete_cursor scripts.

  • Sysplex requires time synchronization   When the DB2 mainframe consolidated database is running in a Sysplex, the clocks of all LPARs in the Sysplex must be synchronized. Failure to synchronize the clocks could result in lost data during database synchronization.

  • Numbers are approximated   Approximate numbers have different possible values. Following is a table of examples.

    Type Entered value DB2 mainframe value ASA value

    Real

    123.456

    123.4559936523

    123.4560012817

    Float

    123.456

    123.45599999999999

    123.4560012817

    Double

    123.456

    123.45599999999999

    123.456

    The recommended approach is to avoid synchronization of double and floating point columns with a DB2 mainframe consolidated database.

Isolation level

See MobiLink isolation levels.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0