If you have ever used more than one database engine for any length of time you may be probably aware of the not always subtle syntax differences in their implementation of SQL. (Not that there’s any reason for that. That’s what standards are for, right?).

In this regard, DB2 for i SQL does not disappoint. Even a simple search for the official online documentation can be daunting for a newcomer (it’s here, by the way). If you are learning to program on iSeries, you might have been grateful when you found out there was support for SQL, until you actually started using it and stumbled upon some of those little but significant annoyances that tend to slow progress down to a crawl.

The good news is that most of what you already know from a different DB engine will work here. For the rest, I started compiling a simple list to get you through some of the most common, repetitive and especially non-obvious tasks. I plan to go back and continue expanding this list as I keep learning how to effectively use the query language on this platform (I may never come back, so don’t just sit around waiting.)

Concatenate strings

This one is deceptively simple, but there’s a twist (so soon?). You can use the CONCAT function or the concatenation operator, with different syntax (but ultimately yielding the same result) depending on the chosen form. Here are the three valid ways of concatenating strings:

Using the CONCAT function:

SELECT CONCAT(CONCAT(FIRSTNAME, ' '), LASTNAME) AS FULLNAME FROM STUDENTS

Using the long form of the operator:

SELECT FIRSTNAME CONCAT ' ' CONCAT LASTNAME AS FULLNAME FROM STUDENTS

Using the short form of the concatenation operator:

SELECT FIRSTNAME || ' ' || LASTNAME AS FULLNAME FROM STUDENTS

Yes, CONCAT is a function AND an operator. It may look weird, but it works. I tend to prefer the compact syntax, as long as I’m not writing C code at the same time.

Create a table with the same data structure as an existing table or view

I wouldn’t be surprised if the group of CREATE statements were the most complex on SQL for i, so it would be understandable if, during your perusal of the official reference, you missed the syntax for copying the column definitions from an existing table or view to a new table. So here it is:

CREATE TABLE MYLIB/STUDENTS_ARCHIVE LIKE PRDDTA/STUDENTS 

Simple, easy to remember. And yet I forget about it all the time (hence its inclusion on this list).

Retrieve top N records

Retrieving the first N records of a query seems to be the quintessential I-don’t-care-how-you-did-it-I’ll-do-it-on-my-own-way query amongst DB engines. In SQL Server, for example, you have the TOP clause:

SELECT TOP 100 * FROM STUDENTS

In Oracle you are supposed to use the ROWNUM pseudocolumn (as long as you don’t need ordered results, in which case a nested query is necessary):

SELECT * FROM STUDENTS WHERE ROWNUM < 100;

DB2 FOR i SQL gets the prize for the most verbose syntax:

SELECT * FROM STUDENTS FETCH FIRST 100 ROW ONLY 

And so on and so forth. You get the point.

Delete duplicate records

I spent a lot of time on this problem, until I remembered it’s 2012 and these days you can Google your way out of any difficult situation. The issue was simple enough: A badly written program inserted duplicate records on a table lacking a unique identifier, and we needed to remove those duplicates via SQL. The solution was kindly provided by Vijayakumar Kannan on his appropriately titled ‘Delete duplicate records using SQL in iSeries 400’ post:

DELETE FROM STUDENTS A WHERE RRN(A) > 
    (SELECT MIN(RRN(B)) FROM STUDENTS B WHERE
     A.FIRSTNAME = B.FIRSTNAME AND A.LASTNAME = B.LASTNAME AND
     A.DAYOFBIRTH = B.DAYOFBIRTH AND ...)

where RRN is a function that “returns the relative record number of a row”.

So there you have it. I like to think that just by having stopped to write this post I probably committed those snippets to memory, but the reality is that I will probably not remember anything the next time I need help.

Finally if you are reading this, hi future me. Don’t forget to leave a comment for future future me.

Last year I decided to transfer my domains from GoDaddy to Namecheap, for no particular reason. With the new registrar I wanted to try some things I never got around to do before, including setting up a subdomain for easy remote access to a location I shall henceforth refer to as Home.

Since Home lacks a static IP I had to enable dynamic DNS for the domain, which basically allows changing the IP address associated to a given subdomain programmatically and with immediate effect. Fortunately there is plenty of information on what needs to be done.

The step I had trouble with was finding a client to perform the DNS updates on a regular fashion, say, every hour, from a Windows PC behind a proxy that requires authentication. Now Namecheap has a Dynamic DNS client of their own, but for reasons I can’t remember at this moment it didn’t work for me when I tried it. Namecheap also provides a list of alternate clients, but again none of them fitted my bill exactly. As a last resort, they also allow updating the DNS via a HTTP GET request, which means you can easily do it with a web browser or programmatically with an appropriate HTTP command/library.

So even though this is probably a problem that has been solved many times, I wanted to indulge in a little bit of NIH and write my own client as a PowerShell script. Specifically I wanted to:

1. Load parameters from a configuration file
2. Retrieve current public IP address
3. Compare IP address against the one on Subdomain’s A record
4. Update A record if found different
5. Send an email with the results

Here’s is what I did.

Configuration File

Instead of hardcoding things like the domain and password I wanted to load configuration details from an INI file, so that the script could be reusable. Why INI and not, say, XML? Because for such a simple list of parameters I find the INI format much easier to read. I could also have used YAML, but then it’s just a simple change from equal sign to colon on the parser. Anyway, here’s the full list of parameters to load from the INI file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# The subdomain to be updated, which has to be previously configured 
# as an A record.
DDNSSubdomain   = "subdomain"
# The domain registered through Namecheap, with Dynamic DNS enabled.
DDNSDomain      = "mydomain.com"
# The password for authorising the updates, obtained from Namecheap
# upon enabling Dynamic DNS (see http://tinyurl.com/d6ao9de).
DDNSPassword    = "1234567890abcdef1234567890abcdef"
# An email account from which a message with the results of the
# update attempt will be sent.
EmailFrom       = "from@example.com"
# The recipient of the message.
EmailTo         = "to@example.com"
# The SMTP server for sending the email. I'm currently using GMail.
SMTPServer      = "smtp.gmail.com"
# The SMTP port.
SMTPPort        = 587
# SMTP username. If you are sending messages through GMail, then it
# would be your Google account.
SMTPAuthUsername= "gmailuser"
# SMTP password.
SMTPAuthPassword= "p4ssw0rd"
# If connecting through a proxy, this parameter should be set to 1.
# Otherwise it should be 0 or left unitialised
ProxyEnabled    = 1
# The proxy address. Ignored if ProxyEnabled is not 1.
ProxyAddress    = "proxy.mydomain.com"
# The proxy port.
ProxyPort       = 8080
# Proxy domain.
ProxyDomain     = "corporate"
# Proxy credentials, if required.
ProxyUser       = "domainuser"
# See above.
ProxyPassword   = "domainpassword"

For parsing the file I used the following snippet, adapted from Artem Tikhomirov’s code posted on Stack Overflow:

1
2
3
4
5
6
7
8
9
10

Function Parse-IniFile ($file) {
    $ini = @{}
    switch -regex -file $file {
        "^\s*([^#].+?)\s*=\s*\`"*(.*?)\`"*$" {
            $name,$value = $matches[1..2]
            $ini[$name] = $value.Trim()
        }
    }
    $ini
}

Retrieving current public IP address

With DNS-O-Matic you can get your current public IP address in a plain format so there is nothing to parse and the result can be used immediately. I don’t remember where I got the tip from so I’ll just give this guy the credit:

1
2
3
4
5
6
7
8

# Retrieve public IP address
$CurrentIp = (New-Object System.Net.WebClient).DownloadString('http://myip.dnsomatic.com/')
# Validate using a regular expression (only IPv4 supported at this stage)
$Pattern   = '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}'
if (!($CurrentIp -match $Pattern)) {
    Log-Error "A valid public IP address could not be retrieved"
    # Error handling
}

Comparing IP addresses and updating A record

I need to perform the GET request to update the A record, but only if the current public IP address is different to the one already set. I do not want, however, to spam the Namecheap servers with continuous queries every time I want to verify the existing value. So instead of retrieving the A record from Namecheap, I use a local environment variable to store the last known IP address. That works because I’m updating the IP from a single server, so there are no possible conflicts to be worried about.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# Retrieve IP address stored on environment variable.
$StoredIp  = [Environment]::GetEnvironmentVariable("PUBLIC_IP","User")

# If IP address hasn't changed, then finish the script.
if ($StoredIp -eq $CurrentIp ) {
    # Log message, etc.
    exit 0
}

# Otherwise, create update URI and perform a Web request.
$UpdateUrl     = "https://dynamicdns.park-your-domain.com/update?host=$DDNSSubdomain&domain=$DDNSDomain&password=$DDNSPassword&ip=$CurrentIp"
$UpdateDDNS    = (New-Object System.Net.WebClient).DownloadString($UpdateUrl)

# Error handling omitted for clarity. Or laziness.

# Finally, set the environment variable to the new IP address
[Environment]::SetEnvironmentVariable("PUBLIC_IP", $CurrentIp, "User")

Sending an email to notify the change

Next is to report the change of IP address via email, so I can validate and/or troubleshoot in case something went wrong.

You can choose between writing a log to a local text file, for example, and then include that file as an email attachment. In my case, since I never expect the logs to be too large, I’m just concatenating to a string in memory and then writing to the body of the message. Here’s my function for sending the email, based on this code from Christian Muggli:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# Send an email with the contents of the log buffer.
# SMTP configuration and credentials are in the configuration dictionary.
function Email-Log ($config, $message) {
    $EmailFrom        = $config["EmailFrom"]
    $EmailTo          = $config["EmailTo"]
    $EmailSubject     = "DDNS log $(get-date -format u)"

    $SMTPServer       = $config["SMTPServer"]
    $SMTPPort         = $config["SMTPPort"]
    $SMTPAuthUsername = $config["SMTPAuthUsername"]
    $SMTPAuthPassword = $config["SMTPAuthPassword"]

    $mailmessage = New-Object System.Net.Mail.MailMessage
    $mailmessage.From = $EmailFrom
    $mailmessage.To.Add($EmailTo)
    $mailmessage.Subject = $EmailSubject
    $mailmessage.Body = $message

    $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, $SMTPPort)
    $SMTPClient.EnableSsl = $true
    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential("$SMTPAuthUsername", "$SMTPAuthPassword")
    $SMTPClient.Send($mailmessage)
}

The full script can be downloaded from Github.

To run the query, open a console and execute the following line:

powershell -command "& 'C:\path\to\script\UpdateDDNS.ps1 -c config.ini' "

The final step would be to schedule the command so it runs every hour, for example. You just need to set up a task on Windows Task Scheduler for that, but since you asked, here are some instructions to get you started.

So that’s it: A quick, cheap and easy way to keep your Namecheap domains with Dynamic DNS up to date. Now that we’re done, let’s return to bashing Bob Parsons on Twitter, shall we?

Exactly 4 years ago, on 2 September 2008, Google released the first beta of their web browser: Google Chrome. I remember how difficult it was to download it that same day, with Google’s servers probably brought to their knees, and then how I stopped at pretty much everyone’s desk to procrastinate exchange impressions. I decided to keep it around for a while to see where it would go. Eventually I stopped using all the other browsers on a daily basis, and today it is the first software I install on any new computer (with the obvious exception of the operating system itself). This is what I thought back then:

Just downloaded Chrome, a new browser by Google, and their latest step towards absolute Internet domination.

First thing I notice after installing: It’s fast. As in impossibly fast, especially when compared to Internet Explorer. Opening tabs is instantaneous: there is no weird delay between pressing the new tab button and that tab being ready to use.

The vertical scrolling in my laptop’s trackpad is broken: It only scrolls down. It does work however with the in-browser adobe PDF reader.

Searching inside a page is simple, although somewhat limited. You can’t specify case-sensitive searches, for instance.

Importing from Firefox worked flawlessly.

Zooming in has a devastating effect on page layout: Only the font size is incremented. I thought this was a solved problem on modern browsers. I hope they fix it soon.

Backpack has a weird alignment in the login page. Everything else is working as expected.

I’m trying some random websites, and all of them are rendering fine: Joel on Software, Coding Horror, Fake Plastic Rock, Stack Overflow, Alltop, Popurls and of course Google itself.

The address bar is not as fast as Firefox’s awesome bar. It’s not bad, though.

Searches through the address bar use the Google site corresponding to my current location and I can’t find a way to change it to google.com instead.

In a perfect world, an archetypal web browser would exist and every single website would render perfectly in that browser. We don’t live in a perfect world, though, and at least in these early stages I don’t see Google Chrome being the one and only browser for every user and every possible situation. I’ll keep an eye on it anyway to see where it goes.

Happy birthday, Google Chrome. You have done well.

I love Raymond Chen’s blog, especially when he goes thermonuclear on his customers (or readers) for asking stupid questions or trying to go off topic or, you know, just plain trolling.

Something I find funny about the highly technical posts on his blog is that Mr Chen usually supplements his explanations with witty remarks like “the customer managed to screw it despite this being clearly documented here and here and there.” Well of course he is being polite, since it should surprise no one the fact that most people just do not care about documentation or doing things the right way in general.

You see, a lot of companies employing programmers (I dare say most of them) are usually not that interested in the long lasting quality of internal software products. If, as a developer, you know your application will have to be ready in less than 2 weeks, will be used only by a handful of people with pretty much identical skill set and low self-esteem, you won’t lose that much time in making sure your solution is absolutely the best anyone can come up with. You won’t care if it’s efficient, with great performance and minimum memory consumption, or if it has a usable user interface.

When a programmer finds an error the usual way to solve it goes like this:

So, this marvellous reasoning means that most of the time we won’t slow down to understand what’s going on, what went wrong and how to solve it nicely. It means that by sheer luck sometimes we’ll come up with a working solution, and most of the time we’ll create a time bomb. The code will work ok on our machines under very specific circumstances, and then it will fail spectacularly on a live environment or down the road when some of the specs/settings change in an unexpected way. And the whole cycle will start again. Most of the time it won’t even get to this point, because the application will be decommissioned, or because we will have moved on to a different project/department/company.

I think of a particular aspect of this reasoning, the “Let’s try the first thing that comes to my mind and see if it works”, as a special case of wishful thinking I like to call Wishful Coding. It basically involves trying to adapt programming tools and languages to our mental model, because “That’s how I imagine this class or function should behave so let’s just assume they will and hope for the best.”

Wishful coding is the reason why The Daily WTF continues to thrive and why there’s a whole subculture of mocking catastrophically funny pieces of code. It’s especially bad on languages with unsafe constructs (like C or C++) which don’t offer enough safety nets by default, such as garbage collection or boundary checks. If you can cause any harm to yourself or society, then for sure you will.

So, is there anything we can do to improve this bleak landscape? Of course there is. A real life version of the Joo Janta 200 Super-Chromatic Peril Sensitive Sunglasses may or may not be in the works, so I would keep an eye on those.

We can also try scaring people into doing things right, despite pressure from management. Somebody said “Always write code as if the person who will be maintaining it is a psychotic serial killer who knows where you live.” So we could try, say, hiring trainee programmers with actual criminal records and give them the rest of the staff’s home addresses.

A different approach which seems to be quite popular these days is making difficult for people to shoot themselves (and others) in the feet. Joel Spolsky says that Something is usable if it behaves exactly as expected.. That also seems to apply to our development tools. Emerging frameworks and languages appear to focus in allowing programmers to express intent instead of complying with the strict rules of machine language. They favour convention over configuration, readability over conciseness, security over performance. Joe Coder shouldn’t be spending any time in deciding what the best technique is for concatenating immutable strings in a loop, just let the platform take care of those details.

Since we already decided to follow this path then why not going all the way down? Why not deprecate technologies that could potentially allow anyone to cause any kind of harm? Sorry, C and C++ and Objective C: You have to go. CGI? OpenGL? Don’t even get me started. Root access? HA! We should keep dumbing down our tools, so let’s make it hard for new programmers to overflow a stack, to divide by zero, to cause a deadlock. Let’s ban kernel access, unsafe pointers and unmanaged memory. Everyone embrace sandboxed languages: Let’s write a H.264 decoder in JavaScript!!! (Oh, wait…)

Seriously now, it’s obvious that current trends on corporate IT will surely continue for decades and most legacy systems will actually outlive us. But at least there are now more chances than ever for the next generation of wishful coders to empower their businesses without creating maintenance nightmares in the process. Every year we are closer and closer to intuitively create robust, scalable systems without falling in an endless cycle of corruption and despair.

Of course, it’s also possible that future programmers will despise our feeble attempts at improving the Status Quo, decide that our tools are too narrow and restrictive, and end up exploiting all kinds of quirks to work around the platform limitations. And we’ll be back at the starting line.

Nobody knows. And since we suck at predicting the future, maybe it doesn’t even matter. We’ll see.

Mi biggest pet peeve on Windows Explorer is that there is no way to show the size of a file in bytes without having to open the properties dialog box. You would think the Details view is a great place for that, considering the remarkable selection of columns you can include. Seriously, there is a Mood column but no Size in bytes or equivalent?

On Windows XP and previous versions, you could write a shell extension that implemented the IColumnProvider interface and add custom columns as needed. TortoiseSVN used these kind of extensions, for example, to show the status of files under source control directly on Windows Explorer.

On Windows Vista, however, Microsoft decided to remove the IColumnProvider interface:

Note Support for IColumnProvider has been removed as of Windows Vista. The Windows property system is used in its place. See Windows Property System for conceptual materials that explain the use of the new system.

My guess is that rogue extensions were causing a lot of performance/stability issues on Windows Explorer and Microsoft didn’t like being blamed for them (“Why is Explorer so slow? Stupid Microsoft Die Die Die!”), so they decided to retire the interface altogether. I haven’t got too far into the Windows property system but I suspect you cannot mimic the full functionality of custom columns on the details view for every type of file.

Anyway, even if I came 11 years late to the party and it’s no longer relevant, I decided to take a shot at it and implemented a shell extension for showing the size of file in bytes. No more undesirable rounding up/down to kilobytes for the benefit of ancient programs that have probably fallen into oblivion.

When installed, the extension adds a new column called Size in Bytes which can be added to the explorer’s Details View:

Select it, and you’ll get an extra column with the file size in glorious bytes:

If you still use Windows 2000 or XP don’t you think is time to move on? and want to use the extension, follow these steps:

• Download the DLL: FileSizeXP.dll.

• Put it wherever you want. C:\Utils is as good a place as any.

• Register it by running the following command:
  regsvr32 C:\path\to\file\FileSizeXP.dll

• Restart the PC.

Note, however, the following caveats:

• The extension does not, I repeat, DOES NOT work on Windows Vista and newer versions. For that you will need need an Explorer replacement, like Zabkat’s Xplorer², or just give up on showing full size in bytes in Explorer.

• I haven’t actually tried the extension on Windows 2000.

• Sorting files by the new column seems to be broken at the moment. I’ll go back and fix it, hopefully before Windows XP goes out of support. Sorting has been fixed.

If you decided to trust a random guy on the Internet and try this extension, please do let me know how it went.