The Informix database server has traditionally been known for high-performance Online Transaction Processing (OLTP) workloads. With the integration of Informix Warehouse Accelerator and the Informix database server, tremendous query acceleration performance has been observed, with customers reporting 60 to 1400 times quicker responses for certain datawarehouse queries. (See Customer Quote.)
The OpenAdmin Tool (OAT) is an administration tool for Informix database servers that can now administer accelerators and data marts associated with the Informix server. This article explains how to create a data mart using OAT. It is assumed that you are familiar with the Informix server and the Informix Warehouse Accelerator.
Since version 3.11, data marts can be created and managed in the Schema Manager plug-in of OAT. This requires Informix server version 12.10 or higher.
OAT creates data marts using the Informix server's workload analysis capability. Whether it is a simple workload with a handful of queries or a complex one with hundreds of queries, when you run the workload, the server analyzes them and determines which queries can be accelerated. Based on the join conditions in the queries, the server creates an optimal data mart definition. This definition is used to create and deploy the data mart on the Informix Warehouse Accelerator server.
You run queries on the Informix server using dbaccess or any client tool or application. With SQL Tracing turned on in the server, the query statements and related information are stored in the SQL Trace buffer.
From the Schema Manager in OAT, select the database (also referred to as the warehouse database) where you intend to create the data mart. On the Actions menu, select Data Marts > Create a Data Mart. Name the new workload and click Start. The SQL trace settings that you use for the workload capture, such as the number of statements, buffer size, and the user executing the queries, are set in the server as the SQL trace setting for the workload capture. Existing SQL trace settings, if any, are restored after you run the workload. See Figure 1.
Run the workload queries. When done, click Stop. (See Figure 2.) Stopping copies the statements to a table in the warehouse database. This table is named with the prefix, 'dwa_saved_workloadtab_' appended to the workload name. The SQL trace settings are restored to the values that they were before you ran the workload. A probe is done on the captured statements to determine which statements can be accelerated.
If the workload is long running and you need to do administrative tasks in OAT, you can save the state of the wizard and retrieve it later to create the data mart. To do this click Save and Close in step 1 of the wizard, as shown in Figure 1 (see larger image). To get back to the wizard, click Open on top of the details area of Schema Manager.
Figure 1. Capturing state in Create Data Mart wizard
Figure 2 (see larger image) shows the capture completed state in the Create Data Mart wizard.
Figure 2. Capture completed state in Create Data Mart wizard
The list of captured statements and whether they can be accelerated is displayed in step 2 of the wizard for review, as seen in Figure 3 (see larger image).
Figure 3. Review Workload in Create Data Mart wizard
Data mart creation
In step 3, enter the data mart name and select an accelerator server. Optionally, you can load the data mart. (See Figure 3.) Data marts tend to have large amounts of data and, hence, the load operation can run a long time. You can schedule loads separately to run immediately or at a predetermined time. Also, you can retain the workload created in step 1 for later use to create a data mart without having to rerun the workload queries. Refer to Figure 4 (see larger image) and Figure 5.
Figure 4. Creating Data Mart
Figure 5 (see larger image) shows the results page in the Create Data Mart wizard.
Figure 5. Result page in Create Data Mart wizard
Data mart details
The data mart is listed in the Schema Manager alongside other database objects such as tables, views, and so on. The data mart details include a link to the accelerator that is associated with it, type of loads configured, load schedule, and the information about the tables that make up the data mart. See Figure 6 (see larger image).
Figure 6. Data mart details
In this article, you learned how to create data marts in the Informix server using OAT to take advantage of the powerful analytics capability of Informix.
- Find out all about OpenAdmin Tool for Informix, including release details, screenshots, demos, and download links on the OpenAdmin Tool Web site.
- Read about OAT in the IBM Informix Information Center.
- Read about Data warehousing and the Informix Warehouse Accelerator in the IBM Informix Information Center.
- Watch OAT demos on the OAT YouTube Channel.
Get products and technologies
- Download the Informix Client SDK, which includes the web-based OpenAdmin Tool for Informix. OAT is included in the Linux x86 (32-bit), Linux x86-64 (64-bit), Windows x86 (32-bit only), and Mac OS X x64 (64-bit only) Informix Client SDK downloads.
- Participate in the OAT IIUG Forum.
- Ask questions about OAT or Informix in the Informix developer and user forum on developerWorks.