Informix IDS Administration with the New Server Studio

Informix IDS Administration with the New Server Studio 4. 0 By Lester Knutsen My experience with the beta of Server Studio and the new Informix database monitoring options that will be available in its next release.

Server Studio™ JE Sentinel Performance Monitoring, Optimization and Autonomics for IBM IDS w e N § Monitor IDS Performance 4 Real-time user-defined monitors created from a matrix of over 90 IDS performance parameters 4 Custom multi-level Alerts 4 Track SQL scripts from user sessions § Time-Series Performance Data Analysis 4 Collect IDS historical performance matrix in the built-in time-series SQL database repository 4 Perform comprehensive correlation analysis on captured IDS performance parameters § Load Simulation for Tuning IDS 4 Create real life load stress conditions scenarios 4 Run hundreds or even thousands of concurrent load-test virtual “user” sessions § IDS Event Management 4 Autonomic response to IDS events by execution of: • User-defined administration scripts • OS commands • SQL scripts • IDS native utilities Monitor and proactively manage your entire IBM IDS infrastructure from a unified graphical console

Server Studio™ JE Sentinel IDS Real-Time Performance and Event Monitoring § Define customized performance monitors from over 90 available IDS parameters § For each IDS instance, real-time monitors can be created at the level of: 4 Server 4 Chunk 4 Dbspace 4 Table 4 Index 4 Session § Assign multi-level threshold Alerts to each parameter in a monitor

Server Studio™ JE Sentinel IDS Event Alerts and Autonomic Response § Customized multi-level Alerts § Centralized Viewer for Event Alerts § Notification via email, pager, cell phone, etc. § Autonomic response to alert events by: 4 User-defined administration scripts 4 OS commands 4 SQL scripts 4 Stored procedures 4 IDS native utilities § Automation of regular maintenance tasks via scheduled customdefined jobs.

Server Studio™ JE Sentinel User-Defined Jobs § Ability to define OS commands and SQL scripts as jobs. § Highly flexible job scheduler. § Ability to map existing administration scripts and Informix utilities as jobs. § Ability to execute jobs as a response to user-defined alert events. § All output produced by jobs is logged into built-in data repository for future analysis.

Server Studio™ JE Sentinel IDS Performance Simulation under Real Life Load Stresses § To optimize your IDS performance, create real life stress conditions scenarios by incorporating into your load tests application-specific: 4 SQL scripts 4 Parametric queries 4 Data § Run hundreds or even thousands of concurrent load-test virtual “user” sessions, using either the same or varying scenario scripts at random or programmed time intervals § Automatically measures statistical user sessions’ response times § Initiate the monitors that will collect IDS performance parameters data matrix for the load-test scenario and record it into the built-in time-series repository for subsequent analysis

Server Studio™ JE Sentinel Time-Series Analysis of IDS Performance Data § For all IDS instances under monitoring, the time-series repository stores in its own built-in SQL database: 4 performance parameters data 4 Event Alerts history 4 SQL scripts captured from user sessions 4 Result sets captured from autonomic execution of administration scripts or IDS native utilities § Correlation analysis can be easily performed over any available time interval to spot performance bottlenecks and anomalies § Run SQL queries against the timeseries repository to export complex performance parameters data sets for analysis in external programs

Server Studio™ JE Sentinel SQL Capture § Capture SQL statements for: 4 selected user sessions 4 specified user 4 statements originating from a specified host § Review captured SQL in real-time § Store captured SQL in the built-in data repository for future analysis. § Easy-to-use viewer for captured SQL.

Server Studio™ JE DBMS Administration § Manage server’s mode: 4 ON 4 Quiescent 4 Off § Examine all of the ONCONFIG parameters at a glance. § Monitor and manage user sessions: 4 number of reads, writes and locks, placed by the session 4 last SQL statement executed by the session 4 locks’ details and I/O activity statistics 4 terminate problematic user sessions § Manage users’ permissions and roles § Update statistics on selected table, group of tables, or entire database

Server Studio™ JE Storage Space Management § Identify easily which storage spaces are getting filled and may require additional chunks. Examine in detail: 4 Db. Spaces 4 BLOBSpaces 4 Chunks 4 Percentage of used and free space 4 List of database objects residing in a given Dbspace and chunk 4 Other storage spaces related information

Server Studio™ JE Storage Space Management § Wizards assist DBA to create or remove Dbspaces and Chunks remotely. § For each database table: 4 Examine number and details of the table’s extents 4 Examine data size and allocation between multiple dbspaces (fragmentation)

Server Studio™ JE Tables Reorganization Wizard § Table Reorganization Wizard helps to: 4 Optimize physical table data allocation 4 Move table data between different db. Spaces 4 Defragment tables to reclaim wasted physical storage space contained in table extents 4 Improve performance of SQL queries § Table Reorganization Wizard allows to choose between two most commonly used table reorganization methods: 4 “in-place” – which applies ALTER TABLE statements to achieve required physical reorganization of data. 4 “drop and recreate” – which exports table data to a temporary text file, drops the table, then re-creates the table and re-imports table data. All table storage parameters can be altered during the execution of the “drop and recreate” method.

Server Studio™ JE Tables Reorganization Wizard

Server Studio™ JE Schema Management § Object Explorer in the Server Studio™ JE enables easy navigation and analysis of the database schema and detail examination of any database object’s properties. § Server Studio™ JE provides a rich collection of graphical editors (e. g. Table Editor, SQL Editor, Permissions Manager) and Wizards to create and manage various database objects. § Schema analytical tools in Server Studio™ JE simplify: 4 Tracking of database objects’ dependencies. 4 Determining differences between two databases schemas. 4 Management of databases schemas and sub- schemas (including copy and export).

Server Studio™ JE SQL Editor

Server Studio™ JE Table Editor

Server Studio™ JE Permissions Manager

Server Studio™ JE Dependencies Analyzer

Server Studio™ JE Schema Manager

Server Studio™ JE Data Management § Ability to view and edit data in a graphical grid. § Data Import and Export Wizard. § Test Data Generator

Where to get Server Studio™ JE § Get your own copy of Server Studio™ JE and see for yourself what you’ve been missing. § Available at: 4 www-3. ibm. com/software/data/informix/tools/serverstudio 4 www. Server. Studio. com.