Db2

You can create the FTM database on Db2®.

Use the instructions for the platform where the database is to be created.

Database on UNIX or the Windows operating system

  1. The scripts for Db2 that are provided by the FTM installation are shown in the following table. These scripts must be installed on the Db2 server.
    Table 1. Db2 database scripts
    File name Description
    MakeCoreDB.cmd Main Windows script to create the database. This script is optional and is contained in FXH_Core_Database_DB2_LUW.zip.
    MakeCoreDB.sh Main UNIX script to create the database.
    00-CreateDB-Unix.sh UNIX script to create the database.
    00-CreateDB-Win.cmd Windows script to create the database. This script is optional and is contained in FXH_Core_Database_DB2_LUW.zip.
    01-FTMDB.ddl The DDL for tables.
    02-NewID.sql The DDL for sequences, procedures, and functions.
    Note: This script can be configured regarding ID management settings - Generation and Caching, Instance, and Partition support. For more information, see ID Management.
    02a-TimestampFunctions.sql Timestamp and time zone functions.
    03-ObjTrig.sql The DDL for triggers.
    04-Views.sql The DDL for views.
    05-Indices.sql The DDL for indexes.
    06-UpViewPt.ddl The view definition for transmission objects. This view definition can be updated.
    07-UpViewTxn.ddl The view definition for transaction objects. This view definition can be updated.
    08-UpViewPay.ddl The view definition for payment objects. This view definition can be updated.
    09-UpViewSec.ddl The view definition for securities objects. This view definition can be updated.
    10-UpViewBat.ddl The view definition for batch objects. This view definition can be updated.
    11-UpViewFrag.ddl The view definition for fragment objects. This view definition can be updated.
    12-UpViewActivity.ddl The view definition for activity objects. This view definition can be updated.
    13-ViewSvcPart.ddl The DDL for service participant views.
    14-ViewsSchedTask.ddl The DDL for scheduler task views.
    15-ConfigSPs.sql The DDL for stored procedures.
    16-HistTrigs.sql The DDL for triggers.
    17-ConfigIDGen.sql The ID generation value table entries.
    Note: This script can be configured regarding ID management settings - Generation and Caching, Instance, and Partition support. For more information, see ID Management.
    18-ViewDefns.sql The view definition value table entries.
    19-Config.sql VALUE table entries for configuration.
    Edit one of the following script files to include the correct table space definitions, security settings, buffer pool locations, hostname settings, and other settings:
    On UNIX
    00-CreateDB-Unix.sh
    On the Windows operating system
    00-CreateDB-Win.cmd
    The following table shows some of the parameters that are set in these script files.
    Parameter type Parameter Default value Description
    General DB_LOCATION "/db2space/FTM/$1" The path to the disk area where the database is created. The $1 refers to the database name (as passed as a parameter to the database creation file). The base path (without the value of $1) must exist for the database to be created successfully. A warning is given if the full path (including the value of $1) exists.
    TERRITORY IE The ISO code for the country that is used as the TERRITORY parameter when the Db2 instance is being created. Ensure that this parameter is set correctly for your environment.
    DB_USER wbiserv Change this parameter to the Db2 user who is creating the Db2 instance.
    DBHOST localhost Server that Db2 instance is running on.
    TCPIP_SVC db2c_db29ins1 To determine TCPIP_SVC, use db2 get dbm cfg and look for TCP/IP Service name (SVCENAME)
    BASE_CONTAINER_PATH "/db2space/FTM/db29ins1/$1/" The path to the disk area where the database container files are created.
    Lock LOCKLIST 20000 This value avoids lock escalation from row to table, which can lead to deadlocks.
    MAXLOCKS 50  
    Cursor CUR_COMMIT DISABLED This value maintains compatibility with previous versions of Db2.
    Memory parameters APPLHEAPSZ 2048  
    APP_CTL_HEAP_SZ 1024  
    SORTHEAP 512  
    Log file parameters LOGBUFSZ 3200  
    LOGFILSIZ 10000  
    LOGPRIMARY 20 As the primary log files are preallocated, you require 800 MB of disk space to accommodate them at installation time. Each log file requires 40 MB.
    LOGSECOND 20  
    Note: Many of these settings are different from the standard Db2 defaults. Modify them as needed. In 00-CreateDB-Unix.sh, several table space containers are defined. If you use this file, modify the paths to these containers as necessary. When you modify any paths, ensure that these paths exist. The default parameters also include minimum table space sizes to limit the size of the database. These defaults need to be revised to ensure that the table space configuration meets the needs of your system. If this system is a production or test system with large volumes, you might also need to change the table space definitions to auto extend as needed.
  2. Ensure that all the SQL files (that is, files with the extension .sql) that are needed to run the following script files are available:
    On UNIX
    MakeCoreDB.sh
    On the Windows operating system
    MakeCoreDB.cmd
  3. Before any of the UNIX shell scripts (.sh) for setup are run, it might be necessary to grant execute privileges to these files by using the chmod command.
  4. A user that has Db2 administrator privileges must run the scripts. The user must also have read and write access to the directories that are to be used by the Db2 scripts to create the database.
  5. Run the MakeCoreDB.sh (on UNIX) or MakeCoreDB.cmd (on Windows) script. Direct the output to a log file, for example, FTMDB.log. This log file is used for installation verification. The parameters to the scripts are the database name and the schema. The following example shows a script that creates the database FTMDB, the schema FTM, and directs the output of the database creation process to the file FTMDB.log.
    MakeCoreDB.sh FTMDB FTM > FTMDB.log

Database on z/OS®

  1. Run the Db2 DDL files in your runtime environment in either of the following ways:
    Run @FTMCJCL
    @FTMCJCL is sample JCL that runs the DDL files that create the FTM database:
    1. Adapt its job card to your system requirements.
    2. You might want to run the SYSIN members one at a time so that you can verify the results before you run the next member. If you do want to run the members one at a time, comment out the lines that are not to be run.
    3. Submit @FTMCJCL until all SYSIN members are run.
    Run the Db2 DDL files by using SPUFI or a similar tool
    Use SPUFI or a similar tool to run the DDL scripts in the same order as they are in @FTMCJCL.
  2. Submit member @FTMDJCL to create the FTM stored procedures.
  3. Member FTMXGR01 contains sample GRANT statements for the database objects that were created in the previous sections. Customize these statements to suit your system requirements.
  4. Use the sample JCL member @FTMGJCL, SPUFI, or a similar tool to run FTMXGR01.
  5. Check whether the configuration data scripts left the database in the check pending (CHECKP) state. If so, run the Db2 CHECK utility. This utility resets the flag on the affected tables. A sample is provided in member @DB20CHK.
  6. Immediately after the database is loaded with initial data, run the Db2 REORG and RUNSTATS utilities to improve query performance.