ibm db2 dae #2

Supports: xenial
Add to new model

Description

IBM DB2 Direct Advanced Edition provides a set of new license metrics to facilitate hybrid cloud deployments. The DB2 Direct Editions provide the same DB2 functionality
but add ease of purchase and licensing flexibility using a newly introduced simplified license metric, the Virtual Processor Core (VPC) sold as a monthly license charge.


Software & terms:

Charm for IBM DB2 Direct Advanced Edition 11.1.1.1

Overview

IBM DB2 Direct Advanced Edition

IBM DB2 Direct Advanced Edition introduce a set of new license metrics to facilitate hybrid cloud deployments. The DB2 Direct Editions provide the same DB2 functionality but add ease of purchase and licensing flexibility using a newly introduced simplified license metric, the Virtual Processor Core (VPC) sold as a monthly license charge. Clients can acquire the product directly online and have the option to deploy either on- premises or on cloud.
For details on DB2 Direct Advanced Edition, as well as information on purchasing, please visit:
Product Page.

More information available at the IBM Knowledge Center

Usage

The charm installs DB2 Version 11.1 Mod1 Fix pack1 as there are installation issues on Ubuntu Z if we install Base Version 11.1.0. To get this version ie 11.1.1.1, user needs to download the required packages from IBM Fix Central site.
User also needs to download the Activation key for IBM DB2 Direct Advanced Edition. Each DB2 database product has its own license key. User can download the license key from Passport Advantage site.
The part number for IBM DB2 Direct Advanced Edition 11.1.1.1 Activation Key is CNGC5ML.

To install the downloaded binaries you must agree to the IBM license. You can view the full license for DB2 DAE by visiting the Software license agreements search website. Search for "DB2 Direct Advanced Edition 11.1.1.1" and choose the license that applies to the version you are using.

In case you already have an IBM account and cannot download the product or for other error during SW download, please refer to the IBM Support Site to solve the error.

Memory and Disk Requirements

At a minimum, a DB2 database system requires 256 MB of RAM. For a system running just a DB2 product and the DB2 GUI tools, a minimum of 512 MB of RAM is required. However, 1 GB of RAM is recommended for improved performance.

On Linux and UNIX operating systems, 2 GB of free space in the /tmp directory is recommended. Please make sure this minimum memory configuration is available before deploying the charm. More information on this can be found in Memory Requirements

Deploy

To deploy DB2-DAE charm, run the following command:
Note : Make sure the appropriate memory requirements are made available.

juju deploy ibm-db2-dae --resource ibm_db2_dae_installer=</path/to/installer.tar.gz> --resource ibm_db2_dae_license=</path/to/license.zip>

Note: This charm requires acceptance of Terms of Use. When deploying from the Charm Store, these terms will be presented to you for your consideration.
To accept the terms:

juju agree ibm-db2-dae/1

Once you have agreed to the Terms, then only the IBM DB2 Direct Advanced Edition charm will be deployed.
Juju deploy command will install db2 and will expose the hostname, port number, db2 instance, install path and userid to the users.

Additional configuration parameters

Following shows the different configurable values for ibm-db2-dae charm:

  • If the user wants to set a different file path used to create databases and logs it can be set using the config value dftdbpath. Use the following command to set it

    juju set ibm-db2-dae dftdbpath=<new-path-to-set>
    

    For eg:

    juju set ibm-db2-dae dftdbpath="/tmp"
    

Installation Verification

Once IBM DB2 DAE charm is deployed, the user can log into the container/machine where it is deployed using the default user(db2inst1). The user can run any db2 commands after this.

  • For creating a new DB the user may run the command:

    db2 create database <db-name>
    
  • For listing the DBs created, run the command:

    db2 list db directory
    
  • For getting db2 command prompt just run the command:

    db2
    

Relating with other consumer charms

IBM DB2 DAE charm can be related to other consumer charms using the following command.

juju add-relation ibm-db2-dae <consumer-charm>

When IBM DB2 DAE charm is related to any consumer charm, it creates a db2instance, username/password as per the service name and will be provided to the consumer charm. IBM DB2 DAE charm also creates DBs as provided by the consumer charm and returns the DB details once they are successfully created. The following code will help the consumer charm authors to pass the DB names to the IBM DB2 DAE charm:

@when 'db.connected'
function configure_dbs(){
  dbnames="db1,db2"
  relation_call --state=db.connected set_dbs $dbnames || true
 }

If no DB name is provided by consumer charm, IBM DB2 DAE charm by default creates DBs according to the service name.

Also to set relation with IBM DB2 DAE charm and make use of all the features, the consumer charm should pass the ssh key from itself to IBM DB2 DAE charm. The following code will help the consumer charm authors to do this :

 @when 'db.connected'
 @when_not 'test.sshconfigured'
 function configure_sshkeys(){
 SSH_PATH=/root/.ssh
 if [ ! -f  $SSH_PATH/id_rsa.pub ]; then
            juju-log "Setting up SSH keys."
            ssh-keygen -t rsa -f $SSH_PATH/id_rsa -N ''
 fi
 key="`cat $SSH_PATH/id_rsa.pub`"
 relation_call --state=db.connected set_ssh_keys $key || true`
 set_state 'test.sshconfigured'
}

Without this IBM DB2 DAE charm will not be ready and will not create the DBs for the related services.

Configuration

See config.yaml file for more information.

  • dftdbpath : This parameter contains the default file path used to create databases under the database manager. If no path is specified when a database is created, the database is created under the path specified by the dftdbpath parameter.

Additional product information

Enabling BLU acceleration for DB2 Direct Advanced Edition (DAE)

The DB2-DAE charm has to be deployed first and then few settings need to be done to enable the BLU feature.
To enable the BLU feature the DB2_WORKLOAD registry variable has to be set to ANALYTICS and then the DB has to be restarted. The following commands will help the user for this.

db2set DB2_WORKLOAD=ANALYTICS
db2stop force
db2start

To test that DB2 DAE with BLU acceleration is enabled run the following command:

db2 create database <test-db>
db2 connect to <test-db> 
db2 get db cfg | grep DFT_TABLE_ORG

The output Default table organization (DFT_TABLE_ORG) = COLUMN confirms that the DB2 DAE with BLU is enabled. If the pre requisites for DB2-DAE is not set (setting the registry variable DB2_WORKLOAD) the above command would return Default table organization (DFT_TABLE_ORG) = ROW. Column-organized tables store each column on a separate set of pages on disk. Organizing data by column on disk reduces the amount of I/O needed for processing queries because only columns that are referenced in a query need to be accessed.

Once this is set all the tables created will be by default COLUMN organized whereas without BLU acceleration the tables will be ROW organized. Also without proper license we cannot create COLUMN organized tables. Once we enable COLUMN organized tables, we can create either ROW enabled or COLUMN enabled tables using the option ORGANIZE BY ROW or ORGANIZE BY COLUMN while creating the tables by running the below commands from db2 command prompt.

create table sales_row(tid integer not null, prod_name varchar(30)) ORGANIZE BY ROW
create table sales_col(tid integer not null, prod_name varchar(30)) ORGANIZE BY COLUMN

More details on BLU feature can be found in the link db2-BLU-acceleration

IBM DB2 Limitations

If you are running linux containers on a host with kernel < 3.16, be advised that db2start will not succeed because of a hard coded 32MB kernel.shmmax setting. You will need to upgrade your host to kernel 3.16 or greater by following the instructions here:

https://wiki.ubuntu.com/Kernel/LTSEnablementStack

DB2 also needs to be deployed using a non-zfs filesystem for container deployment. ZFS file system might cause some of the db2 commands to error out.

IBM DB2 Links and Contacts

(1) General Information
Information on IBM DB2 available at the DB2 Knowledgecenter

(2) Download Information
Information on procuring DB2 product is available at the Passport Advantage Site

(3) Contact Information
For issues with this charm, please contact jujusupp@us.ibm.com


Configuration

dftdbpath
(string) This parameter contains the default file path used to create databases under the database manager. If no path is specified when a database is created, the database is created under the path specified by the dftdbpath parameter.
/tmp