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.