DB2, installing DPF on several machines

Introduction
DPF (Data Partitioning Feature) is a DB2 feature (licensed) providing ability to partition a database within a single server or across several (cluster of) servers. It allows sharing workload between several machines or between several processes  (SQL engines) on a single machine. The purpose is simple : better performance.
DB2 edition
DPF is not available in DB2 Express-C edition but can be installed with DB2 Trial Edition (90 days).
Single server and multi-server installation
It is very easy to set up DPF within a single server. More tricky is installing DPF on several physical servers, each one having its own part of the database on the local datastore. It is described in the InfoCenter but applying this instruction is painstaking job.
Two (or more) machines
In case of multi-server installation one machine ("sun") must be the owner of Catalog Partition which cannot be distributed. All other machines should connect to the Catalog Partition using nfs connection.
Prerequisities

• ksh (Korn shell) installed
• nfs server installed and running (for the owner of Catalog Partition)
• nfs client software for other servers participating in DPF

Install DB2 (DPF licensed )
Assume that we have two machines: sun (the owner of Catalog Paritition) and earth. All machines participating in DPF should have its own copy of DB2 installed. Every installation should be the mirror of sun installation (the same installation path), the same DB2 version and FP level.
Create DB2 instance on sun machine
The simplest way is to install from command line directly.

useradd db2fenc1
useradd db2dpf
passwd db2dpf
db2icrt -u db2fenc1 db2dpf

Log on as db2dpf user
Execute commands:

db2set DB2RSHCMD=ssh 
db2set DB2COMM=tcpip
db2 update database manager configuration using svcename db2c_db2dpf 
ssh-keygen

Make password-less connection to db2pdf

cat .ssh/id_rsa.pub >>.ssh/authorized_keys
chmod 600 .ssh/authorized_key 

Create logical partitions on sun server 
By default an instance is created with one logical partition (defined in sqllib/db2nodes.cfg) configuration file.

0 sun 0

Logical partitions can be added directly by modyfing db2nodes.cfg file

0 sun 0
1 sun 1
2 sun 2

or by executing a command

db2start dbpartitionnum 1 add dbpartitionnum hostname sun port 1
db2start dbpartitionnum 2 add dbpartitionnum hostname sun port 2

After creating logical partitions stop and start db2 to be sure that instance is working

db2stop
db2start

The output should look like:

0/22/2014 06:02:37     1   0   SQL1063N  DB2START processing was successful.
10/22/2014 06:02:37     0   0   SQL1063N  DB2START processing was successful.
10/22/2014 06:02:38     2   0   SQL1063N  DB2START processing was successful.

NFS on sun machine
Make sure that nfs is installed and running on sun machine.

[root@sun ~]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]
Stopping RPC idmapd:                                       [  OK  ]
Starting RPC idmapd:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
[root@sun ~]# service nfs status
rpc.svcgssd is stopped
rpc.mountd (pid 4573) is running...
nfsd (pid 4638 4637 4636 4635 4634 4633 4632 4631) is running...
rpc.rquotad (pid 4569) is running...

Export /home/db2pdf
Modify /etc/exports file

/home/db2dpf *(insecure,rw,async,no_root_squash)

Execute command

export -a
showmount -e sun

[root@sun ~]# showmount -e sun
Export list for sun:
/home/db2dpf *

Create db2pdf user on earth machine
Create group db2dpf and user db2dpf on earth machine. It is very important that uid and gid should be exactly the same as uid and gid on the sun machine (assuming 522 for uid and gid)

mkdir /home/db2dpf
groupadd -g 522 db2dpf
useradd -u 522 -g db2dpf -m -d /home/db2dpf db2dpf
chown db2dpf /home/db2dpf
chgrp db2dpf /home/db2dpf
passwd db2dpf

NFS client on earth machine
 Make sure that nfs client software is installed on earth machine. Check that nfs exported directory from sun machine is visible

[root@earth ~]# showmount -e sun

Export list for sun:
/home/db2dpf *

Verify /etc/services configuration file on earth machine
Entries in /etc/services file on earth machine related to db2dpf instance should be copied from sun host

db2c_db2dpf     50001/tcp
DB2_db2dpf      60016/tcp
DB2_db2dpf_1    60017/tcp
DB2_db2dpf_2    60018/tcp
DB2_db2dpf_3    60019/tcp
DB2_db2dpf_4    60020/tcp
DB2_db2dpf_END  60021/tcp

Mount home directory on earth machine 
Add entry to /etc/fstab

sun:/home/db2dpf        /home/db2dpf    nfs     user,rw,timeo=7,hard,intr,bg,suid,lock,exec

Mount and verify

mount /home/db2dpf

Make sure that owner and group in /home/db2dpf are db2dpf

[root@earth ~]# ls -l /home/db2dpf
razem 12
drwxrwxr-x  8 db2dpf db2dpf 4096 10-22 06:02 db2dpf
-rw-r--r--  1 db2dpf db2dpf   81 10-22 05:45 db2nodes.cfg
drwxrwsr-t 25 db2dpf db2dpf 4096 10-22 05:50 sqllib

If it is not the case (nobody owner) execute

/usr/sbin/nfsidmap -c

Log on to earth machine as db2dpf
If successful execute commands: db2, db2level
Add partitions controlled by earth machine
Log on to the sun machine and extend sqllib/db2nodes.cfg file or add them from command line

0 sun 0
1 sun 1
2 sun 2
3 earth 0
4 earth 1
5 earth 2

Restart db2 on sun machine
db2stop
db2start

[db2dpf@sun ~]$ db2start
10/27/2014 15:51:02     0   0   SQL1063N  DB2START processing was successful.
10/27/2014 15:51:03     2   0   SQL1063N  DB2START processing was successful.
10/27/2014 15:51:03     1   0   SQL1063N  DB2START processing was successful.
10/27/2014 15:51:05     5   0   SQL1063N  DB2START processing was successful.
10/27/2014 15:51:06     4   0   SQL1063N  DB2START processing was successful.
10/27/2014 15:51:08     3   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

Create SAMPLE database 
 If multi-partitioned instance is started successfully create SAMPLE database (it will take several minutes)

db2sampl

Connect to SAMPLE database and verify table distribution

db2 connect to SAMPLE

[db2dpf@sun ~]$ db2 "SELECT DBPARTITIONNUM(EMPNO), COUNT( * ) FROM EMPLOYEE GROUP BY DBPARTITIONNUM(EMPNO)"

1           2          
----------- -----------
          0           6
          2           6
          1           8
          3           9
          4           5
          5           8

  6 record(s) selected.

EMPLOYEE table is distributed between six partitions.
It is final ?
No. Although SAMPLE database is created in partitioned environment and distributed evenly the container (directory where database is located) is located on the sun machine in db2dpf directory.

[db2dpf@NC9128110007 ~]$ db2 list tablespace containers for 2

            Tablespace Containers for Tablespace 2

 Container ID                         = 0
 Name                                 = /home/db2dpf/db2dpf/NODE0000/SAMPLE/T0000002/C0000000.LRG
 Type                                 = File

[db2dpf@sun ~]$ ls db2dpf
NODE0000 NODE0001 NODE0002 NODE0003 NODE0004 NODE0005

Thus the earth server is reading data for 3, 4 and 5 partition from nfs mounted directory which does not make any sense.
Create database distributed between different servers
On sun and earth machine create directory under the same location but outside nfs managed space. The owner of the directory should be db2dpf.

mkdir /db2dpf
chown db2dpf /db2dpf
chgrp db2dpf /db2dpf

Then (from sun machine) create database stored in /db2dpf directory

 db2 create database sales on  "/db2dpf"

Both sun and earth will look for their partitions in /db2pdf container but every time it will be a local directory.
On sun and earth machine

[db2dpf@sun ~]$ ls /db2dpf/db2dpf
NODE0000 NODE0001 NODE0002

[db2dpf@earth ~]$ ls /db2dpf/db2dpf
NODE0003 NODE0004 NODE0005

Conclusion
Creating partitioned database distributed between several servers is painstaking job for the first time. We have to mix some server administration tasks (nfs) and DB2 administrating tasks. But for the second and next time it does not cause many problems.

MSSQL to DB2 migration, C# client

Introduction
Migration from MSSQL to DB2 is not only limited to the server side (database schema, SQL server code, data etc) but also a client side migration can be a challenge. Even if standard and database agnostic interface (like ADO.NET) is used,  traps can be hidden everywhere.
MSSQL simple stored procedure

CREATE PROCEDURE [dbo].[testDef](
  @outpar varchar(100) output,
  @par1 int = 10,
  @par2 int = 20
)
AS
   SET @outpar = 'par1=' +  CAST(@par1 as VARCHAR(100)) + ' par2=' + CAST(@par2 AS VARCHAR(100))
GO

C# client code

namespace SPFramework
{
    public enum DBType { db2, mssql };

    public static class C
    {
        public const int NOPAR = -1;
    }

    public interface IExecuteSP
    {
        String callSP(int par1, int par2);
    }
}

namespace SPFramework
{
    public class CallSP : IExecuteSP
    {
        private readonly DBType dType;
        private readonly String connName;

        public CallSP(DBType dType, String connName)
        {
            this.dType = dType;
            this.connName = connName;
        }

        public String callSP(int par1, int par2)
        {
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbConnection cn = db.CreateConnection();
            cn.Open();
            DbCommand cm = cn.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "testDef";
            db.AddOutParameter(cm, "outpar", DbType.String, 100);
            if (par1 != C.NOPAR) db.AddInParameter(cm, "par1", DbType.Int16, par1);
            if (par2 != C.NOPAR) db.AddInParameter(cm, "par2", DbType.Int16, par2);
            cm.ExecuteNonQuery();
            String res = (String)db.GetParameterValue(cm, "outpar");
            cn.Close();
            return res;
        }
    }
}

C# unit test

    [TestClass]
    public class UnitTestCallSp
    {
//        private const DBType dType = DBType.mssql;
//        private const String connName = "default";

        private const DBType dType = DBType.db2;
        private const String connName = "db2";

        [TestInitialize()]
        public void Initialize()
        {
            try
            {
                DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
            }
            catch (System.InvalidOperationException)
            {

            }
        }


        [TestMethod]
        public void TestMethod1()
        {
//            DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(C.NOPAR, C.NOPAR);
            Assert.AreEqual("par1=10 par2=20", res);
        }

        [TestMethod]
        public void TestMethod2()
        {
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(5, 7);
            Assert.AreEqual("par1=5 par2=7", res);
        }

        [TestMethod]
        public void TestMethod3()
        {
            IExecuteSP iSP = new CallSP(dType,connName);
            String res = iSP.callSP(5, C.NOPAR);
            Assert.AreEqual("par1=5 par2=20", res);
        }

        [TestMethod]
        public void TestMethod4()
        {
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(C.NOPAR, 7);
            Assert.AreEqual("par1=10 par2=7", res);
        }

SP migrated to DB2

CREATE OR REPLACE PROCEDURE TESTDEF (OUT OUTPAR VARCHAR(100),in par1 int DEFAULT 10, in par2 int DEFAULT 20)
P1: BEGIN
      SET OUTPAR = 'par1=' || PAR1 || ' par2=' || PAR2;  
END P1 

C# unit test executed against DB2
Unfortunately, the 4th test will fail for DB2, "par1=7 par2=20" is returned. It is explained under this link. 

Note: When the CommandType property is CommandType.StoredProcedure, named parameters are not supported.

Parameter name is ignored here, the parameters are assigned from left to right. The same problem persists also for Java and C clients.
How to tackle the problem
The problem may be resolved in many ways, the straightforward way is to create parameter list reflecting the order of parameters in SP signature and do not rely on default values. But what to do if we have hundreds of places like that in the application client code, every occurence requires review, comparing against SP signature and fixing.
Solution
DB2 supports parameter name while invoking SP but calling sequence should be different. It is explained under this link - point 3. Use calling sequence: "CALL TestDef(par1=>@value1, par2=>@value2)". If parameter is omitted then the default value is used. But it is not enough to modify 'CommandText' in the C# sample code above.

• CommandType.Text should be set.
• Parameter name means parameter marker (@value1) in the calling sequence, not parameter name in the SP signature.
•  All parameter markers in the calling sequence should be covered by parameter values.

The solution is to dynamically create 'CommandText' string reflecting the current parameter list.

namespace SPFramework
{
    public class CallSP : IExecuteSP
    {
        private readonly DBType dType;
        private readonly String connName;

        public CallSP(DBType dType, String connName)
        {
            this.dType = dType;
            this.connName = connName;
        }

        private void fixDB2(DbCommand cm)
        {
            String cmd = "CALL " + cm.CommandText;
            Boolean notfirst = false;
            foreach (DbParameter par in cm.Parameters)
            {
                if (notfirst) cmd += ","; else cmd += "(";
                notfirst = true;
                cmd += par.ParameterName + "=>@" + par.ParameterName;
            }
            if (notfirst) cmd += ")";
            cm.CommandText = cmd;
            cm.CommandType = CommandType.Text;
        }

        public String callSP(int par1, int par2)
        {
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbConnection cn = db.CreateConnection();
            cn.Open();
            DbCommand cm = cn.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "testDef";
            db.AddOutParameter(cm, "outpar", DbType.String, 100);
            if (par1 != C.NOPAR) db.AddInParameter(cm, "par1", DbType.Int16, par1);
            if (par2 != C.NOPAR) db.AddInParameter(cm, "par2", DbType.Int16, par2);
            if (dType ==  DBType.db2) fixDB2(cm);
            cm.ExecuteNonQuery();
            String res = (String)db.GetParameterValue(cm, "outpar");
            cn.Close();
            return res;
        }
    }
}

'fixDB2' method modifies 'CommandText'.
For instance:

String res = iSP.callSP(C.NOPAR, 7);

Calling sequence will look like:

CALL TESTDEF(param2=>@param2)

Conclusion
This solution requires some additional programming (fixDB method) but does not require painstaking modification of the client calling sequence. What more important - the code is not only easy to migrate but also keeps backward compatibility.

Byliśmy na koncercie

Piękny Brzeg Sztuki to nowa inicjatywa władz dzielnicy składająca się z wydarzeń kulturalnych z różnych dziedzin sztuki. 17 września poszliśmy do kościoła p.w. św. Stanisław Kostki na koncert muzyki kameralnej. Podobało nam się bardzo, jednak wielka szkoda, że z repertuaru wypadła główna atrakcja, czyli "Fratres" Arvo Pärta. W ten sposób koncert, który w zamierzeniu mógłby być prezentacją XX wiecznych kompozytorów z rejony Europy Środkowo-Wschodniej (Bacewicz, Szostakowicz, Arvo Pärt) stał się po prostu wykonaniem dwóch utworów.
Piękny Brzeg Sztuki to bardzo dobra inicjatywa, miejmy nadzieję, że będzie kontynuowana i rozwijana w kolejnych latach, zaś koncerty muzyczne będą w niej zajmować poczesne miejsce.

New version of JavaHotel, mailing

Introduction
I deployed new version of JavaHotel application. Google App Engine demo is available here (U/P user/user), source code can be downloaded here. New feature is mailing.
Booking
Sending a confirmation mail is a part of the booking process. After successful booking confirmation note can be sent immediately.

If answer is yes then a window with note content pops up.

The note content is generated automatically through XSLT transformation. XSL source file is available here. By extending XSL source file it is possible to modify the confirmation note content: add some graphics, information about cancelation policy, payment method etc. Note content can be also modified manually before sending.
Confirmation email can be sent also later or resent again in case of reservation changing.

Mailing PDF receipt
Automatic mailing is enabled for PDF receipt. Hotel guest can ask for a second copy of the receipt after leaving the hotel or wants to make some changes in the receipt content (for instance address data).

The receipt PDF is attached to the note. The content of receipt note is also generated using XSLT and can be modified as desired.

Email collecting
All notes sent from the application are stored internally in the database (independently from 'Send' folder in the mail box).
The user can review all notes related to the particular reservation.

Also all notes sent to the customer/guest can be reviewed.

Next step

Advance payment.

XSLT 1.0, java function

Introduction
XSLT is a powerful tool and I'm planning to use it for creating some standard forms and emails in JavaHotel application. For instance: to send booking confirmation to the customer. To keep all stuff simple I'd like to send a text (not html) email. But even in the text email it were nice to have some simple formatting:

Night       Adults Daily Rate
2014/03/01       2       77  EUR
2014/03/02       2       77  EUR
2014/03/03       2       77  EUR
--------------------------------
                 Total:  231 EUR 

But unfortunately XSLT does not contain anything like 'padding-left' or 'padding-right' function.
Xalan XSLT processor available in JSE Sun Java is 1.0 (I'd like to avoid additional dependencies) so one cannot use XSLT 2.0 features like XPath 2.0 function library or xsl:function (a huge library is available here).
So the only solution is to create a custom padding functions.
XSLT java function enhancement
It is not easy being on short notice to grasp how to create custom Java function for XSLT. What's more - there are differences between Apache Xalan and Saxon. But after picking the essentials everything runs smoothly.
There are two simple ways to declare Java enhancement in XSLT document (very useful link).
The first requires full qualified (with package) Java class name.

<xsl:template name="currentTime" xmlns:java="http://xml.apache.org/xslt/java">
  <xsl:value-of select="java:java.util.Date.new()">
</xsl:value-of></xsl:template>

The second method: the prefix is bound to a specific class and function name can be qualified by namespace only.

<xsl:template name="currentTime" 
              xmlns:date="java://java.util.Date">
  <xsl:value-of select="date:new()"/>
</xsl:template>

Solution 
After passing this Rubicon I created a simple application to have output like that:

-----------------------------------------------------------------
         Description     Unit Price            Qty         Amount
-----------------------------------------------------------------
AMD Athlon                   580.00              6        3480.00
PDC-E5300                    645.00              4        2580.00
LG 18.5" WLCD                230.00             10        2300.00
HP LaserJet 5200            1100.00              1        1100.00
-----------------------------------------------------------------
                                             Total        9460.00

Full source code is available here (Java Main, xslt template, test input file and Java custom library).
Java function code is extremely simple and straightforward. 

public class MyFun {

    public static String upperCase(String s) {
        return s.toUpperCase();
    }

    public static String fillString(int length, String s) {
        StringBuffer b = new StringBuffer();
        for (int i = 0; i < length; i++)
            b.append(s.charAt(0));
        return b.toString();
    }

    public static String paddingLeft(int padd, String s) {
        if (s.length() >= padd)
            return s;
        return fillString(padd - s.length(), " ") + s;
    }

    public static String paddingRight(int padd, String s) {
        if (s.length() >= padd)
            return s;
        return s + fillString(padd - s.length(), " ");
    }

}

MSSQL to DB2 migration, sp_executesql

Introduction
Assume migrating the following SP (stored procedure) from MSSQL to DB2 (full source code)

CREATE PROCEDURE getCustomers(
@CUSTID INTEGER = NULL,
@CUSTNAME VARCHAR(100) = NULL,
@CUSTLASTACT DATETIME = NULL
)
AS
BEGIN
DECLARE @SELECT NVARCHAR(MAX);
DECLARE @WHERE VARCHAR(MAX);

SET @SELECT = 'SELECT * FROM CUSTOMER';

SET @WHERE = '';

IF @CUSTID IS NOT NULL
SET @WHERE += ' AND CUSTID = @pCUSTID';

IF @CUSTNAME IS NOT NULL
SET @WHERE += ' AND CUSTNAME = @pCUSTNAME';

IF @CUSTLASTACT IS NOT NULL
SET @WHERE += ' AND CUSTLASTACT >= @pCUSTLASTACT';

IF LEN(@WHERE) > 0 BEGIN
PRINT @WHERE
SET @WHERE = SUBSTRING(@WHERE,5,9999);
PRINT @WHERE
SET @SELECT = @SELECT + ' WHERE ' + @WHERE;
END;

DECLARE @PARAMDEF NVARCHAR(MAX);

SET @PARAMDEF = '
@pCUSTID INT,
@pCUSTNAME VARCHAR(100),
@pCUSTLASTACT DATETIME';

PRINT @SELECT;
-- PRINT @PARAMDEF;

EXECUTE sp_executesql @SELECT, @PARAMDEF,
@pCUSTID = @CUSTID,
@pCUSTNAME = @CUSTNAME,
@pCUSTLASTACT = @CUSTLASTACT;


END

This SP prepares simple SQL query statement adjusting WHERE clause to the input parameters accordingly. There are 8 combinations of WHERE clause here. All combinations are covered by single sp_executesql statement.
At first glance it is very easy to translate this SP to DB2 syntax. The DB2 equivalence of sp_executesql is PREPARE, EXECUTE or OPEN {cursor} command. But unfortunately DB2 does support named parameter markers in PREPARE statement for dynamic statements.

The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement.

 Parameters are assigned to markers in order from left to right. The number of parameter markers should correspond to the number of parameter values in EXCUTE or OPEN{cursor} statement. Otherwise exception is raised. So single instance of sp_executesql should be replaced by 8 instances of EXECUTE statement covering all cases of WHERE clause.
Of course - it is not feasible.
First solution
One solution is to develop the SQL statement in the fly.
IF pCUSTLASTACT IS NOT NULL THEN

  SET WHERE = WHERE || ' AND CUSTLASTACT >= ' || pCUSTLASTACT;
END IF;

But this solution is vulnerable to SQL injection risk and open a security gap.
Another solution is to add ELSE clause to have a constant number of parameter markers (full source code).

 IF pCUSTLASTACT IS NOT NULL THEN
SET pWHERE = pWHERE || ' AND CUSTLASTACT >= ?';
ELSE
SET pWHERE = pWHERE || ' AND ? IS NULL';
END IF;

IF LENGTH(pWHERE) > 0 THEN
-- PRINT @WHERE
SET pWHERE = SUBSTR(pWHERE,5,9999);
-- PRINT @WHERE
SET pSELECT = pSELECT || ' WHERE ' || pWHERE;
END IF;

PREPARE STMT FROM pSELECT;
OPEN DC USING pCUSTID,pCUSTNAME,pCUSTLASTACT;

But I'm not happy with that and do not like blurring simple SQL query only to meet syntax requirements and decided to develop a simple DB2 implementation of sp_executesql routine.
Simple DB2 implementation of sp_executesql
The solution contains two SP : external Java and SQL

• Keep original MS/SQL query statement untouched (with named parameter markers)
• Prepare associate map: marker name -> parameter value (equivalence of list of parameters to the sp_executesql procedure)
• Java external procedure scans query from left to right and replaces named parameter markers to DB2 ? marker and prepares list of parameter values in the proper order.
• Example:  input query "WHERE CUSTID = @pCUSTID AND CUSTLASTACT=@pCUSTLASTACT" is replaced with "WHERE CUSTID=? AND CUSTLASTACT=?" and output list contains corresponding parameter values in proper order is created accordingly.
• SQL_EXECUTESP routine declares a cursor and invokes OPEN DC clause regarding only the number of markers in the input query string.

PREPARE STMT FROM MQUERY;
IF PARS IS NULL THEN SET CARD = 0;
ELSE
SET CARD = CARDINALITY(PARS);
END IF;
IF CARD = 0 THEN
OPEN DC;
ELSEIF CARD = 1 THEN
OPEN DC USING PARS[1];
ELSEIF CARD = 2 THEN
OPEN DC USING PARS[1],PARS[2];
ELSEIF CARD = 3 THEN
OPEN DC USING PARS[1],PARS[2],PARS[3];
ELSE
ERRVAL = RAISE_ERROR('70001',CA || ' number of markers in the statement too big';
END IF; 

The source code :

• deftype.sql  : contains type definition and external Java SP signature.
• PrepareStm.java : external Java SP procedure body (called internally by SQL_EXECUTESP)
• SQL_EXECUTESP : SQL main procedure

Finally our original MSSQL SP  after migrating to DB2 using solution above (source code).

CREATE OR REPLACE PROCEDURE getCustomers (
pCUSTID INTEGER DEFAULT NULL,
pCUSTNAME VARCHAR(100) DEFAULT NULL,
pCUSTLASTACT TIMESTAMP DEFAULT NULL
)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE pSELECT VARCHAR(32000);
DECLARE pWHERE VARCHAR(32000);
DECLARE NAMEDPAR PARVARARGS;

SET pSELECT = 'SELECT * FROM CUSTOMER';

SET pWHERE = '';

IF pCUSTID IS NOT NULL THEN
SET pWHERE = pWHERE || ' AND CUSTID = @pCUSTID';
END IF;

IF pCUSTNAME IS NOT NULL THEN
SET pWHERE = pWHERE || ' AND CUSTNAME = @pCUSTNAME';
END IF;

IF pCUSTLASTACT IS NOT NULL THEN
SET pWHERE = pWHERE || ' AND CUSTLASTACT >= @pCUSTLASTACT';
END IF;

IF LENGTH(pWHERE) > 0 THEN
-- PRINT @WHERE
SET pWHERE = SUBSTR(pWHERE,5,9999);
-- PRINT @WHERE
SET pSELECT = pSELECT || ' WHERE ' || pWHERE;
END IF;

SET NAMEDPAR['@pCUSTID'] = pCUSTID;
SET NAMEDPAR['@pCUSTNAME'] = pCUSTNAME;
SET NAMEDPAR['@pCUSTLASTACT'] = pCUSTLASTACT;

CALL SQL_EXECUTESP(pSELECT,NAMEDPAR);

END

Final remarks

• The main advantage of this approach is that original SQL query is kept untouched (opposite to the ELSE approach). Also the execution flow is the same. This lessens the danger of introducing regression errors during migration.
• It is not necessary to replace :{marker name} with ? sign. DB2 also supports markers with semicolon as a prefix although the 'name' does not have any meaning.
• It impacts the performance because additional algorithm is performed (parameter matching). In case of warehouse where the main workload is related to the query execution it does not matter a lot (additional execution time is a small constant value). But if performance penalty is too expensive it is possible to optimize the Java routine, replace Java with C++ external or even to get rid of Java routine at all and replace it with pure SQL code.
• While calling Java external routine array of maps (PARVALS type) is replaced by two PARVARARGS arrays. Unfortunately, external Java interface support only ARRAY of simple type values (chars, integers etc) indexed by integers and does not support more complex ARRAYs.
• SQL_EXECUTESP routine declares CURSOR .. WITH RETURN TO CLIENT (not CALLER). It means that result set can be caught only by the client application (Java, C++, CLI) and cannot be intercepted by another SQL calling routine.

MVP Jython framework and mail handling

Introduction
I deployed a new version of MVP framework containing mail handling. Google App Engine demo version is available here (Mail ->Send mail (save)). The purpose is not to create another mail client but to provide simple Jython interface for mail sending (cmail.py). The solution also stores mail sent in the database for future reference.
This interface will be used later in JavaHotel application for tasks like: reservation confirmation, invoice sending etc.
In JEE version JavaMail is used, in Google App Engine Mail Java Api is used.
Test GUI
Sample application contains GUI for testing purpose, a sample usage of cmail.py interface. The test code is available here.
List of mails sent (taken from database, not from 'Sent' folder).

Mail sending (with attachment)

List of attachments from note already sent

Future
Apply the solution is JavaHotel application.