MTI_INFO now includes member name

As part of the latest round of Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11, the MTI_INFO Table function was enhanced with the addition of the member or Table partition name the MTI was created over. I have to admit I was surprised this was not included when the Table function was first introduced.

The new column is called TABLE_PARTITION, it is the third column in the results, and is a VARCHAR of a maximum of 128 characters.

If I just wanted to view all of the MTIs in my partition I would use the following statement:

01 SELECT TABLE_SCHEMA,TABLE_NAME,FILE_NAME, 02 TABLE_PARTITION 03 FROM TABLE(QSYS2.MTI_INFO()) 04 ORDER BY 1,2 05 LIMIT 5

Line 1: I am selecting the library (schema), long (SQL) table name, and the short (system) file name.

Line 2: This is the new column.

Line 4: I want the results in library and table name order.

Line 5: In this example I only want five results.

The results are:

TABLE_ FILE TABLE_ SCHEMA TABLE_NAME _NAME PARTITION -------- --------------- -------- --------- PRODLIB1 CUSTOMER_MASTER FCUST FCUST PRODLIB1 ITEM_MASTER FITEM FITEM PRODLIB1 ORDER_DETAIL FORDDTL FORDDTL PRODLIB1 ORDER_HEADER FORDHDR FORDHDR PRODLIB1 TAX_RATE FTAX FTAX

Let me see if there are any MTIs suggested where the TABLE_PARTITION column is not the same as the table or files names:

01 SELECT TABLE_SCHEMA,TABLE_NAME,FILE_NAME, 02 TABLE_PARTITION 03 FROM TABLE(QSYS2.MTI_INFO()) 04 WHERE FILE_NAME <> TABLE_PARTITION 05 AND TABLE_NAME <> TABLE_PARTITION 06 ORDER BY 1,2

Lines 1 - 3: Same as the previous statement.

Lines 4 and 5: I only want to select results where the file (system) and table (SQL) names are not the same as the member or partition name.

I was returned just two results:

TABLE_ FILE TABLE_ SCHEMA TABLE_NAME _NAME PARTITION -------- --------------- -------- --------- PRODLIB2 BT_COMMANDS BTCMD RCMD PRODLIB2 BT_DEPENDENCIES BTDP RDEP

In my experience, most of the time the TABLE_PARTITION is going to be the same as the FILE_NAME column. Despite this I consider this an useful addition to MTI_INFO.

 

You can learn more about the MTI_INFO SQL Table function from the IBM website here.

 

This article was written for IBM i 7.5 TR5 and 7.4 TR11.