Non Stop dbam Improving management of Non Stop

  • Slides: 38
Download presentation
Non. Stop™ dba/m® Improving management of Non. Stop SQL databases Copying and Use Restrictions:

Non. Stop™ dba/m® Improving management of Non. Stop 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 Non. Stop dba/m® 09/04/2002 Page 1

 • what is Non. Stop dba/m® ? • using the object tree Presentation

• what is Non. Stop dba/m® ? • using the object tree Presentation Outline • managing SQL tables • managing SQL programs • automating SQL partitioning • monitoring your SQL disks • using your calendar-based batch scheduler • analyzing your SQL queries • the latest in Non. Stop dba/m® © 2002 Non. Stop dba/m® 09/04/2002 2

 • text background: Genus Software, Inc. © 2002 • Close partnership on multiple

• 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 Non. Stop dba/m® 09/04/2002 3

background: Non. Stop dba/m® © 2002 • Introduced in 1996 (v 3. 0) •

background: Non. Stop dba/m® © 2002 • Introduced in 1996 (v 3. 0) • Compaq product since 1999 (v 4. 0 and v 5. 0) • Non. Stop dba/m® v 6. 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 Non. Stop dba/m® to manage SQL/MP in ZLE environment Non. Stop dba/m® 09/04/2002 4

what is Non. Stop dba/m® ? © 2002 Non. Stop dba/m® 09/04/2002 Page 5

what is Non. Stop dba/m® ? © 2002 Non. Stop dba/m® 09/04/2002 Page 5

Non. Stop dba/m® Product Goals © 2002 • To increase efficiency / save time

Non. Stop 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 Non. Stop dba/m® 09/04/2002 6

Non. Stop dba/m® Architecture • Client/Server application using TCP/IP to communicate to the server

Non. Stop 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 Non. Stop Himalaya servers using PATHSEND © 2002 Compaq Non. Stop® SQL/MP Pathway processes Compaq Non. Stop® Himalaya server Client PC Compaq Non. Stop® dba/m® client process Non. Stop dba/m® 09/04/2002 7

Workbenches within Non. Stop dba/m® database manager © 2002 • SQL object management •

Workbenches within Non. Stop 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 Non. Stop dba/m® 09/04/2002 8

Non. Stop dba/m® Main Window – Database administration made easy © 2002 Non. Stop

Non. Stop dba/m® Main Window – Database administration made easy © 2002 Non. Stop dba/m® 09/04/2002 9

using the object tree © 2002 Non. Stop dba/m® 09/04/2002 Page 10

using the object tree © 2002 Non. Stop dba/m® 09/04/2002 Page 10

SQL object management - the Object Tree © 2002 • explores nodes in an

SQL object management - the Object Tree © 2002 • explores nodes in an Expand network to create a tree of all SQL objects • 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) Non. Stop dba/m® 09/04/2002 11

SQL Object Management (Object Tree) © 2002 Non. Stop dba/m® 09/04/2002 12

SQL Object Management (Object Tree) © 2002 Non. Stop dba/m® 09/04/2002 12

SQL Object Management (Object Tree) © 2002 Non. Stop dba/m® 09/04/2002 13

SQL Object Management (Object Tree) © 2002 Non. Stop dba/m® 09/04/2002 13

managing SQL tables © 2002 Non. Stop dba/m® 09/04/2002 Page 14

managing SQL tables © 2002 Non. Stop dba/m® 09/04/2002 Page 14

SQL Object Management (Table) • Create, rename or drop a table • Assign a

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 Non. Stop dba/m® 09/04/2002 15

SQL Object Management (Table) Cont. • Big Partition support • constructs SQL command •

SQL Object Management (Table) Cont. • Big Partition support • constructs SQL command • no need to remember syntax • confirm before execution © 2002 Non. Stop dba/m® 09/04/2002 16

managing SQL programs © 2002 Non. Stop dba/m® 09/04/2002 Page 17

managing SQL programs © 2002 Non. Stop dba/m® 09/04/2002 Page 17

SQL Object Management (Program) • Alter/Drop Program • Copy/Move • Compile Old • Compile

SQL Object Management (Program) • Alter/Drop Program • Copy/Move • Compile Old • Compile New • Rename © 2002 Non. Stop dba/m® 09/04/2002 18

SQL Object Management (Program) Cont. • easy SQL program compilation • generates SQLCOMP text

SQL Object Management (Program) Cont. • easy SQL program compilation • generates SQLCOMP text with various options as selected by user © 2002 Non. Stop dba/m® 09/04/2002 19

automating SQL partitioning © 2002 Non. Stop dba/m® 09/04/2002 Page 20

automating SQL partitioning © 2002 Non. Stop dba/m® 09/04/2002 Page 20

Automated Partitioning • examines tables or indexes to determine number of rows in each

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 Non. Stop dba/m® 09/04/2002 21

Automated Partitioning (Cont. ) • graphical comparison of existing and proposed distribution in bar

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 Non. Stop dba/m® 09/04/2002 22

monitoring your SQL disks © 2002 Non. Stop dba/m® 09/04/2002 Page 23

monitoring your SQL disks © 2002 Non. Stop dba/m® 09/04/2002 Page 23

Monitor Disk / SQL Object Usage • monitor free/used space on disks, tables, indexes,

Monitor Disk / SQL Object Usage • monitor free/used space on disks, tables, indexes, partitions • monitor continuously or by start/stop • set alarms © 2002 Non. Stop dba/m® 09/04/2002 24

Monitor Disk /SQL Object Usage (View Alarms) • Alarm triggers if object being monitored

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 Non. Stop dba/m® 09/04/2002 25

using your calendar-based batch scheduler © 2002 Non. Stop dba/m® 09/04/2002 Page 26

using your calendar-based batch scheduler © 2002 Non. Stop dba/m® 09/04/2002 Page 26

Batch Processing (Create & Submit) • create and submit batch jobs to be executed

Batch Processing (Create & Submit) • create and submit batch jobs to be executed by SQLCI • event based – Time – Alarm • periodic execution © 2002 Non. Stop dba/m® 09/04/2002 27

Batch Processing (View) • view batch jobs • delete / stop batch • replicate

Batch Processing (View) • view batch jobs • delete / stop batch • replicate / resubmit © 2002 Non. Stop dba/m® 09/04/2002 28

analyzing your SQL queries © 2002 Non. Stop dba/m® 09/04/2002 Page 29

analyzing your SQL queries © 2002 Non. Stop dba/m® 09/04/2002 Page 29

Query Analysis (Adhoc SQLCI Query) • Explain Plans for entered SQL query • change

Query Analysis (Adhoc SQLCI Query) • Explain Plans for entered SQL query • change control options and compare explain plans and costing information © 2002 Non. Stop dba/m® 09/04/2002 30

Query Analysis (SQL Program Query) • Explain Output for all the queries in SQL

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 Non. Stop dba/m® 09/04/2002 31

Command History • logs all commands issued by users in dba/m® session • sort

Command History • logs all commands issued by users in dba/m® session • sort based on object name, User. ID and timestamp • select previously entered command for re-execution © 2002 Non. Stop dba/m® 09/04/2002 32

Direct SQLCI • direct interface to SQL • type and execute as in SQLCI

Direct SQLCI • direct interface to SQL • type and execute as in SQLCI © 2002 Non. Stop dba/m® 09/04/2002 33

Online help • Non. Stop dba/m® database manager comes with online help that indexes

Online help • Non. Stop dba/m® database manager comes with online help that indexes and has search capabilities • click the F 1 key and get contextsensitive help • get detailed help on SQL syntax © 2002 Non. Stop dba/m® 09/04/2002 34

the latest in Non. Stop dba/m® © 2002 Non. Stop dba/m® 09/04/2002 Page 35

the latest in Non. Stop dba/m® © 2002 Non. Stop dba/m® 09/04/2002 Page 35

enhancements and new features incorporated in Non. Stop dba/m® v 6. 0 © 2002

enhancements and new features incorporated in Non. Stop dba/m® v 6. 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 Non. Stop dba/m® 09/04/2002 36

Non. Stop dba/m® release v 6. 0 update • SUT-based trial V 6. 0

Non. Stop dba/m® release v 6. 0 update • SUT-based trial V 6. 0 is shipping (G 06. 17) • CD version is being shipped (31 AUG 2002) • new licensing mechanism • upgrade utility provided © 2002 Non. Stop dba/m® 09/04/2002 37

 • . . on Non. Stop dba/m® For more information. . . http:

• . . on Non. Stop dba/m® For more information. . . http: //nonstop. compaq. com/view. as p? PAGE=Database http: //www. genussoft. com • . . on the HP Non. Stop database roadmap send email to: kevin. [email protected] com [email protected] com © 2002 Non. Stop dba/m® 09/04/2002 38