Friday, June 27, 2014

DB2 6.1 Personal Edition

Introduction

I'm a sucker for old-timey software. I especially like it when I can shoehorn it into some semi-modern environment somehow. That's always fun. I especially like old database systems, so whenever somebody puts something like DB2 6.1 Personal Edition on eBay for like $6.00, I'm not too slow to grab it up.

Such was the case recently.

Installation

What does DB2 6.1 PE even run on? The CD said Windows 95, 97 and NT. I have Personal Oracle 7.2.2 running on Windows 95, so I figured I'd try that first.

It's probably not impossible to get it working on Windows 95, but I couldn't figure out how. Everything installed but nothing ran. Rather than wrestle with it, I figured I'd just try it on NT and see if it went any better.

It did. Much better.

After one false start trying to install it as myself, I logged in as the Administrator, and the installation was straightforward.

I installed all product options.

DB2 6.1 PE - 1. Options

It asked for a user to run everything as. I accepted the default db2admin user. Oddly, some default password was given but it was also starred out. I guess I could have clicked Next and gotten whatever password it was, but I wouldn't have known it. So, just to be safe, I deleted the default passwords and typed in my own.

DB2 6.1 PE - 2. User

DB2 security is a little different from other DB's. When you create an instance, a system-level user is created and the instance runs as that user. If you're logged in as that user, you can access the DB without supplying credentials, but if you want to access it as another user, then you have to supply the system-level credentials.

It's funny though, when I was first learning DB2, none of the examples I ever saw showed how to supply credentials. For a long time, I thought you had to log in as the owner of the instance to be able to access that instance at all. It was all fairly confusing and it got even more confusing when I wanted access databases on remote machines, but that's another story...

The point is that the DB2 installation actually created an NT user named db2admin. This would be important later.

The rest of the install went smoothly.

DB2 6.1 PE - 3. Install

I eventually had to restart the system and when it came back up I was presented with some "First Steps".

DB2 6.1 PE - 4. First Steps

The Local Database

The first of the first steps was to create a sample database. I clicked that and it failed. Strange... I tried again (for some reason thinking it would work the second time) and got the same result. The Task Manager showed a bunch of db2-ish processes running. What the heck?

Ohhh... Heh. db2admin.

After reboot, I'd logged in as myself rather than the db2admin user that the installation process created.

The create-the-sample-db process was trying to run as me. I have no permissions. After logging out and back in as db2admin, creating the sample DB worked as expected.

The second of the first-steps led to the Command Center - basically a semi-graphical database shell.

DB2 6.1 PE - 5. Command Center

I used it to poke around in the sample database a bit.

The third led to the Control Center...

DB2 6.1 PE - 6. Control Center

...which allowed me to poke around a little bit more.

The fourth of the first-steps led to the Information Center (online documentation).

DB2 6.1 PE - 7. Information Center

The documentation viewer relied on Netscape to display the actual docs and the version of Netscape I had on there was a little flaky. It would complain about not being able to open a page, and then go ahead and open it. Fortunately the DB2 docs were about as old as Netscape itself and it didn't have any trouble rendering them.

Everything worked, but everything also ran terribly slowly. It turned out I'd configured the VM with 64m of ram and that just wasn't enough to run the DB. After bumping it up to 128m, everything ran really well.

Remote Access

So I had a working instance of DB2 in my NT VM and I could poke around in it with the provided tools. What kind of remote access could I get working though?

These days configuring remote access usually just means supplying a host name or address and port. That is, an IP address and TCP port. DB2 has been around for a while though, since long before TCP/IP emerged as the dominant protocol. Even Personal Edition supports TCP/IP, IPX/SPX, NetBIOS and APPC (whatever that is). As such, remote databases are abstracted out a bit beyond host and port.

DB2 achieves this via two catalogs: "nodes" and "databases". Nodes identify servers and databases identify instances of DB2 running on those servers. DB2 clients can be told what database to connect to, and they use the local catalog to figure out how to connect to it.

To add a node to the catalog, you have to specify the network protocol, name of the node, and protocol specific info. To add a database to the catalog, you have to tell specify the instance name (as it's known on the remote machine), a local alias for that instance, and the node it's running on.

I have several versions of DB2 running on various Linux VM's, including an instance of 7.2 running in a Redhat 6.2 VM and an instance of 10.5 in Fedora 19 VM.

I first tried connecting to 6.1 from the Linux VM's. This involved logging in as the db2inst1 user on Linux (the user the Linux instances are running under) and running commands like:

db2 "catalog tcpip node winnt remote winnt server 50000"
db2 "catalog database sample as sample61 at node winnt authentication server"
db2 "terminate"

The first command creates a node named "winnt" aimed at port 50000 on the host named "winnt". The second creates a database known locally as "sample61" aimed at "sample" on node "winnt". The third commits the configuration.

I could then try to access the 6.1 instance from Linux using the db2 shell interactively.

From DB2 7.2 on Redhat 6.2, it worked great.

db2 => connect to sample61 user db2admin using mypassword

   Database Connection Information

 Database server        = DB2/NT 6.1.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE61

db2 => select * from employee where job='CLERK'

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB      EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000120 SEAN                 O'CONNELL       A00      2167    12/05/1963 CLERK         14 M   10/18/1942    29250.00      600.00     2340.00
000230 JAMES        J       JEFFERSON       D21      2094    11/21/1966 CLERK         14 M   05/30/1935    22180.00      400.00     1774.00
000240 SALVATORE    M       MARINO          D21      3780    12/05/1979 CLERK         17 M   03/31/1954    28760.00      600.00     2301.00
000250 DANIEL       S       SMITH           D21      0961    10/30/1969 CLERK         15 M   11/12/1939    19180.00      400.00     1534.00
000260 SYBIL        P       JOHNSON         D21      8953    09/11/1975 CLERK         16 F   10/05/1936    17250.00      300.00     1380.00
000270 MARIA        L       PEREZ           D21      9001    09/30/1980 CLERK         15 F   05/26/1953    27380.00      500.00     2190.00

  6 record(s) selected.

db2 =>

Woohoo!

It didn't work so well from DB2 10.5 on Fedora 19 though.

db2 => connect to sample61 user db2admin using mypassword
SQL30081N  A communication error has been detected. Communication protocol
being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location
where the error was detected: "192.168.123.68".  Communication function
detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".
SQLSTATE=08001
db2 =>

Not so well indeed.

I tried in in reverse too - accessing 7.2 and 10.5 on Linux from 6.1 on Windows NT. I ran analogous catalog commands using Command Center on Windows and got similar results. I could access 7.2 just fine but attempts to access 10.5 resulted in:

SQL5048N  The release level of the client is not supported by the 
release level of the database server.

Yeah, I figured as much. The two versions are separated by over a decade. Got to break back-compatibility at some point, I guess.

So I could connect back and forth between 6.1 on Windows NT and 7.2 on Redhat 6.2. It's a start, but I'd need another link in the chain to be able to get to 6.1 from a modern OS.

SQL Relay

It's been longer than I can remember since I tried running SQL Relay against DB2 7.2. I knew it would build, and there are even #ifdef's in the code for some 7.2 quirks, but would it work?

Turns out no. Not at first at least. DB2 7.2 has a "feature" where if you connect to the DB, then fork the process, the child loses the connection and all subsequent queries fail. At some point I had logic in SQL Relay to deal with that feature, but newer versions of DB2 don't have the problem and the logic had long been removed. It took me a while to figure out what was going on, but once I did I remembered the bug and it was easy to fix. I even fixed it in a more elegant manner than I had in the past.

So Relay could talk to 7.2. Could it talk to 6.1 via 7.2's catalog?

Turns out yes!

DB2 6.1 PE - 8. Access From Fedora 20 x64

Woohoo again!

I now had access to DB2 6.1 Personal Edition from Fedora 20 x64.

And what a contrived chain of software was involved:

SQL Relay client on Fedora 20 x64 ->
SQL Relay server on Redhat 6.2 x86 ->
DB2 7.2 on Redhat 6.2 x86 ->
DB2 6.1 on Windows NT

But hey, it worked.

After a few modifications, I was able to run my standard DB2 test script too.

IDENTIFY:
success
PING:
success
CREATE TEMPTABLE:
success
INSERT:
success
BIND BY POSITION:
success success success
ARRAY OF BINDS BY POSITION:
success
INSERT:
success success success success
AFFECTED ROWS:
success
LONG BLOB:
success success success
SELECT:
success
COLUMN COUNT:
success
COLUMN NAMES:
success success success success success success success success success success success success success success success success success success success success success success
COLUMN TYPES:
success success success success success success success success success success success success success success success success success success success success success success
...

I did have to make a few tweaks to SQL Relay to get blobs and clobs working. There's still some issue with blob binds, but spending time on that is low on my list.

Endearing Features

Once I had everything running, I played with it for a while and began to discover some of the differences between 6.1 and newer versions.

Some of the more interesting ones...

The maximum size for a Blob or Clob column has to be specified during the create statement. For example:

create table test (col1 clob(1M), col2 blob(100K))

You can specify the maximum size in bytes, or use K, M or G.

Conversion errors are somewhat common and you often have to cast values, especially NULL values using CAST(X as CHAR(4)). I never did get a good feel for when this was necessary, but I ran into it a bit.

There's no obvious way to get the hostname from within the DB. In newer versions, you can run:

select host_name from table(sysproc.env_get_sys_info())

...but not in 6.1. I imagine that's another artifact of all the network protocols that it supports.

And my favorite... Stored procedures can only be written in C, Java or COBOL. There's no SQL PL. I guess it just hadn't been invented yet. Ha! I love it.

Ok, so that's it for DB2 6.1 PE, for now at least. Maybe I'll discover more weird stuff later and write more about it.

Maybe.