Clouds over Southern England

Matching: iwd

SQL0289N - Unable to allocate new pages in table space with DB2 ESE in IWD

S4QD_Tony_Barker Tags: iwd 1 Comment 21,550 Views

Had an interesting set of error messages recently when trying to pre-load a DB2 ESE with a large amount of data using three DDL files. This was essentially a migration of data from DB2 on Windows to a DB2 V9.7 Linux "part" within a Virtual System pattern in IBM Workload Deployer.

The three DDL files ran perfectly well until they reached around 35% completion then all of a sudden I was hit with :

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "TABLESPACE1". SQLSTATE=57011

and all subsequent statements failed in the same way.

This confused me for a little while until I looked closer at the CREATE TABLESPACE command. It was set to "MANAGED BY AUTOMATIC STORAGE". Automatic storage simplifies storage management by allowing you to specify storage paths where the database manager can place table space data, and where the database manager allocates space for various uses. By default, DB2 was using the /db2fs file system (/dev/sdd1).

I ran a df -k after ssh'ing into the DB2 machine. It showed that db2fs was 100%.

Looking at the virtual image for DB2 I could see the following default disk sizes :

and the DB2-data.vmdk (/db2fs) was set to just 10Gb. This was clearly not enough for this deployment.
In order to increase the size of the disk I had to extend and capture the existing default DB2 image.

Extending and Capturing

The basic concept of extending and capturing is to take an existing image, deploy it into your private cloud, modify it and save it back within the IBM Workload Deployer environment. This is a great option if you want to add additional functionality to existing images , like perhaps adding your own software, so that you can deploy them as part of your own Virtual System patterns. Additionally, there are options to modify the disk sizes.

I clicked on the "2 disk" icon to begin the cloning process

and you get presented with some options before the image is deployed into the cloud for modification.

In this case I modified the hardware configuration and changed the 10Gb to 30Gb :

Clicking Ok deployed the image.

Once deployed, you can ssh into the instance to make additional modifications. But, as I said earlier, I only wanted to increase the /db2fs disk size.

Now to suck the image back into IWD! This is done from the Virtual Image catalog. Once you have selected your cloned image (ExtendedDB2Image in this case) you can select the Capture icon (cloud and floppy disk icon) to save the modified image back into the catalog.

I could then go back to my original Virtual System pattern and edit the database part. The drop-down Version menu was clever enough to realise that I had a new version of a DB2 virtual image. The cool thing about this is that I didn't need to delete my old database part. Deleting would have removed my script packages as well, and meant that I would have to re-add them and rework the ordering.

I could then re-deploy the pattern again with the new DB2 image that had an increased disk size.

Migrating the Trade Application database to the cloud using IBM Workload Deployer

S4QD_Tony_Barker Tags: dbaas cloud iwd 1 Comment 6,785 Views

There's a lot of talk about a great many things being provided “as-a-service”. One that drew my interest recently was “Database as a service” - the ability to put your database into the cloud and take advantage of its benefits. Sounds neat, and so I decided to take a closer look at how you might achieve this and how easy or tricky it might be.

As always, start simple. I took a fresh Windows VMImage and loaded WAS V7.0.0.19 and DB2 V9.5. I then installed the Trade 6 application which worked superbly well.

Now let's look at Figure 1 :

FIGURE 1: The Environment

So the Windows VM, as I mentioned, had WAS, the Trade Application and DB2 in it. My intention was to use IBM Workload Deployer V3 to provision a new database in the cloud, migrate the trade data to it, and modify the WAS datasource to point to the new database.

Having re-populated the Trade database successfully after initial installation, I logged in as uid:0 and decided to make a few changes from the default values. Note in Figure 2 that I changed the full name, email and also made a few bucks on buying/selling some stock!

FIGURE 2 : uid:0 screenshot

As a basis for this migration I used the following document :

https://www.ibm.com/developerworks/data/library/techarticle/dm-0403melnyk/index.html

According to the link above, the migration begins with a 2-step process : db2move and db2look.

The db2move utility retrieves a list of all user tables in a database from the system catalog. It then exports these tables in PC/IXF format, which is a version of an adaptation of the Integration Exchange Format (IXF) data interchange architecture.

The db2look utility extracts the DDL statements that are required to reproduce database objects on a different system.

On the command line I entered : db2move tradedb export

OUTPUT :

***** DB2MOVE *****

Action: EXPORT

Start time: Fri Nov 04 12:04:23 2011

Connecting to database TRADEDB ... successful! Server : DB2 Common Server V9.5.1
Binding package automatically ... C:\PROGRA~2\IBM\SQLLIB\BND\DB2COMMON.BND ... successful!
Binding package automatically ... C:\PROGRA~2\IBM\SQLLIB\BND\DB2MOVE.BND ... successful!
EXPORT: 46 rows from table "ADMINISTRATOR"."ACCOUNTEJB"
EXPORT: 46 rows from table "ADMINISTRATOR"."ACCOUNTPROFILEEJB"
EXPORT: 234 rows from table "ADMINISTRATOR"."HOLDINGEJB"
EXPORT: 3 rows from table "ADMINISTRATOR"."KEYGENEJB"
EXPORT: 236 rows from table "ADMINISTRATOR"."ORDEREJB"
EXPORT: 1000 rows from table "ADMINISTRATOR"."QUOTEEJB"
EXPORT: 1 rows from table "SYSTOOLS"."STMG_DBSIZE_INFO"

Disconnecting from database ... successful!

End time: Fri Nov 04 12:04:25 2011

A DIR showed that these files had been created :

11/04/2011 12:04 PM 353 db2move.lst

11/04/2011 12:04 PM 876 EXPORT.out

11/04/2011 12:04 PM 16,058 tab1.ixf

11/04/2011 12:04 PM 146 tab1.msg

11/04/2011 12:04 PM 15,919 tab2.ixf

11/04/2011 12:04 PM 146 tab2.msg

11/04/2011 12:04 PM 27,622 tab3.ixf

11/04/2011 12:04 PM 147 tab3.msg

11/04/2011 12:04 PM 4,627 tab4.ixf

11/04/2011 12:04 PM 145 tab4.msg

11/04/2011 12:04 PM 58,689 tab5.ixf

11/04/2011 12:04 PM 147 tab5.msg

11/04/2011 12:04 PM 135,595 tab6.ixf

11/04/2011 12:04 PM 148 tab6.msg

11/04/2011 12:04 PM 4,391 tab7.ixf

11/04/2011 12:04 PM 145 tab7.msg

I then ran the db2look : C:\temp>db2look -d tradedb -e -a -o db2look.sql

OUTPUT :

-- Generate statistics for all creators

-- Creating DDL for table(s)

-- Output is sent to file: db2look.sql

-- Binding package automatically ...

-- Bind is successful

-- Binding package automatically ...

-- Bind is successful

To create a new copy of this database in the cloud I needed to run these three steps :

1) create a database called tradedb in the cloud

2) run db2look with the previously created db2look.sh
3) run db2move with the rest of the files

Within IBM Workload Deployer it is possible to deploy Virtual Systems or Virtual Applications depending on your requirements. Because of the need to run scripts to populate the database with the trade data, a Virtual System is a much better fit in this case. Virtual Systems on IWD give you the ability to run script packages. One existing script package was available to create a database of my choosing (in this case tradedb). I needed to create two script packages to run the db2look and db2move.

Creating a script package basically involves writing a cbscript.json file and compressing that file, together with any other required files, into a ZIP. The ZIP is uploaded to IWD and during deployment is sent to the virtual system, unpacked and executed.

For db2look, this is my cbscript.json :

[ {

"name": "DB2Look",

"version": "1.0.0",

"description": "This script package runs db2look to re-populate a tradeDb",

"command": "/bin/sh /tmp/db2look.sh",

"log": "/tmp",

"location": "/tmp",

"timeout": "240",

"commandargs": "",

"keys":

[{

"scriptkey": "",

"scriptvalue": "",

"scriptdefaultvalue": ""

]}]}

To begin with I tried to run the db2look directly from the json script but kept getting an execution failure (DB21007E). I tracked this down to the fact that I needed to run dos2unix on the db2look.sql file because of file mismatches between dos and unix. I created a shell script called db2look.sh and called this from the json :

su root -c "dos2unix db2look.sql"

su db2inst1 -c "/opt/ibm/db2/V9.7/bin/db2 -tvf /tmp/db2look.sql"

I then zipped up the cbscript.json, db2look.sql and db2look.sh.

Db2move proved a little easier. This is the cbscript.json contents :

[ {

"name": "DB2Move",

"version": "1.0.0",

"description": "This script package runs db2move to re-populate a tradeDb",

"command": "/opt/ibm/db2/V9.7/bin/db2move",

"log": "/tmp",

"location": "/tmp",

"timeout": "240",

"commandargs": "tradedb load",

"keys":

[{

"scriptkey": "",

"scriptvalue": "",

"scriptdefaultvalue": ""

]}]}

As before I zipped up the cbscript.json file and all the files previously created with the db2move export command.

Next, I logged in to IBM Workload Deployer (IWD) and created the two script packages for db2look and db2move.

FIGURE 3 : db2look script package

FIGURE 4 : db2move script package

I then created new virtual system pattern called tradeDb repopulate and added my new script packages together with the IBM-supplied one to create the tradedb.

FIGURE 5 : Pattern

I decided to order the execution of the script packages to make sure they ran in the correct order :

FIGURE 6 : Ordering

then deployed to cloud. After a short while I could check that the script packages had successfully ran :

FIGURE 7 : Check Script Packages

I obtained the IP address of the new database and went back to the Windows VMImage and added a hosts entry for "dbaas". After that I used the WAS Admin Console to
modify the Trade datasource to point to the new remote database (Figure 8) :

FIGURE 8 : Modify datasource

I then retested the Trade Application. It performed flawlessly, and logging back in as uid:0 you will observe that the details I changed before migration have obviously been retained.

FIGURE 9 : Retest

This simple post does show that migration from a local DB2 database to one in the cloud is not as difficult as it might first appear!

Passing variables into script packages with IWD/PureApp

S4QD_Tony_Barker Tags: iwd pureapplication vsp 5,948 Views

I just wanted to write a quick entry to explain how variables can be passed into the script packages within IWD or PureApplication. It's just one of those things that causes confusion, and rightly so. It's not necessarily difficult, but takes some effort to understand.

So, let's assume you have WebSphere Application Server and that you need to configure it using a script package. Let's say that, amongst other things, the script package will create a JDBC Provider and a datasource.

Now, at deploy time, a cell name and node name are provided for us but they can be changed if they are not locked down. How do we capture these values so that we don't hard code them into our scripts? Similarly, how can we ever create a datasource without knowing all the details about the database itself?

Some of the values that we will need are contained in the virtualimage.properties file which resides in the /etc directory :

CELL_NAME=CloudBurstCell_1
NODE_NAME=CloudBurstNode_1
WAS_PROFILE_ROOT=/opt/IBM/WebSphere/Profiles/DefaultAppSrv01

but for the database details we need to expose them in the cbscript.json for the script package. Here we have exposed the database name, user, password, host and port.

{
    "name": "WASConfiguration",
    "description": "Configure WebSphere",
    "command": "sh /tmp/MyApp/Configure.sh",
    "log": "/tmp/MyApp",
    "location": "/tmp/MyApp",
    "timeout": 120000,
"keys":
      [
         {
          "scriptkey": "DATABASE_NAME",
          "scriptvalue": "MyDB",
          "scriptdefaultvalue": "MyDB"
         },
   {
          "scriptkey": "DATABASE_USERNAME",
          "scriptvalue": "db2inst1",
          "scriptdefaultvalue": "db2inst1"
         },
   {
          "scriptkey": "DATABASE_PASSWORD",
          "scriptvalue": "ibmdb2",
          "scriptdefaultvalue": "ibmdb2"
         },
   {
          "scriptkey": "DATABASE_HOST",
          "scriptvalue": "${DB2_ESE.hostname}.${DB2_ESE.domain}",
          "scriptdefaultvalue": "${DB2_ESE.hostname}.${DB2_ESE.domain}"
         },
   {
          "scriptkey": "DATABASE_PORT",
          "scriptvalue": "50001",
          "scriptdefaultvalue": "50001"
         }
      ]
}

The other parts in a pattern all have properties too, and you can see from the DATABASE_HOST we have used these to our advantage. Look here for further details on how to take advantage of that. Basically, we can gather hostname and domain name details of our database at deploy time which is great because otherwise we will have no idea what it was given. Remember that IP addresses are assigned from a pool - we can't guarantee which one it will get!

So, at this point our script package has all the variables in the virtualimage.properties files AND the extra variables we have assigned in the cbscript.json.

The cbscript.json points towards Configure.sh. Inside Configure.sh is this line:

${WAS_PROFILE_ROOT}/bin/wsadmin.sh -conntype SOAP -lang jython -f /tmp/MyApp/WASConfiguration.py ${CELL_NAME} ${NODE_NAME} $DATABASE_NAME $DATABASE_USERNAME $DATABASE_PASSWORD $DATABASE_HOST $DATABASE_PORT

It's a wsadmin scripting command. It's specifying the WASConfiguration.py file which contains all the scripting to configure WAS. Notice how all the variables are
being passed as arguments.

The WASConfiguration.py shows this (NOTE: these are only some commands. The SAVE commands and other admin commands are missing to save space and time!) :

cellName=sys.argv[0]
nodeName=sys.argv[1]
dbName=sys.argv[2]
db2UserName=sys.argv[3]
db2Password=sys.argv[4]
dbHost=sys.argv[5]
dbPort=sys.argv[6]

jdbcDB2ProviderId=AdminTask.createJDBCProvider('[-scope Node='+nodeName+',Server=server1 -databaseType DB2 -providerType "DB2 Using IBM JCC Driver" -implementationType "XA data source" -name "DB2 Using IBM JCC Driver (XA)" -description "Two-phase commit DB2 JCC provider that supports JDBC 4.0 using the IBM Data Server Driver for JDBC and SQLJ." -classpath [${DB2_JCC_DRIVER_PATH}/db2jcc4.jar ${UNIVERSAL_JDBC_DRIVER_PATH}/db2jcc_license_cu.jar ${DB2_JCC_DRIVER_PATH}/db2jcc_license_cisuz.jar ${PUREQUERY_PATH}/pdq.jar ${PUREQUERY_PATH}/pdqmgmt.jar ] -nativePath [${DB2_JCC_DRIVER_NATIVEPATH} ] ]')

authenticationAlias = AdminTask.createAuthDataEntry('[-alias "MyDB database DB2" -user '+db2UserName+' -password '+db2Password+' -description]')

jdbcDB2DataSourceId = AdminTask.createDatasource(jdbcDB2ProviderId, '[-name "MyDB" -jndiName "jdbc/MyDB" -dataStoreHelperClassName com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper -containerManagedPersistence true -componentManagedAuthenticationAlias "'+nodeName+'/MyDB database DB2" -xaRecoveryAuthAlias "'+nodeName+'/MyDB database DB2" -configureResourceProperties [[databaseName java.lang.String '+dbName+'] [driverType java.lang.Integer 4] [serverName java.lang.String '+dbHost+'] [portNumber java.lang.Integer '+dbPort+']]]')

AdminConfig.create('MappingModule', jdbcDB2DataSourceId, '[[authDataAlias "'+nodeName+'/MyDB database DB2"] [mappingConfigAlias ""]]')

So the incoming arguments are captured and used to replace the hardcoded scripting in the relevant places.

I use the WAS admin console to generate the scripting (using VIEW ADMINISTRATIVE SCRIPTING COMMAND FOR LAST ACTION) .

And that's how you use variables in your script packages and pass details into shell scripts and wsadmin scripts.

A simple use of Environment Profiles in IWD to separate departments

S4QD_Tony_Barker Tags: iwd 4,600 Views

Introduction

Within IWD you can deploy your patterns to either a simple Cloud Group or make use of an Environment Profile.
Environment Profiles offer a great deal of flexibility in the way that you deploy. Did you know that it's actually possible to allow multiple departments in your organization to use IWD and have complete independence from each other? Well you can with Environment Profiles, but before we take a closer look, let's understand them a bit better.

Environment Profiles

Consider the deployment process. In any such deployment hypervisors are selected, the virtual machines are created and IP addresses are assigned.
You can deploy simply to a cloud group, and thus accept a number of default settings. There is nothing wrong with that approach and it works well. However, what if you want a little more control over your deployments? That's where Environment Profiles come in.

Within IWD you can create Environment Profiles and deploy through them. In effect an Environment Profile is an additional layer above the Cloud Group which controls some of the deployment criteria.

By using Environment Profiles, you can control the following :

The naming format of the virtual machines
Who provides the IP addresses
Which Cloud Group you deploy to
Environment limits : CPU, memory, storage and licenses

Figure 1 : Environment Profile options

When you couple together Environment Profiles and User Groups, and set permissions, you get something very powerful.

Department Separation

I have two departments within my organization : Development and HR. I have members of my IT team who manage the servers for these departments and are quite comfortable creating virtual images using IWD. However, the number of patterns, script packages and deployed instances is large and has no naming convention. Therefore, to try and prevent confusion, we can set up Users, User Groups and Environment Profiles to help us filter out assets created by another user/department. The end goal is for each user to be blissfully unaware of the patterns, script packages and instances created by others.

Here's how...

1. As Administrator, create two users, a Development User and a HR User.

2. Create two User Groups called HR and Development. Grant the following permissions :

i.Deploy patterns in the cloud

ii.Create New Patterns
iii.Create new environment profiles
iv. Create new catalog content

3. Create 2 different Cloud Groups, Development and HR, and give read-only access to the appropriate group. You will need 2 hypervisors - one in each Cloud Group.

4. Place users in appropriate groups

Results

Patterns, deployments created by 1 user group will not be visible to other groups

In this example, only the HR WAS Pattern is visible to the HR User.

Here, the Devuser WAS Pattern is only visible to the Devuser.

Finally, users will not be able to access information on reports, resources, logs except on those that they create, own or share.

Easy!

Understanding Virtual Application Layers in IBM Workload Deployer

S4QD_Tony_Barker Tags: iwd 5,331 Views

Introduction

Think of the word "layers" and it could conjure up any number of thoughts about cakes, clothing on a cold day or even cars! Yes, if you think about, even cars have layers. When I was younger I could only afford the base models, and watched jealously as others had "Sport" models. The Sport model was a base model with larger wheels, better sound system and tinted windows. In other words extra layers were placed on to the base model. And keeping with the car analogy, remember how the car brochure was easy to read, and used tables to indicate different options? It was fairly easy to spot which options you would get with your model of car.

And finally, what if there was a design improvement on the car's door locks? Since the change would happen at the base model, all other models would be changed too.

The layered approach is, perhaps, more common than we first thought and is available when designing virtual applications.

What are virtual applications?

Virtual applications are a Platform as a Service solution in which your application takes center stage. Looking at the diagram below we can see that IBM Workload Deployer is capable of supporting three deployment models: Images, Topologies and Workloads. There are benefits and trade-offs with each one, of course, and they are discussed elsewhere, but
we are interested in the Workloads model - or virtual applications.

With virtual applications we are essentially delegating the hassles of creating the topology to the IBM Workload Deployer - in short, the appliance configures the environment.
Although we bid farewell to a certain degree of flexibility, we enjoy great simplicity and the quickest Time to Value. If the pattern consists of nothing more than :

Your application (EAR or WAR)
Dependent assets (for example, WebSphere Application Server, DB2, LDAP)
Policies (QoS)

then potentially the virtual application pattern allows you take advantage of a reduction in the need for middleware skill requirements.

There is no doubt that, today, options are a little limited. For example, there is no support for the SIBus or remote EJBs. However, if you can fit your application into the virtual application pattern, you stand to reap massive rewards.

Virtual application layers

Layers are available within the virtual application pattern, and offer a way to incrementally build out a virtual application. Each virtual application has one layer by default, and you don't necessarily have to add more. So, if you decide that your virtual application fits nicely into a single layer then that's just fine, but let's take a closer look at layers anyway.

There are two approaches to using virtual application layers : the "create your own" and the "re-use". Each one can save you time and help you to better understand your virtual application pattern.

The "create your own" approach to virtual application layers

This approach is, as the name suggests, a way to build your own layers to create a pattern. Let's look at a simple example.

In this example I have created a very simple virtual application which comprises of an enterprise application, a database and a link to an existing LDAP.

There is no mention of the layer on the canvas because I am only using one layer. I can view all layers by clicking towards the bottom left of the palette on the "up arrow" to expose
the layers.

The only layer here is "layer_1". I can add a new layer by clicking on the left icon :

As soon as I add a new layer, not only is it shown in the Layers section, but the three components on the canvas are designated as being in "layer_1".

I can rename the layers by clicking on them in the left pane. In this example I have renamed layer_1 as "Base" and the second layer as "QoS".

Clicking on the blue "+" I have added a scaling policy. By default it is assigned to the QoS layer. You can change the assigned layer of a component or policy by clicking on the layer name within the component itself.

Finally, by clicking on the eyes next to the names of the layers in the palette you can enable or disable the layers. When disabling a layer the canvas view will alter and grey out all components in the disabled layer.
However, be aware that the enable/disable feature does NOT affect the deployment. When the virtual application is deployed ALL layers are deployed as well. Therefore the enable/disable feature is only for editing, to allow you to breakdown and review complex deployments more easily. I would suggest that "enable/disable" is perhaps not the best wording, and that "show/hide" is probably more accurate.

So, in this example above, you can see how to create your virtual application pattern and add layers to reduce complexity. As already mentioned, upon deployment all layers are used, e.g. you cannot only deploy baseline and internal QoS....... we would use a different technique for that..... Reference Layering.

Reference layers - the art of reusing

As you saw above, you can create your own virtual application and add layers on top. You can, however, use the concept of reference layers.
This is where you can "import" an existing virtual application pattern as a layer and build on it. What makes this neat is that :

A) The virtual application pattern already exists and you are just going to tweak it for your purposes, and

B) In the event that the "referenced" virtual application pattern is changed, this change is reflected in your own pattern.

To demonstrate this, I can create a new virtual application pattern and first specify a blank application. Although the canvas will be clear, there will still be a "layer1" and anything

created will be placed on that layer.
Now, let's add a reference layer by clicking on the following icon :

and import a virtual application as a reference layer

If I add the virtual application that I created earlier example, you can see that there is a new layer created and all the components are specified as being in that layer.

I can rename "layer" to something different and add, for example, a scaling policy.

Bear in mind that the reference layer is "Read Only" so you will not be able to move the scaling policy into the Default Application Pattern layer, nor will you be able to move any components out of that layer.

This could now be saved, and subsequently deployed as a Development/Test version of the Default Application Pattern virtual application. Furthermore, you could create yet another new blank virtual application, use the same reference layer but add a different scaling policy and perhaps a JVM policy. That could form a production-ready version of this virtual application pattern.

And finally, if the Default Application Pattern changes, any patterns that use it as a reference layer will automatically be changed too.

Conclusions

Virtual application layers help you to build out your virtual application - adding layers of complexity to ensure it meets your requirements.

Enabling/disabling the layers will help you to understand the components of your virtual application, but will not affect deployment.

Reference layers are a way of using an initial pre-built virtual application for your needs, and adding your own layers on top of it. The reference layer is "read only".
Any changes to the "referenced " virtual application used as a reference layer will be reflected in the new layered pattern.

Feed for Blog Entries

Blogs