IBM DB2: Back to DSN

The devs told me that getting DB2 and Informix drivers to work took a bit of fiddling. That was the understatement of 2015. The driver setup experience is so bad that we can’t include it in the Ajilius installer.

On every platform we needed to manually copy files around, adjust environment variables, sometimes patch libraries, and often it just didn’t work. We tried the Python ibm_db adapter, we tried IBM’s ODBC / CLI adapter, and experienced nothing but pain.

As a result, we’re dumping the work we’ve done on native adapters, and reverting to the use of ODBC DSNs for data sourcing against DB2 and Informix.

To use DB2 as an Ajilius data source, have your IT department deploy an appropriate ODBC connection on your Ajilius server, then create a DSN to your data source. Select “ODBC DSN” to create your data source in Ajilius, then enter the DSN name in the Database field.

It is sad that IBM’s quality has sunk so far. I started my IT career on IBM System/34 computers well over 30 years ago, and at various times worked on System/38 and AS/400. I used one of the first DB2 mainframe installations in Australia, followed by the first OS2/EE implementation of DB2, before using DB2 on Windows as the core of a successful ISV product. Later, I ran a DBA team that included DB2 on mainframe, Linux and Windows in its portfolio.

You couldn’t call me a DB2 hater with that background, but the current connectivity options are rubbish.

DB2 is not a bad DBMS, but what good is a DBMS  without great connectivity? I’d struggle to recommend it to anyone based on my most recent exposure and I’d definitely not recommend it to any Python developer.

IBM Bluemix

During the past week we have been testing the new data source adapters for DB2 and Informix. This time we’re using IBM’s Bluemix cloud to host our test databases.

The initial experience with Bluemix is awful. A bizarre labyrinth of errors about missing spaces and empty containers, all solved when you finally realise that the service you want to provision is only available in some regions, and your default is not one of them.

Depending on the region you have chosen, there are many supported databases including variants of Informix, DB2 and Netezza, as well as a variety of open source, big data and NoSQL products.

Once you’re up and running, the actual database experience is quite good. I like the data load feature, which quickly helps you to move test data into the database. The help around connectivity – CLI, ODBC and JDBC – is also good, with all the connection information clearly presented for each of the options.

The free database allowance for SQL DB (the old DB2 LUW) is quite generous, enough for us to complete all our testing. If you want more than the free tier supports, though, the next step jumps from zero to $500 per month. That is expensive compared to Azure SQL and AWS RDS databases.

dashDb (Netezza) and Time Series (Informix) start at around $55 per month, which is reasonable value compared to other vendors.

Our testing is focussed on connectivity and extracts, not in-database performance, so we can’t comment on how well the platform scales.

Well, time to get the testing finished, as this is the last task between us and Version 1.4.

MySQL, MariaDB and Aurora

This afternoon I signed off the enhanced data source adapters for MySQL, MariaDB and Amazon’s new Aurora database.

These adapters are compatible with both on-premise and cloud-hosted databases, with full Unicode support.

We’re fully tested against the Employee, Sakila and Classic Models sample databases, so now it is time to see some customer databases being loaded.

That’s another big step on the path to Version 1.4, only the enhanced DB2 adapter remains in the queue.

Adwords puzzle

For 10 months we’ve been testing combinations of Google Adwords keywords to drive traffic to Ajilius. Over that time we’ve got a pretty good idea of which keywords work.

As we planned a new campaign for the New Year, we decided to turn off Google advertising for a couple of weeks, to reset our no-advertising baseline.

The expected result was that traffic would go down, and it has, but not quite as much as we expected. That suggests search results rather than advertising may be driving a higher volume of traffic than we thought.

What was unexpected was the change to Google search results.

When running an Adwords campaign, if we searched for one of our terms such as “data warehouse automation” we were usually on result page 2 or 3, with many competitor ads above us.

Now that we’re not running a campaign we’re in roughly the same search position, but there are far fewer competitor advertisements showing above us.

Could it be that Google artificially inserts advertisements above yours, pressuring you to increase your budget to move you up the page?

That theory smells of tin-foil hats, but it is something for us to watch over the next few weeks.

Dependency diagrams

With Version 1.4 due by the end of December, we’ve just made our final implementation decision regarding dependency diagrams.

Over the past month, the team has produced literally hundreds of diagrams, testing fourteen different approaches to diagram generation.

The short list of implementation technologies came down to three Javascript libraries – Mermaid,VisJS and Cytoscape.

Mermaid’s Dagre layout algorithm produced the best technical appearance in diagrams, but its layout engine experienced severe difficulties when diagrams became more complex. Development is lagging, and is dependent on an unsupported core library.

VisJS was good, but it suffered from hierarchy layout problems, as well as labelling conflicts in diagrams. I think VisJS is more generally powerful, but fell back in our use case.

The best compromise solution was Cytoscape. It is in active (and enthusiastic) development, has a growing selection of layout algorithms, does the best job of laying out our diagrams, and supports features we need such as custom colours and tooltips.

Here’s a sample of what you can expect with the coming release of V1.4.