Technical Solutions

ISBN13 to ISBN10 Converter for SQL Server

October 24, 2014 | Blog Technical Solutions

ISBN13 -> ISBN10

Here’s a handy function for converting a 13-digit ISBN to its 10-digit equivalent in SQL Server T-SQL.

CREATE FUNCTION TO_ISBN10 ( @isbn varchar(13) )
    DECLARE @isbn10 varchar(10);
    DECLARE @forwards int;
    DECLARE @backwards int;
    DECLARE @check int;
    DECLARE @thisval int;

    SET @isbn10 =substring(@isbn,4,9);
    SET @forwards = 1;
    SET @backwards = 10;
    SET @check = 0;

    WHILE @forwards <= 9
        SET @thisval =cast(substring(@isbn10, @forwards, 1) AS INT)* @backwards;
        SET @check = @check + @thisval;
        SET @forwards = @forwards + 1;
        SET @backwards = @backwards - 1;

    SET @check = 11 -(@check % 11);

    IF @check = 10 begin
        SET @isbn10 = @isbn10 +'X';
        SET @isbn10 = @isbn10 +cast(@check AS VARCHAR(1));

    RETURN @isbn10;

Technical Solutions

Listing Outlook mailbox rules for all users

June 25, 2014 | Technical Solutions

Does your company use Outlook email forwarding? As your organisation has grown, have the rules become hard to manage?

We have a customer using Exchange Server for a few dozen users. They use mailbox rules so they can delegate various activities when staff are out of the office. Occasionally they lose track of what’s being forwarded, and it’s a pain to get this information from each user one by one. Here’s a little PowerShell script I threw together to automate it. I ran this on their server from the “Exchange Management Shell”. It’s my first PowerShell script ever; I’m used to Linux shell scripts and up till now haven’t made complex scripts under Windows. That may change:

PowerShell Script to list Outlook mailbox rules for users in an organisation

$users = get-mailbox

ForEach ($user in $users)
        $rules = get-InboxRule -Mailbox $
        if ($rules.length -gt 0) {
                echo ""
                echo $
                echo ""
                $rules | select name, priority, description | fl
                echo ""

Save this in a file with the extension “ps1” (that’s a one on the end) and run it from PowerShell to get the output – use > to redirect to a file if necessary. If you put the script in the current directory, you may need to explicitly specify the path, for example: .\run.ps1 > rules.txt Note that this only shows the server-side rules, such as placing messages in a folder, forwarding, etc. Anything that interacts with the client machine such as a popup will not appear in this report – this suits our customer because they want the rules to work when a user is not even logged in. The customer is pleased they can see the server-side rules in one report, and I’ve learned that it’s not just Linux that can support powerful scripting!

Technical Solutions

Oracle Function-based Index

May 7, 2014 | Technical Solutions


In this post, we give an example to show how a function-based index in Oracle can increase the speed of a query where null and not null values are being compared.

Our customer asked us to modify their application to enable an ETL tool to collect invoice records from an Oracle database. The invoice table contained upwards of 8 million records, but we were really only interested in looking at records which had been flagged for collection but not yet flagged as collected. However, the ETL tool’s method of flagging these records did not allow us to use a simple bitmap index to quickly retrieve the rows we needed.

What is ETL?

Extract, Transform, Load refers to a process that extracts data from outside sources (e.g. a file produced by a customer), transforms it to fit operational needs, and then loads it into your target database.

You can read about an example ETL tool, Pentaho Data Integration on our Technologies page.

Invoices example

Consider the following example data set for our Invoices table
Invoices table rows

In this example, the two columns with names starting ETL_ behave like flags. The possible values are null or a varchar2 value which happens to be based on a timestamp. For the purposes of this exercise, we are only interested in whether the values are null or not null.


which would retrieve the following rows

Queried rows from the Invoices table

Optimising the query’s performance using an index

In a table containing several million rows, this query would take too long to run. Adding a B-Tree index on ETL_collect and ETL_done wouldn’t help because the null values would not be included in the index. Bitmap indexes do include nulls but we have such a range of different not null values in our table, that a bitmap index is not the best choice.

We need to transform our ETL columns from null / not null values to 0 / 1 and then use an index to optimise the query. First we create a function that tests if a value is null, and returns a 1 or 0 accordingly –

    field_in IN INTEGER
  if field_in is not null then

By declaring this function to be DETERMINISTIC, we indicate that the function returns the same result value whenever it is called with the same argument.

Now we create an index on our ETL columns which makes use of this function on both of the columns we are testing –

ON Invoices (ETL_Match(ETL_collect), ETL_Match(ETL_done));

And now to revisit our original query. To force it to use this index, we need to reference the indexed columns in the where clause, and we can do that as follows:

WHERE ETL_Match(ETL_collect) = 1
AND ETL_Match(ETL_done) = 0;


With an underlying invoices table containing more than 8 million records, the original query took 17 seconds to retrieve 70 rows. The new query using a function-based index returns 70 rows in less than 3 seconds.

Can we help you?

If you have a database or application which is running slowly, we can help you make it perform better. Please give us a call or use the contact us form on the right, and we will get in touch.

Technical Solutions

Backups affecting system performance – our LVM2 solution

April 4, 2014 | Technical Solutions


Our customer had a vendor product which used a MySQL database.  The nightly backup of the database was preventing users from accessing the data for the duration of the backup.  As the data grew, the backup was taking longer and longer, and this was causing problems with other system activity – timeouts and failures had become an issue.

There are various approaches to backing up MySQL which we don’t cover here.  In this case, it was not feasible to change the customer’s backup procedure at the time, so this workaround, which took just a couple of hours to implement and test, helped them avoid regular downtime for a few months until an alternative backup approach could be rolled out.

We reduced downtime from over an hour to a few seconds

Before we addressed the problem, our customer’s data would be inaccessible for at least one hour each night.  After we implemented our solution, the downtime was reduced to a few seconds.

If you are interested in reading the technical details of this solution, we share them below.  If you’d prefer to speak to us about a similar problem in your organisation, please fill in the contact form on the right and we will get in touch.


Ewan’s Technical Notes

A redeployment of the vendor software was already on the horizon, including database replication as the vendor’s preferred backup strategy. In the meantime, what was the most cost effective way to prevent the old backup from disabling the system?

LVM2 – Logical Volume Management

I had previously used LVM2 snapshots to perform low level file system backups on a running system. The commands are well documented elsewhere, so a summary should suffice: If a file system (volume) has been created in a volume group with enough free space left over, an instantaneous snapshot can be taken and maintained by LVM2, using copy-on-write.  My previous use of LVM2 was to create such snapshots and run “dump” on those to perform a backup, in the knowledge that if I’d got my sums right, the snapshot would remain valid long enough to complete the backup, without interrupting any continued attempts to change the real file system.

Of course, database backups should not normally be a simple backup of the file system. Even if a snapshot is taken to guarantee no changes to the data being backed up during the process, a restore does involve an element of recovery – to the database, it looks like the system was shut down uncleanly. How could the instant snapshot to be used to take a conventional database backup?

The solution was simple, and had been hinted at elsewhere in my Google searches: mount the LVM2 snapshot as a normal file system, and create a second MySQL configuration to allow it to be accessed, albeit through a non-standard MySQL port. Then a backup could be done by pointing “mysqldump” to this copy. After the backup, the second MySQL would be shut down, the file system unmounted and the snapshot deleted.

Digram of how we used LVM Snapshots to backup MySQL

Using LVM Snapshots to backup MySQL

So that the second MySQL would not “wake up” to a file system requiring recovery, there was the need to lock the tables just like the previous “mysqldump” would do – but only for long enough to create the LVM2 snapshot, which is just a few seconds. Reducing the system pause to such a short time meant there were no longer any timeouts or failures.

This change to the backups was quick to implement and bought us time to properly roll out new deployments of the vendor software with an entirely different mechanism for backups. Having previously enjoyed using LVM2 for more conventional uses, this was an interesting and novel use which gave us a few months to breathe more easily.