Easy IWA: An Introduction to the Easy IWA Technical Articles Series

[fusion_builder_container hundred_percent="no" equal_height_columns="no" menu_anchor="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" class="" id="" background_color="" background_image="" background_position="center center" background_repeat="no-repeat" fade="no" background_parallax="none" parallax_speed="0.3" video_mp4="" video_webm="" video_ogv="" video_url="" video_aspect_ratio="16:9" video_loop="yes" video_mute="yes" overlay_color="" video_preview_image="" border_color="" border_style="solid" padding_top="" padding_bottom="" padding_left="" padding_right="" type="legacy"][fusion_builder_row][fusion_builder_column type="1_1" layout="1_1" background_position="left top" background_color="" border_color="" border_style="solid" border_position="all" spacing="yes" background_image="" background_repeat="no-repeat" padding_top="" padding_right="" padding_bottom="" padding_left="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="small-visibility,medium-visibility,large-visibility" center_content="no" last="true" min_height="" hover_type="none" link="" border_sizes_top="" border_sizes_bottom="" border_sizes_left="" border_sizes_right="" first="true"][fusion_text columns="" column_min_width="" column_spacing="" rule_style="default" rule_size="" rule_color="" content_alignment_medium="" content_alignment_small="" content_alignment="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" sticky_display="normal,sticky" class="" id="" margin_top="" margin_right="" margin_bottom="" margin_left="" font_size="" fusion_font_family_text_font="" fusion_font_variant_text_font="" line_height="" letter_spacing="" text_color="" animation_type="" animation_direction="left" animation_speed="0.3" animation_offset=""] Abstract This article provides an overview of the Easy IWA Technical Articles Series. Using the TPCH database schema, Data Generation utilities and sample queries from http://www.tpc.org , this series covers everything to get up and running with IBM Informix Warehouse Accelerator including data generation, creation of an accelerator, loading a Data Mart and running a set of queries. Content The introduction of IBM Informix Warehouse Accelerator (IWA) added the capability to place data from tables within an IBM Informix database into an in-memory, columnar data store using a derivative of Huffman encoding to encode and compress, based on the BLU development by IBM. The resultant performance was phenomenal, taking queries which scan millions of rows down to seconds on commodity hardware. Following discussions between Oninit Consulting Ltd and IBM Informix R&D there followed the additional capability to load data from external tables, which obviated the need for data storage requirements within the source IBM Informix database and allowed any data source able to be presented as an external table, file or pipe, to be loaded into a Data Mart within IWA. This has now been further extended so that views and synonyms can now also be used as a source for loading into IWA.[br] This series of technical articles was written in order to provide a comprehensive, technical introduction to getting up and running with IWA and culminates with a set of queries demonstrating the raw performance that can be achieved with minimal resources.[br] The articles are as follows and are provided with attached files where applicable:[br] Easy IWA Part 1 Got any data? – The TPCH database, data generation and IBM Informix 12.10.FC4 Easy IWA Part 2 Creating an IBM Informix Warehouse Accelerator under 12.10.FC4 Easy IWA Part 3 Creating a Mart under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator Easy IWA: Part 4 Running a selection of TPCH Queries accelerated by IBM Informix Warehouse Accelerator 12.10.FC4 All of the material was developed on a laptop running Windows 7 with an OpenSuse 13.1 Virtual Machine allocated 4 CPU cores and 6 GB RAM running IBM Informix Dynamic Server Enterprise Edition 12.10.FC4 with IWA (although this would be far within licensing limitations for Enterprise growth Edition).[br] As an example of the performance achieved on the above host, the following query with 48,000,000 lineitems took 22 seconds.[br] [fusion_syntax_highlighter theme="" language="x-sh" line_numbers="" line_wrapping="" copy_to_clipboard="" copy_to_clipboard_text="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" class="" id="" font_size="" border_size="" border_color="" border_style="" background_color="" line_number_background_color="" line_number_text_color="" margin_top="" margin_right="" margin_bottom="" margin_left=""]c2VsZWN0CmxfcmV0dXJuZmxhZywKbF9saW5lc3RhdHVzLApzdW0obF9xdWFudGl0eSkgYXMgc3VtX3F0eSwKc3VtKGxfZXh0ZW5kZWRwcmljZSkgYXMgc3VtX2Jhc2VfcHJpY2UsCnN1bShsX2V4dGVuZGVkcHJpY2UgKiAoMSAtIGxfZGlzY291bnQpKSBhcyBzdW1fZGlzY19wcmljZSwKc3VtKGxfZXh0ZW5kZWRwcmljZSAqICgxIC0gbF9kaXNjb3VudCkgKiAoMSArIGxfdGF4KSkgYXMgc3VtX2NoYXJnZSwKYXZnKGxfcXVhbnRpdHkpIGFzIGF2Z19xdHksCmF2ZyhsX2V4dGVuZGVkcHJpY2UpIGFzIGF2Z19wcmljZSwKYXZnKGxfZGlzY291bnQpIGFzIGF2Z19kaXNjLApjb3VudCgqKSBhcyBjb3VudF9vcmRlcgpmcm9tCmxpbmVpdGVtCmdyb3VwIGJ5CmxfcmV0dXJuZmxhZywKbF9saW5lc3RhdHVzCm9yZGVyIGJ5CmxfcmV0dXJuZmxhZywKbF9saW5lc3RhdHVzOw==[/fusion_syntax_highlighter] If this is not fast enough, then by just adding another 4 cores with no other changes, duration is nearly halved to 13 seconds![br] These articles were written and developed by Oninit Consulting Ltd and kindly reviewed by Martin Fuerderer from IBM Informix R&D in Germany, thanks also go to Sandor Szabo![br] Martin is key in the IBM Informix Warehouse Accelerator development, writes a thorough and enjoyable blog on IWA at http://tinyurl.com/the-iwa-blog and is also a significant contributor the "Query Acceleration for Business Using IBM Informix Warehouse Accelerator" IBM Redbook http://www.redbooks.ibm.com/abstracts/sg248150.html?Open Conclusion This article provides an introduction to IBM Informix Warehouse Accelerator and an overview of a set of supporting technical articles to enable the reader to go through all requirements of generating a set of sample data, creating an Accelerator, creating and loading two Data Marts and finally running a set of controlled queries utilising the TPCH standard benchmarks. Disclaimer The code fix suggested above is provided "as is" without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement. [/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container][fusion_builder_container hundred_percent="no" equal_height_columns="no" menu_anchor="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" class="" id="" background_color="" background_image="" background_position="center center" background_repeat="no-repeat" fade="no" background_parallax="none" parallax_speed="0.3" video_mp4="" video_webm="" video_ogv="" video_url="" video_aspect_ratio="16:9" video_loop="yes" video_mute="yes" overlay_color="" video_preview_image="" border_color="" border_style="solid" padding_top="" padding_bottom="" padding_left="" padding_right="" type="legacy" admin_label="Contact us"][fusion_builder_row][fusion_builder_column type="1_1" layout="1_1" background_position="left top" background_color="" border_color="" border_style="solid" border_position="all" spacing="yes" background_image="" background_repeat="no-repeat" padding_top="" padding_right="" padding_bottom="" padding_left="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="small-visibility,medium-visibility,large-visibility" center_content="no" last="true" min_height="" hover_type="none" link="" border_sizes_top="" border_sizes_bottom="" border_sizes_left="" border_sizes_right="" first="true"][fusion_text columns="" column_min_width="" column_spacing="" rule_style="default" rule_size="" rule_color="" content_alignment_medium="" content_alignment_small="" content_alignment="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" sticky_display="normal,sticky" class="" id="" font_size="" fusion_font_family_text_font="" fusion_font_variant_text_font="" line_height="" letter_spacing="" text_color="" animation_type="" animation_direction="left" animation_speed="0.3" animation_offset=""] Contact us If you have any questions or would like to find out more about IBM Informix Warehouse Accelerator, simply contact us. [/fusion_text][fusion_button link="https://www.oninitgroup.com/contact-us/" text_transform="" title="" target="_self" link_attributes="" alignment_medium="" alignment_small="" alignment="" modal="" hide_on_mobile="small-visibility,medium-visibility,large-visibility" sticky_display="normal,sticky" class="" id="" color="default" button_gradient_top_color="" button_gradient_bottom_color="" button_gradient_top_color_hover="" button_gradient_bottom_color_hover="" accent_color="" accent_hover_color="" type="" bevel_color="" border_width="" border_radius="" border_color="" border_hover_color="" size="" stretch="default" margin_top="" margin_right="" margin_bottom="" margin_left="" icon="" icon_position="left" icon_divider="no" animation_type="" animation_direction="left" animation_speed="0.3" animation_offset=""]Contact us[/fusion_button][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

IBM Informix Warehouse Accelerator

Abstract

This article provides an overview of the Easy IWA Technical Articles Series. Using the TPCH database schema, Data Generation utilities and sample queries from http://www.tpc.org, this series covers everything to get up and running with IBM Informix Warehouse Accelerator including data generation, creation of an accelerator, loading a Data Mart and running a set of queries.

Content

The introduction of IBM Informix Warehouse Accelerator (IWA) added the capability to place data from tables within an IBM Informix database into an in-memory, columnar data store using a derivative of Huffman encoding to encode and compress, based on the BLU development by IBM. The resultant performance was phenomenal, taking queries which scan millions of rows down to seconds on commodity hardware. Following discussions between Oninit Consulting Ltd and IBM Informix R&D there followed the additional capability to load data from external tables, which obviated the need for data storage requirements within the source IBM Informix database and allowed any data source able to be presented as an external table, file or pipe, to be loaded into a Data Mart within IWA. This has now been further extended so that views and synonyms can now also be used as a source for loading into IWA.

This series of technical articles was written in order to provide a comprehensive, technical introduction to getting up and running with IWA and culminates with a set of queries demonstrating the raw performance that can be achieved with minimal resources.

The articles are as follows and are provided with attached files where applicable:

All of the material was developed on a laptop running Windows 7 with an OpenSuse 13.1 Virtual Machine allocated 4 CPU cores and 6 GB RAM running IBM Informix Dynamic Server Enterprise Edition 12.10.FC4 with IWA (although this would be far within licensing limitations for Enterprise growth Edition).

As an example of the performance achieved on the above host, the following query with 48,000,000 lineitems took 22 seconds.

Copy to Clipboard
Syntax Highlighterselect
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;

If this is not fast enough, then by just adding another 4 cores with no other changes, duration is nearly halved to 13 seconds!

These articles were written and developed by Oninit Consulting Ltd and kindly reviewed by Martin Fuerderer from IBM Informix R&D in Germany, thanks also go to Sandor Szabo!

Martin is key in the IBM Informix Warehouse Accelerator development, writes a thorough and enjoyable blog on IWA at http://tinyurl.com/the-iwa-blog and is also a significant contributor the “Query Acceleration for Business Using IBM Informix Warehouse Accelerator” IBM Redbook http://www.redbooks.ibm.com/abstracts/sg248150.html?Open

Conclusion

This article provides an introduction to IBM Informix Warehouse Accelerator and an overview of a set of supporting technical articles to enable the reader to go through all requirements of generating a set of sample data, creating an Accelerator, creating and loading two Data Marts and finally running a set of controlled queries utilising the TPCH standard benchmarks.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.