Feed items

Improving remote query performance by tuning FET_BUF_SIZE

I thought I’d write blog post as a nice example of where tuning the client-side variable, FET_BUF_SIZE, really speeded up a remote query.

FET_BUF_SIZE is documented by IBM in the context of a Java application using JDBC here and as a server environment variable here.

Submitted by bthompson on April 3, 2017 - 14:12

Informix or Client SDK install: No Java virtual machine could be found

This is a something of a note to self. For some time it has been been the case that you may see this message when attempting an Informix server or Client SDK install if there is a problem starting the installer’s Java runtime environment:

Submitted by bthompson on January 5, 2017 - 12:44

Intermittent “CSM: authentication error” with JDBC

This article will only concern you if:

  • you connect using JDBC.
  • you use simple password encryption.

Simple password encryption just does one thing: it encrypts the password sent to the database server in transit preventing it from being obtainable by network packet sniffing.

At the client end configuring simple password encryption can be done simply by adding
";SECURITY=PASSWORD"
to your JDBC connection string.

Submitted by bthompson on December 21, 2016 - 18:24

Buffer waits

Is it really a year since I last wrote a blog post? It does mean I have had time to think of a few topics to write about.

Recently a desk visitor came to me at work about a performance issue. From what he said I got the impression that he thought that DBAs spend most of their time tuning SQL queries, something I spent little time on during a typical day, perhaps one of the advantages of working on a mature system.

Submitted by bthompson on November 21, 2016 - 14:30

Zone reclaim mode

Non-uniform memory access or NUMA is not a new concept but high end multiprocessor Intel-based servers are increasingly configured with this architecture, bringing it more to the mainstream. Put simply NUMA means that instead of all processors accessing your main system memory through a common bus, each processor is allocated an even share of the memory that it can address directly. If a processor needs to access memory controlled by another processor it can do so through that other processor.

Submitted by bthompson on October 16, 2015 - 17:51

Simple password encryption

Here’s a blog post on something I didn’t even know existed until recently: Informix simple password encryption. This does what it says in the tin and nothing else: it encrypts the password you use to authenticate to the database server which is sent in the clear otherwise. If you weren’t aware of this then it’s certainly worth reading on!

Submitted by bthompson on September 28, 2015 - 17:48

Auditing and onaudit

Compliance is one of those things you can hardly ignore as a DBA these days. Whether it’s a PCI-DSS, financial or internal best practice audit, at some point someone is going to ask you whether you are using database auditing. In my experience the auditors struggle to ask Informix specific questions but this is one that always comes up.

I guess there are three answers to this question:

Submitted by bthompson on July 21, 2015 - 15:38

Large parallel index builds and temp space

This is a quick post about parallel index builds. Today I was building with PDQPRIORITY a unfragmented detached index on a large table fragmented by range with ten large fragments and I saw this message in the online log:

10:28:53 WARNING: Not enough temp space for parallel index build.
Space required = 110566014 pages; space available = 8385216 pages.
Partial index build started.

You can see I am quite a long way short of the temp space required here; I need just over thirteen times more.

Submitted by bthompson on July 7, 2015 - 17:53

Experience with Auto Update Statistics (AUS)

Introduction

This is based on my previous blog post, Working with auto update statistics which I’ve expanded and significantly improved. I presented this at the Informix International User Group Conference 2015.

Submitted by bthompson on April 30, 2015 - 03:13

Temporary tables

Again, this is another blog post about nothing new at all but an attempt to put down my understanding of temporary tables down in a way that will help me when I refer back to it and hopefully be of more wider use.

When looking at things like this a test system is always essential for checking things and finding some surprising results. If you don’t have one you can soon set one up with VMWare or Virtual Box and a copy of Informix Developer Edition. For this post I am going to set up a simple test instance with four specific dbspaces:

Submitted by bthompson on March 29, 2015 - 17:00