Prepare the runtime environment - connecting to the database

  1. In order to connect to IBM® DB2®, you must have the database driver "dbmdb2" in $FGLDIR/dbdrivers.
  2. If you want to connect to a remote DB2 server, the "IBM DB2 Client Application Enabler" must be installed and configured on the computer running the BDL applications.
    You must declare the data source set up as follows:
    1. Login as root.
      1. Create a user dedicated to the DB2 client instance environment, for example, "db2cli1".
      2. Create a client instance environment with the db2icrt tool as in following example: 
        # db2dir /instance/db2icrt -a server -s client instance-user
    2. Login as the instance user (the environment is set automatically, verify DB2DIR).
      1. Catalog the remote server node:
        # db2 "catalog tcpip node db2node remote hostname server tcp-service"
      2. Catalog the remote database:
        # db2 "catalog database datasource at node db2node authentication server"
      3. Test the connection to the remote database:
        # db2 "connect to datasource user dbuser using password"

        (where dbuser is a database user declared on the remote database server)

    See IBM DB2 documentation for more details.
  3. Important: If you have a non-English environment, you may need to set the PATCH2=15 configuration parameter in the db2cli.ini file, to ensure that DECIMAL values will be properly inserted or fetched: 
    [datasource]
PATCH2=15

    For more details, see the DB2 README.TXT file in the SQLLIB directory.

  4. Make sure that the DB2 client environment variables are properly set.
    Check variables such as DB2DIR (the path to the installation directory), DB2INSTANCE (the name of the DB2 instance), INSTHOME (the path to the home directory of the instance owner). On UNIX™, you will find environment settings in the file $INSTHOME/sqllib/db2profile. See IBM DB2 documentation for more details.
  5. Check the database client locale settings (DB2CODEPAGE, etc).
    The database client locale must match the locale used by the runtime system (LC_ALL, LANG). See CHAR and VARCHAR data types for more details.
  6. Verify the environment variable defining the search path for DB2 CLI database client shared libraries (libdb2.so on UNIX, DB2CLI.DLL on Windows®).
    Table 1. Shared library environment setting for DB2 version
    DB2 version Shared library environment setting
    DB2 11.1 and higher

    UNIX: Add $DB2DIR/lib32 (for 32 bit) or $DB2DIR/lib64 (for 64 bit) to LD_LIBRARY_PATH (or its equivalent).

    Windows: Add %DB2DIR%\bin to PATH.
  7. To verify if the DB2 client environment is correct, you can, for example, start the db2 command interpreter and connect to the server:
    $ db2
db2 => CONNECT TO dbname USER username USING password
  8. Setup the FGLPROFILE entries for database connections.
    1. Define the IBM DB2 database driver: 
      dbi.database.dbname.driver = "dbmdb2"
    2. The "source" parameter defines the name of the IBM DB2 database name. 
      dbi.database.dbname.source = "test1"
    3. Define the database schema selection if needed:

      Use the following entry to define the database schema to be used by the application. The database interface will automatically perform a SET SCHEMA name instruction to switch to a specific schema:

      dbi.database.dbname.db2.schema = 'name'

      Here dbname identifies the database name used in the BDL program ( DATABASE dbname ) and name is the schema name to be used in the SET SCHEMA instruction. If this entry is not defined, no "SET SCHEMA" instruction is executed and the current schema defaults to the user's name.

  9. Setup db2cli.ini to disable deferred prepare option, if PREPARE/DECLARE instructions must raise errors when the SQL statement is invalid: 
    DeferredPrepare = 0

    By default, DB2 CLI uses deferred prepare, which gives best performances but does not raise an SQL error with PREPARE/DECLARE in case of invalid SQL.