DB2, federation and three-part names

Federation
Federation (comprehensive redbook on that topic can be downloaded here) allows correlating data from different data sources. Although InfoSphere Federation Server requires additional licensing, so called "homogenous" federation (comprising only DB2  databases, local and remote)  is available also in free DB2 Express Edition.
DB2 supports also three-part name referring to remote object. The dbname.schema.tablename notation allows migration from other databases (like Oracle or MS SQL). Although it works nicely unfortunately I found a problem which made me several nervous evenings.
Recreate the problem
Firstly enable DB2 instance for federation

db2 update dbm cfg using FEDERATED yes

db2stop
db2start

Create two databases

db2 create database test
db2 create database feder

Database 'test' will contain our tables and database 'feder' will act as a federated database.
Create two simple tables in 'test'

db2 "CREATE TABLE X1 (A1 INT, A2 INT)"
db2 "CREATE TABLE X2 (B INT)"

Then connect to 'feder' and create federation server

db2 "create wrapper drda"
db2 "create server testserver type db2/udb  version '10.5'  wrapper drda  authid user_name  password \"user_password\"  options(  add dbname 'TEST')"
db2 "CREATE USER MAPPING FOR PUBLIC SERVER testserver  OPTIONS (REMOTE_AUTHID 'user_name', REMOTE_PASSWORD 'user_password')"

Important: pay attention to lack of ' or " in user_name in CREATE SERVER \" in user_password and ' in CREATE USER MAPPING. You can spend several hours trying to discover it on your own !
Create a view (inner join)

db2 "create or replace view XXX as select * from testserver.db2inst1.x1,testserver.db2inst1.x2"

So far so good. But while testing the view there is a surprise.

db2 "select * from xxx"
SQL0158N  Liczba kolumn podana dla tabeli "DB2INST1.XXX" różni się od liczby
kolumn w tabeli wynikowej.  SQLSTATE=42811

There is no way to overcome it. Running this join directly (without passing through view) is successful. It seems that : view on join between remote tables accessed through three-part name is not working.
Solution 
Just come back to old DB2 school and use nicknames.

db2 create nickname testserver_db2inst1_x1 for testserver.db2inst1.x1
create nickname testserver_db2inst1_x2 for testserver.db2inst1.x2
db2 "create or replace view XXX as select * from testserver_db2inst1_x1,testserver_db2inst1_x2"

db2 "select * from xxx"

A1          A2          B          
----------- ----------- -----------

  Wybrano rekordów: 0.

Conclusion 
Hoping to be fixed in next release. Besides, three-part naming convention is very useful.

MS SQL -> DB2 migration, foreign keys

Introduction
During migration from MS SQL Server to DB2  I was facing a problem to generate foreign keys (in DB2 supported format) from MS SQL generated object script without having an access to MSSQL database. It is not a problem to recreate them manually if you have several objects but it could be a problem if you are dealing with hundreds or thousands of them.
So I decided to spend some time on creating a simple program (in Python) to accomplish the task automatically.
The source code is available here (Eclipse PyDev project).
Packages
The solution comprises several packages.
readfiles : read lines from several files (list of files in constructor). It flattens several files to one single reader.
atomizer : transforms input into sequence of atoms. For instance:

CREATE TABLE [dbo].[departments](
 [dept_no] [char](4) NOT NULL,
 [dept_name] [varchar](40) NOT NULL,
PRIMARY KEY CLUSTERED 

Atomizer will output: CREATE TABLE [dbo.department] ( [dept_no ... etc. It simply breaks input  (list of text lines) into sequence of elements ignoring spaces, line breaks etc.
tokenizer : transforms list of atoms into the sequence of recognized keywords ignoring elements out of importance here.
For instance.
Assuming set of constants describing keywords important here:

ALTER=0
SEMICOLON=1
TABLE=2
ADD=3
CONSTRAINT=4
FOREIGN=5
KEY = 6
REFERENCES = 7
BEGCOMMENT=8
ENDCOMMENT=9
CREATE=10
VIEW=11
FUNCTION=12
PROCEDURE=13
GO=14

The output will be the sequence: CREATE (constant 10) TABLE [dbo.department] (as single tekst) ( [dept_no ... (etc). Tokenizer makes further analysis more easier.
foreign : selects foreign key definition and prepares data structure: base table, constraint name, list of columns, reference table name and reference column list. Example:

ALTER TABLE [dbo].[dept_emp]  WITH CHECK ADD FOREIGN KEY([dept_no])
REFERENCES [dbo].[departments] ([dept_no])
ON DELETE CASCADE

or

ALTER TABLE [dbo].[dept_emp]  WITH CHECK ADD CONSTRAINT DEPT_NO_DEPARTMENT_FK FOREIGN KEY([dept_no])
REFERENCES [dbo].[departments] ([dept_no])
ON DELETE CASCADE

publish : takes data structure (describing foreign key definition as describe above) and prepares DDL in DB2 format. For instance:

ALTER TABLE dbo.dept_emp ADD CONSTRAINT "FK_dept_emp_departments" FOREIGN KEY
   (dept_no)
   REFERENCES dbo.departments
   (dept_no)
@

The main program

def test4():    
    R = readfiles.ReadFiles(INF)
    A = atomizer.Atomizer(R)
    T = tokenizer.TOKENIZER(A)
    F = foreign.ForeignSearcher(T)
    a = F.nextForeign()
    f = open("output/foreign_keys.db2","w")
    while a != None :
        s =  foreignDB2.foreignDB2(a)
        print s
        print ""
        print ""
        f.write(s)
        f.write("\n")
        f.write("\n")

        a = F.nextForeign()
    f.close()

The first four statements (objects construction) can be fused into a single statement.
Example input and output.
Conclusion
I found this approach useful. I also reused it in resolving several other problems.

• Migrate only a subset foreign keys definition. Read firstly list of tables (without foreign keys) already migrated and emits only foreign keys related to them.
• Prepare list of all objects (tables, view, UDF and SP) in MS SQL object script. 

Byliśmy na koncercie

22 listopada 2014 roku byliśmy na przedstawieniu opery Richarda Straussa "Ariadna na Naksos" w Filharmonii Narodowej, występ podobał się bardzo, chociaż nie wszystkim byliśmy zachwyceni.
"Ariadna na Naksos" to jedna z tych oper, za którą tak bardzo lubimy Richarda Straussa. W Polsce rzadko wystawiana, na scenie Filharmonii po raz pierwszy. Przyczyną są najprawdopodobniej trudności realizacyjne, wymaga zaangażowania aż 17 solistów i niezbyt licznej, ale za to lubiącej wyzwania orkiestry, zdolnej sprostać skomplikowanej partyturze. Na szczęście w Warszawie w zupełności się to udało, i to nawet w całości krajowymi siłami z pomocą tylko czterech śpiewaków z importu. Szczególnie się podobała Anna Simińska w roli żywiołowej Zerbinetty, po brawurowym wykonaniu niezwykle efektownej arii "Großmächtige Prinzessin" artystka zebrała długie i zasłużone oklaski. Znakomicie także śpiewała Meagan Miller jako Primadonna. Potężny głos tenora Andreasa Schagera zdawał się rozsadzać salę Filharmonii, jakby artysta zapomniał, że nie śpiewa roli boga wojny Marsa czy władcy piorunów Jowisza, a boga winorośli Bachusa.
Wadą była niestety sama forma przedstawienia, gdyż było to przedstawienie koncertowe, bez akcji scenicznej. Brak scenicznego ruchu nadrabiał ekran na którym był wyświetlany polski przekład. Ale pomimo tego prolog, którego znaczną część wypełniają monologi i recytatywy zwyczajnie nużył. W drugim akcie znacznie lepiej było nawet zrezygnować z śledzenia napisów, a po prostu wsłuchać się w muzykę, która jest wspaniała i niezwykła.
Akcja opery jest statyczna, są tutaj dwa akty i na scenie nie musi się wiele dziać. Jednak zgubił się sam zamysł opery, która jest przecież zestawieniem kontrastów. Przeszłości i teraźniejszości (prolog i opera), świata rzeczywistego i mitycznego, wzniosłości i trywialności (Kompozytor i Baletmistrz), tragedii i komedii. Sztuki przez duże S, ale pozbawionej pieniędzy kontra duże pieniądze szukające sztuki przez małe s czy nawet śmiesznych sztuczek.
Muzyka Straussa, zwłaszcza w tak znakomitym wykonaniu, na szczęście zawsze brzmi i zachwyca tak samo, jednak brak scenicznej inscenizacji wyraźnie ubożył odbiór tego dzieła. Trzeba mieć nadzieję, że to wystawienie nie zakończy kariery "Ariadny na Naksos" na warszawskich scenach i będziemy mieć okazję poznać dzieło Straussa w kompletnej formie.

Byliśmy na operze

28 października byliśmy w Teatrze Wielkim na operze Andrzeja Czajkowskiego  "Kupiec wenecki", bardzo nam się podobało.

"Kupiec wenecki" to budząca kontrowersje sztuka Szekspira ze względu na złowrogą postać mściwego Żyda i lichwiarza Shylocka. Budzi kontrowersje oczywiście według współczesnych standardów politycznej poprawności, w czasach Szekspira i późniejszych doskonale wpasowywała się w obowiązujące wówczas antyżydowskie stereotypy.

Ale początkową przyczyną zainteresowania tą sztuką Czajkowskiego, żydowskiego ocaleńca z Zagłady nie była chęć zmierzenia się z tym antysemickim bagażem, ale piękny fragment z V aktu odnoszący się do  muzyki:

How sweet the moonlight sleeps upon the bank.
Here we will sit and let the sounds of music / strains
Creep in our ears. Soft stillness and the night / Let
Become the touches of sweet harmony.

Opera Czajkowskiego jest jednak przede wszystkim współczesnym odczytaniem sztuki na scenie operowej, a nie spojrzeniem na problem antysemityzmu. Oczywiście, dla potrzeb sceny zostały dokonane pewne skróty i przesunięcia, np. zniknęła postać błazna Lancelota, zamiast monologów Księcia Maroka i Księcia Aragonii jest pantomima, zaś mowa Shylocka pojawia się w scenie sądu. Jednak ciągłość i integralność samej sztuki jest zachowana i wiernie oddana. Mocnym akcentem, wykrzyknikiem Czajkowskiego, jest tylko potężny krzyk tłumu "Żyd" jako ostateczne pohańbienie Shylocka po przegranym sądzie. W epilogu realizatorzy wprowadzili niemą postać Shylocka, który skrapia głowę wodą na znak chrztu i zapada się w grobie. Podkreśla to jedność losów Antonia i Shylocka, jeden traci córkę i religię, a drugi ukochanego Bassania, jeden jest wykluczony jako Żyd, a drugi jako homoseksualista.

Inscenizacja przenosi akcję w czasy bardziej współczesne. Nie ma tutaj weneckiego karnawału, kanałów czy pałaców. Scena, gdy Antionio pożycza pieniądze od Shylocka to świat biznesu, królem jest pieniądz. Ucieczka Jessiki i grabież domu Shylocka nasuwa skojarzenia z ponurymi wydarzeniami z czasów III Rzeszy, ale to raczej inwencja realizatorów, a nie zamysł kompozytora.

Głównym elementem scenografii są dwa prostokątne bloki zmieniające swoje położenie, raz przedstawiają salę giełdy i bankierów, a innym razem uliczkę w mieście, zaś innym razem wnętrze domu bogatej dziedziczki Portii. Wiele scen jest wizualnie bardzo pięknych, np. scena z Księżycem w pełni czy scena wyboru pudełka przez Bassania i następującej radosnej zabawy. Czasami ma się jednak wrażenie, że na scenie dzieje się za dużo, sama inscenizacja skupia nadmierną uwagę.

Kulminacyjną sceną jest oczywiście scena sądu, konflikt między wezwaniem do okazania miłosierdzia i zapiekłym dążeniem do wywarcia zemsty przez Shylocka. Bardzo dobrze się tutaj wpasowuje przemowa Shylocka.

Hath not a Jew eyes? Hath not a Jew hands, organs,
dimensions, senses, affections, passions; fed with
the same food, hurt with the same weapons, subject

Nasuwa się tutaj analogia z innym słynnym monologiem.

Tylko dlatego, że się dobrze urodziłeś (hrabio Almaviva),
to już uważasz że jesteś geniuszem ? ..
Co to takiego uczyniłeś, że doszedłeś do takiej fortuny ?
Tylko tyle, że się dobrze urodziłeś i nic więcej !

Shylock w swojej zaciekłości brnie do samego końca, ale zaplątuje się we własne sidła i pada ofiarą perfidnej, prawniczej sztuczki. Cieszymy się, że dobry i szlachetny Antonio ucieka sprzed noża, ale trochę dreszcze nas ogarniają.

Wykonawcami jest międzynarodowy zespół, ale trudno powiedzieć, żeby jedna rola dominowała nad całością i zapadała w pamięć, nawet słynny kontratenor Christopher Robson w roli Antonia. Robson, bardzo wyrazisty wykonawca i posiadający głos o specyficznej barwie zdawał się być idealnym wykonawcą roli Antonia, postaci kruchej, wrażliwej, płaczliwej, zmiennej w nastrojach. Wykonawcami ról Shylocka i jego córki Jessici są ciemnoskórzy wykonawcy (Lester Lynch i Marisol Montalvo), ale nie należy się w tym doszukiwać próby zbudowania pomostu pomiędzy wykluczeniem Żydów i rasizmem. W tej inscenizacji raczej podkreśla obcość, odmienność. Jessica nawet w epilogu, po wyrzeczeniu się ojca, religii i poślubieniu chrześcijanina jest dalej obca (tak samo jest przecież w sztuce Szekspira) i kolor skóry to podkreśla, jest znamieniem nie do zdarcia.

Główną wartością przedstawienia jest wspaniała i niezwykła muzyka. Można szukać podobieństw do "Króla Rogera" Szymanowskiego czy "Zamku Sinobrodego" Bartoka, oczywiście, w samej strukturze, a nie poprzez bezpośrednie zapożyczenia. Muzyka nie jest tłem dla sceny, podkreśleniem czy zaakcentowaniem akcji która się dzieje, ale podąża za słowem czy nawet gestem. Nie ma przerw, nawet chwili wytchnienia, muzyka cały czas jest obecna, jej gęstość niemal fizycznie się wyczuwa. Stawia duże wymagania wykonawcom, ale także słuchaczom, wymaga skupienia i ciągłego nadążania za frazą.

Słuchając tego pięknego przedstawienia trudno zrozumieć, dlaczego tak wspaniałe dzieło pokrywało się kurzem przez ponad 30 lat. Trzeba mieć nadzieję, że teraz, gdy kurz został starty i blask przywrócony wejdzie na stałe do repertuaru operowego.

Więcej informacji o Kupcu Weneckim Czajkowskiego - tutaj.

Google App Engine and Jython 2.7beta3

Problem
I spent several sleepless nights trying to figure out why Jython 2.7beta3 suddenly refused to work in Google App Engine while Jython 2.7 beta2 worked nicely.
It crashes while initializing site.py standard package because Google App Engine blocks any attempt to use ProcessBuilder.

com.jythonui.client.service.JythonService.runAction(com.jythonui.shared.RequestContext,com.jythonui.shared.DialogVariables,java.lang.String,java.lang.String)' threw an unexpected exception: Traceback (most recent call last):
  File "/base/data/home/apps/s~testjavahotel/5.380117830403911812/WEB-INF/lib/jython-standalone-2.7-b3.jar/Lib/site.py", line 571, in 
  File "/base/data/home/apps/s~testjavahotel/5.380117830403911812/WEB-INF/lib/jython-standalone-2.7-b3.jar/Lib/site.py", line 552, in main
  File "/base/data/home/apps/s~testjavahotel/5.380117830403911812/WEB-INF/lib/jython-standalone-2.7-b3.jar/Lib/site.py", line 231, in check_enableusersite
 at jnr.posix.JavaPOSIX.geteuid(JavaPOSIX.java:102)
 at jnr.posix.LazyPOSIX.geteuid(LazyPOSIX.java:115)
 at org.python.modules.posix.PosixModule.geteuid(PosixModule.java:343)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:45)

java.lang.NoClassDefFoundError: java.lang.NoClassDefFoundError: Could not initialize class jnr.posix.JavaPOSIX$LoginInfo

 at com.google.gwt.user.server.rpc.RPC.encodeResponseForFailure(RPC.java:389)
 at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:579)
 at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:265)
 at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:305)
 at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)

This exception (raised in Development and Production mode) can be resolved easy by setting Options.no_user_site = true; before starting the Jython interpreter.

But then came up the second one which unveils only in Production mode (works in Development mode).

javax.servlet.ServletContext log: Exception while dispatching incoming RPC call
com.google.gwt.user.server.rpc.UnexpectedException: Service method 'public abstract com.jythonui.shared.DialogVariables com.jythonui.client.service.JythonService.runAction(com.jythonui.shared.RequestContext,com.jythonui.shared.DialogVariables,java.lang.String,java.lang.String)' threw an unexpected exception: Traceback (most recent call last):
  File "__pyclasspath__/site$py.class", line 571, in 
  File "__pyclasspath__/site$py.class", line 553, in main
  File "__pyclasspath__/site$py.class", line 286, in addusersitepackages
  File "__pyclasspath__/site$py.class", line 261, in getusersitepackages
  File "__pyclasspath__/site$py.class", line 250, in getuserbase
  File "__pyclasspath__/sysconfig$py.class", line 112, in 
  File "__pyclasspath__/posixpath$py.class", line 391, in normpath
AttributeError: 'NoneType' object has no attribute 'startswith'

 at com.google.gwt.user.server.rpc.RPC.encodeResponseForFailure(RPC.java:389)
 at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:579)
 at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteService

Attribute sys.prefix is None for some mysterious reason. Because debugging in Production mode is impossible the only way was to compile Jython from sources (which is not trivial for the first time), add logging messages and trying to encircle the bug. Finally I realized that in Google App Engine Production mode system property "java.class.path" is null.  So the code

        if (root == null) {
            String classpath = preProperties.getProperty("java.class.path");
            ll.warning("classpath=" + classpath + "!");
            if (classpath != null) {
                String lowerCaseClasspath = classpath.toLowerCase();
                int jarIndex = lowerCaseClasspath.indexOf(JYTHON_JAR);
                if (jarIndex < 0) {
                    jarIndex = lowerCaseClasspath.indexOf(JYTHON_DEV_JAR);
                }
                if (jarIndex >= 0) {
                    int start = classpath.lastIndexOf(File.pathSeparator, jarIndex) + 1;
                    root = classpath.substring(start, jarIndex);
                } else if (jarFileName != null) {
                    // in case JYTHON_JAR is referenced from a MANIFEST inside another jar on the
                    // classpath
                    root = new File(jarFileName).getParent();
                }
            }
        }
        if (root == null) {
            return null;
        }

does not find the root path for Jython libraries. There is a bug in this code because clause:

 
   } else if (jarFileName != null) {
                    // in case JYTHON_JAR is referenced from a MANIFEST inside another jar on the
                    // classpath
                    root = new File(jarFileName).getParent();
                }
            }

should be placed outside if classpath != null clause (not inside) and root directory cannot be extracted from Jython jar file path as well.
Solution
But finally I found the solution and it was simple as usual.

 
    protected PythonInterpreter(PyObject dict, PySystemState systemState, boolean useThreadLocalState) {
        if (dict == null) {
            dict = Py.newStringMap();
        }
        globals = dict;

        if (systemState == null)
            systemState = Py.getSystemState();
        this.systemState = systemState;
        setSystemState();

        this.useThreadLocalState = useThreadLocalState;
        if (!useThreadLocalState) {
            PyModule module = new PyModule("__main__", dict);
            systemState.modules.__setitem__("__main__", module);
        }
        
        if (Options.importSite) {
            // Ensure site-packages are available
            imp.load("site");
        }

So it was enough to set Options.importSite = false; before launching PythonInterpreter and the whole stuff related to site.py (useless in Google App Engine restricted environment) is disabled.

New version of JavaHotel application

Introduction
New version of JavaHotel application is deployed to Google App Engine (User/Password user/user). Source files are available here.
Changes implemented:

• Visualization of the reservation state in the reservation panel
• Advance deposit for guaranteed booking
• More convenient to work with reservation
• Confirmed/Not confirmedbooking
• Access to the reservation data from the list of reservations (not only from reservation panel).
• Warning before making the reservation persistent without refreshing it.

Visualization of the reservation state

The following states are recognized:

• Reservation scheduled (not confirmed)
• Reservation confirmed
• Reservation, advance deposit paid by the customer
• Reservation, advance deposit payment date expired
• Customer not arrived. Current date is greater then the beginning of the reservation  and no check-in yet
• Vacant, not paid. Last day of the stay expired and the bill is not balanced
• Vacant. Last day of the stay expired and fully paid
• Vacant excess payment. Last day of the stay expired and the payment exceeds the bill
• Occupied, not paid.
• Occupied, advance paid. Advance deposit payment exceeds the bill still
• Occupied paid
• Occupied excess payment (including advance deposit)

Advance deposit for guaranteed booking
During making the reservation an advance deposit can be specified.

 The advance deposit amount can be entered directly or calculated automatically as a percentage of the total payment. The advance deposit payment date can be specified also.
Important: Current version supports advance payment only as an additional information for the operator. No automatic action is performed.
The advance deposit data can be specified also later by changing the existing reservation.

 

The dialog allows also to enter payment done by the customer.

More convenient way of working with the existing reservation
After clicking the reservation in the panel a smaller windows pops-up.

This window allows simpler access to some functionality related to the reservation. After clicking on "Change reservation" the full reservation dialog is displayed.
Confirmed/not confirmed
Reservation can be marked as confirmed/not confirmed. This indication can be specified at the beginning and can be modified later. Important: confirm/not confirmed status is only for an information, no automatic action is implemented.
Access to the reservation data also from reservation list
"Administration" -> "Reservations"

List of reservations displays also reservation status. After clicking the column the reservation (or stay) details are displayed. It is the same dialog window as in the reservation panel. Also reservation (or stay) modifications can be done here.
Warning before making the reservation persistent
Reservation (also modification) should be done in three steps:

1. Fill reservation data (number of days, number of persons, price list etc)
2. Refresh the reservation. It applies reservation data entered in step 1 and recalculates it. Also checks if reservation does not overlaps with other reservation. 
3. Make reservation. It makes the reservation persistent. Uses the result of the last refreshing. So if user omits step 2 the final result can be different then expected.

In order to  minimize the risk of confusion a warning window is displayed signalizing that some changes were made without refreshing the reservation.

The user can finalize reservation (ignoring changes) or return to the booking dialog.

Next steps

• Taxation
• Reports

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.

New version of JavaHotel application

Description
I uploaded a new version of JavaHotel application. It is available here (U/P user/user), source code is available here. The main change is adding some charts reflecting hotel room occupancy. it is the first, draft version, visualization of different hotel activities will be elaborated more.
The charts are available: (upper menu) -> Administrator -> List of rooms -> Info (right column)

 

Next step
Reservation confirmation by e-mail

Byliśmy na koncercie

18 czerwca 2014 roku byliśmy na koncercie w ramach XXIV Festiwalu Mozartowskiego organizowanym przez Warszawską Operę Kameralną. Koncert odbył się w surowym wnętrzu warszawskiego Kościoła Ewangelicko-Reformowanego, zaś głównym punktem koncertu były tzw. Sonaty kościelne Wolfganga Amadeusza Mozarta. Podobało nam się bardzo, gdyż to muzyka bardzo ładna, ale w Polsce rzadko wykonywana, jakby przytłoczona przez inne, bardziej popularne dzieła Mozarta.
Sonaty kościelne Mozarta nawiązują do dawnej tradycji wykonywania krótkich utworów instrumentalnych w przerwach między czytaniami podczas odprawiania Mszy Świętej. Jest to muzyka na niewielki zespół instrumentów oraz organy. Nie mogą to być utwory zbyt lekkie ze względu na powagę uroczystości ani zbyt ciężkie, aby nie odciągały uwagi słuchaczu od głównego celu obrzędu. W Salzburgu Mozart napisał 17 takich sonat w latach 1772-1780. Krótko po wyjeździe Mozarta z Salzburga sonaty przestały być tam wykonywane jako część nabożeństwa liturgicznego, ale zaczęły drugie życie jako muzyka kameralna.
Wykonawcą podczas koncertu w Kościole Ewangelicko-Refomowanym był zespół muzyków z Warszawskiej Opery Kameralnej. Trochę było szkoda, że podczas wykonywania Sonat organy zostały zastąpione przez pozytyw, stały za tym zapewne trudności wykonawcze.  Może wyjściem byłoby przeniesieniu instrumentalistów z prezbiterium do chóru i wyznaczenie słuchaczom miejsce na bocznych antresolach ? Ale wykonanie ogromnie się podobało, muzycy jako bis powtórzyli jeden z utworów. Jest bardzo miłą tradycją, że w ramach Festiwalu Mozartowskiego w Warszawie przypominany jest także mniej znany repertuar Mozarta.

MVP Jython framwork and visualization

Introduction
I added visualization diagramss to the framework. The brief description is available here and the sample application is available here ('Charts' submenu). Adding visualization to the application is very simple: just add chart tag to the dialog xml specification and enhance Jython backing code to set sequence with proper names and numbers.
To implement visualization I used GWT wrapper around Google Charts. For the time being only basic charts are implemented: Pie Chart, Area Chart, Bar Chart, Column Chart, Line Chart and Scatter Chart. Also a subset of available option is implemented. It will be enhanced gradually.
Pie Chart
Pie Chart is a circular chart divided into sectors illustrating proportion between different data category.

In sample application it is possinle dynamically modify the numbers and texts (also adding new ones and removing existing) to see the effect.
XML definition for this chart and Jython backing code.
Scatter Chart
Scatter chart simply plots points using Cartesian coordinates.

 Another example of scatter chart.

It is possible dynamically change the data for this charts and see the effect.
XML definition : plot and circle.
Jython backing code: plot and circle.
Bar, Column, Area and Line Charts
These charts are very similar to each other. They use horizontal (or vertical) axis for names and another axis for numbers and illustrates proportion between different categories.

XML definition for charts.
Jython backing code for charts.
Next step
Add visualization to JavaHotel application

MS SQL, SP and Python

Introduction
MS SQL allows exporting database objects into sql script file (for instance: stored procedures with the body). ( Database -> Tasks->Generate Scripts) This option allows creating one single script file for all objects exported or separate file for every object.
But what to do if we have one single file with all stored procedures without an access to MS SQL. Or if we want pure SP body not enriched by additional stuff created by "Generate Scripts".
Sometimes we have two versions of the script file (earlier and later) and want to compare what was developed in the meantime. It is much easier to compare having every SP in different file, we can use a standard comparing software (like Meld).
Facing this problem I created a simple Python script which extracts all SP from a single script file and put them into separate files.
Python code - Extract class

'''
Created on 2 lip 2014

@author: sbartkowski
'''

import logging
import os
import shutil

LOGNAME="EXTRACT"

_L = logging.getLogger(LOGNAME)

_CREATE="CREATE"
_PROCEDURE="PROCEDURE"
_GO="GO"

class EXTRACT() :
    '''
    Extracts SP from single export file. Every SP extracted is placed in a separate file.
    It is assumed that the body of the SP is enclosed by CREATE PROCEDURE and GO statements.
    The file naming convention is : schema_SP name.sql
    Example: 
       CREATE PROCEDURE [sales].[SelectUnsold] 
       ....
       GO
       file name: sales_SelectUnsold.sql
    The output directory is created or cleaned if exists   
       
    Attributes:
      infile : the name of the input file containg a bunch of SP
      outdir : output directory
      f : 'file' object' opened as 'infile'        
    '''


    def __init__(self, infile,outdir):
        '''
        Constructor
          Args:
            infile : input file
            outdir : output directory
        '''
        self.infile = infile
        self.outdir = outdir
        self.f = None
        
    def _isProc(self,l):
        '''
        Test if the line contains CREATE PROCEDURE
        Args:
          l : source line
        Returns: 
          True : if CREATE PROCEDURE
          False: otherwise  
        '''
        tokens = l.split()
        if len(tokens) < 3 : return None
        if tokens[0].upper() != _CREATE or tokens[1].upper() != _PROCEDURE : return None
        return tokens[2]
     
    def _isClosingGo(self,l):
        '''
          Test if the line contains GO statement (denoting the end of SP)
          Args:
           l : source line
          Returns:
          True: line with GO
          False: otherwise         
        '''
        
                    
        tokens = l.upper().split()
        if len(tokens) != 1 : return False
        return tokens[0] == _GO
    
    def _outFileName(self,procName) :
        '''
          Constructs the name of the output file related to the SP name
          Args:
            SP name in shape of [schema name].[proc name]
          Returns:
            The output file name (without path)   
        '''
        pName = procName.replace("[","").replace("]","").replace(".","_")
        outFileName = os.path.join(self.outdir,pName) + ".sql"
        return outFileName
   
    def _writeFile(self,fileName,listofl):
        '''
          Flushes the body of the SP to the output file
          Args:
            fileName : the name of the output file
            listofl : the list of lines with SP body          
        '''
        f = open(fileName,"w")
        f.writelines(listofl)
        f.close()          
        
    def run(self):
        '''
          The main method, bounding them all          
        '''
#        print self.infile, self.outdir
        _L.info("Start executing")
        _L.info("Source : "  + self.infile)
        _L.info("Output dir :" + self.outdir)
        if os.path.exists(self.outdir):
            _L.info("Removing content of " + self.outdir)
            shutil.rmtree(self.outdir)         
        _L.info("Creating directory " + self.outdir)
        os.mkdir(self.outdir)
        _L.info("Opening source file")
        self.f = open(self.infile, 'r')
        _L.info("Success, opened")
        listofl = None
        procName = None
        noprocs = 0
        nolines = 0
        # read line by line
        for l in self.f :
            if listofl == None :
                procName = self._isProc(l)
                if procName : 
                    _L.info(procName)
                    listofl = [l,]
                    noprocs = noprocs + 1
                continue
            else :
                listofl.append(l)
                if self._isClosingGo(l) :
                    _L.info("End of " + procName + " found")
                    outfileName = self._outFileName(procName)
                    nolines = nolines + len(listofl)
                    _L.info("Creating " + outfileName + " lines:" + str(len(listofl)))
                    self._writeFile(outfileName,listofl)                    
                    listofl = None
                    
        _L.info("Closing source file")
        _L.info(str(noprocs) + " SP extracted with " + str(nolines) + " lines of code")
        
        self.f.close()    

Python code -  main
Below is an example of usage of this files. It reads two scripts file containing SP and put them into two directories, "old" and "new"

'''
Created on 2 lip 2014

@author: sbartkowski
'''
import logging

from extr import extr

def setLogger():
    logger = logging.getLogger(extr.LOGNAME)
    logger.setLevel(logging.INFO)
    ch = logging.StreamHandler()
    ch.setLevel(logging.INFO)
#formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
#ch.setFormatter(formatter)
    logger.addHandler(ch)


INDIR1="/home/sbartkowski/Dokumenty/db2/SP/2014-03-22"
INDIR2="/home/sbartkowski/Dokumenty/db2/SP/2014-06-22"

OUTDIR1="/home/sbartkowski/Dokumenty/db2/SP/old"
OUTDIR2="/home/sbartkowski/Dokumenty/db2/SP/new"

INFILE="SP.sql"

def main() :
    setLogger()
    E = extr.EXTRACT(INDIR1+INFILE,OUTDIR1)
    E.run()
    E = extr.EXTRACT(INDIR2+INFILE,OUTDIR2)
    E.run()
    

if __name__ == '__main__':
    main()

JavaHotel, new version

Introduction
I deployed next version of JavaHotel application. Demo version (Google App Engine) is available here (U/P user/user) and source code here.
Searching
I added a searching (so far new reservation was activated after clicking in the hotel room/day cell).

After clicking "Search" button a list of hotel rooms available is displayed :

and the user can select a room and a reservation can be started.
Reservation extension
After reservation is booked it is possible to extend it by adding another room to the reservation or extend the reservation period for the room already booked.  This option is available after clicking "Extend reservation" button.

 It is the same searching mechanism as above but after clicking "Add to reservation" button the room selected is added to the existing reservation.
Reservation modification
Also more detailed modification of existing reservation is possible after clicking "Modify reservation" button.

The user can modify reservation for the single day/room line, remove one line from the reservation and add a new line.
Customer/guest info
This option is available through "Upper menu" -> "Administrator" -> "List of customers" -> "Info".

This window shows the activity of the particular customer.
Room/service info
"Upper menu" -> "Administrator" -> "Services" -> "Modify" -> "Show rooms"

List of hotel rooms sharing this service.
Additional changes

• Modifying list of guests (checked in) also during a stay.
• "Administrator" - blockage for removal objects (rooms, customers, services, price list) if there exists any dependent object (for instance: cannot remove a hotel room if there is exist a reservation for this room). Future: add option "out of use" or "deactivate" the object.
• "Spinner" widget (look above) to make user interface more user friendly.

Next step
Implements charts (I'm going to use Google Charts ) to visualize room, service or customer activity.

MVP Jython framework, new enhancements

Introduction
I added several enhancement to MVP Jython framework. Sample application is available here, source code here.
URL parameters
Access to URL parameters in the Jython backing code. It allows passing some additional parameters to the application.  Description is available here. In the sample application : Different lists -> URL params.

Additional standard toolbars for list
It is the small enhancement but allows to avoid repeating the same sequence of buttons. Description is available here.
Application parameters
This enhancement allows injecting some application parameters into the dialog definition. Description is available here.
Spinner widget
Spinner widget allows entering integer values without typing keyboard. It is convenient method if the range limit is not very big. Sample application : Status -> Spinner and Status -> List with spinner. For dialog form spinner range values can be modified dynamically. Description is available here.
JavaScript backing method
JavaScript function can be defined as a response action. Because JavaScript is executed at the client side and it makes user interface more agile. Defining JavaScript response action is possible only if server side action (for instance database querying) is not required. Sample application : Alert -> Dialog with JS. Description is available here.

Byliśmy na operze

W dniu 24 maja 2014 byliśmy w Warszawskiej Operze Kameralnej na przedstawieniu "Napój miłosny" Gaetano Donizettiego, podobało nam się bardzo, chociaż nie wszystkim byliśmy zachwyceni.
"Napój miłosny" to jedno z najbardziej znanych dzieł Donizetttiego, stało się sukcesem niemal od pierwszego wystawienia i po dziś dzień jest jednym z najbardziej popularnych i najchętniej wystawianych dzieł z operowego repertuaru.
"Napó̉j miłosny" jest obecny  w repertuarze Warszawskiej Opery Kameralne od wielu lat, całe przedstawienie jest bardzo dobrze przygotowane i zaśpiewane. Czasami się tylko wydawało, ze głosy śpiewaków rozsadzają skromną scenę teatru. Znakomicie się zaprezentowała młoda śpiewaczka, Agnieszka Kozłowska w roli Aldiny, bardzo dobrze jej partnerowali pozostali soliści.
Natomiast nie spodobała się scenografia. Scenę, oprócz solistów, ożywiają mimowie ubrani w stroje z włoskiej komediu dell'arte, na scenie widzimy postacie przypominające Arlekina i Kolombinę. Mimowie gestami i ruchami podkreślają rozwój akcji, czasami biorąc w niej aktywny udział. Bardzo dobrze się to sprawdza w początkowej scenie, gdy Adina głośno opowiada przeczytaną historię Tristana i Izoldy i mimowie na drugim planie odgrywają tę scenę. Ale taka konwencja nadaje całemu przedstawieniu wyraz ogromnej sztuczności, umowności. "Napój" to przecież historia wielkiej miłości biednego, niepiśmiennego i nieśmiałego wieśniaka Nemorina do bogatej i kapryśnej dziedziczki Adiny. Z pozoru wszystko przemawia przeciwko Nemorinowi, ale jego uczucie jest tak gorące i szczere, że "doktor" oszust Dulcamara przemienia się w prawdziwego cudotwórcę, fałszywa mikstura staje się magicznym napojem, sierżant Belcore, istny bóg wojny Mars któremu pada w ramiona nawet bogini miłości Wenus, poddaje się i rejteruje, sam Nemorino dziedziczy majątek i na końcu zdobywa uczucie pięknej Adiny. Rzeczy fałszywe i niemożliwe stają się prawdziwe i osiągalne.
Jest to historia bajkowa, ale przecież nie baśniowa, nierealistyczna, i to właśnie trochę zgrzyta ze scenografią.
Od premiery tej opery w Warszawskiej Operze Kameralnej minęło już 7 lat, może czas najwyższy aby odświeżyć sceniczną koncepcję przedstawienia i dać lepszą oprawę do wspaniałej gry solistów.