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();

What happened 2015?

So, as you noticed, I did not really blog a lot this year, i.e. 2015.
Let me quickly recap what I was busy with in the last couple of month (lame excuse, I know).

In summer I changed job from Oracle to vmware. Still handling the channel/partner business, but now no longer for (boring) hardware, but for - to me - exiting software. The concept of the software defined datacenter - or SDDC for short - is something I really like. Virtualize everything. Move from on-premise to cloud(s) seamlessly... pretty cool I think.

Enough of the professional plug.
I still found time for some nice hacks at home.

I've been recording my electricity and gas consumption at home for years, nay, decades now. What started with a plain ascii file in the early 90s, turned in to a DB2 application on OS/2 (with visual REXX) , and then to a very rudimentary web application (against the same DB2 database) under Windows XP and 7. Over the years I slightly modified the web application to also be mobile friendly, so I could take my phone (Nexus 5 right now) or iPad and enter the data while I was reading the meter.
Did I mention that I'm a nerd and do this weekly? No?
Well perhaps I should.
Not very interesting from a hack perspective, is it?

So this year I decided to change this, and I

1. created an Android app for it
2. added the anyline meter reading to it (because I did not want to get into the OCR stuff myself)
3. created a REST interface to my database (building upon the old web application I had), using Jersey.

Two really impressive things here. Anyline... check it out. Excellent meter reading SDK (and other OCR stuff) on iOS and Android. 

And Jersey, wow!! REST with the click of a button, yet you are still in full control. And also getting JSON and XML with the same code. Nice.

Now for step 4 I'll re-write the old web-app to a pure Ajax/jQuery/JSON thing, and will only keep the REST interface to the database.

And step 5, already started: I bought some NFC tags (from whiztags, thanks for the hint, Max). One will go to the gas meter, the other one to the electricity meter, and both are registered to my app on Android. So when I go near the meter, it will automatically open the app with the OCR scanner for the respective meter. Click, and done.

So that's the last half year - or so - in a nutshell.

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.

Java updates without Ask Toolbar

One thing that has annoyed me, since Sun started this, was that the Java Installer / Updater would not only have crapware like the Ask Toolbar, but also - of course - have this selected by default.

I always found it inappropriate for a large IT vendor like Sun to include such intrusive sponsoring.
Especially with a security sensitive thing like Java.

Anyway, I just learned that you can turn this off.. Not sure since when.
In the Java Options (Windows Control Panel -> Java) on the Advanced tab, scroll down to the bottom, and there you have it.

Suppress sponsor offers when installing or updating Java.

Quite hidden, and not easily found by exactly those average Joe Users who would actually install the Ask toolbar ...
But ... eh... thanks ?

JSP/JSTL migration problem

While migrating some of my work stuff from my old WinXP laptop (ThinkPad T500... good old machine) to my new Windows 7 T430 (also fine iron), I switched from Tomcat v5 (yes, indeed, old) to Tomcat v7.
Tomcat v7 comes with a new servlet, JSP and EL spec. Not a real problem - I thought.

But once I got all the data sources etc up and running, my JSPs would not compile and gave EL parsing errors like "javax.el.ELException: Failed to parse the expression".

A bit googling revealed this nice post on Stackoverflow.

Turns out, that with the new EL standard, Java keywords are forbidden as variables.

And I had "static" as an URL parameter  in ${empty param.static} as well as "class" as a variable to hold css/style class-names.

Once I renamed those everything worked fine again.
The first once was nasty, though, because as an URL parameter the name was of course exposed externally... Had to change some other scripts as well.

Singleton

Oh, yes, faux singletons... haven't we all had them.

via Geek&Poke

James Gosling to leave Sun/Oracle

Well, this kind of sort of in a way was to be expected. James Gosling left Sun Oracle, as he reported here.

Annotations

via geek & poke

Qando being really really stupid

Qando is an excellent service for quering up to date (and realtime) information about the (mainly) Viennese public transport.
I used to know it from the iPhone - or rather from my iPod touch only, where it was not THAT useful, because it only worked when I had a WiFi connection ... it does an excellent job on a fully GPRS/3G connected iPhone, though.

Found out that it is also available as a JavaME version for more or less or other handsets, including my E71.

Installs OK, starts fine, however, it could NOT access my GPS device (a common problem for JavaME apps).

But I wouldn't even let me enter station/address data by hand.
There is a search input field, but I failed miserably to enter anything there...

...except for numbers, I found out after a while.

And then I played with it a little more, and noticed that - get this! - emulate a T9 input, even when the phone has a full QWERTY keyboard!
So if I hit e.g. the 4-key 3 times it will walk through g-h-i... like it would on a 12 key phone[1].

Aaaaaa... how stupid can one be as a programmer. Who in this century (or the last 20-30 years for that mattters) codes keyboard input and key-stroke-decoding by hand! Let the opsys do that! That's what it is here for. Even on a phone.

So please, Qando programmers, you can do better! And it's a lot easier for you!
--
[1] or whatever the correct number of keys on a numeric-pad-only phone is.

JavaDeus 08 was a huge success

On Thursday we [1] hosted Austria's first major Java Developer conference [2] in St. Pölten on the FH campus - as I already reported.

The event was - not only in my opinion - a great success. We had 400+ people attending (not counting the Sunnies), from students over smaller and larger ISVs to coporate developers. We had a mix of Sun and non-sun content, a mix of local and international speakers. And it was free (literally as in "free beer").

Starting with an excellent key note from Reggie Hutcherson (Manager of the Sun Technology Evangelism group), followed by the coolest demos, most importantly of the SunSPOT thingies - small Java programmable "thing" with light and 3d motion sensors and stuff... We gave on set of them away for free, and one is still to be won - or rather earned with a good idea for an application.

In the afternoon there were 4 parallel streams of break out sessions, covering everything from MySQL (were we obviously were not clear enough that the 2 presentations were not the same, sorry), openESB (excellent presentation and demo by Jason), GlassFish by Alexis (who else ;-) ), NetBeans by the Netbeans guys from Praha, SunSPOT details by Salzburg Research, etc, etc. And of course and outlook on Java 7 ...

Presentations will be posted for download on the Sun site soon (Monday, I think), I will put up a reminder here on my blog as soon as they are ready.

I had excellent conversations throughout the day and - apart from being slightly stressed - enjoyed every bit of it.

One of my favorite moments was, when - more sales focused - colleagues came almost running out of the really good jMaki presentation by Doris Chen and complained that people were talking about actualy code in there. *grin*

After that beer and BBQ were well deserved by everyone... still 200+ at the party (I guess), people only started to leave when Germany started to beat Portugal and we had to watch it...

Other reports on the conference should be searchable with the keyword "javadeus08" or "javadeus", or can be found on del.icio.us and technorati. (That is the plus side of a unique name like JavaDeus... apart from that, I still don't like it).

See y'all next year, at JavaDeus 09.

[1] Sun Microsystems, if you don't know by now ;-)
[2] well WE [1] think it was the "first major..." and people at the conference confirmed this. Sorry, if I missed someone else's efforts.

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.

Java Conference in Austria

Finally, Sun talks Java again in Austria. [1]

On June 19th there will be a one day Java Conference called JavaDeus[2] in St. Pölten.
Free admission of course.

Topics will cover Java 7, Netbeans, openESB... as well as some not-so-directly-Java-related stuff like MySql and Solaris Dtrace... stuff for developers, if you will.
Details can be found here at www.sun.at/javadeus08



[1] Not only do I work for Sun, but I'm also involved in organizing this event.
[2] don't blame me for the name.

Joel Spolsky on closures

Joel Spolsky has an excellent explanation on why you'd want closures ... without using the term "closure" at all.

Can Your Programming Language Do This? - Joel on Software

JSTL fineprint

The following fine print in the JSTL fmt:formatDate documentation just cost me about 45 minutes of my life:

If this action fails to determine a formatting locale, it uses java.util.Date.toString() as the output format.

Meaning that, if you do not set the locale explicitly in the JSP (with fmt:setLocale) and you e.g. use WGET against your JSP page - which per default does not carry any locale information - you get the stupid default format

Sat Jan 26 18:10:50 CET 2008

instead of whatever you specify. Even if you explicitly give a pattern with fmt:formatDate value="${now}" pattern="yyyy-MM-dd'T'HH:mm:ss'Z'"
you nevertheless get the verbose stuff from above.
You have to setLocale first...

Sick. Just sick.

NetBeans 6.0 is out

and I installed it a couple of days ago.
Migration from 5.5 is pretty smooth (I only had to add the JAX-RPC WS plugin), and all projects compiled again.
I even noticed tons of nonsense in my code... Just in a couple of hours.

More on NB6 in the upcoming days, weeks, months.

Glassfish book

I just noticed that Glassfish (the Sun lead open-source JavaEE Application Server) has its first book.

Java EE Development Using Glassfish Application Server

There is a review by Arun Gupta (one of my favorite Java bloggers).

More on Glassfish here.

James Gosling: JavaME is NOT dead

Boy, I thought I misread all the recent posts about James Gosling (godfather of Java , i.e. God and father of Java) declaring Java ME dead.
He rightfully points out that it is NOT. James Gosling: on the Java Road

Sun and Samsung developing 'Java phone'

A late edition of an April Joke ???

"McNealy said Tuesday in Seoul that the companies were working on a 'Java phone' that would surpass Apple's iPhone in functionality and cost less, the mass-circulation JoongAng Ilbo reported."

Report: Sun Microsystems, Samsung developing 'Java phone' - International Herald Tribune

OpenOffice as a Java Update

This just popped up on my computer...



would be even cooler if it detected my (running) StarOffice ...

From one to many

Last weekend I (again) discovered what every programmer / architect knows anyway:

The step from doing something on ONE item to doing it on MANY items is the hardest.

And there's an add-on:

Moving from ONE to TWO (in the above sense) is more complex than from TWO to MANY (unlimited).

The reason for the latter is that (to me) "2" is still not as generic as "n", or as I like to put it:

2 is just a special case of 1

quite often if you (in code) have to do something specifically twice, you don't use any loop or iteration construct, you just do it twice.
E.g.
foo.doSomething();
bar.doSomething();

only if you have to push beyond 2 people start to use collections and iterations.
for (Object o: somecollection)
{
o.doSomething();
}

just for 2 objects, no-one bothers to create a collection, they just instantiate two objects.

At least thats what I usually do - and I know others as well.

But - as I said - I experienced the "from 1 to n" problem last weekend - let me elaborate:

In our neighborhood we have a great service that delivers a box of vegetables right to our home.
There's also a website, where you can check this weeks contents of the box.

Unfortunately, though, the site does not offer an RSS feed.
No problem, I just create(d) my own, with a simple job, that runs daily:

1. connect to the site
2. scrape the contents of the site
3. re-format them
4. and put them into a (static) RSS file, hosted on my homepage.

Put that RSS URL into iGoogle and have the contents right there in my personal google portal.

Easy.

Just 2 weeks ago, we decided to have 2 boxes (one with vegetables only, one with vegetables and fruit) on alternating weeks. So I had to re-write my little thing to handle two different "boxes" instead of just one.
(Frankly, I just went for the n-solution, not the 2)

I more or less had to totally re-design the whole hack... About the only thing that remained quite stable was the core RSS handling (through Rome) and the scraping/parsing of the HTML (using HTMLparser).
What I had earlier looked more like a classic C (not C++) program, with almost everything done in main()... I knew it was a bad design... but I did not anticipate the initially.

So, this was a really good lesson for me again, to "design for n" early.