ifx_probe2Mart() procedure

The ifx_probe2Mart() procedure converts the data that is gathered from probing into a data mart definition.

Syntax

database: The name of the database that contains the data warehouse. This is the warehouse database on which the workload queries are run. See Usage.
data_mart_name: The name that you want to use for the data mart definition. The name is also the name of the data mart that is created later, based on the data mart definition. If the name you specify is an existing data mart definition, the probing data is merged into the already existing data mart definition. If the data mart definition you specify does not exist, the data mart definition is created.
sqlid: Optional. The ID of the query SQL statement, which identifies the probing data from that query. If the sqlid is not provided, all of the probing data from the specified database is added to the data mart definition.

Usage

The ifx_probe2Mart() procedure should be run from a different database than the warehouse database. This separate database must be a logging database. You can use a test database, if the test database is already a logging database, or you can create a different database that keeps these tables separated from your other tables.

When the procedure is run, the probing data is processed and stored in the logging database in a set of permanent tables.

The tables keep the data mart definition in a relational format. The tables are automatically created when the probing data is processed into a data mart definition for the first time.

The ifx_probe2Mart() procedure creates a data mart definition by converting the probing data and inserting rows into the following data mart schema tables.

Table 1. Data marts schema tables
Table	Description
'informix'.iwa_marts	Names of the data mart definitions
'informix'.iwa_tables	All of the tables that are used in any data mart definition
'informix'.iwa_columns	All of the columns that are used in any data mart definition
'informix'.iwa_mtabs	Tables for a specific data mart definition
'informix'.iwa_mcols	Columns for a specific data mart definition
'informix'.iwa_mrefs	References (join descriptors) of a specific data mart definition
'informix'.iwa_mrefcols	Reference columns (join predicates) of a specific data mart definition

Examples

To convert, or merge, all of the probing data into a data mart definition, use this form of the syntax:

EXECUTE PROCEDURE ifx_probe2Mart('database', 'mart_name');

For example, to generate a data mart definition named salesmart from all of probing data that is available for the database sales, use this statement:

EXECUTE PROCEDURE ifx_probe2Mart('sales', 'salesmart');

You can also merge the probing data from a specific query into a data mart definition. You need to look up the SQL ID number of the query that was captured by SQL tracing. SQL tracing must be ON to designate data from specific queries. Queries are identified by a statement ID.

For example, to merge the probing data from SQL statement 8372 into the data mart definition salesmart, run this command:

EXECUTE PROCEDURE ifx_probe2Mart('sales', 'salesmart', 8372);

To create a data mart definition from queries that run longer than 10 seconds, use this SQL statement:

 SELECT ifx_probe2Mart('sales','salesmart',sql_id) 
    FROM sysmaster:syssqltrace  
    WHERE sql_runtime > 10;