Transcript NonStop™ Himalaya™ Application Infrastructure

NonStop™ dba/m®
Improving management of
NonStop SQL databases
Copying and Use Restrictions:
Material under this presentation is the Intellectual Property of HP
Corporation and Genus Software. Any use of the this material, in part or
whole, except in context of dba/m, without written permission from HP
and Genus is prohibited.
© 2002
NonStop dba/m® 09/04/2002
Page 1
• what is NonStop dba/m® ?
• using the object tree
• managing SQL tables
Presentation
Outline
• managing SQL programs
• automating SQL partitioning
• monitoring your SQL disks
• using your calendar-based batch
scheduler
• analyzing your SQL queries
• the latest in NonStop dba/m®
© 2002
NonStop dba/m® 09/04/2002
Page 2
• text
background:
Genus Software,
Inc.
© 2002
•Close partnership on multiple
database tools
•Long history working with NSK
•Compaq Solutions Alliance
partner
•Compaq Professional Services
partner
•OEM relationship w/Compaq
•Development centers in
Cupertino, CA & in India
•Onsite technical staffing
capability
•ZLE-trained software
development teams
NonStop dba/m® 09/04/2002
Page 3
background:
NonStop dba/m®
© 2002
• Introduced in 1996 (v3.0)
• Compaq product since 1999
(v4.0 and v5.0)
• NonStop dba/m® v6.0 being
introduced with hp partnership
 new features and functionality
introduced
 state of the art technology for
Front End
 familiar Windows look and feel
and experience for Users
 positions NonStop dba/m® to
manage SQL/MP in ZLE
environment
NonStop dba/m® 09/04/2002
Page 4
what is
NonStop dba/m®
?
© 2002
NonStop dba/m® 09/04/2002
Page 5
NonStop
dba/m® Product
Goals
© 2002
•To increase efficiency / save
time for operations and
database administration staff,
while reducing downtime due to
errors
•Easy-to-learn graphical
interface (GUI)
•Automated calculation of
partition boundaries
•Batch jobs for SQL
management tasks
•Run them when you want
•Repeat jobs w/o having to
retype commands
•Query analysis pinpoints
performance problems and
checks the effect of a schema
change
•Easy disks / SQL objects space
management
NonStop dba/m® 09/04/2002
Page 6
NonStop dba/m® Architecture
• Client/Server application
using TCP/IP to
communicate to the
server
•
Windows®–based
GUI
running on PCs with
Windows 95, Windows
98, Windows NT®, or
Windows 2000
operating systems
• Server components run
on NonStop Himalaya
servers using
PATHSEND
© 2002
Compaq
NonStop®
SQL/MP
Pathway processes
Compaq NonStop® Himalaya server
Client PC
Compaq NonStop® dba/m® client
process
NonStop dba/m® 09/04/2002
Page 7
Workbenches within
NonStop dba/m®
database manager
© 2002
•SQL object management
•Partition management and design
•Space monitoring and disk analysis
•Query analysis and tuning
•Batch management
•Historical command analysis
•Direct SQLCI
NonStop dba/m® 09/04/2002
Page 8
NonStop dba/m® Main Window –
Database administration made easy
© 2002
NonStop dba/m® 09/04/2002
Page 9
using the object
tree
© 2002
NonStop dba/m® 09/04/2002
Page 10
• explores nodes in an Expand
network to create a tree of all SQL
objects
SQL object
management
- the Object Tree
© 2002
• can limit exploration to particular
objects by using custom trees
• multiple custom trees supported
• display context-sensitive Menu
with right click
• drill down to show properties of
SQL objects (catalogs, tables,
views, indexes, partitions,
programs with SQL statements)
NonStop dba/m® 09/04/2002
Page 11
SQL Object Management (Object Tree)
© 2002
NonStop dba/m® 09/04/2002
Page 12
SQL Object Management (Object Tree)
© 2002
NonStop dba/m® 09/04/2002
Page 13
managing
SQL tables
© 2002
NonStop dba/m® 09/04/2002
Page 14
SQL Object Management (Table)
• Create, rename or drop
a table
• Assign a primary key
• Alter table attributes
• (Re-)Partition a table
• Display and/or update
table statistics
• Invoke table definition
• Add, change or delete
table constraints,
constraint comments
• Add, change or delete
help text and comments
• Save DDL to file for
later re-use
© 2002
NonStop dba/m® 09/04/2002
Page 15
SQL Object Management (Table) Cont.
• Big Partition support
• constructs SQL
command
• no need to
remember syntax
• confirm before
execution
© 2002
NonStop dba/m® 09/04/2002
Page 16
managing
SQL programs
© 2002
NonStop dba/m® 09/04/2002
Page 17
SQL Object Management (Program)
• Alter/Drop
Program
• Copy/Move
• Compile Old
• Compile New
• Rename
© 2002
NonStop dba/m® 09/04/2002
Page 18
SQL Object Management (Program) Cont.
• easy SQL
program
compilation
• generates
SQLCOMP text
with various
options as
selected by user
© 2002
NonStop dba/m® 09/04/2002
Page 19
automating
SQL partitioning
© 2002
NonStop dba/m® 09/04/2002
Page 20
Automated Partitioning
• examines tables
or indexes to
determine
number of rows
in each partition
• previews impact
of proposed
changes
• generates
primary key
values to evenly
distribute data
• generates SQL
statements to
create partitions
© 2002
NonStop dba/m® 09/04/2002
Page 21
Automated Partitioning (Cont.)
• graphical
comparison of
existing and
proposed
distribution in bar
chart form
• feature supported
for Index
partitions as well
• Partonly option
supported
© 2002
NonStop dba/m® 09/04/2002
Page 22
monitoring your
SQL disks
© 2002
NonStop dba/m® 09/04/2002
Page 23
Monitor Disk / SQL Object Usage
• monitor
free/used
space on
disks,
tables,
indexes,
partitions
• monitor
continuously
or by
start/stop
• set alarms
© 2002
NonStop dba/m® 09/04/2002
Page 24
Monitor Disk /SQL Object Usage (View
Alarms)
• Alarm triggers if
object being
monitored exceeds
threshold value
• Visual alert as bell
icon turns red from
yellow
• Helps preventing
any potential
problem
• Helps deciding
follow-up action
© 2002
NonStop dba/m® 09/04/2002
Page 25
using your
calendar-based
batch scheduler
© 2002
NonStop dba/m® 09/04/2002
Page 26
Batch Processing (Create & Submit)
• create and
submit
batch jobs
to be
executed
by SQLCI
• event
based
– Time
– Alarm
• periodic
execution
© 2002
NonStop dba/m® 09/04/2002
Page 27
Batch Processing (View)
• view batch jobs
• delete / stop
batch
• replicate /
resubmit
© 2002
NonStop dba/m® 09/04/2002
Page 28
analyzing your
SQL queries
© 2002
NonStop dba/m® 09/04/2002
Page 29
Query Analysis (Adhoc SQLCI Query)
• Explain Plans
for entered SQL
query
• change control
options and
compare explain
plans and
costing
information
© 2002
NonStop dba/m® 09/04/2002
Page 30
Query Analysis (SQL Program Query)
• Explain Output for
all the queries in
SQL compiled
program
• must compile under
dba/m® control to
see explain plans
• each compilation
generates new
version of explain
plans
• Explain Plans in
graphical tree
format
© 2002
NonStop dba/m® 09/04/2002
Page 31
Command History
• logs all commands
issued by users in
dba/m® session
• sort based on
object name,
UserID and
timestamp
• select previously
entered command
for re-execution
© 2002
NonStop dba/m® 09/04/2002
Page 32
Direct SQLCI
• direct interface
to SQL
• type and execute
as in SQLCI
© 2002
NonStop dba/m® 09/04/2002
Page 33
Online help
• NonStop
dba/m®
database
manager comes
with online help
that indexes and
has search
capabilities
• click the F1 key
and get contextsensitive help
• get detailed help
on SQL syntax
© 2002
NonStop dba/m® 09/04/2002
Page 34
the latest in
NonStop dba/m®
© 2002
NonStop dba/m® 09/04/2002
Page 35
enhancements and new
features incorporated in
NonStop dba/m® v6.0
© 2002
• modern front end
• enhanced object tree
• improved partition
management
• calendar based (periodic)
batch scheduling
• event based batch scheduling
• volume pools
• shared access option
• graphical explain plans
• improved statistics
• FUP reload interface
• SMF interface
• smart monitoring and alert
management
NonStop dba/m® 09/04/2002
Page 36
NonStop dba/m®
release v6.0
update
• SUT-based trial V6.0 is
shipping (G06.17)
• CD version is being
shipped (31AUG2002)
• new licensing mechanism
• upgrade utility provided
© 2002
NonStop dba/m® 09/04/2002
Page 37
• . . on NonStop dba/m®
For more
information . . .
http://nonstop.compaq.com/view.asp
?PAGE=Database
http://www.genussoft.com
• . . on the HP NonStop
database roadmap
send email to:
[email protected]
[email protected]
© 2002
NonStop dba/m® 09/04/2002
Page 38