A guided tour to IBM Database Patterns, Part 4: Provision and manage your database using the REST API and command-line interface

IBM® Database Patterns provides solutions to easily provision and manage databases on IBM Workload Deployer (IWD) in a private cloud. IWD is a cloud management appliance that delivers a patterns-based approach to deploy and manage application environments in the cloud. Its REST API and command-line interface enables you to use IBM Database Pattern in batch processing with no GUI, thereby allowing it to be mashed up into existing applications and user interfaces.

Share:

Yan Yan Liu (yylbj@cn.ibm.com), Software Engineer, IBM

Author photoYan Yan Liu is a Software Engineer working on the IBM Database Patterns development team. She joined IBM in 2009 in IBM China Software Development Lab. Yan Yan is in charge of UI development for IBM Database Patterns in IBM PureApplication System and IBM Workload Deployer. She holds a master's degree in computer science from Renmin University of China.



Xiao Feng Wang (wangxf@cn.ibm.com), Staff Software Engineer, IBM

Author photo of Xiao Feng WangXiao Feng Wang is a Staff Software Engineer on the IBM Database Patterns development team.



07 March 2013

Also available in Russian

Introduction

This article is Part 4 in a series of articles on IBM Database Patterns. Part 1 introduced IBM Database Patterns and how they provide solutions to easily provision and manage databases on IBM Workload Deployer in a private cloud. Part 2 showed how to create database images and new databases using images in IBM Database Patterns 1.1.0.1. Part 3 discussed the concepts of database workload standards, including how to create database workload standards and manage their life cycles.

This article introduces how to call the IBM Database Patterns 1.1.0.1 REST API and use a command-line interface to create a pattern, then deploy and manage a database.


Before you begin

Before calling the REST API, you need to configure your HTTPS client to set the HTTP headers required for authentication, authorization, and content negotiation. Refer to the Resources section for more information.

Before using the command-line interface, you need to download it to a local machine. Refer to the Resources section for more information.

You then need to invoke it in either interactive mode or batch mode from a local machine running either Windows® or Linux® operating systems. Refer to the Resources section for more information.


Create a database pattern with specific attributes

Database patterns define what you want a database to look like. You can deploy several databases using the same pattern. There are several key attributes to define a database pattern, including the following.

  • source: A database can be deployed with a system pre-defined workload standard (with the value workloadStandardApproach), or with a customized workload standard, or from an existing backup image (with the value of cloneApproach).
  • workloadStandard: If you're using the source workloadStandardApproach, then the value of this attribute is either dynamic_datamart or departmental_OLTP, which means that you're creating a data mart or OLTP database. If you're using the source cloneApproach, this attribute is not needed.
  • databaseImage: If you're using the source cloneApproach, then the value of this attribute is the ID of a record you want to use from the backup image list. You can get the list by calling the REST API: GET /resources/dbimages?dbaasversionge=1.1. Refer to the Resources section for more information on using the REST API for database, and list database images by dbaasversionge. If you're using the source, workloadStandardApproach, this attribute is not needed.
  • sqlType: This attribute describes whether you're deploying a standard DB2 database or Oracle mode enabled database, and the value is DB2 or ORACLE accordingly.
  • sqlFile: The sqlFile attribute is used to pre-populate the database with tables and data. Please note that it's uploaded by a separate REST API, which will be introduced later.

The following example describes how to create a pre-defined OLTP database pattern, and later this pattern will be used to deploy a database. Listing 1 shows how to create a pre-defined OLTP database pattern named OLTPPattern. The database compatibility mode is DB2, and the REST API should be POST /resources/applicationPatterns.

Listing 1. Request body of create database pattern
{
    "model": {
        "app_type": "database",
        "patterntype": "dbaas",
        "version": "1.0",
        "name": "OLTPPattern",
        "description": "This is a OLTP type of database pattern",
        "nodes": [
            {
                "attributes": {
                "dbname": "mydb",
                "purpose": "production",
                "source": "workloadStandardApproach",
                "dataSizeForWorkload": 10,
                "workloadStandard": "departmental_OLTP",
                "sqlType": "DB2"
            },
            "type": "DB2",
            "id": "database"
          }
      ]
   }
}

You will get the response shown in Listing 2 once the pattern is created successfully (with response code 200). A unique ID (app_id) is generated, which will be used in later operations.

Listing 2. Create database pattern response
{
    "content_type": "application/json",
    "last_modifier": "cbadmin",
    "create_time": "2012-03-18T03:09:32Z",
    "last_modified": "2012-03-18T03:09:34Z",
    "access_rights": {
        "cbadmin": "F"
    },
    "content_md5": "24DB40B99DEC9B0CBFB6DAF530CA2EE5",
    "app_type": "database",
    "app_name": "OLTPPattern",
    "app_id": "a-a537039d-1228-488c-8721-6deae800ba70",
    "locked": "false",
    "creator": "cbadmin",
    "Collection": "."
}

As shown in the following command line, since the input parameter is long, it's recommended that you put it into a local json file. In the following example, the content of /home/createDBPattern.json is the same as the REST API request body.

deployer.applications.create("/home/createDBPattern.json")

Upload a SQL file for the database pattern

If you want to pre-populate the database with tables and data, you can use the following REST API and command line. It needs the pattern ID (in this example pattern ID is a-a537039d-1228-488c-8721-6deae800ba70).

PUT /resources/applicationPatterns/a-a537039d-1228-488c-8721-6deae800ba70/artifacts/createDB.sql.

The response is shown in Listing 3.

Listing 3. Upload SQL file response
{
    "fileName": " createDB.sql",
    "file": "artifacts/ createDB.sql",
    "file_name": " createDB.sql"
}

The command is as follows.

deployer.applications.get("a-a537039d-1228-488c-8721-6deae800ba70")
.artifacts.upload("/home/createDB.sql")

Deploy a database to a target cloud group

Choose a predefined target cloud group in which to deploy a database. Depending on your network configuration, this can take a while to complete. In this example, a database named OLTPdb is deployed.

POST /resources/applicationPatterns/a-a537039d-1228-488c-8721-6deae800ba70/virtualApplications.

The request body is shown in Listing 4.

Listing 4. Request body of deploying a database
{
    "deployment_name": "OLTPdb",
    "cloud_group": "1",
    "ip_version": "IPv4"
}

Every database deployment includes credentials for two users: appuser and appdba. The appuser ID has limited privileges and is meant to be used during application development. The appdba user has extended privileges and is meant to be used for database administration. For databases deployed as part of a virtual application, two JDBC URLs are included (one for the appuser user with attribute name AppUserENDPOINT, and one for the appdba user with attribute name AppDBAENDPOINT). You will get the response shown in Listing 5 once the database is deployed successfully (with response code 200).

Listing 5. Deploy database response
{
    "status": "RUNNING",
    "virtual_system": {
        "platform": "ESX",
        "cloud": "/resources/clouds/2",
        "id": "5",
        "image": "1"
    },
    "deployment_name": "OLTPdb",
    "deployment_id": "d-38751eb2-c965-4526-b4f8-3b8d5896b473",
    "app_type": "database",
    "app_id": "a-a537039d-1228-488c-8721-6deae800ba70",
    "start_time": "2012-03-18T03:09:32Z",
    "creator": "u-0",
    "instances": [
        {
            "master": true,
            "private_ip": "172.16.37.165",
            "reboot.count": 0,
            "start_time": "2012-03-18T03:09:32Z",
            "name": "database-db2.11314779310983",
            "last_update": "2012-03-18T03:09:32Z",
            "vmId": 5,
            "status": "RUNNING",
            "stopped.by": "",
            "volumes": [
                "AVAILABLE: 1314779311328: /home/db2inst1"
            ],
            "id": "database-db2.11314779310983",
            "public_ip": "172.16.37.165",
            "roles": [
                {
                    "node": "database-db2.11314779310983",
                    "status": "RUNNING",
                    "last_update": "2012-03-18T03:09:32Z",
                    "external_uri": [
                        {
                            "AppUserENDPOINT":"jdbc:db2://172.16.37.165:50000/OLTPdb:
                            user=appuser;password=81Fq1Lj39y2XhoCL;"
                        },
                        {
                            "AppDBAENDPOINT":"jdbc:db2://172.16.37.165:50000/OLTPdb:
                            user=appdba;password=R85obr7pun8qWMNV1Ap8;"
                        }
                    ],
                    "id": "database-db2.11314779310983.DB2"
                }
            ]
        }
    ],
    "creator_name": "cbadmin",
    "role_error": false
}

The command is as follows.

deployer.applications.get("a-a537039d-1228-488c-8721-6deae800ba70")
.deploy("OLTPdb", deployer.clouds[0])

Manage your databases

After you successfully deploy a database, you can manage it by performing a limited set of post deployment operations.

The post deployment operations that available are as follows.

  • Update the user and database administrator passwords.
  • Create the database backups.
  • Configure an automatic scheduled backup frequency.

Please note that the TSM plugin should be configured prior to database deployment if you want to create database backups and set auto schedule backup frequency.

Create a database backup

Post: /resources/virtualApplications/d-38751eb2-c965-4526-b4f8-3b8d5896b473/operations.

The request body is shown in Listing 6.

Listing 6. Request body of create database image
{
    "role": "database-db2.DB2",
    "type": "backup",
    "global": false,
    "parameters": {
        "imageName": "OLTPdbBackup",
        "imageDescription": "This image is backup from OLTPdb"
    },
    "script": "backup.py",
    "method": "",
    "roleType": "DB2"
}

You will get the response shown in Listing 7 if the operation is performed successfully (with response code 201), and the backup image can be viewed from the list of all images. Refer to the Resources section for a link to the list of all database images.

Listing 7. Create database image response
{
    "method": "",
    "artifacts": [],
    "operationId": "o-df8f696a-24a3-41cd-9148-f29c3146ea4d",
    "global": false,
    "role": "database-db2.DB2",
    "type": "backup",
    "parameters": {
    "imageDescription": "This image is backup from OLTPdb",
    "imageName": "OLTPdbBackup "
    },
    "locale": "en,zh;q=0.7,en-us;q=0.3",
    "script": "backup.py",
    "result": {
        "database-db2.11305274949390.DB2": "PENDING"
    }
}

Use the command line shown in Listing 8 to create the image.

Listing 8. Create image
deployer.virtualapplications.get("d-38751eb2-c965-4526-b4f8-3b8d5896b473")
.operations.create({"role":"database-db2.DB2","type": "backup","global": 
"false", "parameters":{"imageName": "OLTPdbBackup", "imageDescription": 
"This image is backup from OLTPdb "},"script": "backup.py","method":
"backup","roleType": "DB2"})

Configure automatic scheduled backup frequency

The frequency can be set to daily, weekly, or off, which means the automatic scheduled backup can be done every day, every week, or not at all according to your application's requirement. The default value is set to daily if you have configured the TSM plugin prior to the database deployment. The example shows the frequency set as weekly.

Post: /resources/virtualApplications/d-38751eb2-c965-4526-b4f8-3b8d5896b473/operations.

The request body is shown in Listing 9.

Listing 9. Request body of set automatic scheduled backup frequency
{
    "role": "database-db2.DB2",
    "type": "auto-backup",
    "parameters": {
    "frequency": "weekly"
    }
}

You will get the response shown in Listing 10 if the operation is performed successfully (with response code 201). The backup image can be viewed from the list of all images. Refer to the Resources section for a link to the list of all database images.

Listing 10. Set automatic scheduled backup frequency response
{
    "artifacts": [],
    "global": false,
    "operation_id": "o-ed9ad4a7-101d-4625-95da-df2b4e6dc33c",
    "role": "database-db2.DB2",
    "locale": [
        "en",
        "zh"
    ],
    "parameters": {
        "frequency": "daily"
    },
    "type": "auto-backup",
    "script": "change_backup.py",
    "result": {
        "database-db2.11305274949390.DB2": "PENDING"
    }
}

The command line to set automatic scheduled backup frequency is shown in Listing 11.

Listing 11. Automatic scheduled backups
deployer.virtualapplications.get("d-38751eb2-c965-4526-b4f8-3b8d5896b473").
operations.create({"role":"database-db2.DB2","type":"auto-backup",
"parameters":{"frequency":"weekly"}})

Delete database

All of the data in the database will be deleted. Administrators can delete all databases, while other users can delete only the databases that each user has created.

PUT: /resources/virtualApplications/d-38751eb2-c965-4526-b4f8-3b8d5896b473.

The request body is shown in Listing 12.

Listing 12. Delete database request body
{
    "operation": "kill" 
}

The response code is 201 if the database is deleted successfully.

The command line to delete the database is as follows.

deployer.virtualapplications.terminate("d-38751eb2-c965-4526-b4f8-3b8d5896b473")

Conclusion

This article introduced how to use the REST API and command-line interface to create a pattern, deploy a database, and manage a database. You can get more details from the Database Patterns Information Center, located in the Resources section.


Acknowledgement

Special thanks to Ning Wang, IBM Database Patterns Architect, for his review and advice.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

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 Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=857148
ArticleTitle=A guided tour to IBM Database Patterns, Part 4: Provision and manage your database using the REST API and command-line interface
publish-date=03072013