onstat -g aqt command: Print data mart and accelerated query table information

Use the onstat -g aqt command to display information about the data marts and the associated accelerated query tables (AQTs).

Syntax:

Example output

Figure 1. onstat -g aqt command output

AQT Dictionary Cache for database school:

mart: school
accelerator: DWAFINAL
last load: 2011/07/29 07:00:39

AQT name                                  FactTab #tab #matched     address
--------------------------------------------------------------------------------
aqt4d11b552-7d41-4b0c-824b-7714b6cb580a    103    1      328         0x4d187e08
aqt61498fab-3617-4c8c-ab40-fd8af4253998    103    2       42         0x4d84a448
aqtbc2da77c-bca8-4ce7-9191-8180a860da34    103    2      768         0x4d187f60
aqt88757e9d-81ee-43b4-87b2-0bf48c98fa55    103    3       15         0x4d84a190
aqta786d0dc-8e95-4de0-a1bd-773aa03a52db    103    3     1475        0x4d84a650
aqt8dd61c80-2c1c-4f0e-8f0c-91babe789f41    103    4      632         0x4d84a908

mart: school2
accelerator: DWAFINAL
last load: 2011/07/29 07:01:04

AQT name                                  FactTab #tab #matched     address
--------------------------------------------------------------------------------
aqt56d5aea7-32f4-44e6-8d98-02a7af37630f    103    1      845        0x4d84ac70
aqt03ec4c20-7ba8-4c3a-ae56-4134b005269d    103    2       27        0x4d95c298
aqt4ae7c2fd-5b94-423d-bc49-9ca3f5f38799    103    2     3912        0x4d84adc8
aqt5ed69a75-15e3-45cc-9892-4f5386257895    103    3       83        0x4d95c4a0
aqtdf314aa6-177d-4443-9f6d-f14ba766995a    103    3       37        0x4d95c028
aqt7e36b1f2-4646-4075-ac0b-5fdee475cd7e    103    4      518        0x4d95c758

mart: school3
accelerator: DWAFINAL
last load: 2011/07/29 07:01:50

AQT name                                  FactTab #tab #matched     address
--------------------------------------------------------------------------------
aqt92b36a8a-1567-4146-833c-385cd103f5d4    103    1      678        0x4d95cac0
aqt3189bec1-b6c9-417d-b969-92c687ef2e44    103    2       59        0x4d95cc18
aqt8d3b3dc8-59b6-4e34-822b-75b06b99c900    103    2     4487        0x4d90c0d8
aqt5f9c2a05-9131-4738-a929-036fcf77f65c    103    3       71        0x4d90c2e0
aqtee08ed16-6a5c-4478-ac57-fc4f99539c74    103    3      795        0x4d95ce20
aqt04d1c96a-022b-4ed7-938d-caf765bc9926    103    4      367        0x4d90c598

18 entries

If you use the AQT name for the optional aqt_name parameter, the command prints information about the specific AQT.

Figure 2. onstat -g aqt aqt_name command output

AQT: aqt6de1afdd-f10a-45b0-93e9-0c208405fefd
database: iwadb
AQT tabid: 125
Fact table: 111
Number of times matched: 8947

Join structure: alias(tabid)[colno,...] = alias(tabid)[colno,...] {u:unique}
0(111)[1] = 1(110)[1] u
            1(110)[2] = 2(109)[1] u
                        2(109)[5] = 3(101)[1] u
                                    3(101)[3] = 4(100)[1] u
0(111)[2] = 5(106)[1] u
            5(106)[2] = 6(103)[1] u
            5(106)[3] = 7(104)[1] u
            5(106)[4] = 8(105)[1] u
                        8(105)[3] = 9(101)[1] u
                                    9(101)[3] = 10(100)[1] u
            5(106)[5] = 11(102)[1] u
0(111)[2,3] = 15(108)[1,2] u
              15(108)[1] = 16(106)[1] u
                           16(106)[2] = 17(103)[1] u
                           16(106)[3] = 18(104)[1] u
                           16(106)[4] = 19(105)[1] u
                                        19(105)[3] = 20(101)[1] u
                                                     20(101)[3] = 21(100)[1] u
                           16(106)[5] = 22(102)[1] u
              15(108)[2] = 23(107)[1] u
                           23(107)[2] = 24(101)[1] u
                                        24(101)[3] = 25(100)[1] u
0(111)[3] = 12(107)[1] u
            12(107)[2] = 13(101)[1] u
                         13(101)[3] = 14(100)[1] u

Output description

The AQTs are grouped by the data mart that they belong to. The groups are sorted by accelerator name, and then by data mart name. Within the data mart groups, the AQTs are sorted in the following order: Fact table tabid (FactTab), number of tables (#tab), and AQT name.

The output comes from the entries in the dictionary cache that refer to the AQTs of the data marts. The output is shown only if the AQTs have been loaded into the dictionary cache, which normally occurs when a query is being matched against the AQTs.

Before the server attempts to match a query against the AQTs, the AQTs do not have any entries in the dictionary cache. The onstat -g aqt command will not show any entries in the output. When the dictionary cache is initialized during the database server startup, the columns #matched and address get new values.

The onstat -g aqt command prints the following information:

mart: The name of the data mart
accelerator: The name of the accelerator instance
last load: The time stamp for when the data mart was last loaded
AQT name: The unique system-generated name of the AQT
FactTab: The tabid of the fact table for the AQT
#tab: The number of tables that are part of the AQT
#matched: The counter for query matches that have occurred for the AQT
address: The internal database server memory address for the AQT

The onstat -g aqt aqt_name command prints the following information:

AQT: The unique system-generated name of the AQT
database: The name of the database to which the AQT belongs
AQT tabid: The tabid for the entry that constitutes the AQT in the systables system catalog table of the database server.
Fact table: The tabid of the fact table of the AQT
Number of times matched: The counter for query matches that have occurred for the AQT

The information about the AQT is followed by a textual representation of the star schema of the data mart. The textual representation shows how the columns of the tables are related to each other in the star join.

For information about the Informix® Warehouse Accelerator, see the IBM® Informix Warehouse Accelerator Administration Guide.