Preparing for IBM PureApplication System, Part 3: Choosing a database option

Part 3 of the article series introduces the integrated IBM® DB2® database formats that are found in IBM PureApplication® System. It describes the different forms of DB2 found in PureApplication System, helps you decide which forms to choose in different situations, and covers some of the recommended best practices using DB2 inside PureApplication System.

Share:

Jason Chan (jasoncha@ca.ibm.com), Technical Manager, IBM

Photo of Jason ChanJason Chan is a Technical Manager in the Information Management Technology Ecosystem team, specializing in Platforms and Solutions with IBM Information Management products. His daily work covers the many diverse platforms that work with IM products, such as operating systems, virtualization platforms, and cloud computing. Jason and his team also apply their knowledge of platforms to produce integrated solution stacks with IM products, such as IBM Smart Analytics System 5710 - a workload optimized data warehouse solution aimed for small and medium business markets.



Hua Lu (hualu@ca.ibm.com), Software Developer, IBM

Hua Lu is a Software Developer in the Information Management Technology Ecosystem team. His tasks include the support of IBM Information Management products on various Linux distributions and virtualization platforms. His latest focus is on IBM PureApplication System and IM products' various interactions with this integrated system, which includes assisting ISVs with application integration work into IBM PureApplication System.



11 April 2012

Also available in Chinese Russian Japanese Vietnamese Spanish

Introduction

The previous articles of this series concentrated on how you can deploy applications onto IBM PureApplication System. Once the application is deployed, you need to consider how the application stores and accesses data. Relational database management systems offer a standard interface for data storage and retrieval, a developed security model around data access, and the support of concurrency with multiple users accessing the stored data.

With PureApplication System, an enterprise grade Relational Data Base Management System (RDBMS) is available to deploy applications in the form of IBM DB2 software. This article will introduce DB2, the different ways that DB2 is included in PureApplication System, and the processes and best practices to deploy a DB2 database alongside an enterprise application in PureApplication System.


DB2 in PureApplication System

IBM DB2 is an RDBMS product that supports different operating systems, such as Linux, UNIX, and Windows®, and z/OS. Specifically, PureApplication System includes the DB2 Linux, UNIX, and Windows (LUW) version of the product.

As of April, 2012, the most current version of DB2 LUW is DB2 10.1. This new version introduces features such as adaptive compression to further improve upon existing data compression techniques, continuous data ingestion for populating data tables without interruption to continued data access and availability, and time travel queries to view and easily report on data at a given point in time from the past. Other enhancements are also found in DB2 10.1, including general query optimization and performance improvements and refinements in security and workload management. PureApplication System includes DB2 10.1 as well as DB2 9.7, in case applications are not yet certified on the newer version.

The DB2 LUW 10.1 family is comprised of different editions in its portfolio, from DB2 Express-C (a no-charge edition of DB2 with community support) to the Advanced Enterprise edition (several add-on features included as standard). However, because all DB2 editions in the DB2 LUW family use the same underlying database engine, any application developed for one edition of DB2 LUW will work with all editions of DB2 LUW. In PureApplication System, there are DB2 virtual systems patterns that are offered in both DB2 Express and Enterprise editions with different feature add-ons, while the DB2 database workload patterns provide you with DB2 Enterprise edition.


Choosing a database for applications in PureApplication System

In the context of PureApplication System, there are multiple ways to deploy or configure a database. As IBM DB2 software is integrated inside PureApplication System, the use of DB2 as a deployed application's database involves no extra costs, and thus there is a reduction in overhead and other license tracking mechanisms. This inherent benefit of DB2 in PureApplication System reduces the total cost of ownership of the platform. Furthermore, the unified nature of DB2 within PureApplication System allows for best practices and expert focused integration to be applied and followed throughout an application's use of DB2 as the database backend service.

DB2 virtual system patterns

Like other applications that run on IBM PureApplication System, DB2 is available as either a DB2 virtual system pattern or as part of a DB2 database workload pattern. As described in previous articles of this series, the DB2 virtual system pattern allows for more flexibility in the control and configuration of the middleware environment. Currently, there are multiple images available that can be deployed as a DB2 virtual system pattern:

  • DB2 Enterprise
  • DB2 Express
  • DB2 Enterprise (Primary Node for High Availability Disaster Recovery (HADR) feature)
  • DB2 Enterprise (Secondary Node for HADR)
  • DB2 Express (Primary Node for HADR)
  • DB2 Express (Secondary Node for HADR)

DB2 database workload patterns

In addition to the DB2 virtual system patterns available, DB2 database workload patterns are also found within PureApplication System where the configuration and best practices are applied for a specific context. The deployment of a DB2 database workload patterns is very simple, with the flexibility for changes to some of the configuration parameters within the database layer. For more information about the Database Workload Standard feature for DB2 database workload patterns, see the Database pattern section. Currently, there are two different database workload patterns available for DB2:

  • IBM Transactional Database Pattern
  • IBM Data Mart Pattern

The IBM Transactional Database Pattern is designed to accommodate departmental online transaction processing (OLTP) applications that do not require high levels of database customization. This database workload pattern includes automated configuration for departmental OLTP deployment, virtual machine deployment sizing templates, and database backup scheduling. Within this pattern is the use of DB2 Enterprise edition, with the Storage Optimization feature enabled for data compression.

The IBM Data Mart Pattern provides a set of capabilities that are essential to the provisioning and management of the data mart infrastructure for data-centric applications within PureApplication System. Tuned for the unique I/O throughput required of data mart workloads, the IBM Data Mart Pattern includes data compression capabilities and data movement tools - all designed to help move the business forward with the information needed without delay. Within this pattern is the use of DB2 Enterprise edition, with the Storage Optimization feature enabled for data compression. Included inside this pattern are the SQL Warehousing tools for creating and modifying physical models, control flows, and data flows of the target data marts.

DB2 SQL compatibility feature

For users that have not used IBM DB2 software as a database solution before, PureApplication System is an excellent instance to evaluate DB2 for production deployments alongside the existing enterprise applications. Along with the previously mentioned benefits inherent with DB2 usage in PureApplication System, the DB2 virtual system and database workload patterns allow you to turn on an SQL compatibility mode to assist with migrations of applications written to use other competing database software. With this feature turned on, native SQL written for other competing databases are compiled natively in the DB2 engine, without the use of slow emulation software. In addition, a compatible data concurrency model is available, and DB2 includes tools that are compatible with existing scripts and personnel skills, which simplifies the transition to DB2.

For more information about the DB2 SQL compatibility feature, see Run Oracle applications on DB2 9.7 for Linux, UNIX, and Windows.

Using a remote database outside of PureApplication System

In some use cases, there may be a need for a deployed application within PureApplication System to access and link up with a database system that is on a remote system. For example, perhaps performance and other criteria have categorized a particular database workload to be in a mission critical tier one database category, and thus a dedicated physical system is required to host such a database system.

When defining a virtual application pattern, you can attach existing remote database components to the pattern. These databases will be outside of PureApplication System, and the configuration properties of these components will define the connection parameters to the remote database.

Steps to choose a database

Given the information above, the following is a simplified procedure to choose a database deployment for an associated application in PureApplication System:

  1. By default, start with the use of the DB2 database workload patterns. These patterns already incorporate best practice guidelines in their implementation of DB2. If needed, create and reference a new database workload standard in order to have the changes in the configuration apply to an associated application.
  2. If (due to performance or other criteria) you choose to have the database reside outside of PureApplication System, use an appropriate interface available to attach an existing remote database component into a virtual application pattern.
  3. If DB2 database workload patterns are too restrictive to use with an application, use a DB2 virtual system pattern. This provides greater flexibility for you to control this middleware environment.

Best practices for deploying DB2 in PureApplication System

This section provides instructions on how to create a DB2 virtual system and database pattern within PureApplication System. The instructions also include how to adjust the DB2 virtual system or database patterns to manipulate the configuration of these resources with the use of script packages that you can use to create tables, populate data, and pre-tune the database.

DB2 virtual system pattern

When using a DB2 virtual system pattern, a script package is usually created to change certain characteristics of the DB2 virtual system and to suit the needs of the associated application. In the following example, a script package is created so that it creates a database and updates the database parameter LOGBUFSZ. The script package is then uploaded to PureApplication System and used in DB2 virtual system patterns.

Create and upload a script package

The script package usually comes with two script files, a JSON file and a Shell script file. The JSON file contains the structure information about the script requirement, such as the package name, description, execution command, and working directory. The Shell script file contains the commands or script to invoke other files. To create a sample script package:

  1. Open an editor and create a JSON file, cbscript.json. Listing 1 shows an example of what the file looks like.
    Listing 1. Example of a JSON file, cbscript.json
    {
       "name": "Create Database and Tune ",
       "version": "1.0.0",
       "description": "This script package creates a database and update db parameter 
         in a DB2 instance",
       "command": "/bin/sh /tmp/createDatabase/createDatabase.sh",
       "log": "/tmp/createDatabase",
       "location": "/tmp/createDatabase",
       "timeout": "0",
       "commandargs": "",
       "keys":
       [
          {
           "scriptkey": "DATABASE_NAME",
           "scriptvalue": "",
           "scriptdefaultvalue": ""
          }
       ]
    }
  2. Save and exit this file.
  3. Create a Shell script file using the name defined before in the JSON file, for example, createDatabase.sh. Input the following lines in the file shown in Listing 2.
    Listing 2. Shell script example in package
    echo "DB2INSTANCE=db2inst1" >> /etc/virtualimage.properties
    source /etc/virtualimage.properties
    export DB2INSTANCE=$DB2INSTANCE
    su db2inst1 -c "/opt/ibm/db2/V9.7/bin/db2 CREATE DATABASE $DATABASE_NAME"
    su db2inst1 -c "~/sqllib/bin/db2 connect to $DATABASE_NAME;~/sqllib/bin/
     db2 update db cfg using logbufsz 1000;~/sqllib/bin/db2 terminate;"
  4. Save and exit the Shell script file.
  5. Compress these two files into a zip package and make sure the package name is the same as defined in the JSON file. For example, in the cbscript.json file, if the value of the field location is /tmp/createDatabase/, then the package name should be createDatabase.zip. The file is extracted into the folder /tmp/createDatabase/.
  6. Login to PureApplication System as an administrator and navigate to Catalog, and then the Script packages page.
  7. Click the New icon on the top left corner to add a new script package.
  8. Enter the package name and click OK. Make sure it is the same as defined in the cbscript.json file, for example, Create Database and Tune (see Figure 1).
    Figure 1. Package name dialog box
    Package name dialog box
  9. On the "Create Database and Tune" page, click on the input box besides Script package files. Select the script package that was just created and click Upload.
  10. Depending on the package size, it will take some time to upload and compile the package. After the process is done, the parameters defined in the cbscript.json file are loaded on the page, as shown in Figure 2.
    Figure 2. Information webpage for the uploaded package
    Information webpage for the uploaded package
  11. Double-check the parameters to make sure they fit the requirements.

The script package has been uploaded to PureApplication System and you can use it in the virtual system pattern now.

Create a virtual system pattern

This section describes how to create a virtual system pattern that contains one DB2 Enterprise database server and how to use the script package that was just uploaded. To create a virtual system pattern:

  1. Open the virtual system pattern page by clicking Patterns, and then Virtual Systems from the menu.
  2. Click the New icon on the top left corner to create a new virtual system pattern.
  3. Enter the name and description for the pattern, as shown in Figure 3. Click OK to continue.
    Figure 3. Virtual system pattern name dialog box
    Virtual system pattern name dialog box
  4. On the detailed information page of the virtual system pattern just created, click the Pencil icon on the top right corner to bring up the pattern editor.
  5. On the pattern editor page, select the Parts section from the left menu to expand it, and then drag and drop the item DB2 Enterprise to the right panel.
  6. Click on the Scripts section from the left menu to expand it. Drag and drop the script package just created, Create Database and Tune, on DB2 Enterprise, as shown in Figure 4.
    Figure 4. Attaching the package to the virtual system pattern
    Attaching the package to the virtual system pattern
  7. Click on Edit on the DB2 Enterprise part and enter the required information about the system, as shown in Figure 5. Click OK to continue.
    Figure 5. Dialog box for DB2 virtual system pattern configuration
    Dialog box for DB2 virtual system pattern configuration
  8. Click Edit on the script package and enter the DATABASE_NAME that is required by the script, as shown in Figure 6. Click OK to continue.
    Figure 6. Database name dialog box
    Database name dialog box
  9. Click Done editing on the top right corner to save and exit pattern editor.
  10. On the "VSpattern" page, click Deploy on the top right corner to deploy a virtual system from this pattern.
  11. Click Instances, and then Virtual Systems from the top menu. This page displays the deployment status of the virtual system, as shown in Figure 7. Once the deployment is completed, you can retrieve the connection information from this page.
    Figure 7. Deployment status Web page for DB2 virtual system pattern
    Deployment status Web page for DB2 virtual system pattern

Database pattern

This section describes how to use database pattern on PureApplication System. There is a feature called "database workload standards" that allows administrators to create workload standards and configure most part of the database. Instructions in this section include how to create a sample workload standard and use it to deploy a database and to update parameters. For more information about the database workload standards, refer to the IBM Workload Deployer Information Center.

Create a database workload standard

The database workload standards is a zip file that contains the following five first-level directories. Each directory contains an entry script that can also invoke other scripts or files. The create_db directory and its entry script, create_db.sh, are mandatory. All other directories are optional.

The scripts are executed as operating system user "db2inst1" and invoked in the following order as shown in Table 1.

Table 1. Script invocation order for database workload standard
Directory Entry script
tune_inst tune_inst.sh
post_start_inst post_start_inst
create_db create_db.sh (Mandatory)
tune_db tune_db.sh
init init.sh

The sample workload standard includes two directories: "create_db" and "tune_db". Each directory only has the entry script, create_db.sh and tune_db.sh. The script create_db.sh creates the database with the database name users entered. The script tune_db.sh updates the database parameters after the database is created. For more information about the parameters and other requirements of the scripts, see the IBM Workload Deployer Information Center.

To create a workload standard:

  1. Create a directory called "create_db" and under this directory, create a Shell script file called "create_db.sh". The content of this file looks similar to Listing 3.
    Listing 3. Example of the create_db.sh script
    #!/bin/sh
    inst_name=$1
    db_name=$2
    
    outStr=$(db2 "CREATE DATABASE ${db_name} ON /home/${inst_name} 
     USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192")
    if [ $? -ne 0 ] ; then
       echo "database creation failed, aborting: ${outStr}"
       exit -1
    else
       exit 0
    fi
  2. Save the file and exit.
  3. Create another directory called "tune_db" and under this directory, create a Shell script file called "tune_db.sh". The content of this file looks similar to Listing 4.
    Listing 4. Example of the tune_db.sh script
    #!/bin/sh
    db_name=$2 
    
    db2 connect to ${db_name}
    db2 UPDATE DB CFG USING LOGBUFSZ 1000
    db2 terminate

    The script updates the database parameter LOGBUFSZ to 1000. Add in more commands as needed.

  4. Save the file and exit.
  5. Compress both directories in a zip file, for example, WLDstandard.zip.

Upload a database workload standard

After the package is created, upload it to PureApplication System.

  1. Login as an administrator.
  2. Select Catalog and then Database Workload Standards from the top menu to open the workload standard page.
  3. Click the New icon on the page to create a new workload standard. Enter all the required fields and upload the zip package just created, as shown in Figure 8.
    Figure 8. Database workload standard configuration dialog box
    Database workload standard configuration dialog box
  4. Click Save to save this standard and exit.

Create a database pattern and deploy it

After the database workload standard is uploaded to PureApplication System, create a database pattern using this standard. To create and deploy from a database pattern:

  1. Login as an administrator.
  2. Click Patterns, and then Database Patterns from the top menu.
  3. Click the New icon to create a new database pattern.
  4. Enter all the required fields and choose the workload standard just created, as shown in Figure 9.
    Figure 9. Deployment of a DB2 database pattern using a custom database workload standard
    Deployment of a DB2 database pattern using a custom database workload standard
  5. Click Save to save the pattern and exit.
  6. Click on the pattern just created, Dev DB Pattern, to open the pattern page.
  7. Click Deploy to bring up the deploy window. Enter the database name, for example mydb, and select the cloud group or environment profile to deploy, as shown in Figure 10.
    Figure 10. Database pattern configuration dialog box
    Database pattern configuration dialog box
  8. Click OK to start deploying and go to Instances> Databases page to check the deployment status.
  9. After the deployment is completed, you can retrieve the connection information on the "My databases" page, as shown in Figure 11.
    Figure 11. Deployed database pattern status Web page
    Deployed database pattern status Web page
  10. Catalog this database on the local database directory by executing following commands shown in Listing 5.
    Listing 5. Commands for the catalog of a remote DB2 node and database
    db2 catalog tcpip node <node name> remote <Host IP address> 
     server <Port number>
    db2 catalog db <Database name> at node <Node name>

    For example:
    db2 catalog tcpip node rnode remote 9.26.167.36 server 50000
    db2 catalog db mydb at node rnode

    Execute following commands shown in Listing 6 to retrieve the database parameter LOGBUFSZ value.

    Listing 6. Connecting to a remote database and fetching LOGBUFSZ configuration parameter
    db2 connect to mydb user appdba using <appdba password>
    db2 get db cfg | grep LOGBUFSZ

    Listing 7 shows the output

    Listing 7. Example output of LOGBUFSZ configuration parameter
    Log buffer size (4KB)          LOGBUFSZ) = 1000
  11. The default value of LOGBUFSZ is 256. It changed to "1000" as defined in the tune_db.sh script.

Conclusion

This article introduced the IBM DB2 database software and its inclusion in IBM PureApplication System. DB2 provides PureApplication System users with an integrated relational database system that can be used at no extra charge, providing associated applications with a secure and robust solution to store application data. As there are multiple ways to deploy a DB2 database in PureApplication System, the recommendation is to start with one of the DB2 database patterns first and if necessary, use a self-defined database workload standard to apply the configuration changes. After the choice is made, follow the instructions in the article to complete a deployment of DB2 in PureApplication System.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management, Cloud computing
ArticleID=808937
ArticleTitle=Preparing for IBM PureApplication System, Part 3: Choosing a database option
publish-date=04112012