Transcript Table Editing - Options

®
IBM Software Group
OPTIM Data Studio – 3.1.1
Jon Sayles, IBM/Rational
November, 2012
© 2012 IBM Corporation
IBM Trademarks and Copyrights

© Copyright IBM Corporation 2007,2008, 2009, 2010, 2011, 2012. All rights
reserved.

The information contained in these materials is provided for informational purposes only,
and is provided AS IS without warranty of any kind, express or implied. IBM shall not be
responsible for any damages arising out of the use of, or otherwise related to, these
materials. Nothing contained in these materials is intended to, nor shall have the effect
of, creating any warranties or representations from IBM or its suppliers or licensors, or
altering the terms and conditions of the applicable license agreement governing the use
of IBM software. References in these materials to IBM products, programs, or services do
not imply that they will be available in all countries in which IBM operates.

This information is based on current IBM product plans and strategy, which are subject to
change by IBM without notice. Product release dates and/or capabilities referenced in
these materials may change at any time at IBM’s sole discretion based on market
opportunities or other factors, and are not intended to be a commitment to future product
or feature availability in any way.

IBM, the IBM logo, the on-demand business logo, Rational, the Rational logo, and other
IBM Rational products and services are trademarks or registered trademarks of the
International Business Machines Corporation, in the United States, other countries or
both. Other company, product, or service names may be trademarks or service marks of
others.
@ Copyright 2012 - IBM
2
What is Data Studio 3.1.1?
DB2 development software
 Free Data Tooling software
 Integrates into RDz
Becomes a Perspective + Views in the RDz workbench)
 Provides:
Data Analysis Tools
SQL Programming Options
SQL Performance Analysis & Tuning Features
DB2 Table Editing Options
DBA Functionality
@ Copyright 2012 - IBM
3
Data Analysis Tools
 Data Source Explorer – like RDz, but shows additional "physical" DB2 objects
 Administrative Explorer – provides filtered lists of DB2 object meta-data. List includes
Row Count, Tablespace/Database/Partitioning information, etc.
 Context Menu – Overview diagram, access to list of user Privileges, host of DBA tools
@ Copyright 2012 - IBM
4
Additional Meta-Data
 Discover the Database, Table Space and
Storage Group for a Table
 If authorized you can update Statistics for the Tablespace
 Launches a series of DSNU… utilities
 The table properties will reflect statistics
@ Copyright 2012 - IBM
5
Additional Meta-Data – Value Distribution in Table Columns
 Same as selecting a column, and
asking for: Data > Sample Contents
(but a lot nicer visual representation)
@ Copyright 2012 - IBM
6
SQL Development Tools
 Content Assist and Context menu options (like RDz Data tooling)
 SPUFI-like run options
 Additional SQL functionality for:
 Visual Explain
 OPTIM SQL Query tuning (requires InfoSphere z/OS components)
 SQL Results view – same as RDz Data Perspective tooling
 Can contract the tabs for more
SQL coding real estate
@ Copyright 2012 - IBM
7
Context Menu
 And SQL Results
 Note – additional options
available in Data Studio
from the Context Menu
@ Copyright 2012 - IBM
8
Visual Explain
 Multiple ways to invoke
 Requires DB2 "Plan Tables"
 Produces:
 Illustration of Optimizer access path
 Cost estimate of running query
 Print/save graph, or Cost estimate
@ Copyright 2012 - IBM
9
Visual Explain of Complex SQL Statement
 Requires deep (DBA-level) understanding of DB2
access path theory and (current) physical database
design
@ Copyright 2012 - IBM
10
SQL Performance Tuning (SQL Query Advisor)
 Requires IBM InfoSphere – installed on z/OS
 Multiple
steps/stages
1. Code the query
 Run
2. Select tuning
options
3. Select the
tuning
(Access Path)
reports
@ Copyright 2012 - IBM
`
11
SQL Performance Tuning – Review the Query Advisor Recommendations
4. Review the various tuning and DB2 utility recommendations
@ Copyright 2012 - IBM
12
SQL Performance Tuning – Review the Access Plan Graph
6. Visual Explain – but with some additional analysis
options (separate Query Block diagrams, etc.)
@ Copyright 2012 - IBM
13
SQL Performance Tuning – Explore each access plan Query Block
7. Each QB revealed, including: CPU Millisecond, I/O (VSAM & Paging) and Service Unit costs
Note – Can save
and compare
versions of the
same SQL for cost
@ Copyright 2012
- IBM
analysis
14
SQL Performance Tuning – Review the Query Tuner Report Details
8. Recommended actions for
utilities and Query re-coding
@ Copyright 2012 - IBM
15
SQL Performance Tuning – Capture the SQL Query – for Re-testing
8. Can select copy and paste the automatically tuned query – for iterative (subsequent) analysis
runs
@ Copyright 2012 - IBM
16
SQL Performance Tuning – Capture the SQL Query – for Re-testing
9 There are a number of DBA-level reports available for study (scroll down for reports)
@ Copyright 2012 - IBM
17
Table Editing – Options
From the Data Source Explorer:
 Can edit multiple tables – but no table subsetting
 Can also access (run utilities & reports against): Databases/Storage Groups/Tablespaces
@ Copyright 2012 - IBM
18
Table Editing – Subsetting and other options
From the Administration Explorer
 Right-click an object type
 Use the Database Catalog Filter to expose specific objects for editing
(and other) actions
@ Copyright 2012 - IBM
19
Table Editing - Options
Some options same as RDz Data Tools – but others extend the functionality:
 Browse rows (in tabular format)
 Generate pureQuery Code
 Manage DB2 privileges (GRANT/REVOKE)
 Alter table
 Generates DDL – even
for options that require
dropping a table
 Runs script – or can run
as Job
@ Copyright 2012 - IBM
20
Table Editing – Subsetting
Start by editing the table data
Select: Filter the data
@ Copyright 2012 - IBM
21
Table Editing – Subsetting
 Select columns to show
 Specify Row Selection conditions
 Update Rows to Return
Click OK
@ Copyright 2012 - IBM
22
DBA Functionality
Execute RUNSTATS against
the Tablespace
Alter a table
 Add a column
 Add a Primary or
Foreign key
 Modify table
GRANT
(access permissions)
@ Copyright 2012 - IBM
23
What can you "not" do with OPTIM




Can't edit in hex
Can't copy/paste rows
Doesn't allow multiple table-subsetting "Templates"
All of the above can be done through the IBM PD Tools "File Manager"
product
@ Copyright 2012 - IBM
24