NULL values again

Today I learned that you essentially cannot concatenate (concat, ||) nullable columns properly in DB2/SQL.

To be honest, I should have know this of course.

I have a table that records money spent, with a date, text, account and a physical location, e.g. a town, district, ... indicating where the money was spent.

It is nullable because not all transactions need to have a place recorded.

For some statistical analysis I wanted to create one column with text and account and the location simply concatenated into one, like account||' '||text||' '||location.

Turns out, then the location is NULL the whole string will turn NULL

Which reminded me - painfully, after about 30min - that NULL is not a value. You cannot do anything with NULL.

So you have to cast it away to a default value, e.g. with coalesce(location,'dummylocation').

Sigh.

ISO compliant year-week function in DB2

 I've already shown how to create a year-month function in DB2, which - when it comes to date arithmatic - is quite straightforward, because very year (in ISO/Gregorian) calendar starts with the first month.

Some systems argue whether this should have the ordinal 1 or 0, but thats the usual 0/1 issue in programming.

Weeks however, are far more complex, because not every year starts with the begin of a week (whether this is Sunday or Monday in your preference / area).

It might just start with a Thursday... WOW.

So for that ths ISO 8601 standard set a definition on what is to be considered week 1 of a year:

The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it

Luckily, DB2 has a function for that - WEEK_ISO.

So let's just try that with a 

rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)

Takes the year (need to rtrim it) and adds 2 digits week to it (you might want to insert a "w") between them.

However, this leeds to e.g. 2021-01-03 being in week 53, because week 1 start on 2021-01-04.

the yearweek for 2021-01-03 therefore should be 2020-53 not 2021-53 as the above formular would yield.

Now we need to make sure that if we get a week 53 and its January we return the previous year... Only for January, because some days in December might also be week 53, and we need to keep the year there.

Voila:

create function yearweek_iso(TS timestamp)
returns varchar(6) no external action deterministic 
return    
CASE

     WHEN (week_iso(TS)=53 AND month(TS)=1) Then
              rtrim(char(year (TS)-1)) || right(digits(week_iso(TS)),2)   
     ELSE
              rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)
END

The results now match whatever java.time package might do with week parsing. In order to get the first day of this week back (in Java, where I needed it), you parse as follow:

new DateTimeFormatterBuilder()
            .appendValue(IsoFields.WEEK_BASED_YEAR, 4)
            .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR,2)
            .parseDefaulting(WeekFields.ISO.dayOfWeek(), 1)
            .toFormatter();

How to get a year-month value in DB2

I quickly needed to check the monthly progress of a certain variable over time. Problem was that those bookings/incomes are spread over the month, sometimes only one booking line, sometimes two... So I needed a grouping variable, that the "absolute month", i.e. the usual year-month value,that you know from financial system, e.g. 201709 for this current month.

Here's the easy way to put that into a function on DB2:

create function yearmonth(TS date) returns varchar(6) no external action deterministic return rtrim(char(year(TS))) || right(digits(month(TS)),2)

Then you can easily do a group by yearmonth(date).
(Don't omit the "not external action" and "deterministic" parts, because a) they are true and b) they are needed for grouping)

Of course you don't have to create a function for this, but easier then re-typing it, or creating a view.

(You might want to create the same function for timestamp as well... just so happened that I now only needed it with the date signature).

You're welcome :)

NetBeans and DB2 again

So for various reasons (mainly the 64bit v 32bit problem with the native driver) I changed my DB2 JDBC driver to type 4, i.e. the Universal driver.
However, when I connect from NetBeans with an URL like jdbc:db2://localhost:50000/sample the schema would stay empty.
Nothing. Zip. Zilch. Zero.
Nada.
Quite some googling and debugging - mainly with a little java program like this:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class MainDB2 {
    
    private static final String URL = "jdbc:db2://localhost:50000/SAMPLE";
    private static final String USER = "roman";
    private static final String PASSWORD = "pwd";
    private static final String SCHEMA = "roman";
    
  public static void main(String[] args) throws Exception {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        try {
            DatabaseMetaData dmd = conn.getMetaData();
            ResultSet rs = dmd.getTables(null, SCHEMA, "%", new String[] { "TABLE" });
            try {
                while (rs.next()) {
                    int count = rs.getMetaData().getColumnCount();
                    System.out.println("Column count: " + count);
                    int i;
                    for (i = 1; i <= count; i++) {
                        try {
                            System.out.println(rs.getString(i));
                        } catch (SQLException e) {
                            System.err.println("Exception reading column " + i);
                            e.printStackTrace();
                        }
                    }
                    System.out.println();
                }
            } finally {
                rs.close();
            }
        } finally {
            conn.close();
        }
    }
}



I was able to trace it back to an SQLCODE -443SQL0443N with diagnostic text "SYSIBM:CLI:-805". SQLSTATE=38553. Google this and you will get to here and learn that - again - a package was not bound, this time the db2schema.bnd file. Bind it as suggested in the article with the usual db2 bind db2schema.bnd blocking all grant public

and voila, NetBeans will find the schema.

Gets me every second year, it seams.

How to hide a userid from Windows login screen

With DB2 on my home PC[1] I have a service account for the DB2 instance, makes life a lot easier than trying to map it to you actual user(s).

However, regular Windows (Home) setup has 2 defaults that are annoying:
a. password and account expiry for this user.
b. the userid appears on the Windows login/lock screen, although nobody is supposed to sign in with it.

Create a maintenance free user
The first issue I fixed a couple of month ago, because that was really annoying. Because DB2 just would not start. And the first two times this happened I had to work through db2diag.log to find out what happened.
So, make sure the account actually never expires (that's separate from the password). With admin privileges run
  net user db2admin [2]
to check if the user account expires.
If it does, then run a
  net user db2admin /expires:never
to fix this.

The password expiration is trickier, since it cannot be done with the net user command.
To make the password everlasting, run
  WMIC USERACCOUNT WHERE "Name='db2admin'" SET PasswordExpires=FALSE

I found this thanks to the folks at StackExchange.

Hide the service account from the login screen
I wanted to do this on Windows 7 already, but never found the time or cared enough. Now with the move to Window 10 I thought of it again and fixed it.
So this can be done with group policies and stuff, but not on a standalone Windows Home edition.

Registry and Microsoft Technet to the rescue:

Create an entry under
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList and list the db2admin user with a dword (32) of 0 to hide it. Remove the entry or set it to 1 to show the user again.

Voilà.

--
[1] Ha, now that I no longer work for Oracle, I can again freely admit it... Not that I really held back on this in the past 5 years.
[2] replace db2admin with the userid you need this for

Maps mash up: Underground vs post office

The other day, when I had to post an important letter, I was wondering if there is any post office close to an underground station on my commute, and I could not come up with one. So - me being me after all - I decided to do some in-depth analysis on this. And learn Python along the way.

1.  The Idea

Get all the underground stations and post offices as geo coordinates, and find those closest to each other.

2. Getting the underground data

Easy, the geo data of all the public transport stations (or ony the underground stations) can be found easily, since the are part of the open government data, shared by the municipality of Vienna.

3. Going postal

Getting the post office data is rather challenging, because they are seemingly not considered to be public enough.

Still, this can be done; so first get a list of all the post offices in Vienna from post.at. Best with a little python script (my first!!) to parse it.
Get all those with a ZIP code starting with '1' into a CSV file including their full street address.

Then - thanks for the hint, martin - use the Yahoo! PlaceFinder API to convert those postal addresses to geo coordinates. Get an Appid for Yahoo!, if you don't yet have one.

Important trick here: Don't place everything into the q=... query string, but separate it into postal, city, street, etc. Like this

u = yahooURL+"?appid="+yahooAppidy
u += "&postal="+po.zip
u += "&city="+po.city
u += "&street="+quote_plus(unicodedata.normalize('NFKD', po.addr).encode('ascii','ignore'))
u += "&country=Austria"

From Y! we get some fine XML back and use XPath to access the geo coordinates

doc = ElementTree(file=urlopen(u))
lat = doc.findall('.//Result/latitude')[0].text
lon = doc.findall('.//Result/longitude')[0].text

So now we have the geo coordinates of all the underground stations and all the post offices.

4. Visualize & Verify the data
Let's again use Python to create a simple KML file to load the date acquired so far into Google Maps (or Google Earth). There's a Simple KML python library to do just that. Here's are the maps (post offices, underground stations)

5. Do the Geo Math
This is the tricky part. I decided to put all the data into a (relational) database, and since I have DB2 installed on my system, it was of course DB2. I did not use any geo/spatial extension, but just put the latitude and longitude into proper types. Then I created a user defined function (UDF) to do the geo math. Actually, for this purpose this could have been simpler, since one can disregard all spherical aspects and assume the surface (of the Earth in Vienna) to be flat.
With my newly created haversine function the query then looks like this:

select haversine(p.lat,p.lon, u.lat,u.lon) as distance, p.plz,p.street,p.lat,p.lon,u.station,u.lat,u.lon
from ubahn.post p, ubahn.stationen u
where haversine(p.lat,p.lon, u.lat,u.lon) <1
order by 1 asc
fetch first 20 rows only

I'm only interested in post offices that are maximum 1km (distance<1) from an underground, and I only want the closest 20 of those (first 20 rows only)

6. Create a map
Export those data into a CSV file, and run a pyhton script that creates the KML file for this, with the post office being a point, the distance being a line, and the underground again a point. Map can be found here.

Turns out, there are more pairs than I actually thought.

Geo-Distance vlg Haversine as a DB2 UDF

In a hack I'm currently working on (to be published soon) I needed to calculate the distance between two locations (on the surface of our planet). Admittedly, those are close addresses (all in Vienna), so I could have assumed them all to be on a plane (not an airplane, a flat surface...), but I felt like some real math.

This is a workaround for all of you who do not have spatial extender available or installed.

To calculate the distance of two points on (a perfectly spherical earth) you apply the haversine formula.
This is the representation of it as a DB2 user defined function (UDF)

CREATE FUNCTION HAVERSINE( lat1 decimal(10,8), lon1 decimal(10,8), lat2 decimal(10,8), lon2 decimal(10,8))
RETURNS DECIMAL(15,8)
F1: BEGIN ATOMIC
declare dlat, dlon, a, d double;
set dlat = radians(lat2-lat1);
set dlon = radians(lon2-lon1);
set a = sin(dlat/2) * sin(dlat/2) + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon/2) * sin(dlon/2);
set d = 2*6367*asin(sqrt(a));
return d;
END

The two points are given in latitude and longitude (in degrees) as lat1/lon1 and lat2/lon2. It returns the distance in kilometers. If you want to change this, replace 6367 (which happens to be the Earth's radius im km in Vienna, for which I needed this) with your radius and units,e.g. 3956 if you want miles in London.
Check it simply at Wolfram Alpha with "earth radius in london".

I guess it is not perfect, but it does work well. I should probably make it DETERMINISTIC (because it is) to improve performance.
Go ahead, play around.

Glassfish configuration for DB2

I ran into this problem at least twice, so here is the solution (at least for my own reference in the future):

If you want to use a DB2 (UDB) database from glassfish v2 using a Type 2 JDBC driver [1], you must make sure that the (native) library db2jcct2.dll (or .so) is in the (native) library path. The error message usually says something like "Failure in loading T2 native library db2jcct2".

This DLL resides in your DB2 install directory bin directory, e.g. C:\Programme\IBM\SQLLIB\BIN\db2jcct2.dll on windows, so you have to add this directory (C:\Programme\IBM\SQLLIB\BIN) to the glassfish (!!) native library path in the JVM settings

In the domain.xml config file this is the
native-library-path-prefix="c:\programme\IBM\SQLLIB\BIN" option of the java-config setting.

In the admin web GUI it is on the Application Server page;

under the JVM Settings -> Path Settings you will find the option Native Library Path Prefix:

One additional note:
[1] The IBM Type 2 JDBC driver is the one that uses the native DB2 client (used to be called Client Application Enabler or short CAE at my times with DB2 UDB v5) for DB2 access and connectivity. Hence, the necessity to be able to locate the native DLLs... So when the appserver and the DB2 engine run on the same host, you should use type 2 as well.

On the relative importance of MySQL

Since the announcement of Sun buying MySQL recently we (Sun) of course had a lot of conversations with customers, partners and Sun internally about the value and position of MySQL.

To show the relevance of MySQL I searched for a couple of database products (or their names) on some job portals (jobfinder.at, jobfinder.de, stepstone.de, monster.at, monster.de, monster.co.uk), the (not new) idea being, that the number of job postings does correlate to the importance of a platform or system.

I know that this is not representative... but it gives a nice overview. And the ranking of the databases on the various job portals is consistent. (All figures rounded, and those 5 were the only ones I looked for... so it is strictly not percentage of the DB market but it gives you the relative positioning)

So:

#1	Oracle (to be expected)	50%
#2	SQL Server	29%
#3	MySQL (!!)	13%
#4	DB2	6%
#5	postgresql	1%

I did the same on amazon.com on books which gives a similar, but not identical picture:

#1	Oracle (again)	85%
#2	SQL Server	6%
#3	DB2	6%
#4	MySQL	3%
#5	postgresql	1%

Not really surprising that DB2 ranks before MySQL here... What does surprise me is that almost all books seem to be on Oracle.

Also of course there is a skew towards Oracle, since I only search (literally) for “Oracle” so the results might contain jobs and books about Oracle applications or (in case of books, not jobs I guess) the Oracle of Delphi, or other totally unrelated Oracles ... see the Wikipedia disambiguation page for a list of oracular things in this world.

Anyway: the results to me are clear:

MySQL has more importance than DB2 today (this of course kind of hurts me, me being an old DB2 fan) and is not that far off from SQL-Server... half of its “value” in both book and job search.

JDBC on DB2 UDB 8 stopped working

All of a sudden my netbeans would no longer connect to my DB2 UDB 8.1 database.
Got me a missing package error. So I tried all the usual db2rbind all and bind db2ubind.lst and db2cli.lst and whatnot (whatever I could remember from my DB2 days at IBM).

Still no help.

So I googled for the error message, and indeed IBM changed the package with a fixpak, the details can be found here at the IBM site.

Problem

After upgrading your server to DB2 UDB Version 8.1 FixPak 10 (also known as Version 8.2 FixPak 3), you will encounter an SQL0443N error if invoking a DB2 Call Level Interface (CLI) catalog function (such as SQLTables(), SQLColumns(), or SQLStatistics()). For example: SQL0443N Routine "SYSIBM.SQLTABLES" (specific name "TABLES") has returned an error SQLSTATE with diagnostic text SYSIBM:CLI:-805. SQLSTATE=38553

Cause
The CLI catalog functions execute routines on the DB2 UDB server. These routines use packages that are created by the db2schema.bnd bind file. The package name for db2schema.bnd has a different name in DB2 UDB Version 8.1 FixPak 10 than it did in previous fixpaks. If this bind file does not get bound to all of your existing databases on your server, you will get the SQL0443N error message.

Solution
As documented in the FixPak Readme file for DB2® Universal Database™ (DB2 UDB) Version 8.1 FixPak 10, you will need to bind the db2schema.bnd file locally against each database, as follows:


At a command prompt:
db2 terminate
db2 connect to
db2 bind /db2schema.bnd blocking all grant public sqlerror continue
db2 terminate

...where represents the name of a database to which the utilities should be bound, and where is the full path name of the directory where the bind files are located (usually sqllib/bnd).

The bind operation needs to be done within a local connection to the database.