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.