ibm db2 #1

Supports: trusty

Add to new model

Description

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


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 into an SFTP server. The IBM DB2 charm will connect to the SFTP server, download and deploy the same. The SFTP server can be any Power Ubuntu/X86 Ubuntu server and should be copied into a directory structure as below
/home/charm/repository/<productname>/<package.tar>. The complete path where the package is stored is a configurable item in config.yaml file and needs to be configured before deploying the charm. The charm will download the package from this server using SFTP. Hence make sure the SFTP link is accessible.

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 SFTP server, package name, directory in SFTP server where package is kept, username and password to connect to SFTP host and checksum value to proceed with installation, by using juju set command. To provide these run the following command:

    juju set ibm-db2 host=<host-name>
    juju set ibm-db2 package_dir=<full-path-to-the-directory>
    juju set ibm-db2 username=<user-name>
    juju set ibm-db2 password=<passwd>
    juju set ibm-db2 db2_package_name=<package-name>
    juju set ibm-db2 sha= <checksum-value>
    

For eg:

    juju set ibm-db2 host="x.x.x.x"
    juju set ibm-db2 package_dir="/home/charm/repository/ibm-db2"
    juju set ibm-db2 username="user1"
    juju set ibm-db2 password="password"
    juju set ibm-db2 db2_package_name="DB2_Svr_10.5.0.3_Linux_x86-64.tar.gz"
    juju set ibm-db2 sha=80501df818faf4f095b5a58210a0e110509fcb70

The command to find the checksum value is sha1sum <Package Name> | cut -d" " -f1

  1. 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 accept-ibm-db2-license=True
    

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

Uninstallation of IBM DB2

Setting the accept-ibm-db2-license to False will uninstall the product.

    juju set ibm-db2 accept-ibm-db2-license=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"
    
  • If you want to add a new user/password and instance name for DB2 you can do that by giving the following commands.

    juju set ibm-db2 db2_user=<newusername>
    juju set ibm-db2 db2_pw=<newuserpassword>
    

    For eg:

    juju set ibm-db2 db2_user="user1"
    juju set ibm-db2 db2_pw="user123"
    

If the db2_user and db2_pw is not set, then be default the charm will create db2usr1/db2usr123 using which you can connect and work with DB2. A new instance name with same name as db2_user will also be generated.

Installation Verification

Once IBM DB2 charm is deployed, the user can log into the container/machine where its deployed and use the db2_user/db2_pw combination to login as the db2 user. 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
    

Configuration

See config.yaml file for more information.

  • accept-ibm-db2-license: 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 accept-ibm-db2-license to True if you agree to IBM DB2 license.

  • sha : Check sum value to check integrity of IBM DB2 package. The Charm uses sha1sum to check the integrity. If empty, it will fail to deploy DB2. Cryptographic value should be altered by the user only when the package name is changed. Else it should not be altered. The command to find the check sum is

    sha1sum <Package Name> | cut -d" " -f1
    

    This value can be got from the administrator or the user can run the above command from the sftp server where the package resides.

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

  • db2_user : The user name to connect to DB2 and work on the same. Db2 will create a new instance with the same name as this user name.

  • db2_pw : The password for db2_user

  • host : SFTP server where IBM DB2 packages is kept

  • package_dir : Path to find the IBM DB2 packages on the SFTP server

  • username : User name to connect to the SFTP server

  • password : Password to connect to the SFTP server

  • db2_package_name : Name of the DB2 package to be deployed

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 needs 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 where as 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 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

accept-ibm-db2-license
(boolean) The DB2 software can only be used by accepting the terms and conditions for IBM DB2 License. Setting this option to True indicates that you have read and accepted the IBM DB2 terms and conditions found in the license file. The details about accessing the license(s) can be found in the README. Do not set this to False if a relation exists
db2_package_name
(string) Name of the DB2 package. If not set, the Charm uses a predefined package name for download from the url location. If the predefined package is not available at url location or if set incorrectly,the charm cannot install the DB2 software.
DB2_Svr_10.5.0.3_Linux_x86-64.tar.gz
db2_pw
(string) The password used by the db2_user to connect to db2.
db2usr123
db2_user
(string) The username using which the user can connect to the db2. Once db2 is installed this user name is generated and the user can perform all db2 operations using this user.
db2usr1
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
host
(string) SFTP server where IBM DB2 packages is kept. If not set or set incorrectly, the charm cannot find the IBM DB2 software.
package_dir
(string) Path to find the IBM DB2 packages on the SFTP server. If not set or set incorrectly, the charm cannot find the IBM DB2 software.
password
(string) Password to connect to the SFTP server. If not set or set incorrectly, the charm not able to download the software.
sha
(string) Checksum value to check integrity of IBM DB2 pacakage. The Charm uses sha1sum to check the integrity. If empty, it throws an error and stop the deployment. The command to find the checksum is sha1sum <Package Name> | cut -d" " -f1
username
(string) User name to connect to the SFTP server. If not set or set incorrectly, the charm not able to download the software.