Analysis of SQL statements going through a database engine can be the most important task to improve user response times. Even if you think all is well, you may discover coding faults or unexpected choices made by the query optimizer, resulting in longer execution times and higher system load, that can often be easily fixed once you know which are the worst. It isn’t just the longest queries that matter: saving a few milliseconds on a statement can have a big impact if it’s run thousands of times. Increasingly, database security and auditing is becoming a focus. For example, there may be a requirement to identify which users have made schema changes or updated certain tables. Most auditing solutions focus on how specific data records have changed, however, sometimes it is more meaningful to know what SQL was actually run to generate that change. Whatever the requirement, in order to analyse SQL workload, we need to capture SQL statements. Over recent years, a number of commercial solutions have entered the market that are designed to provide a SQL capture capability. We’ve evaluated most of these products including: iWatch (Exact Solutions); SQL Power (SQL Power Tools). An analysis of these tools is outside the scope of this particular article. This article will focus on what can be achieved with the underlying Informix software utilities (including Informix SQL/SPL scripting approaches) and various Informix management interfaces and tools.

[Read More…]

Fragmentation vs Sharding. So, what is it? Fragmentation has been available in Informix since V7.00 which was released in the mid-nineties. It allows you to group data rows and/or indexes for a table according to a user-defined distribution scheme and physically place these fragments in separate dbspaces and on separate physical disks. This can bring benefits to query performance by spreading the I/O over multiple devices, by elimating the need to read fragments not relevant to a particular query or even scanning multiple fragments in parallel. As data volumes grow, the ability to fragment large tables across multiple dbspaces can also reduce the requirement to create dbspaces with larger page sizes and the additional buffer pools required for them. But, in today’s Big Data era, as data storage requirements grow at an ever increasing pace, what if the performance and capacity of a single server can no longer meet these demands ? One possible answer could be Sharding. Sharding was introduced at V12, it allows you to group data rows and index keys for a table according to a user-defined distribution scheme and physically place these fragments on separate servers, locally or remotely. This allows the resources of some or all of these servers to be used when processing queries. As your database grows, rather than scaling up by adding more processors and/or RAM to an existing server, you can scale out by adding more servers. Also, as Sharding makes use of Informix Enterprise Replication, there is no requirement for the server hardware and operating systems to be the same. Read on to discover the pros and cons of Informix V12 Fragmentation vs Sharding.

[Read More…]

This article follows on from the earlier TPC-C benchmarking performed on IDS 12.10, if you missed it, you can read it here. This article takes a view on the topic of DIRECT_IO and its use within IDS, it also highlights the impact of file system caching on Linux, which is relevant for those editions of Informix where DIRECT_IO cannot be used. Lastly, a ‘just for fun’ comparison of DIRECT_IO and RAW devices for readers to come to their own conclusions. Read on to learn more about the impact of DIRECT_IO and File System Caching on IDS.

[Read More…]

In this series of articles, we are exploring some of the lesser known, lost and forgotten commands buried away in your Informix bin directory. Today we’re going to have a closer look at the lesser known IDS commands: genoncfg utility. As the name suggests, this command generates a basic onconfig file for you, given a set of input parameters. Although this was our first encounter with the utility, it became apparent this utility is not working as expected; as a result, we’re not going to suggest you use it to generate a configuration for an important environment. Instead, this article will concentrate on the parts that do appear to work, and how the results differ from a configuration generated by the installer. These tests were carried out on IDS V12.10.FC6IE.

[Read More…]

If you’re building or fixing Informix ESQL-C applications then you’re almost guaranteed to come across the UNIX ‘make’ utility. This article describes Makefile, a simple Informix ESQL-C  that can be adapted for many small projects.

[Read More…]