|Last updated: 29 January 2012
A quick introduction: What is Sybase IQ ?
If you're new at Sybase IQ, it may be useful to get some terminology straight and provide some basic background information. Here we go:
Sybase IQ is a database server optimized for analytics/BI. IQ is very good for ad-hoc queries that would be difficult to optimize in a transactional RDBMS. For example: how many female customers have shoe size 8 and bought a blue dress on a Saturday this year ?
IQ is the world's leading analytics database; according to Sybase, IQ has more customers than all other column-oriented analytics databases combined.
IQ is used in virtually every line of business. One well-know customer is Nielsen Media (the folks doing the USA TV viewer ratings); many others are described at www.sybase.com/iq.
If you've never seen it before, IQ performance is just incredible: response time can easily be 100's or 1000's times faster than in OLTP-oriented transactional databases. This is due to IQ's column-oriented internal architecture that allows fundamentally more efficient access to data for bulk operations. The column-oriented architecture also has the effect that the total data volume tends to shrink after loading it into IQ (for transactional databases, the data volume typically expands significantly, so the difference with IQ can be an order of magnitude).
Unlike many other analytics products, IQ does not require pre-aggregations ('cubes') to be created, thus avoiding much costly development work (IQ supports such aggregates if desired).
IQ is not good at OLTP (performance-wise, that is), as opposed to Sybase ASE, which is optimized for OLTP.
IQ runs on the main flavours of Unix, on Linux, and on Windows. Go here to find out if IQ is certified on particular hardware or OS.
IQ is certified with many third-party BI/ETL products; see www.sybase.com/iq.
The most recent IQ release is IQ version 15.4 (released December 2011).
Around 1994 a company named Expressway was acquired by Sybase. They had taken a novel approach towards handling the type of workload we would describe as 'analytics' today.
Their innovation was to store data not by row, but by column, and still fully support the conceptually row-oriented ANSI SQL standard.
Sybase renamed the product "IQ" (for "Intelligent Query") and developed it into the powerful analytics engine it is today.
Sybase IQ is sometimes referred to as 'ASIQ' after its old name 'Adaptive Server IQ'.
The past few years, a new generation of IQ version has been released, collectively known as IQ 15.
Some highlights for the major IQ 15.x versions:
- IQ 15.1 introduced the concept of IQ 'user-defined functions'. These are functions residing in libraries outside IQ, and that can be called from SQL inside IQ. Typically such functions are used for complex analytical purposes that cannot easily be implemented in SQL (e.g. Monte Carlo simulations).
- IQ 15.2 (reelased in 2010) introduced full text search based on text indexes. This includes things like proximity search, fuzzy search, and customizable term breaker functions.
- In IQ 15.3 was released in 2011 and was the first IQ version to supprot distributed query processing (DQP), under the feature name of 'PlexQ'. This requires IQ multiplex (the clustered version of IQ), where an individual query can be chopped up in smaller parts which are all processed on different cluster nodes, thus resulting in faster response times.
For more details on these (and other) IQ features, please consult the IQ documentation.
- IQ 15.4 (end of 2011) introduced further advancements in the external user-defined functions, including the capability to use Hadoop and Map/Reduce functionality to process IQ queries.
When working with IQ, you really need to have a copy of my Sybase IQ quick reference guide: see www.sypron.nl/iqqr.