DB2 SQL - Some secrets

I don't know how many people know about this, but I found a neat trick when using DB2 SQL.
Usually you have to query something like this:

Now in DB2 SQL (at least it works in iSeries/AS400), you can simplify it like this:

Maybe it doesn't seem very useful, but most of the tables that I work with have a double or triple key (one being product and the other a serial number per product).
The real advantage comes when using a group of rows. Instead of using a temporary table and joining in the query, or making something horrible like:

It's much more simple to write it like this:

Firebird Embedded in SQuirreL SQL (in Windows)

After 5 days I have finally been able to setup Firebird Embedded in SQuirreL SQL.

And I call myself a programmer... Shame on me!

Since I haven't found any concise tutorial on how this can be achieved (for newbies in Firebird, at least), I'll write here on how to do it.

You will need three things:

• SQuirreL SQL
• Jaybird (a.k.a., Firebird JDBC)
• Firebird Embedded "edition" (Follow the current release link, then select an "embed" installation)

Unpack both zips into the same folder. You will only need the following:

Jaybird archive:

• jaybird-full-*.jar
• jaybird21.dll

Firebird embedded archive:

• fbembed.dll
• ib_util.dll
• icudt30.dll
• icuin30.dll
• icuuc30.dll
• firebird.conf (if you wish to provide custom configuration)
• firebird.msg
• intl\* (folder)

After the libraries and SQuirreL SQL are installed, you have to setup a connection and some paths for the libraries.

Before opening SQuirreL, go to the directory where it is installed. In there you will find the main runnable batch file: squirrel-sql.bat. Edit the file and insert the red colored text in the last line:

start "SQuirreL SQL Client" /B "%LOCAL_JAVA%w" -Djava.library.path=/path/to/jaybird_dll -Xmx256m -cp %TMP_CP% net.sourceforge.squirrel_sql.client.Main %TMP_PARMS%

Replace with the path where the file jaybird21.dll is installed. It will typically be in the same folder as jaybird-full-*.jar.

Open SQuirreL and choose "Firebird JayBird" from drivers. On "Extra Class Path", point to the jaybird-full.jar that was unpacked from the archive you downloaded.

Since it is the embedded version, make sure your database string is:

jdbc:firebirdsql:embedded:<database_path>