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.


Release 1.3.11

This release fixes a couple of minor errors, and makes some productivity improvements to existing functions.

Stage Column Documentation
If a column was manually added to a stage table after initial creation, the documentation entered at this point was not updated. This has been fixed.

First Column Calculation / Literal
If a calculated column, or literal, was moved to the first column position in a table, Ajilius would generate invalid transform SQL. This has been fixed.

We’ve made the following changes to existing functions.

Enable Transformations on New Columns
We made an early design decision that transformations were applied by changing a column. When adding a column, we did not know if it was capable of being transformed until after the addition. This means that if you wanted to re-add a previously deleted column, and upper-case it at the same time, that you had to go through two steps – add the column, then change it to add the transformation.
We have now modified the Add Column function to enable you to enter your choice of source-column, role, transform and calculation. Roles and transforms can only be applied to selected source-columns, not to calculations. If both a source-column and a calculation have been entered, the source-column selection will take precedence.

Enable Column Selection on Join Tables
You might recall a post, many months ago, titled Our Join Editor Sucks. Well, Join Editor V2 was a big improvement, but still had some workflow issues. When you joined a table, all columns from that table were copied into the join set. You could delete columns from the column management functions, but users (self included) did not like this interruption to the thought process that typically happens when joining tables.
The new Join Editor enables column selection at the point of join definition. A toggle supports select-all functionality on the list, or you may select individual columns. This is a small improvement when you want all columns from a joined table, but a huge leap forward in productivity when you only want one or two columns from a larger table.
You can still used the column management functions to add, change and delete individual columns from the join set, as well as sort the columns into your preferred sequence in the table.

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact

PostgreSQL message encoding

We ran into an interesting error this week, where a job was ending with an encoding error on the completion message.

Our first thought was that the database encoding did not match the value in the metadata, but no, both were set to UTF-8.

This is an Italian customer, and after much head-scratching, we discovered that the configuration value for lc_messages was set to ‘Italian_Italy.1252’. Ajilius expects messages returned by the server to be in the same encoding as the data warehouse.

In this case, the DBMS had been installed with settings from the Italian locale on the server, but the data warehouse database had been created with UTF-8 encoding.

We will now recommend that servers are initialised with initdb using the preferred encoding.

In this case, the customer changed the value of lc_messages in postgresql.conf, and the problem was resolved.

Release 1.3.10

This release is a quick maintenance update, with a minor enhancement to built-in transforms.

WHERE Test Error
The Test button on the WHERE clause editor may have triggered a fatal error screen. The clause was correctly written, but could not be tested. ELT jobs worked correctly. This error has been corrected.

We’ve made the following changes to existing functions.

New Transforms
We’ve added the following transforms to the Change Column screen:
– Cast to Integer
– Cast to Numeric

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact

Release 1.3.09

The devs have been busy!

This release brings a bunch of new features to make it easier to re-run whole sections of the data warehouse during development and testing, as well as some functions to keep your development environment neat and tidy.

Load | Create All
Create, or drop and re-create, all Load tables.

Load | Process All
Execute the extract, push (for cloud DW), and load processes for all Load tables.

Stage | Create All
Create, or drop and re-create, all Stage tables.

Stage | Process All
Execute the transform processes for all Stage tables.

Dimension | Create All
Create, or drop and re-create, all Dimension tables.

Dimension | Process All
Execute the transform processes for all Dimension tables.

Fact | Create All
Create, or drop and re-create, all Fact tables.

Fact | Process All
Execute the transform processes for all Fact tables.

Warehouse | Clear Scripts
Erases the contents of the SCRIPTS directory.

Warehouse | Clear Extracts
Erases the contents of the EXTRACTS directory.

Warehouse | Drop Tables
This function lists all the tables in your data warehouse. You may check one or more tables, then press the DROP button to remove those tables from your DW database. Use this function with care, once dropped, tables can only be restored from backup.

We’ve also made one small change to the Load Tables list, to bring it into line with the other table displays.

Load | List Load Tables
The column role has been added to the table columns list at the right side of the screen.

There are no metadata changes in this release, and no known errors to be fixed.

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact

Release 1.3.06

We’re now growing fast enough that we need to formalise the release update process.

Each time we publish an update, we’ll post the details of the update in this topic.

This release brings the following user changes:

  • Amazon RDS PostgreSQL as DW.
    We always supported RDS as a data source, now we support it as a first-class target platform as well. This means that you can use PostgreSQL on premise, in the cloud on IAAS, or in the cloud as PAAS. Three-click migration means that you quickly and easily migrate between these platforms, as well as Snowflake, Redshift and Azure SQL Data Warehouse.
  • New Oracle driver offering more simple setup.
    The previous Oracle driver was extremely complex to install, and caused more support issues than any other aspect of installation and configuration. We have now switched to a driver that does not depend on the Oracle client software, with much better results.
  • Optional command line credentials.
    ELT scripts use environment variables to pass credentials and paths. Some users reported difficulty setting environment variables in complex server environments, so we have added the option of command-line credentials to all jobs.
  • Deleting a table or column no longer deletes descendants.
    If a table or column is deleted, we now set the descendant columns to NULL. This leaves downstream tables intact, while enabling you to restructure earlier tables in the ELT flow.
  • A new Fast Map feature to quickly adjust table and column sourcing.
    If you have restructured precedent tables or columns, or changed your mind about the sourcing of a table, you can now quickly update the column sources through this function.
  • A new Fast Delete feature to rapidly delete large numbers of columns.
    You might only require a handful of columns from a set of joined tables. This feature makes it quick and easy to delete all the unwanted columns in one pass, rather than separately deleting each column.
  • Documentation enhancements.
    We have added more obvious references to column roles, transformations and calculations in the data warehouse documentation generated by Ajilius.

All users will receive an email with the new code and instructions. There are no database changes in this release, just copy the executable to your Ajilius directory