Leveraging sysmaster to Graphically Monitor IDS

1. Informix User Forum 2005Moving Forward With Informix Leveraging sysmaster to Graphically Monitor IDS Peter Richardson Rocket Software Atlanta, Georgia December 8-9, 2005

2. Agenda • Graphically leveraging sysmaster information • Demonstration of Visionary-powered SMI Application • How it works - review of the sysmaster database • Configuration • Database structure • Performance • How it works – review of Rocket Visionary

3. ‘Sysmaster’ – An introduction • A ‘special’ database that contains tables related to the performance, configuration and structure of your Informix server • Collectively, these tables provide the Informix ‘System Monitoring Interface’, known as SMI • Lester Knutsen covers the sysmaster database in detail: • See his session at this conference • See his web site for additional SMI documentation • We’ll review a few key tables but will primarily focus on leveraging this resource to graphically monitor your Informix servers

4. What is Visionary? • Provides a means of rapidly building and deploying database visualization solutions • Product heritage: • From a Berkeley research project (1995) • To an Illustra prototype (1996) • To an Informix product (1996) • To an IBM product (2001) • To a Rocket Software product (2002) • Fundamental value propositions: • Exceptionally good data visualization capabilities • Rapid Application Development model – an ‘app in a day’ product • Informix heritage – first class support for Informix databases

5. Visionary-Powered Performance Monitor • Provides comprehensive analysis and monitoring of Informix servers across all platforms • Reports performance and configuration information for all databases running within each designated server • Displays the broad range of information available within the sysmaster database • Implemented as a real-time, interactive graphical dashboard • Available as a desktop application (Windows or Java) or as a 3-tier web-based application

6. Information Categories • Performance • Engine configuration & profile information • Virtual processor & log statistics • Summary of user sessions and current locks • Summary of databases and associated dbspaces • List of current SQL statements (incl. SQL text) • Configuration • Lists all configuration settings, along with documentation for each • Databases • DB summary with drilldown to underlying details (tables, locks, etc.) • DBSpaces • Dbspace summary with drilldown to supplementary details • Lists allocations, devices, configuration and performance data

7. Sample Performance Screens

8. Server Configuration Review

9. Sample Database Screens

10. Sample DBSpace Screens

11. Three deployment options: • Client/Server Windows application. • Client/Server Java application. • Web-based (3-tier) Java applet. Product Architecture

12. Demonstration Rocket Software, Inc. Confidential

13. How it Works • The system monitoring interface (SMI) consists of tables and pseudo-tables that the database server automatically maintains • Although the SMI tables appear to the user as tables, the database server constructs the tables in memory, on demand, based on information in shared memory at that instant • When you query an SMI table, the database server reads information from these shared-memory structures • Since the database server continually updates the data in shared memory, the information that SMI provides lets you examine the current state of your Informix server • The SMI tables provide information about the following topics:

14. Documented SMI Tables/Views Source: http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.adref.doc/adrefmst193.htm#sii02rsmi1011428

15. Reading the Server Configuration Information • The sysconfig table describes the effective, original, and default values of the server’s configuration parameters • 150+ rows in IDS 10 • Provides read-only access Source: http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.adref.doc/adrefmst199.htm

16. DBSpace Summary • Uses sysdbspaces and syschunks select d.dbsnum, d.name dbspace, d.owner, sum(c.chksize) SumPageSize, sum(c.chksize) - sum(nfree) SumPagesUsed, sum(nfree) SumPagesFree, round ((sum(nfree)) / (sum(c.chksize)) * 100, 2) PercentFree from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum group by 1, 2, 3 order by 1;

17. Looking at the locks… • Provides locks by database, table, row (where applicable) and lock owner (user session) • Combine with syssessions to determine user information Source: http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.adref.doc/adrefmst206.htm

18. User Sessions • Session information is available via thesyssessions view • Many of the performance-related tables refer to this table using the OwnerID FK to the sid PK Source: http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.adref.doc/adrefmst211.htm

19. Additional Details • Available online at: • http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.adref.doc/adrefmst185.htm • Look at the SQL in the Visionary SMI application • Email me for a free copy of the SMI application • I’ll also provide you with the project sources • prichardson@rocketsoftware.com

20. Rocket Visionary Overview Rocket Software, Inc. Confidential

21. Rocket Visionary • Provides a means of rapidly building and deploying database visualization solutions, putting enterprise data into the hands of non-technical executives and decision makers • The product is well seasoned: • From a Berkeley research project (1995) • To an Illustra prototype (1996) • To an Informix product (1996) • To an IBM product (2001) • To a Rocket Software product (2002)

22. For a major airline

23. For a wireless equipment manufacturer

24. For IBM DB2 tools customers

25. For a telco operations center

26. Rocket Developments • Rocket Visionary V3 • Java-based World Server for 3-tier content distribution • Over 20 three-dimensional controls • Team development support • Rocket Visionary V4 • Java Applet for rich client rendering (unsigned for auto-download) • Linear regression and numerous other analysis functions • Full National Language Support (NLS)

27. The Business Need • Enterprises generate an abundance of key operational data • Traditional forms and reports are inadequate – users need rich visualizations to accelerate understanding • Reports should be dynamic, with on-demand drilldown, tailoring content to the type of user (analyst, manager, executive)

28. Rocket Visionary Solutions • Ideally suited to the development and deployment of interactive, graphical database applications: • Executive dashboards • Information portals • ‘What if’-style analytical applications • Data mining applications • Key benefits: • Rapid development model - ‘app in a day’ onsite prototyping • Intuitive, browser-like interface – no end user training required • Flexible programming model to meet wide array of industry-specific needs

29. Visual Concepts – Graphical Motifs • Contains over 20 ‘stock’ visual layouts • Line and bar charts • Geographic maps • Pie charts • Horizon charts • Hierarchical charts • Tabular layouts • Flexible rendering • Motifs can be nested indefinitely (e.g. pie charts upon bars within maps etc.) • Customized graphical ‘templates’ – build your own chart types

30. Native Support for Geospatial Data • Efficiently visualize operational data by geography • On-demand drilldown to geographic ‘trouble areas’ • Supports both the SDE and Spatial DataBlade

31. Rocket Visionary Architecture

32. Summary • Powerful database visualizations • ‘At a glance’ knowledge • On demand drill-down • Interactive real-time visualizations • Data insight, not just access • Rapid authoring model • Rapidly build or customize reporting solutions • No coding required • Enterprise Deployment • Java Servlet-based ‘World Server’ • Application or browser-based end user delivery • ActiveX or Java Applet Viewer • Concurrent JDBC access to multiple database types & instances

33. Visionary Demonstrations 1) Sample Visionary Applications 2) Visionary Developer Studio Rocket Software, Inc. Confidential

34. Informix User Forum 2005Moving Forward With Informix Leveraging sysmaster to Graphically Monitor IDS Peter Richardson prichardson@rocketsoftware.com Atlanta, Georgia December 8-9, 2005