ibm db2 #4

Description

IBM DB2 Enterprise Server Edition provides scalable database server software to handle the demanding workloads of large and midsize enterprise servers.


Software & terms:

lorem-ipsum ›

Charm for IBM DB2 10.5

Overview

IBM DB2

IBM DB2 Enterprise Server Edition provides scalable database server software to handle the demanding workloads of large and midsize enterprise servers. It delivers high performance across multiple workloads, while helping to reduce administration, storage, development and server costs.
DB2 Server is provided as part of this charm.

For details on DB2, as well as information on purchasing, please visit:
Product Page LUW and Product Page Express

More information available at the IBM Knowledge Center

Usage

The user will need to procure the appropriate license to be able to download and use IBM DB2.

To acquire and download DB2, follow instructions available at Product Page LUW or Product Page Express.
The tab Support&downloads in Product Page LUW gives different options for downloading the packages.

Download steps for IBM DB2 10.5 using Passport Advantage is mentioned in Downloading IBM DB2 10.5 for LUW. The Part Number used is CIXV0ML for X86-64 platform and that for Power is CN3YQML. To get more information on accessing Passport Advantage please visit the link Accessing Passport Advantage.

To install the downloaded binaries you must agree to the IBM license. You can view the full license for DB2 by visiting the Software license agreements search website. Search for "DB2 Enterprise Server Edition 10.5.0.5" 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.

Download of Product Binaries

For deploying IBM DB2 charm the user needs to download the correct package as mentioned in the Usage section and host it on an SFTP or Web server. The IBM DB2 charm will connect to the server(either by SFTP or HTTP method) to download and deploy the same. The host server can be any Power Ubuntu/X86 Ubuntu servers and should be copied into a directory structure as below

/<path to charm dir>/charm/repository/<productname>/<package.tar>

The complete path where the package is stored is a configurable item which needs to be configured before deploying the charm.

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 charm, run the following command:

  1. Make sure the appropriate memory requirements are made available

  2. For deploying either use an existing machine, or create a new machine using

    juju add-machine
    juju deploy --to <machine number> --repository=<path_to_charms_folder> local:trusty/ibm-db2
    
  3. Set all required config values like the curl__opts to log into the host machine and curl_url which specifies the url to download the packages, by using juju set command. To provide these run the following command:

    juju set ibm-db2 curl_opts=<login credentials>
    juju set ibm-db2 curl_url=<url to download package?shavalue>
    

    For eg:

    juju set ibm-db2 curl_opts="-u user:password"
    juju set ibm-db2 curl_url="http://<ipaddress>/debs/ibm-db2/DB2_Svr_10.5.0.3_Linux_x86-64.tar.gz?sha512=ae20be99e3cd2cef24d53a28331871d7193cfc7f7c24c580e6e78dc58c9ffb364cbcc69cb3773d2f0135b8c9be7ee40a7dc4e09fec0a4d731c4864bbba87d31e"
    

    The command to find the checksum value is sha512sum <Package Name>

    In the above example sha512 is used, you can use sha256 or md5 as well.
    Also in the above example, the packages are hosted on webserver, so http protocol is used, if you have placed the packages on sftp server, then replace the protocol with sftp.

  4. At this point DB2 install will wait for you to accept the License. To install the downloaded binaries you must agree to the IBM license. The license file(s) can be found in the product binaries you downloaded.

If you agree to the license, run the following command :

juju set ibm-db2 license_accepted=True

Juju deploy command will install db2 and will expose the hostname, port number, db2 instance, install path and userid to the users.

Uninstallation of IBM DB2

Setting the license_accepted to False will uninstall the product.

juju set ibm-db2 license_accepted=False

Additional configuration parameters

Following shows the different configurable values for ibm-db2 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 dftdbpath=<new-path-to-set>
    

    For eg:

    juju set ibm-db2 dftdbpath="/tmp"
    

Installation Verification

Once IBM DB2 charm is deployed, the user can log into the container/machine where it is deployed using the default user/password (db2inst1/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 charm can be related to other consumer charms using the following command.

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

When IBM DB2 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 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 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 charm by default creates DBs according to the service name.

Also to set relation with IBM DB2 charm and make use of all the features, the consumer charm should pass the ssh key from itself to IBM DB2 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 charm will not be ready and will not create the DBs for the related services.

Configuration

See config.yaml file for more information.

  • license_accepted: True to accept license. Before you can use or install IBM DB2, you must accept the terms of International License Agreement for Non-Warranted Programs and additional license information. Please read the license agreement carefully. The license file(s) can be found under the server/db2/license directory after downloading and extracting IBM DB2 there. Set the value of license_accepted to True if you agree to IBM DB2 license.

  • 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.

  • curl_url : Location of the IBM product installation file(s). This should be a URL that curl can use to download files. Multiple URLs should be separated by a space.

  • curl_opts : The options passed to the 'curl' command when fetching files from curl_url.

Additional product information

Enabling BLU acceleration for DB2 Advanced Enterprise Server Edition/DB2 Advanced Workgroup Server Edition

The same DB2 charm(DB2 Enterprise Server Edition) can be extended to enable the BLU acceleration(DB2 Advanced Enterprise Server Edition/DB2 Advanced Workgroup Server Edition) by following the below section. The DB2 charm has to be deployed first and then few settings need to be done to enable the BLU feature.

To upgrade DB2 Enterprise Server Edition (DB2 ESE) to DB2 Advanced Enterprise Server Edition(DB2 AESE) or DB2 Advanced Workgroup Server Edition (DB2 AWSE) the user need to accept the license for the same. Run the following command for accepting the AESE/AWSE license which the user procures on buying the Advanced Enterprise/ Workgroup Server Edition product. The user needs to log in using the configured user name/password to run the below commands.

db2licm -a </path/to/license/file/db2aese_u.lic>
or
db2licm -a </path/to/license/file/db2awse_u.lic>

db2licm -l command will show the license details once you apply the license.
Now 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 AESE 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 AESE/AWSE with BLU is enabled. If the pre requisites for DB2 for AESE/AWSE is not set (accepting the proper license and 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

The following command will convert the ROW organized tables to COLUMN organized if the proper license is accepted. Without having valid license the following command will error out.

db2convert -d   <test-db>

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
license_accepted
(boolean) Some IBM charms require acceptance of a license before installation can proceed. If required, setting this option to True indicates that you have read and accepted the IBM terms and conditions found in the license file referenced by the charm.
curl_url
(string) Location of the IBM product installation file(s). This should be a URL that curl can use to download files. Multiple URLs should be separated by a space. NOTE: cryptographic verification is required and must be specified as part of the URL query string with the key a valid hash algorithms md5, sha256, or sha512, and the the checksum value itself (http://<url>?[md5|sha256|sha512]=<checksum>). For example: 'http://example.com/file.tgz?sha256=<sum>' 'sftp://example.com/file1.tgz?md5=<sum> ftp://example.com/file2.tgz?md5=<sum>'
package_status
(string) The status of service-affecting packages will be set to this value in the dpkg database. Valid values are "install" and "hold".
install
extra_packages
(string) Space separated list of extra deb packages to install.
curl_opts
(string) The options passed to the 'curl' command when fetching files from curl_url. For example: '-u <user:password>'
install_keys
(string) List of signing keys for install_sources package sources, per charmhelpers standard format (a yaml list of strings encoded as a string). The keys should be the full ASCII armoured GPG public keys. While GPG key ids are also supported and looked up on a keyserver, operators should be aware that this mechanism is insecure. null can be used if a standard package signing key is used that will already be installed on the machine, and for PPA sources where the package signing key is securely retrieved from Launchpad.
install_sources
(string) List of extra apt sources, per charm-helpers standard format (a yaml list of strings encoded as a string). Each source may be either a line that can be added directly to sources.list(5), or in the form ppa:<user>/<ppa-name> for adding Personal Package Archives, or a distribution component to enable.