Build a database using a VAP for PureApplication System

Explore the Custom Workload Standard, a function for flexible database configuration

Learn some best practices that are useful for developing patterns for IBM® PureApplication™ System In this article, the author introduces how to configure and use the Custom Workload Standard, a new function for building databases with a virtual application pattern.

Yuki Miyata (ymiyata@jp.ibm.com), IDR Technology Executive, IBM

Yuki Miyata is a technology executive with ISV and Developer Relations Japan, specializing in solutions and platforms with WebSphere products and PureApplication System. He has more than 12 years experience in WebSphere technical support, including WebSphere beta projects and cloud-related projects. Since last year, he has been working on enabling ISV solutions on IBM PureApplication System.



04 October 2012

Also available in Chinese Japanese Portuguese

In previous versions of IBM Workload Deployer, the database configuration supported by virtual application patterns was restricted. In IBM PureApplication System, the virtual application pattern provides support for more flexible database configuration by using a feature known as the Custom Workload Standard that enables support for a broader set of database use cases.

Let's look at how the component responsible for building a managing virtual patterns traditionally handled database building.

Developing a database with a virtual application pattern

IBM PureApplication System includes advanced pattern technology originally introduced in IBM Workload Deployer. This advanced pattern technology provides the ability to easily create, deploy, and manage patterns of expertise. Patterns of expertise embody best practice implementation, configuration, management, and monitoring capabilities for specific software solutions based on years of experience. These patterns are provided by IBM and independent software vendors in an online catalog. Patterns of expertise are simplified to provide value quickly while at the same time guaranteeing consistent, predictable results. Clients can also create custom patterns of expertise using integrated tooling provided with IBM PureApplication System and share those patterns across their entire organization.

Originally, the component for building and managing virtual patterns carried restrictions for building databases with a virtual application pattern. But it is not feasible that all solutions be built as virtual applications. For instance, to build virtual application database, DB2's CREATE options must be default and the operations for creating tables all the way through loading data must be described as a single SQL file.

Following are examples where the database had to be built as a virtual system pattern instead of virtual application pattern; this occurs if the database:

  • Needs to specify DB2 CREATE options such as TERRITORY, CODESET, COLLATE, and PAGESIZE.
  • Needs to create TABLESPACEs and BUFFERPOOLs.
  • Uses DB2MOVE or some tools with external data files to load initial data.
  • Needs particular database tunings.

In PureApplication System, with the benefit of enhanced virtual pattern handling, databases such as the ones just mentioned are now supported as a virtual application pattern or a database pattern (DBaaS). The function to enable this is called the Custom Workload Standard.

Next, learn how to configure a database with Custom Workload Standard.


Create shell scripts for Custom Workload Standard

In Custom Workload Standard, you need to create the shell scripts in the folders listed in Table 1 and package them to a ZIP file. The shell scripts are executed by db2inst1, the instance owner, in order of the number on Table 1.

Among the shell scripts, only create_db.sh in create_db folder is mandatory and the others are options.

Table 1. Shell scripts for Custom Workload Standard
No.FolderShell scriptDescription
1tune_insttune_inst.shExecuted just after creating DB2 instance. Describe here instance tunings.
2create_dbcreate_db.shExecuted at the database creation (mandatory). Describe here Create DB command.
3tune_dbtune_db.shExecuted just after DB creation. Describe here DB tunings.
4init_dbinit_db.shExecuted after tuning DB. Describe here TABLE/VIEW/INDEX creation commands and data INSERT/LOAD/IMPORT commands.
5post_start_instpost_start_inst.shExecuted every time after starting DB2 instance. Describe here what should be done after starting instance such as starting necessary processes.

The following sections explain how to describe the shell scripts.

tune_inst.sh

tune_inst.sh is executed after creating a DB2 instance. It primarily describes instance-tuning commands such as DB2 UPDATE DBM CFG. The following parameter is available in the shell script:

  • First parameter: Instance name

Note that the instance name is always db2inst1 and cannot be changed.

Listing 1. Example for tune_inst.sh
#!/bin/sh
inst_name=$1
echo "========== tune_inst.sh start =========="
echo "inst_name: " ${inst_name}
## Tuning Instance
db2 "UPDATE DBM CFG USING JAVA_HEAP_SZ 1024 DEFERRED"
echo "========== tune_inst.sh end =========="

create_db.sh

create_db.sh is executed when creating a database. It describes the database creation command DB2 CREATE. Any options for DB2 CREATE can be added. The following parameters are available in the shell script:

  • First parameter: Instance name
  • Second parameter: Database name
Listing 2. Example for create_db.sh
#!/bin/sh
inst_name=$1
db_name=$2
echo "========== create_db.sh start =========="
db2 "CREATE DATABASE $db_name USING CODESET UTF-8 TERRITORY Ja_JP COLLATE 
  USING IDENTITY"
rc=$?
if [ $rc -ne 0 ]
then
  echo "Failed to create DB"
fi
echo "========== create_db.sh end =========="

tune_db.sh

tune_db.sh is executed after creating the database. It describes commands for BUFFERPOOL creations, TABLESPACE creations, and database tuning using DB2 UPDATE DB CFG. DB2 GRANT also should be described if the application user needs to have specific authorizations.

The following parameters are available in the shell script:

  • First parameter: Instance name
  • Second parameter: DB name
  • Third parameter: Application user name
  • Fourth parameter: Application user password
  • Fifth parameter: Application DB administrator
  • Sixth parameter: Application DB administrator password

Note that the application user name is always appuser. The application DB administrator is always appdba. Both user names cannot be changed.

Listing 3. Example for tune_db.sh
#!/bin/sh
inst_name=$1
db_name=$2
app_user=$3
app_password=$4
appdba=$5
appdba_password=$6
echo "========== tune_db.sh start =========="
db2 "connect to $db_name"
## Create BUFFERPOOLs
db2 "CREATE BUFFERPOOL ADD_SYSB1 IMMEDIATE SIZE 250 PAGESIZE 16K"
## Create TABLESPACEs
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ADD_SYSTMP1 PAGESIZE 16K \
  MANAGED BY SYSTEM USING ('xxxsys00') EXTENTSIZE 16 OVERHEAD 10.5 \
  PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL ADD_SYSB1"
## Tune DB 
db2 "UPDATE DB CFG USING LOGFILSIZ 5000 DEFERRED"
db2 "UPDATE DB CFG USING APPLHEAPSZ 512 DEFERRED"
## If the application user needs to have specific authorizations, describe them here
db2 "GRANT DBADM ON DATABASE TO USER $app_user"
db2 "connect reset"
db2 "terminate"
echo "========== tune_db.sh end =========="

init_db.sh

init_db.sh is executed after tuning the database. It describes creation commands for TABLE, VIEW, INDEX, etc., and data import commands such as INSERT, LOAD, and DB2MOVE.

Necessary DDL files should be placed in the init_db folder. If LOAD or DB2MOVE is used, the necessary data files (IXF, XML, LOB, etc.) should also be placed in the init_db folder.

Note that the init_db files should connect to the database whenever an application user wants to create tables and other objects since the application gets access to the database through the application user. As previously mentioned, the application user name is always appuser, so be careful to use appuser as the schema name if you import tables by using DB2MOVE or similar tools.

The following parameters are available in the shell script:

  • First parameter: Instance name
  • Second parameter: DB name
  • Third parameter: Application user name
  • Fourth parameter: Application user password
  • Fifth parameter: Application DB administrator
  • Sixth parameter: Application DB administrator password
Listing 4. Example for init_db.sh
#!/bin/sh
inst_name=$1
db_name=$2
app_user=$3
app_password=$4
appdba=$5
appdba_password=$6
echo "========== init_db.sh start =========="
## Connect to DB by the application user
db2 "CONNECT TO ${db_name} USER ${app_user} USING ${app_password}"
## Execute the DDL
db2 +p -s -v -f xxx.ddl
# In case of loading the initial data by db2move (Package the data files 
  in the same folder)
db2move ${db_name} import -io insert
db2 commit work
db2 connect reset
db2 terminate
echo "========== init_db.sh end =========="

post_start_inst.sh

post_start_inst.sh is executed every time after starting a DB2 instance. It describes, for example, starting commands for processes that need to be executed every time after starting a DB2 instance (for example, DB2 Text Search). The following parameters are available in the shell script:

  • First parameter: Instance name
  • Second parameter: DB name
Listing 5. Example for post_start_inst.sh
#!/bin/sh
inst_name=$1
db_name=$2
echo "========== post_start_inst.sh start =========="
## Start DB2 Text Search
db2ts "ENABLE DATABASE FOR TEXT CONNECT TO ${db_name}"
/home/db2inst1/sqllib/db2tss/bin/startup.sh
db2ts "START FOR TEXT"
rc=$?
if [[ ${rc} -ne 0 ]] ; then
   echo "Failed to post start the database."
   exit ${rc}
fi
echo "========== post_start_inst.sh end =========="

Next, create the ZIP file for the feature.


Create a ZIP file for Custom Workload Standard

When you've finished creating the shell scripts, package all the folders to a ZIP file. Figure 1 shows the ZIP file that packages the five shell scripts.

Figure 1. ZIP file for Custom Workload Standard
ZIP file for Custom Workload Standard

Next, register the ZIP file.


Register the ZIP file for Custom Workload Standard

  1. Select Catalog > Database Workload Standards from the PureApplication System menu.
  2. Click the New icon. The Database Workload Standards window opens, see Figure 2.
    Figure 2. Custom Workload Standard registration
    Custom Workload Standard registration
  3. Upload the created ZIP file and save the new custom workload standard (the sample CWS in Figure 2).

Using the Custom Workload Standard feature

Now that it is set up, you can use the Custom Workload Standard feature.

  1. Drag and drop a database component in the canvas of Virtual Application Builder.
  2. Apply a default database workload standard is selected by default in the Source section of the Property frame on the right. Change it to Apply a database workload standard.
  3. A list of defined custom workload standards is displayed as shown in Figure 3. Select a custom workload standard that you want to use.
    Figure 3. Source property for DB component
    Source property for DB component
  4. Complete the virtual application pattern and deploy it as usual. The database is created and configured as you defined in the custom workload standard.
  5. You can use a custom workload standard in a database pattern (DBaaS) in a similar way. From the PureApplication System menu, select Patterns > Database Patterns.
  6. Click the New icon. The Database Pattern window opens.
    Figure 4. Database Pattern definition window
    Database Pattern definition window
  7. Change the Source field to Apply a database workload standard and select a custom workload standard that you want to use.
  8. Save and deploy the database pattern. The database is created and configured as you defined in the custom workload standard.

In conclusion

This article introduced Custom Workload Standard as a flexible database configuration method in PureApplication System. By using Custom Workload Standard, you can build your application with a virtual application pattern even if it has a database that was previously only supported by a virtual system pattern. Following the steps in this article, you should be able to build your virtual application pattern with greater database flexibility.

Resources

Learn

Get products and technologies

Discuss

  • Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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 Cloud computing on developerWorks


  • Bluemix Developers Community

    Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.

  • developerWorks Labs

    Experiment with new directions in software development.

  • DevOps Services

    Software development in the cloud. Register today to create a project.

  • Try SoftLayer Cloud

    Deploy public cloud instances in as few as 5 minutes. Try the SoftLayer public cloud instance for one month.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Cloud computing, Information Management
ArticleID=838921
ArticleTitle=Build a database using a VAP for PureApplication System
publish-date=10042012