I do a lot of consulting a legacy shops, and most
of the time it is SQL performance consulting. When you are
looking at the performance of a statement, it helps to know what
logical files, (indexes), are available to the query optimizer
that can be used for statement implementation. The problem is
that most legacy shops have very obscure naming conventions for
their fields, so divining the intent of the index can be quite
difficult. Most of the time, these legacy shops have column
text descriptions of their fields that are more human readable
and understandable, and since I do not like the output of DSPFFD,
and that requires having a green screen up, I created the index
listing spreadsheet so I could get a nice printed report of the
indexes available for the resolution of my queries along with
the column text explanation of the field.
The spreadsheet is implemented using ADO, ODBC
and the VBA Macro language in Excel. When you open the
spreadsheet, you should see the first tab with a table like the
following displayed:
AS/400 Table and Index Lister
Library
HD1100PD
Table Name
ARPMBCH
Data Source
MYAS400
User ID
HARNER
Password
SECRET
What you do is enter the name of the library
into the Library field, the table name you want information on
into the Table Name field, identify a valid ODBC data source,
(created via the ODBC Data Sources applet in the control panel),
and enter your user ID and Password. Next, you can press either
the List Indexes or List Columns. The following text describes
the code that is executed for each of the buttons.
List Columns
This button is linked to a macro called
GetTableInfo. The macro creates a connection to your AS/400 via
the data source name that you specified and then uses the
OpenSchema method of the connection object to return the
adSchemaColumns resultset for that identified table. The macro
then reads each record in the result set and formats a pretty
spreadsheet of the columns in your table including the table
text. The resulting spreadsheet it suitable for framing!
List Indexes
This button is lined to a macro called
GetIndexInfo. The macro creates a connection to the AS/400 and
then gets a recordset of all of the columns in the target
table. This recordset is then disconnected from the AS/400 to
allow for programmatic "adjustment" on the client computer.
Next, the macro uses the OpenSchema method of the connection
object to return a list of indexes, (logical files suitable for
SQL query processing), and for each entry prints a pretty report
of the indexes. During the printing of an individual column of
index information, the macro uses the Filter property of the
disconnected columns recordset in order to look up the defined
column text for the column it is printing. Becasue this is a
disconnected recordset, this lookup involves no additional
communications with the AS/400 and is therefore quite fast.
Click Here
to view more information on disconnected recordsets.
