1 / 23

Comprehensive DBA Tools for Data Modeling, Design, and Management

Explore essential DBA tools focused on data modeling and design. Key functionalities include creating physical data models, verifying their accuracy, and generating standard DDL automatically. Emphasis on change management through database alteration and comparison tools is key for maintaining integrity and efficiency. Other crucial tools include auditing, performance monitoring, and ETL for data warehousing. Learn to interface various tools and evaluate third-party vendors systematically to enhance your database management capabilities.

hamilton-mack
Télécharger la présentation

Comprehensive DBA Tools for Data Modeling, Design, and Management

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. Chapter 22 DBA Tools

  2. Data Modeling & Design Tools • Look for • Support standard tasks (E/R, normalization, etc.) • Create a physical data model • Provide an expert system to verify accuracy of physical data model • Cross-reference logical to physical

  3. Modeling & Design Cont. • Generate standard DDL automatically • Interface with application development tools and repository products

  4. Change Management Tools • Most used is database alteration and comparison tool • ALTER tool should • Maintain tables easily • Retain or reapply all dependent objects, authorizations and data is DROP • Navigate from object to object

  5. Change tools cont. • Provide GUI modification with before and after definitions • Batch requested changes into a work list for executing • Analyze changes to be sure DDL rules aren’t violated • Provide capability to monitor changes

  6. Comparison Tools • Should perform comparisons on: • One live database to another live database • A live database to a DDL script file • One DDL script file to another DDL script file

  7. Object Migration Tools • Migrates database objects from one database server or subsystem to another

  8. Referential Integrity Tools • These should • Analyze data for both system and user-managed referential integrity constraint • Execute faster than the DBMS-provided integrity checking utility • Enable additional types of RI to be supported

  9. Auditing Tools • Examination of a practice to determine correctness • Provide capability to read the database logs and report activity • Produce a set of prepackaged reports • Must report who makes each change • provide capability to interface with other auditing features • Provide standard and requested reports

  10. Catalog Query & Analysis • Create syntactically correct DDL statements • Modify any updatable statistical columns • Create syntactically correct authorization/security statements • Perform “drop/analysis” on DROP

  11. Query continued • Provide hierarchic listing of database objects • CREATE and DROP database objects • Operate directly on system catalog to reduce contention

  12. Security Tools • GRANT and REVOKE operations

  13. Table Editors • SQL DELETE, INSERT and UPDATE • Database utilities such as LOAD or IMPORT

  14. Performance Tools • In background mode as a batch job to report performance statistics • Foreground mode as an online monitor for application execution • Sampling the DB kernel and user address spaces for reporting • Capture DB trace information

  15. Performance cont. • Capacity planning device for statistical information about application • After-the-fact analysis tool on a workstation for application performance

  16. DB performance tools • Collect statistics for tables and indexes • Read underlying data sets to capture statistics • Set thresholds for automatic scheduling • Provide series of canned reports

  17. Application Tools • Analyzes SQL for paths • Issue warnings for SQL commands such as group by, etc. • Suggests alternative SQL solutions • Extends rules for expert systems • Analyzes the subsystems requests • Stores multiple versions of EXPLAIN

  18. DB Utilities • DBMS utilities and 3rd party vendor utilities • 3rd party utilities should • Not subvert integrity of data • Provide same features as native utility • Doesn’t subvert standard DB features • Provides twice the execution time • Correct deficiencies of standard utility

  19. Warehousing Tools • Extract, Transform and Load (ETL) • Replication tools • Propagation tools

  20. Query & Reporting Tools • DBMS usually has simple query tool • Use 3rd party query and reporting toold

  21. Programming Tools • Tests SQL statements • Perform predictive performance • Explains SQL statements for editing • Generate complete code • Provide enhanced for procedural SQL • Interface with 4GLs

  22. Other Tools • Checkpoint/restart tools • Testing tools • Debugging tools • Space management tools • Online manuals • Compression tools

  23. Vendor Evaluation • Look at check list 601-604 • Should be comprehensive evaluation of potential 3rd party vendors and DBMS vendors

More Related