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.


  • 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.

Advanced DBA Certification Guide and Reference for DB2 UDB v8 for Linux, Unix and Windows
Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows
ISBN: 0130463884
EAN: 2147483647
Year: 2003
Pages: 121

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net