Blog do projektu Open Source JavaHotel

piątek, 13 lutego 2015

SQL Server to DB2 migration, UDF

Introduction
One of the element of any migration is built-in functions conversion. Some of them are the same, some are similar but different and some does not have any direct equivalent and requires additional developing. Freely available http://www.redbooks.ibm.com/redbooks/pdfs/sg246672.pdf ("Function mapping" chapter) contains a good number of examples how to convert SQL Server built-in functions to DB2.
Conversion is quite easy but what to do if we want to get SQL statements migrated to DB2 and keep backward compatibility at the same time.
It is more complicated but also possible. I created a simple project with examples - it is available here.
Solution for DateAdd, DateDiff and DatePart
The main problem is DateAdd and DateDiff functions. Example:
SELECT DATEADD(month, 1, '2006-08-30');
Unfortunately, this statement cannot be migrated directly to DB2 because DB2 does not support enumeration type. So it is necessary to modify also SQL Server statement to achieve backward compatibility.
Instead of using DATEADD(month ...) function replace it with DATEADD_MM function.
CREATE FUNCTION DATEADD_MM(@mins INT, @da DATETIME)
RETURNS DATETIME
AS 
BEGIN
  RETURN DATEADD(mm,@mins,@da)
END 
and modify the statement (all not built-in UDF in SQL Server should be qualified with schema name).
SELECT dbo.DATEADD_MM(1, '2006-08-30');
DB2 equivalent
CREATE OR REPLACE FUNCTION DBO.DATEADD_MM(IN NOD INT, IN DAT TIMESTAMP)
RETURNS TIMESTAMP
RETURN DAT + NOD MONTHS
@
and DB2 SQL stamement
SELECT dbo.DATEADD_MM(1, '2006-08-30') FROM SYSIBM.DUAL;
After this transformation function dbo.DATEADD_MM can be used any way in the SQL statements executable in SQL Server and DB2 without any modification.
In the files:  DB2 and SQL Server are more examples for DATEADD, DATEDIFF and DATEPART function.
Solution for other built-in scalar functions
Other functions are easy to migrate, just create its DB2 equivalent.
Example:
SELECT SUBSTRING('ABCDEF',2,2)
DB2 equivalence
CREATE OR REPLACE FUNCTION SUBSTRING (IN STR VARCHAR(32672),IN STA INT, IN LEN INT)
RETURNS VARCHAR(32672)
  RETURN SUBSTRING(STR,STA,LEN,CODEUNITS16)
@
More examples:  DB2 and SQL Server
Performance issue
A question can be raised if the performance will not suffer. The question is worth considering because instead of replacing SQL Server function by DB2 equivalent we are adding additional wrapper function to keep backward compatibility. Using additional UDF (particularly in WHERE clause ) can impact performance.  But it is not the case because in most case it is "inline" function or even "source" DB2 function which does not involve any additional function calling.
Conclusion
In most cases it is not a problem to migrate SQL Server build-in scalar function to DB2. Adding some consideration also migrating and keeping backward compatibility is possible.

niedziela, 1 lutego 2015

New version of JavaHotel, taxation

Introduction
I uploaded new version of JavaHotel application. Demo version (U/P user/user) is available here, source code here.
Taxation
While issuing a document (receipt, bill) a taxation is calculated using tax level assigned to the service.

Detailed information related to the document is also available. This information if stored in the database (as XML file) for every document issued.
Taxation information is also exposed in "Stay summary" window providing information about the current status of the stay.
Next step 
Receptionist journal registering the information about user activity.

Ubuntu 14.04 and USB tethering

Introduction
For some reason I was unable to connect to a hotel wi-fi network from my Ubuntu 14.04 desktop. What is more interesting, I connected without any problem my Kindle reader and mobile (Galaxy S2, Android 2.3.6). The hotel receptionist was very kind but also threw up his hand.
Solution
After spending some time googling on my mobile I found the solution and it was extremely simple, connect to the Internet through a mobile using USB tethering.

  1. Find a public place with wi-fi connection and install usbip package (yum install usbip)
  2. Load vhci-hcd module (modprobe vhci-hcd)
  3. Be sure that mobile is connected to wifi network
  4. Connect mobile and desktop via USB cable
  5. Enable USB Tethering in your mobile (Application -> Setting -> Wireless and network -> Tethering and portable hotsp -> Enable Tethering
And that's final, I have raised from death and connected to the world again.