Take advantage of SQL's Materialized Queries to build quick and easy work files.
Despite our best efforts to
achieve excellent database normalization, sometimes it's necessary or simply
more efficient to create a temporary table containing some data as of a certain
time. SQL satisfies this need with Materialized Query Tables (MQT). These
specialized SQL objects allow a database administrator to define the data
selected for inclusion and then easily refresh the query with current data from
the database tables as needed.
The most powerful use of an MQT is not
having users access it directly, but allowing the SQL optimizer to choose to use
the MQT instead of the underlying base tables to satisfy other SQL statements.
Anytime an application needs static data—such as end of year, end of
month, or end of day statistics—this information, which might normally be
found by accumulating data from detail tables, could instead be provided by
creating an MQT that contains this static data.
Creating a Materialized Query
Creating MQTs relies upon the SQL CREATE statement, as
shown below:
CREATE TABLE INVTOTALS AS
(WITH TEMP AS (SELECT DEC(SUM(QTY),7,0) as TOTAL,
PART FROM INVDETL
GROUP BY PART)
SELECT INVMAST.PART, DESC, PRICE, COST, TOTAL FROM INVMAST
LEFT JOIN TEMP ON INVMAST.PART = TEMP.PART)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER
This statement creates an MQT named INVTOTALS. This query contains the
part number, description, price, and cost from the inventory master file joined
with the sum of the quantity column for each part. TOTAL is the name assigned to
the total quantity. This statement uses WITH to define a temporary table object
before resolving the main select. This temporary table, named TEMP, contains
only a list of parts from the INVDETL file and the total of the QTY column for
each part. Using WITH frequently simplifies the code needed later in the main
select. Following the main select are four clauses that further define the MQT.
- DATA INITIALLY DEFERRED indicates that the MQT is not loaded with data
at the time of creation. Use DATA INITIALLY IMMEDIATE to load it with data right away if
necessary.
- REFRESH DEFERRED indicates that the user may choose to refresh the table at
any time by issuing the REFRESH TABLE mqt-name statement. This clause is not
optional.
- ENABLE QUERY OPTIMIZATION indicates that the SQL engine may choose to use
this MQT to satisfy other SQL statements issued against the underlying table(s).
To deactivate this feature, use DISBALE QUERY OPTIMIZATION instead.
- MAINTAINED BY USER indicates that the user is responsible for loading the
correct data into the MQT using INSERT, UPDATE, DELETE, and REFRESH TABLE
statements. This is not optional.
Managing Materialized Queries
The data within an MQT is static, meaning that once
data is loaded into it, it will not change regardless of what happens to the
underlying tables. Programmers may choose to manually update the MQT with
INSERT, UPDATE, and DELETE statements, or more likely, they can repopulate the
table with current data using the REFRESH TABLE statement such as the one shown
below.
REFRESH TABLE INVTOTALS
QAQQINI File
To enable the use of MQTs within the SQL optimizer,
the QAQQINI file must be updated, as the default settings do not allow the use
of Materialized Queries during the optimization process. These settings are
needed:
MATERIALIZED_QUERY_TABLE_USAGE = *ALL
MATERIALIZED_QUERY_TABLE_REFRESH_AGE = *ANY
Want to Know More?
Materialized Queries are a
powerful new tool for improving SQL performance within DB2 for i5/OS. This
article just touches the tip of the iceberg. For more information on MQTs, check
out IBM's white paper "Creating
and using materialized query tables (MQT) in IBM DB2 for i5/OS" or contact
DMC Consulting about its new "SQL:
Data Definition Language" class. This newly created class explores the details
of creating DB2 for i5/OS databases through SQL. Contact DMC by email at
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
or by phone at
419.535.2900 to either sign up for a class in the open schedule or schedule a
private class for your own company. Find out more about DMC Consulting, a
Premier IBM Business Partner, at the company's Web site. And check out DMC's
offerings in the MC
Showcase Buyer's Guide.
Kevin Forsythe has
worked on IBM midrange systems for more than 20 years. With many years of
experience in programming, analysis, consulting, and instruction, he is uniquely
skilled at making difficult material more easily understood. As the lead
instructor for DMC
Consulting's AS/Credentials training classes, Kevin is
responsible for developing up-to-date courseware as well as providing
instruction on a wide range of topics. This comprehensive background includes
practical application, education, and research and provides a perspective that
allows him to help others steer their way through the technical maze that
surrounds them.
Kevin speaks regularly at
conferences and user group meetings, volunteers as a Subject Matter Expert for
COMMON, has written numerous articles on a variety of iSeries topics, and
authored the bestselling book SQL for eServer i5 and
iSeries.
|
