WMB 6.1 @ Ubuntu 9.04

I have WMB 6.1.0.4 with DB2 9.5.0.4 & MQ 7.0.0.2 running on my Ubuntu 9.04 laptop, this blog posting shows what I have done to get that working.

<user> and <password> are the user and password that you use on your Ubuntu pc.

Ubuntu multi-media setup

If you have a default Ubuntu install then not everyting from *.ibm.com is working. To get Download Director, Education Assistant, etc working you can use below commands.

sudo wget http://www.medibuntu.org/sources.list.d/jaunty.list --output-document=/etc/apt/sources.list.d/medibuntu.list
sudo apt-get update && sudo apt-get install medibuntu-keyring && sudo apt-get update

sudo apt-get install ubuntu-restricted-extras libdvdcss2 libdvdread4 w32codecs msttcorefonts flashplugin-installer 
sudo /usr/share/doc/libdvdread4/install-css.sh

sudo apt-get install sun-java6-jdk sun-java6-jre sun-java6-fonts sun-java6-plugin
sudo apt-get install mplayer mozilla-mplayer

Turn of Visual Effects

A bug in Java or DB2-setup give sometimes a grey screen, turning of Visual Effects solves this. (and your laptop is faster)

System -> Preferences -> Appearance -> Visual Effects -> None.

Allow other local users to connect locally to the X-Server

Somtimes you are working as the DB2 instance user, MQM owner and want to start a GUI, with below tweaks this is possible.

sudo vi /etc/gdm/gdm.conf
change "DisallowTCP=true" into "DisallowTCP=false"

sudo vi /etc/profile
add the line "export DISPLAY=localhost:0.0"

vi ~/.profile
add the line "xhost +localhost"

Add more shared memory

Tuning Ubuntu for DB2 and/or MQ can be difficult, for now we only need below setting.

sudo vi /etc/sysctl.conf
add the line "kernel.shmmax=536870912"

Change the password algorithm

The default ubuntu setting for password hashes (sha512) creates a hash thats to long for DB2

sudo vi /etc/pam.d/common-password
change "sha512" to "md5"

Change your password to make the change active, if you want to keep the same password you must do it as root.

sudo passwd <user>

Restart the machine now to make all above ubuntu tweaks active.

DB2, MQ, WMB software

Below the files I used, A total of 5.3 GB. You can get it from IBM PartnerWorld and the support sites for DB2, MQ & WMB.

~/install/db2/DB2_WSE_V95_Linux_x86.tar
~/install/db2/DB2_WSE_Auth_User_Activation_V95.zip
~/install/db2/db2exc_952_LNX_x86.tar.gz

~/install/mq/CZ0EWML.tar.gz
~/install/mq/7.0.0-WS-MQ-LinuxIA32-FP0002.tar.gz

~/install/broker/C19YSML.tar.gz
~/install/broker/6.1.0-WS-MB-LINUXIA32-FP0004.tar.Z

~/install/toolkit/C19Z2ML.tar.gz
~/install/toolkit/C19Z3ML.tar.gz
~/install/toolkit/C19Z4ML.tar.gz
~/install/toolkit/agent.installer.linux.gtk.x86.zip
~/install/toolkit/MB6100_6104.zip

~/install/info/db2_v95_linuxia32_nonroot_infocenter.tar.gz
~/install/info/WebSphere_MQ_V7_Information_Center_Lin_TGZ.zip
~/install/info/wmb_help_linux.tgz

DB2 install

sudo apt-get install libaio1
cd ~/install/db2
mkdir base
cd base
tar xf ../DB2_WSE_V95_Linux_x86.tar
sudo ./db2setup2

Now there are many options possible, below what I did.

- Custom install.
- Selected "Select All" and then de-select 'Informix datasource support' under servers
- Not installed the SA MP Base Compoment
- User configuration:
    DB2 administration server:
      user: db2admin
      group: db2
    DB2 instance:
      user: db2
      group: db2
    Fenced user:
      user: db2soft
      group: db2
- No autostart
- No SMTP notifications

Add yourself to the DB2 groups (in above example it is just one group named db2)

sudo usermod -a -G db2 root
sudo usermod -a -G db2 

DB2 license

cd ~install/db2
mkdir lic
cd lic
unzip ../ DB2_WSE_Auth_User_Activation_V95.zip
sudo /opt/ibm/db2/V9.5/adm/db2licm -a db2/license/db2wse_u.lic

DB2 upgrade

. /home/db2/sqllib/db2profile
db2stop

. /home/db2admin/das/dasprofile
db2admin stop

cd ~/install/db2
mkdir update
cd update
tar xf ../db2exc_952_LNX_x86.tar.gz 
cd expc

sudo ./installFixPack 
/opt/ibm/db2/V9.5

Starting the DB2 control center.

su - db2
db2cc

MQ install

cd ~/install/mq
mkdir base
cd base
tar zxf ../CZ0EWML.tar.gz

sudo apt-get install rpm

sudo ./mqlicense.sh

rm MQSeriesMsg*
rm MQSeriesKeyMan*

sudo rpm -i MQSeries* --nodeps

Adding root and yourself to the mqm group.

sudo usermod -a -G mqm root
sudo usermod -a -G mqm <user>

MQ update

cd ~install/mq
mkdir update
cd update
tar zxf ../7.0.0-WS-MQ-LinuxIA32-FP0002.tar.gz

rm MQSeriesMsg*
rm MQSeriesKeyMan*

sudo rpm -i MQSeries*

Starting the WebSphere Exploper

sudo strmqcfg

Broker install

cd ~install/broker
mkdir base
cd base
tar zxf  ../C19YSML.tar.gz
cd  messagebroker_runtime1

sudo groupadd mqbrkrs

sudo ./setuplinuxia32 -console

Adding root and yourself to the mqbrkrs group

sudo usermod -a -G mqbrkrs root
sudo usermod -a -G mqbrkrs <user>

Broker update

cd ~install/broker
mkdir update
cd update
tar zxf ../6.1.0-WS-MB-LINUXIA32-FP0004.tar.Z
cd disk1

sudo ./setuplinuxia32 -console

Toolkit install

cd ~install/toolkit
mkdir base
cd base
tar zxf ../C19Z2ML.tar.gz
tar zxf ../C19Z3ML.tar.gz
tar zxf ../C19Z4ML.tar.gz

cd disk1
sudo ./installToolkit.sh

The default location is a bit strange, eq /opt/IBM/ , all other IBM software is installed under /opt/ibm/ (under Linux those are 2 different directories), I used below targets.

/opt/ibm/toolkit/shared
/opt/ibm/toolkit/manager
/opt/ibm/toolkit/wmb/code>

Toolkit - Upgrade - Internet connection

Online (internet connection)

sudo /opt/ibm/toolkit/manager/eclipse/IBMIM

Click on upgrade (twice, once for IM and once for WMB)

Toolkit - Upgrade - no internet connection

cd ~install/toolkit
mkdir update
cd update
mkdir im
cd im
unzip ../../agent.installer.linux.gtk.x86.zip
cd ..
mkdir wmb
cd wmb
unzip ../../MB6100_6104.zip

sudo /opt/ibm/toolkit/manager/eclipse/IBMIM

create 2 local repositories ( file -> preferences )

install/toolkit/update/im/repository.config
install/toolkit/update/wmb/MB6100_6104/repository.config

and update the software (twice, once for IM, once for WMB)

Starting the toolkit

/opt/ibm/tookit/wmb/eclipse -product com.ibm.etools.msgbroker.tooling.ide

Btw: The toolkit is the only thing that has installed itself in the Ubuntu menu: Applications => IBM Software Development Platform

Localy installing Information Centers

Both DB2 and MQ have options to install the Information Center as a service, below a portable solution that you can put on your USB stick and use on every Linux machine.

cd ~install/info
tar zxf db2_v95_linuxia32_nonroot_infocenter.tar.gz
unzip WebSphere_MQ_V7_Information_Center_Lin_TGZ.zip
tar zxf WebSphere_MQ_V7_Information_Center_Lin.tgz
tar zxf wmb_help_linux.tgz

cd ~
mkdir InfoCenters
cd InfoCenters

mv /home/herbert/install/info/DB2\ v95\ Information\ Center/ db2
mv /home/herbert/install/info/InfoCenter_for_Linux/ mq
mv /home/herbert/install/info/ibm_help wmb

vi db2/help_start 
add option '-port 25001'

vi mq/ibm_help/help_start.sh 
add option '-port 25002'

vi wmb/help_start.sh 
add option '-port 25003'

chmod 755 wmb/help_start.sh help_stop.sh
chmod 755 wmb/jre/jre/bin/java

vi start.sh

#!/bin/sh
cd `dirname $0`

cd db2
nohup ./help_start &

cd ../mq/ibm_help
nohup ./help_start.sh &

cd ../../wmb
nohup ./help_start.sh &

vi stop.sh

#!/bin/sh
cd `dirname $0`

cd db2
nohup ./help_end &

cd ../mq/ibm_help
nohup ./help_end.sh &

cd ../../wmb
nohup ./help_end.sh &

Make the scripts executable.

chmod 755 start.sh stop.sh

Creating the Default Configuration

The Default Configuration Wizard is not working for me. It thinks that my unix password is wrong, and worse, it does not see that my user is in de DB2 groups. So I created the default configuration by hand.

. ~db2admin/das/dasprofile
db2admin start

. ~db2/sqllib/db2profile
db2start
db2 create database DEFBKD61

Now we must make an ODBC entry.

sudo vi /var/mqsi/odbc/.odbc.ini

and add below lines

[DEFBKD61]
Driver=/opt/ibm/db2/V9.5/lib32/libdb2.so
Description=DB2V9DB DB2 ODBC Database
Database=DEFBKD61

Create the config manager and the broker.

. /opt/ibm/mqsi/6.1/bin/mqsiprofile
mqsicreateconfigmgr WBRK61_DEFAULT_CONFIGURATION_MANAGER -i <user> -a <password> -q WBRK61_DEFAULT_QUEUE_MANAGER
mqsicreatebroker WBRK61_DEFAULT_BROKER -i <user> -a <password> -q WBRK61_DEFAULT_QUEUE_MANAGER -n DEFBKD61 -u <user> -p <password>

The mqsicreate.. commands are creating a queuemanager if needed, however the listener is not created ( ^D means you must press control-D there )

runmqsc WBRK61_DEFAULT_QUEUE_MANAGER

DEFINE LISTENER(TCP) TRPTYPE(TCP) PORT(2424) CONTROL(QMGR)
start LISTENER(TCP)
^D

Ok, let's start it:

mqsistart WBRK61_DEFAULT_BROKER
mqsistart WBRK61_DEFAULT_CONFIGURATION_MANAGER

Wait a few seconds and then look at the result.

tail -n 50 /var/log/syslog

Now we can start the Broker toolkit to add the Default Configuration to our workspace.

/opt/ibm/tookit/wmb/eclipse -product com.ibm.etools.msgbroker.tooling.ide

If you want to import examples into your workspace it is important to use below names for the Domain connection, see also the screen-shots.

Server project: LocalProject
Connection name: LocalDomain.configmgr

Starting and stopping

Finaly we can create a start and stop script, it combines everything installed/configured above.

Start script.

#!/bin/sh

strmqm WBRK61_DEFAULT_QUEUE_MANAGER

. ~db2admin/das/dasprofile

db2admin start

. ~db2/sqllib/db2profile

db2start

. /opt/ibm/mqsi/6.1/bin/mqsiprofile

mqsistart WBRK61_DEFAULT_BROKER
mqsistart WBRK61_DEFAULT_CONFIGURATION_MANAGER

nohup db2cc &
nohup strmqcfg &
nohup /opt/ibm/toolkit/wmb/eclipse -product com.ibm.etools.msgbroker.tooling.ide &

~/InfoCenters/start.sh

stop script

#!/bin/sh

endmqm WBRK61_DEFAULT_QUEUE_MANAGER

. ~db2admin/das/dasprofile
db2admin stop

. ~db2/sqllib/db2profile
db2stop

. /opt/ibm/mqsi/6.1/bin/mqsiprofile

mqsistop WBRK61_DEFAULT_BROKER
mqsistop WBRK61_DEFAULT_CONFIGURATION_MANAGER

~/InfoCenters/stop.sh

..... Read more .....

pQg - New release, new name

Hi, I have released a new release of my SQL to JDO wrapper for use at Google App Engine. Of course the name changes at every release, it's now pQg, PHP with SQL on Google App Engine.

It is still in an early stage, it's a hobby project.

See it live @ Google App Engine

For now this is the Manual :-) ..... Read more .....

How to use pQg

Ok, there are still a lot of bugs and most of the functionality is not yet implemented, but here is a HOW-TO posting about using pQg in your own PHP scripts at GAE.

pQg stands for PHP with SQL on Google App Engine, with pQg you can use SQL statements in PHP at Google App Engine.

Download and installation

You can download the sources here. To use this this zip file you must follow below three steps:

1 - Create a GAE project in Eclipse

See the "Java getting started" tutorial on the GAE website how to do this.

2 - Enable PHP in this GAE project

See my blog posting "Using PHP on GAE" how to do this.

3 - Copy pQg to this GAE project

Copy the files from the zip file to the same directory locations into the GAE project.

Introduction

Google uses BigTable as the datastore for GAE. BigTable is a key-value database, basicly it is just a big hash. It is written by Google for massive scalability in the Google Cloud. Compared to traditional relational databases like Oracle, DB2 & MySQL it has limited functionality.

Most PHP applications are LAMP (Linux, Apache, PHP & MySQL) The LA part of LAMP is done by Google with GAE. Caucho is doing P from LAMP with the excelent product Quercus.

So we have M left from LAMP, normaly this is a relational database like MySQL, on Google App Engine pQg tries to do this for you.

PHP is running inside Java at GAE. Google did make a JDO layer above BigTable. So Java programs (and PHP that runs in the Java environment) have a nice JDO interface to talk to BigTable. pQg is using this JDO layer.

JDO is object based. SQL is relational. Normaly the java folks are storing Java Objects in Relational databases like DB2, Oracle. They use ORM software for this, Object-Relational Mapping. pQg does the oposite, it translate SQL statements to JDO api calls. So pQg is a ROM, Relational to Object mapping :-)

Java JDO classes

First you must write a java JDO class with annotations for every database table you want to use. See below the one that I did wrote for the last demo.

package myDatabase;

import java.lang.reflect.Method;

import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.IdentityType;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;

@PersistenceCapable(identityType = IdentityType.APPLICATION)

public class person {

  @PrimaryKey @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
    
                private   Long      id;
  @Persistent   private   String    firstName;
  @Persistent   private   String    lastName;
  @Persistent   private   String    email;
  @Persistent   private   Double    salary;

  public   Long     getId         ()             { return id;           }
  public   String   getFirstName  ()             { return firstName;    }
  public   String   getLastName   ()             { return lastName;     }
  public   String   getEmail      ()             { return email;        }
  public   Double   getSalary     ()             { return salary;       }
  
  public   void     setFirstName  ( String in )  { this.firstName = in; } 
  public   void     setLastName   ( String in )  { this.lastName  = in; } 
  public   void     setEmail      ( String in )  { this.email     = in; } 
  public   void     setSalary     ( Double in )  { this.salary    = in; } 

}

Above file has the same functionality same as below SQL "CREATE TABLE" statement. ( It is on my todo to automatic generate the JDO classes from a SQL "CREATE TABLE" statement )

CREATE TABLE myDatabase.person (
  id         BIGINT AUTO_INCREMENT ,
  firstName  TEXT ,
  lastName   TEXT ,
  email      TEXT ,
  salary     DOUBLE ,
  PRIMARY KEY (id)
)

pQg Rules for the java JDO class files

• The first field must be a Long with the name id or with the same name as the table. It will be uses as the primary key.


• Every field must have a getXXX and setXXX method (exception for the primary key, that one does not need a setXXX method)


• Only Long, String & Double are currently supported


• This java file must be store under src/ in your GAE project in Eclipse.

Including the pQg source

The best place for the pQg is somewhere under WEB-INF, below a template for a PHP script that loads pQg.

<?php

 // pQg - Import the JDO java classes for the database tables.
 
 import myDatabase.*;
 
 // pQg - Database structure
 
 $pQgCfgClass  ['person'] = 'myDatabase.person';
 $pQgCfgKey    ['person'] = 'id';
 $pQgCfgFields ['person'] = array('id',   'firstName', 'lastName', 'email',  'salary');
 $pQgCfgTypes  ['person'] = array('Long', 'String',    'String',   'String', 'Double'); 

 // pQg - settings
 
 define('PQG_CHECK', TRUE  );
 define('PQG_LOG',   FALSE );
 
 // pQg - Include 
 
 $pQgLocation = './WEB-INF/pQg';
 include "$pQgLocation/pQg.inc";

 
  < your PHP code goes here >

  
  // Close the pQg datastore connection (the open is done on first usage)
 
  pQgClose();
 
?>

Rules how to include the pQg source

• $pQgLocation must be set, pQg uses it.


• All the database tables used must be defined with $pQgCfgClass, $pQgCfgKey, $pQgCfgFields & $pQgCfgTypes


• Setting PQG_CHECK is optional. When set to TRUE pQg will check the SQL statement for correct syntax.


• Setting PQG_LOG is optional. When set to TRUE it will give a lot of information when pQg crashes.

The sql() function

It all about the sql() function of pQg, with ths function you can do the full SQL CRUD ( Create, Read, Update, Delete ).

sql("insert into demo (field1, field2) values(1, 'Yes it works')");

$rows = sql("select demo, field1, field2 from table order by id desc");

sql("update demo set field1 = $field1 where id = $did");

sql("delete from demo where id = $id");

Return value of the sql() function

The return value of the sql() function depends on the SQL statement executed:

• "create" returns the value of the primary key for the created record.


• "select" returns a 2 dimensional array. First dimension are records. This second dimension is an associative array with the field name as key.


• "update" returns the number of records that are updated.


• "delete" returns the number of records that are deleted

Wrappers around the sql() functions

There are 3 wrappers around the sql() function for use with the sql SELECT statement, this way you can control the return value.

• sqlField() - A single field


• sqlRecord() - An associative array


• sqlRecords() - An array with an associative array;

$salary = sqlField("select salary from person where  ... ");
echo $salary;

$record = sqlRecord("select * from person where ... ");
echo $record['firstName'] . ' ' . $record['lastName'];
 
$records = sqlRecords("select * from person ... ");
foreach ($records as $record)
  echo $record['firstName'] . ' ' . $record['lastName'] . '<br>';

SQL expression

There is no SQL engine, so you can not use SQL expressions, however we have a PHP engine, this makes PHP expressions possible. Most of the time you will not notice that it is PHP and not SQL that is evaluating your expression, for example below is the same in SQL and PHP.

set salary = salary + 1500

When using functions there are a lot of differences. For exampel, this SQL:

set abc = upper(substring(xyz, 2, 5))

in PHP this is:

set abc = strtoupper(substr(xyz, 3, 5))

So open 2 browser windows, one with the SQL reference and one with the PHP manual and start translating ...

The expresions in the SQL WHERE clause is the exception for this. Here you must use the JDO syntax, this part is given one on one to the JDO api.

Contents of the ZIP file

The zip file contains below files.

- readme.txt                                             - A small introduction
- war/pQg.php                                            - Testing pQg 
- war/WEB-INF/pQg/*                                      - The PHP sources for pQg
- war/WEB-INF/pQgTest/*                                  - Test files for pQg
- war/WEB-INF/logging.properties                         - Set the log level for pQg to INFO
- src/pQg/database.java                                  - Java source for the database connection
- src/pQg/log.java                                       - Java source for the logging
- src/myDatabase/demo.java                               - Database definition for the first demo
- src/myDatabase/person.java                             - Database definition for the second demo
- src/META-INF/services/com.caucho.quercus.QuercusModule - Tell Quercus about the log function

Status

It is working for the 2 demo scripts and some other test scripts :-) It works for 2 small web-app's from me. Will it work right now for your SQL scripts? Maybe, try it and send me the error reports :-)

Please let me know what you think about it, what you want that pQg must do. ..... Read more .....