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.