1 / 27

Automating DBA Tasks with OpenAdmin Tool

Automating DBA Tasks with OpenAdmin Tool. Erika Von Bargen Session: D09 IBM Tuesday 5/17 10:30am. Agenda. Intro How OAT works Automating Storage Tasks Automating Statistics Automating Fragmentation Automating Workload Distribution

kera
Download Presentation

Automating DBA Tasks with OpenAdmin Tool

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Automating DBA Tasks with OpenAdmin Tool Erika Von Bargen Session: D09 IBM Tuesday 5/17 10:30am

  2. Agenda • Intro • How OAT works • Automating Storage Tasks • Automating Statistics • Automating Fragmentation • Automating Workload Distribution • Automating Backups • Automating Log File Management Automating DBA Tasks with OpenAdmin Tool

  3. Intro • Why use OAT? • Free • Remote administration • Easy-to-use, graphical interface • Support for all of the newest Informix 11.70 features • Lowers the learning curve for new and existing Informix features • Customizable with plug-ins • What is OAT? • Open source, web-based administration tool for Informix • Monitor and administer one or more Informix server instances • Automated installers available for Windows, Linux, and Mac OS • Independent of Informix installation • Interface available in multiple languages A Graphical Exploration of 11.70 OpenAdmin Tool for Informix 5/16/2011 3

  4. How OAT Works OAT builds off of the database administration capabilities in IDS

  5. How OAT Works OAT accesses the database server using only SQL statements OAT utilizes three key aspects of the IDS server System catalog tables in the sysmaster database to retrieve performance and monitoring data SQL Admin API to make configuration changes to the data server Database Schedulerin thesysadmin databaseto run tasks and gather data through sensors SQL Statement Returned Data

  6. Database Scheduler Self-managing technology to simplify database administration Provides the ability to schedule SQL, stored procedures or a UDR Tasks Execute a specific job at a specific time or interval Why use them? Periodically check and/or analyze collected data to ensure the data server is operating efficiently Perform administration or maintenance tasks Sensors Specialized tasks designed to collection information Easy to add and configure Why use them? Collect information and store it in the sysadmin database for later analysis

  7. Automating Storage Tasks: Space Expansion Storage Provisioning feature (Informix 11.70) Automatic expansion of spaces to avoid “out-of-space” errors Two modes: Reactive: Chunks are extended and/or created as needed. Proactive: Configure the database server to proactively expand spaces when free space falls below a threshold Idea behind proactive expansion of space: Use Database Scheduler to monitor and expand space as necessary

  8. Automating Storage Tasks: Space Expansion In OAT, configure schedule from the Task Scheduler > Task Details page Configure threshold from the Space Administration > Storage > Storage Pool page

  9. Automating Storage Tasks: Space Optimization Idea: Save DBA time and effort by using the DB Scheduler to automatically compress, repack, shrink, and defragment tables based on policies DBA configures: Schedule when optimization operations (compress, repack, shrink, defrag) can run. Policies that define which actions are taken Compress tables/fragments that exceed a specified number of rows Repack tables/fragments that exceed a specified % of discontiguous storage space Shrink tables/fragments that exceed a specified % of free space Defragment tables that exceed a specified number of extents

  10. Automating Storage Tasks: Space Optimization In OAT, manage this on the Space Administration > Storage > Tables and Indexes page…

  11. Automating Storage Tasks: Space Optimization … and monitor the results.

  12. Automating Statistics Why Automate Update Statistics (AUS)? Optimizer needs accurate statistics to make an optimal decisions But… New tables & indexes are often forgotten Many do not run update statistics encountering sub-optimal performance Running it too often wastes resources Which tables to run and when to run them is a difficult decision And… What statistics and distributions need to be updated? Not easy to understand Change periodically Differs for each system

  13. Automating Statistics Idea: Save DBA time and effort by using the DB Scheduler to automate the collection of statistical and distribution data DBA configures: Schedule Policies when statistics should be updated

  14. Automating Statistics

  15. Automating Fragmentation field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field field • Interval Fragmentation Strategy • Time-cyclic data management (roll-on, roll-off) • Attach and detach online without requiring exclusive lock and access to the table • Attach new intervals automatically • Automatically kicks off background process to recollect statistics Dec 08 field field field field field field field Jan Mar Feb Apr May 09 enables storing dataover time A Graphical Exploration of 11.70 5/16/2011 15

  16. Interval Fragmentation Strategy In OAT, use the Create Table wizard to create tables with interval (date-range) fragmentation: Use the Schema Manager to view fragments created automatically by the database server: A Graphical Exploration of 11.70 5/16/2011 16

  17. Automating Workload Management Connection Manager: a daemon program which accepts a client connection request and then re-routes that connection to one of the best fit nodes in and Informix cluster or grid

  18. Automating Workload Management Connection Manager makes decisions on where to re-route client based on Service Level Agreement (SLA) Informix gathers information from each server in a cluster or grid and automatically connects the client application to the server that has the least amount of activity. Turn on the Quality of Data feature, and the Connection Manager will also take failure and latency into consideration when deciding where to re-direct the client.

  19. Automating Log File Management Log File Rotation/Removal Programmatically manage the Informix message log files without DBA intervention Online Message Log Onbar Activity Log DBA sets up schedule and max number of logs to keep Database server automatically (via the Database Scheduler) rotates and delete online message log files

  20. Automating Log File Management

  21. Automating Log File Management

  22. Automating Backups Automate ontape storage space backups with OAT and the Database Scheduler. Configure and schedule level 0, level 1, and level 2 ontape to run automatically. Also use to OAT to Monitor the status of backups Run on-demand ontape backups

  23. Automating Backups

  24. Automating Backups

  25. Resources Automating DBA Tasks with OpenAdmin Tool • Informix Free Product Downloads • https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd • Download • OAT • Informix Virtual Appliance: includes Informix 11.70 and OAT • OpenAdmin Tool Website • www.openadmintool.org • Download link, product information, demos and more • Demos of OAT and 11.70 features: • www.youtube.com/OpenAdminToolChannel • OAT Forum • www.iiug.org/forums/oat 5/17/2011 25

  26. Questions ?!? Automating DBA Tasks with OpenAdmin Tool

  27. Automating DBA Tasks with OpenAdmin Tool Session: D09 Erika Von Bargen vonbarg@us.ibm.com

More Related