EXPLAIN Output

An example of EXPLAIN output on DB2 ESE Solaris is shown below:

SQL Statement:
  select acct_type
  from dntnck.account_xref

Estimated Cost        = 3
Estimated Cardinality = 1000

Distributed Subquery #1
|  #Columns = 1
Return Data to Application
|  #Columns = 1

Distributed Subquery #1:
Server: HOST390  (DB2/390 7.1)
Subquery SQL Statement:

     SELECT A0."ACCT_TYPE"
     FROM "TST1TXP"."ACCOUNT_XREF" A0

Nicknames Referenced:
   DNTNCK.ACCOUNT_XREF  ID = 165  Base = TST1TXP.ACCOUNT_XREF

The output shows the Federated Database translation of the nickname into the target table residing on the OS/390. To see the access path from the HOST390 server, you must use the Visual Explain for the OS/390.

A few differences between the Visual Explain implemented on UNIX (i.e., Solaris) and on OS/390 are listed below.

On UNIX:

• It is part of the Control Center.

• It automatically creates the Plan Tables as needed.

• It provides timeron values for each operation (join, lookup, sort, etc.), which gives an indication of how costly certain operations are.

On OS/390:

• It is a stand-alone product that can be downloaded from the IBM Web site.

• It requires the Plan Tables to be available under your TSO user ID.

• It provides an easy way to check whether statistics information for tables and indexes is current.

Under the current federated database system, both UNIX and OS/390 EXPLAIN will be required to get a complete picture of query performance. On UNIX, we will expect to see a very simple plan consisting ideally of a fetch from a remote query. To see which indexes and join paths, etc., are used, we will need to look at the DB2 for OS/390 EXPLAIN.