Creation of an LDAS database

The steps described below are to be executed under database user ldasdb. Please visit IBM DB2 web site for details IBM DB2 web site for DB2 specifics.

Installation of IBM DB2

If DB2 software has not been installed, follow the directions in this link to install DB2 software. Otherwise skip this step.

Establish the correct include/library links for LDAS

The link /ldcg/IBMdb2 points to the current IBM installation include and library files to be used in compilation of LDAS software and running of utilities invoking db2 clients.

Any time a new IBMdb2 installation is performed, the headers and shared libraries in /ldcg/IBMdb2 must be updated to point to the new installation.

As user install, perform the following steps:

  1. Create a new directory under /ldcg/stow_pkgs: e.g. for IBMdb2 V8.2.1, 
    mkdir IBMdb2-8.2.1
mkdir IBMdb2-8.2.1/IBMdb2
mkdir IBMdb2-8.2.1/IBMdb2/include
mkdir IBMdb2-8.2.1/IBMdb2/lib

  2. Upload or copy ( if standalone box ) the include files from DB2 instance ldasdb sqllib/include to /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/include via sftp. e.g.

    cd  ~ldasdb/sqllib/include
sftp install@gateway
> cd /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/include
> puts *
or
cp ~ldasdb/sqllib/include/* /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/include

  3. Upload or copy (if standalone box ) the library files from DB2 instance ldasdb sqllib/lib to /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/lib via sftp. e.g.

    cd  ~ldasdb/sqllib/lib
sftp install@gateway
> cd /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/lib
> puts *
or
cp ~ldasdb/sqllib/lib/* /ldcg/stow_pkgs/IBMdb2-8.2.1/IBMdb2/lib

  4. Unstow the old installation if any and stow the new installation: 
    cd  /ldcg/stow_pkgs
stow -D IBMdb2-8.1.5
stow IBMdb2-8.2.1

Configuring for LDAS applications

The following modifications to the configuration parameters are recommended for running DB2 with LDAS system:

  1. Set database manager system grop to ldas so user ldas can create/drop databases or other administrative work. 
    
	db2 > UPDATE DBM CFG USING SYSTEM_GROUP LDAS
  2. Increase query heap size e.g. for a SUN server with 4 GB memory, we set it to 40000 4 KB pages but you should tailor this to your system's configuration. 
    
	db2 > UPDATE DBM CFG USING QUERY_HEAP_SZ 40000
  3. Increase size of the secondary log files for the database after a database has been created. e.g. increase to 100 
    
	db2 > update database configuration for <your database> using logsecond 100
  4. Set the size of locklist to 400 to avoid time out from obtaining a lock on the database 
    
	db2 > update database configuration for <your database> using locklist 400
    This command is done by createall script when it is used to create a new database.

Configuring for multithreaded applications

For platforms that can support multiple threads, the following is recommended in the IBM DB2 documentation to reduce contention and potential deadlocks in applications accessing the database:

  1. Setting environment variable DB2_FORCE_NLS_CACHE to TRUE will cause the code page and country code to be saved in cache the first time a thread accesses it. The cached information will be available for subsequent threads as long as the application does not change settings between connections.

  2. Have a time out value (in seconds) for database configuration parameter LOCKTIMEOUT instead of the default value of -1. If there is a deadlock execution can resume by timing out the transaction that is unable to request the database lock.

    You can set the database parameter via the db2 client: 

    db2 => update database configuration for ldas_tst using locktimeout 10

Start/Stop Database Manager

If the database manager has not been started yet, following the directions in this link for starting the database manager. Otherwise skip this step.

Make changes to an existing LDAS database

After you have created the database as described under Create database, check this link for steps to follow when making changes to an LDAS database.

Validate LDAS database functionality

If the IBM DB2 software has been updated or databases have been restored, you can follow these steps to validate LDAS database functionality is intact.

Create database

To create database tables in db2, create the directory for holding the database files by doing mkdir , then run db2 client utility db2 in command mode and issue the following:

db2 => create database <dbname> on <directory> alias <dbname> e.g.

mkdir /usr1/databases
(if the above directory does not exist ) 
db2 => create database dev_test on /usr1/databases alias dev_test

The sql and shell scripts mentioned below can be obtained by downloading the package db2-table-0.n.m.tar.gz where n and m are major and minor version numbers, e.g. db2-table-0.2.4. To unpack the package, use the command `gzip -dc db2-table-0.n.m.tar.gz | tar xf- '. This will create the sub-directory db2-table-0.n.m with the scripts in it.

Create LDAS tables

The two simplest ways to create all of the LDAS database tables are
  1. Use cmonClient: select LDAS control -> LDAS utilities -> create new database

  2. As user ldas, run the LDAS script /ldas/bin/createLDASdb <database alias> <database path> e.g.
    /ldas/bin/createLDASdb new_db /usr1/databases

  3. Use shell script createall <dbname>
    1. db2 'connect to <database-name> user <db-user> using <db-password>'

      2.    Database Connection Information

 Database server        = DB2/SUN 8.2.1
 SQL authorization ID   = LDASDB
 Local database alias   = dev_test
    2. cd to directory connecting createall.sh and the sql scripts mentioned below.

    3. Run the shell createall <dbname> to create all the tables in the database, e.g. 
      ./createall dev_test
      createall invokes

      1. Individual sql scripts for creating tables.

      2. Sql scripts to create triggers: after the tables have been created successfully, add the database triggers that are used to remove the child record reference when the parent record is deleted, e.g. sngl_datasource and sngl_transdta reference one of the events tables; when the parent event table is deleted e.g. sngl_inspiral, the trigger is invoked to remove the corresponding sngl_datasource and sngl_transdata record.

        To create database triggers in db2, run the listed sql scripts below with db2 client utility db2 in command mode:

        db2 -f <script-file> (one of the following)
         

      3. grant.sql script to grant table access permission

        This script issues a revoke command to deny permissions on a table and then issues the new permissions. 

        echo table runlist;
revoke all on table runlist from public;
grant select on table runlist to public;
        The revoke command will report errors the very first it is ran on a table since the table does not have the necessary permissions: 
        table process
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0556N  An attempt to revoke a privilege from "PUBLIC" was denied because 
"PUBLIC" does not hold this privilege.  SQLSTATE=42504

DB20000I  The SQL command completed successfully.
        but the second command of granting permissions should complete successfully. 
        DB20000I  The SQL command completed successfully.

      Scripts to automate table selection and deletion

      Run scripts as user ldasdb or connect to db2 as follows: 
      	db2 'connect to <dbname> user <dbuser> using <dbuser-password>'

        The following scripts can be used to retrieve data from all tables created by the shell script createall:

        e.g. db2 -tf selectall.sql

      • Select data from all tables: selectall.sql

      • To delete all data from the database server:
          delall.sql

      • To drop all tables from the database server:
          dropall.sql

      To terminate db2 connection, enter: 

      	db2 terminate.

    How to link a database to your LDAS system

    To make your database known to your LDAS system, LDAS resources files must be configured, either by editing them or by running programs to generate the configuration files. This process must be done as user ldas instead of user ldasdb for proper file ownership. The following resource files must be configured:

    1. Where is db2 client? LDAS database web pages resource file /ldas_outgoing/LDASdb2utils.rsc

      This file is used by the utility db2utils to build the database web pages at the sites under the LDAS database link (the example shown is for site ldas-dev). In addition, this file is also used by the scripts dbUniqueIds and dbDsnames described below to generate site information for the metadataAPI.

      Make a copy of this file from /ldas/bin/LDASdb2utils.rsc to /ldas_outgoing. Edit the following variables in the file to reflect the db2 paths of your installation: 

      ;## path of db2 client
set ::db2 "/usr2/ldasdb/sqllib/bin/db2" 
;## path of database directory
set ::dbdir "/usr1/databases"

      Make sure environment DB2INSTANCE is set to your database user by doing 

      setenv DB2INSTANCE <dbuser>
      in csh or 
      set DB2INSTANCE=<dbuser> ; export DB2INSTANCE
       in Bourne shell.

    2. What are the database users and passwords ? metadataAPI database name file /ldas_outgoing/LDASdsnames.ini

      This file keeps track of the database users and their passwords for each database at the site. 

      cd /ldas_outgoing
/ldas/bin/dbDsnames

      When the screen comes up with the list of databases at your site, enter the logins ( or use the defaults ) and passwords ( echoed with * ) and hit the APPLY button. When completed, it displays a message that the LDASdsnames.ini file has been created. The LDASdsname contains the following lines for each database 

      array set <your dbname> { login { ldasdb <encoded password> } }
      and a line showing all databases at your site: 
      array set ::site_dbnames { ldas-dev { dev_1 ldas_tst dev_test } }

    3. How are passwords to connect for database page information being set up?

      This is done in the LDAS database web pages resource file: /ldas_outgoing/cntlmonAPI/LDASdb2utils.rsc

      The database user name and password have to be defined for the utility db2utils ( started by cntlmonAPI ) to connect to the database and retrieve information to build the web pages.

      Edit the LDASdb2utils.rsc file again to put in the password from the LDASdsnames.ini file and to set your database machine name: 

      ;## database user password 
set ::dblock <encoded-password> 
set ::host

    4. Which is the default database for metadataAPI? LDAS global resource file: /ldas_outgoing/LDASapi.rsc

      5. This file contains the variable to tell the metadataAPI which database to open as default.

      Copy this file from the ldas installation ldas-0.0/bin to the run directory /ldas_outgoing. Enter your database name as the default database for your domain on the line below: 

      set ::DATABASE_NAME dev_test

      The following variable should be set to the database instance in the managerAPI's resource file /ldas_outgoing/LDASmanager.rsc

      ;## desc=database user Id
set ::DB2INSTANCE ldasdb
      and also in the LDAS startup script runLDAS: 

      /usr/bin/env HOST=dataserver PATH=@prefix@/bin:/ldcg/bin:${PATH} LD_LIBRARY_PATH=
@prefix@/lib:@DB2_LIBS_DIR@:/ldcg/lib:${LD_LIBRARY_PATH} 
DB2INSTANCE=ldasdb 
/bin/nohup managerAPI $1 1>manager.log 2>&1 &

      In the LDAS build process, the path of the IBM db2 library should have been resolved with the expansion of @DB2_LIBS_DIR@ variable to the actual path of the IBM db2 directory.

      e.g. in the LDASmanager rsc file: 

      set ::LD_LIBRARY_PATH ${::LDAS}/lib:/ldcg/lib:@DB2_LIBS_DIR@

      should become the following in the build target: 

      ;## desc=dynamic load lib path ! mod=no
set ::LD_LIBRARY_PATH ${::LDAS}/lib:/ldcg/lib:/ldcg/IBMdb2/lib

      and the runLDAS script should have the actual DB2 path in the build target: 

      /usr/bin/env HOST=dataserver PATH=/ldas/bin:/ldcg/bin:${PATH} 
LD_LIBRARY_PATH=/ldas/lib:/ldcg/IBMdb2/lib:/ldcg/lib:${LD_LIBRARY_PATH} 
DB2INSTANCE=ldasdb 
/bin/nohup managerAPI $1 1>manager.log 2>&1 &

      If you need to hard code the IBM DB2 path, you can edit the LDASmanager.rsc file to have the actual IBM DB2 path e.g. if path is /usr/IBMdb2/V8.2.1/lib, in LDASmanager.rsc file: 

      
;## desc=dynamic load lib path ! mod=no
set ::LD_LIBRARY_PATH ${::LDAS}/lib:/ldcg/lib:/usr/IBMdb2/V8.2.1/lib

      For systems running on linux, LDAS must be build to link with the IBM db2 libraries. See How to build LDAS for more details.

    5. Start LDAS via cmonClient: cmonClient resource file: /ldas/lib/cmonClient/cmonClient.rsc

      6. If you are using the cmonClient package, either from the ldas installation or locally on your system with the gzip tar package, you should edit the cmonClient.rsc file to include your database name. 

      ;## desc=ldas dsnames at each site
array set ::dbnames { <your domain> { <dbname1> ...} }
array set ::dbnames { ldas-dev { dev_test dev_1 ldas_tst } }

    If you have more than one database, only one of them can be set as the default database. But add the names of the other databases to the list of dbname arrays in the above files. This is only needed if you want to use the cmonClient to start your LDAS system for the first time so it has to know about the site databases. Once cmonClient connects to the LDAS cntlmonAPI server, the site database information will be downloaded to the client.

    After the resources files are configured, you must reboot your LDAS system to have the changes in effect. The metadataAPI log indicates which database it has inserted to based on the specification in the -database option of the user command or use the default database if the -database option is not specified e.g. 

    750200312 LDAS-DEV16969219 metadata::submitInsertReaper putMetaData inserted 6 rows into ldas_tst database
 table process at the rate of 216.08306 rows/sec, took walltime 0.02777 secs
750200312 LDAS-DEV16969219 metadata::submitInsertReaper putMetaData inserted 100 rows into ldas_tst database 
table gds_trigger at the rate of 2223.40804 rows/sec, took walltime 0.04498 secs

    You can now use LIGO tool GUILD to submit queries or submit LDAS commands via the cmonClient LDAS Test Panel.

    Last modified Oct 14th, 2003

    Suggestions or Comments? Please Contact  mlei@ligo.caltech.edu