DB2 for zOS V8 DB2 Management Clients Package by ezw15872

VIEWS: 0 PAGES: 54

									                                     ®




               IBM Software Group


DB2 for z/OS V8:
DB2 Management Clients Package

June Lundstrom
Certified Consulting IT Specialist
DB2 for z/OS and DB2 for LUW
         IBM Software Group | DB2 Information Management Software



DB2 Management Clients Package
  Includes:
      Visual Explain
      DB2 Connect
      DB2 Administration Server for z/OS
      DB2 UDB for z/OS Control Center Enablement
  Tools provided on CD-ROM
  DB2 UDB for z/OS Control Center Enablement is shipped on a tape or
   cartridge
      DB2 Management Clients Package Program Directory has installation
       directions
  www.ibm.com/software/data/db2/os390/cc390 for Control Center
   customization information




                                                                           2
          IBM Software Group | DB2 Information Management Software



Database Administration Server (DAS)
   DAS provides a general mechanism for running z/OS level functions to
    support the IBM Universal Database GUI Tools such as Control Center,
    Command Center and Replication Center.

   DAS runs as a USS (Unix System Services) daemon

   DAS provides the following functions:
     Building and creating JCL jobs (Control Center Version 8
      supports creating and storing JCL jobs for most functions
      including executing DB2 utilities or cloning a subsystem)
     Reading and writing data sets
     Querying operating system catalog information
     Executing shell scripts in z/OS UNIX
     Issuing MVS system commands through an extended console


                                                                           3
           IBM Software Group | DB2 Information Management Software



DB2 for z/OS Control Center Enablement:


    IBM DB2 Control Center provides support to help you manage DB2
     databases on an array of operating systems in your work place

    A set of stored procedures, a user-defined function and a set of batch
     programs must be installed at each DB2 UDB for z/OS subsystem
      Control Center
      Replication Center
    The z/OS Enablement provides these stored procedures, user-defined
     functions and batch programs in the form of an SMP/E installable package
     (with FMID JDB881D).




                                                                                4
            IBM Software Group | DB2 Information Management Software



Control Center 390 Enhancements for V8
   DB2 Control Center V8 provides database administration functionality in one easy to
    use tool with point-and-click navigation capabilities that make it easy to find, view,
    and work with a large number of objects across subsystems.

   Version 8 has a revised navigation tree for DB2 for 2 z/OS(TM) and OS/390(R)
    subsystems with improved object folder ordering and filtering functionality.

   Examples of tasks that can be performed on DB2 objects include
     Create and execute basic data definition (Create, Alter, Drop)
     Create and execute basic data control (Grant, Revoke)
     Create and execute DB2 commands (Display, Start, Stop)
     Execute online utilities (including COPY, REORG, RUNSTATS)
   These tasks can either be executed online or be built as JCL jobs (New for Version
    8) and scheduled for later execution using default or custom skeletons.




                                                                                             5
            IBM Software Group | DB2 Information Management Software



Control Center 390 Enhancements for V8 - continued
  Control Center also supports some common system administrative tasks – V8 only:
    Homogeneous system copy (offline subsystem cloning)
    Displaying and setting of subsystem parameters
    Starting and stopping subsystems
  Command Center, which is integrated into Control Center, allows application
   developers and system administrators to issue SQL queries and host system
   commands (including MVS(TM) console commands

  If you run an online utility such as RUNSTATS on a large number of database
   objects that were the result of a catalog query or multiple object selection,
     Control Center uses a server-side utility scheduler to sort the objects according
        to their expected execution time and executes them in parallel. This allows for
        the overall fastest utility execution whilst retaining control over the maximum
        number of parallel subtasks to be used. The scheduler can also be used in batch
        mode through JCL jobs.

  Create Cloning Session wizard:
    generates and saves all of the JCL jobs needed to homogeneously copy entire
      subsystems. By entering concise information it is possible even for relatively
      inexperienced system administrators to quickly and successfully clone DB2 for
      z/OS and OS/390 subsystems.

                                                                                          6
              IBM Software Group | DB2 Information Management Software



DB2 Objects that can be Managed by Control Center

   Aliases                                       Schemas

   Application objects                           Storage groups
   Buffer pools                                  Synonyms
   Catalog tables                                Table spaces
   Database users
                                                  Tables
   Databases
                                                  Triggers
   Data sets
                                                  Utility objects (data set
   Indexes                                        templates, object lists, and utility
                                                   procedures)
   Locations
                                                  Views
   Schemas




                                                                                          7
        IBM Software Group | DB2 Information Management Software



Extended Support of DB2 (DSN) Commands
   Bind Package

     Bind Plan

     Free Package

     Free Plan

     Rebind Package

     Rebind Plan

     Rebind Trigger Package




                                                                   8
IBM Software Group | DB2 Information Management Software




                                                           9
          IBM Software Group | DB2 Information Management Software


Visual Explain
  Includes:
      Graphical Depictions of Access Plans:
        Relationships between database objects (e.g. tables and
         indexes) and operations (e.g. tablespace scans and sorts) are
         illustrated in the graphs
      Generate customized reports on explainable statements
      View subsystem parameters
      View data from the plan table, the statement table, and the
       function table
      Stat Advisor:
        Provides a report of what statistics are suggested to maximize
         your access path
      Rewritten for V8
        Most recent edition is V1.0.10a as of 8/20/2006 .

                                                                          10
         IBM Software Group | DB2 Information Management Software



Visual Explain V8
 System requirements:
   Windows NT Version 4.0, Windows 2000, or Windows XP
   150 MB of hard disk space
   256 MB of RAM
   Pentinum III equivalent or above
   Either DB2 Connect™ Personal Edition Version 7 (or later), or
    DB2 Client Application Enabler™ (CAE) with a DB2 Connect
    Enterprise Edition Version 7 (or later) gateway
   A TCP/IP connection to a DB2 for z/OS Version 7 or Version 8
    host system
   A java-enabled browser to view HTML files




                                                                    11
           IBM Software Group | DB2 Information Management Software



Visual Explain V8 Enhancements

  More context-sensitive tuning suggestions are provided. You can link to
   these suggestions directly from the graph. You can also link from the graph
   to additional statistics and descriptions for each object or operation that is
   used in the access plan

  Each graph can display multiple query blocks, so that you can view the
   entire access plan in one graph. In previous versions of Visual Explain,
   each graph displayed only one query block. In this version, you still have
   the option to view only one query block at a time

  You can use Visual Explain to catalog and uncatalog databases on your
   local machine (for DB2 on Linux, Unix, and Windows)

  You can use Visual Explain to run a query and view the formatted results

  http://www-306.ibm.com/software/data/db2/zos/osc/ve/


                                                                                    12
            IBM Software Group | DB2 Information Management Software



Visual Explain Operational Environment for DB2 on z/OS
   Visual Explain uses some sample stored procedures:

   DSNACCMD: ServiceSQL uses this stored procedure to generate complete ALT
    BUFFERPOOL statements when collecting service information to send to IBM
    service during problem investigations

   DSNWZP: DSNWZP stored procedure is required to support Browse Subsystems
    Parameters function

   DSN8EXP (See APAR PQ90022): This stored procedure supports the explain with
    stored procedure option. The purpose of explain with stored procedure is to allow
    users that should be able to explain SQL against objects that users do not have
    authority to natively execute queries against

   DSNUTILS: Statistics Advisor generates RUNSTATS statements

   Java Universal Driver (See APAR PQ62695): Installing the Java Universal Driver on
    DB2 for z/OS provides Visual Explain with stored procedures used by Visual Explain.
    Additionally, stored procedure SYSIBM.SQLCAMESSAGE is installed which
    provides error message description for SQL errors in Visual Explain



                                                                                          13
          IBM Software Group | DB2 Information Management Software



How to Interpret Access Path Graph

 Visual Explain V8 displays a much more information in previous
  versions

 Read the online help (ouch!)

 To open context sensitive help for a particular graph node, right
  click the node and select Help. Information for that node is
  displayed.

 If the node indicates an operation, you can find more information,
  including optimization suggestions, by clicking on the link to the
  appropriate operation under the Related Constructs heading.
  (Constructs are groups of nodes that represent a single operation.)




                                                                        14
          IBM Software Group | DB2 Information Management Software



Saving the access path graph in Visual Explain V8

 Visual Explain V8 uses XML to store the graph information

 When a statement is explained, the access path graph is displayed
  on the Access Plan page of the Tune SQL notebook

 On this page, select File -> Save XML File to save the XML file to
  your local workstation

 To retrieve the graph, select File -> Open XML File.
   You can also send the XML file to other people. If they have
     DB2 Visual Explain installed, they will also be able to see the
     graph.




                                                                       15
          IBM Software Group | DB2 Information Management Software



Visual Explain for DB2 for z/OS Control Center Plugin


    Allows Visual Explain for DB2 for z/OS to be launched from DB2
     Control Center V8 for DB2 OS/390 and z/OS subsystems.

    Once the plugin is installed, a menu option, 'DB2 Optimization
     Service Center Plug-insV8', will be visible.

    Select this menu option to launch Visual Explain V8




                                                                      16
          IBM Software Group | DB2 Information Management Software



Visual Explain Control Center Plug-In
 System requirements:
   Windows NT Version 4.0, Windows 2000, or Windows XP
   150 MB of hard disk space
   256 MB of RAM
   Pentinum III equivalent or above
   DB2 UDB Control Center V8
   Visual Explain for DB2 for z/OS V8
   Either DB2 Connect Personal Edition Version 7 (or later), or
    DB2 Client Application Enabler (CAE) with a DB2 Connect
    Enterprise Edition Version 7 (or later) gateway
   A TCP/IP connection to a DB2 for z/OS Version 7 or Version 8
    host system
   A java-enabled browser to view HTML files



                                                                     17
           IBM Software Group | DB2 Information Management Software



Visual Explain – Statistics Analyzer – V8 ONLY
   Use the Tune SQL notebook to determine which statistics need to be collected to
    improve the performance of a query

   In the Visual Explain window, click Plugin --> SQL Tuning Level Plugin and ensure
    that the Statistics Advisor option has a check mark next to it.

   Open the Tune SQL notebook

   On the SQL Text page, enter an SQL statement

   Click Analyze.

   The recommended RUNSTATS statements are displayed on the Statistics Advisor
    page

   Use the RUNSTATS, Explanation, and Conflict Report tabs to learn more about the
    reasons for the recommendations

   Alternatively, you could copy the RUNSTATS statements and run them in a batch
    job.




                                                                                        18
IBM Software Group | DB2 Information Management Software




                                                           19
IBM Software Group | DB2 Information Management Software




                                                           20
IBM Software Group | DB2 Information Management Software




                                                           21
IBM Software Group | DB2 Information Management Software




                                                           22
IBM Software Group | DB2 Information Management Software




                                                           23
IBM Software Group | DB2 Information Management Software




                                                           24
IBM Software Group | DB2 Information Management Software




                                                           25
IBM Software Group | DB2 Information Management Software




                                                           26
IBM Software Group | DB2 Information Management Software




                                                           27
IBM Software Group | DB2 Information Management Software




                                                           28
IBM Software Group | DB2 Information Management Software




                                                           29
IBM Software Group | DB2 Information Management Software




                                                           30
IBM Software Group | DB2 Information Management Software




                                                           31
IBM Software Group | DB2 Information Management Software




                                                           32
IBM Software Group | DB2 Information Management Software




                                                           33
IBM Software Group | DB2 Information Management Software




                                                           34
IBM Software Group | DB2 Information Management Software




                                                           35
IBM Software Group | DB2 Information Management Software




                                                           36
IBM Software Group | DB2 Information Management Software




                                                           37
IBM Software Group | DB2 Information Management Software




                                                           38
IBM Software Group | DB2 Information Management Software




                                                           39
IBM Software Group | DB2 Information Management Software




                                                           40
IBM Software Group | DB2 Information Management Software




                                                           41
IBM Software Group | DB2 Information Management Software




                                                           42
IBM Software Group | DB2 Information Management Software




                                                           43
IBM Software Group | DB2 Information Management Software




                                                           44
IBM Software Group | DB2 Information Management Software




                                                           45
IBM Software Group | DB2 Information Management Software




                                                           46
IBM Software Group | DB2 Information Management Software




                                                           47
IBM Software Group | DB2 Information Management Software




                                                           48
IBM Software Group | DB2 Information Management Software




                                                           49
IBM Software Group | DB2 Information Management Software




                                                           50
IBM Software Group | DB2 Information Management Software




                                                           51
IBM Software Group | DB2 Information Management Software




                                                           52
IBM Software Group | DB2 Information Management Software




                                                           53
IBM Software Group | DB2 Information Management Software




THANK YOU!


                                                           54

								
To top