Video Demos

AGS S1 E1 HD – GUI SQL Editor

This presentation runs through the basics of the SQL Editor interface and explains how to use the following features:

• Code completion: select a column name from a drop-down for the table in context.Using the command history window.
• Checking SQL syntax of highlighted statements.
• Executing specific statements in a complex SQL script.
• Preserving multiple result sets in spreadsheet-style grids.
• Export wizard to save results or use LOAD and UNLOAD statements directly.
• Using the Graphical Query Designer.

AGS S1 E2 HD – Graphical query analyzer

This presentation shows how to use the integrated Execution Plan Analyzer. This tool provides a visual representation of how the database server will execute a SQL statement and gives valuable insight into how you can start tuning the performance of SQL queries.

AGS S1 E3 HD – Stored Procedure Debugger

Have you ever wasted valuable time trying to figure out why a stored procedure doesn’t work? AGS provides a debugger comparable to that of Microsoft Office Visual Basic. It allows the user to execute stored procedures line-by-line and step into nested procedures. You can also watch the values of procedure arguments and variables and set execution breakpoints and break infinite loops.

AGS S1 E4 HD – Schema Comparison

Before a software release, it’s great to be able to remind developers what schema changes they will need to apply by comparing test and live databases – and check they haven’t forgotten anything! There are command line tools to do this, but they are awkward to use compared with AGS. This presentation explains how to use this powerful tool.

AGS S1 E5 HD – Object Explorer

Have you ever tried to calculate the total size of a database when you have several in the same instance? This is one of many mysteries unlocked in AGS. Just click on an object at any level, and all the information that you are likely to need is immediately available, together with the ability to create/modify/drop objects (depending on permissions). At the instance level, view profile data, logical logs, configuration (comparing startup, current and default values) and the message log: no more obscure “onstat” commands! Want to know which tables have the most extents? Click on the table node and sort by the extents column. This presentation also includes an introduction to the session explorer and explains how to examine user level activity and control user sessions.

AGS S1 E6 HD – Table Reorganization

If a table has an excessive number of extents, right click on it and choose Reorganize. After choosing a better next extent size, you can choose to recreate the table in-place using “alter index to cluster” or by exporting the data to a file, optionally using the Informix High Performance Loader (HPL). In all cases, everything is done for you, and all dependent objects such as views are faithfully recreated afterwards. The biggest time saving is if you choose HPL, which is extremely quick and avoids long transaction errors for large tables: HPL normally has a steep learning curve, and its complexity is hidden by AGS.

AGS S1 E7 HD – SQL Statement Capture

Sometimes the single biggest reason for slow performance is the most obvious: missing indexes. This will cause unnecessary sequential scanning, and the best way of working out what additional indexes are needed is to record a sample of SQL statements over a few days. AGS Sentinel (a service that runs in background) can easily be programmed to capture SQL statements involving sequential scans with a high optimizer cost, for subsequent production of a league table of the worst and/or most frequent such queries. The developers can then decide whether to alter the statement, add optimizer directives or add an index.

AGS S1 E8 HD – Monitors, Alerts, Reports

Sentinel can monitor a wide variety of Informix and operating system metrics, such as system load and available space. If a metric goes outside of a specified range of values, an alert can be sent using a built-in email client. At the end of the month, graphs can be produced showing time series of monitored metrics. Sentinel provides far better Informix support than general purpose system monitoring products, and is generally cheaper.

AGS S1 E9 HD – Miscellaneous

And finally, some other things that we just had to mention…

AGS S2 E1 HD – Connections / Projects / Tabs / Variables

• Storage Replication Script generator enables recreation of dbspaces and chunks (with correct sizes and parameters) on another server.
• SQL Trace administration and statement analysis for IDS 11 upwards are now in Server Studio, so you no longer need Open Admin Tool for this.
• User session properties for IDS 11+ provides a list of historical SQL statements executed by the session as well as the last executed SQL statement.
• New SQL Reports provide information about SQL statements captured in SQL Trace cache of IDS 11+ servers.

AGS S2 E2 HD – External Tables / Space Optimization / Data Masking

• External tables can be created and edited using a graphical editor.
• Space Optimization feature allows Compress, Repack, Shrink and De-Fragment of tables or indexes for IDS 11.50.FC4+.
• Data Masking can obscure sensitive data for non-production environments. Masked data can either be exported to text files, or data can be updated in place after cloning the database. Methods provided include random substitution, lookup from database objects and files, shuffling, number and date variance.
• Enhanced character encoding support for LOAD/UNLOAD SQL commands and Data Import/Export wizards (including Data Masking).

AGS S2 E3 HD – Columns / Mapped Users / Storage Pool

• The columns folder under a database node in the Object Explorer eases searching for a column name across an entire database or for dependency analysis.
• Mapped Users can be created and edited using the Server Studio graphical editors (requires Informix 11.7+).
• Automatic Storage Provisioning administration is provided for IDS 11.7+ servers.

AGS S2 E4 HD – Storage Generation / SQL Trace

• Storage Replication Script generator enables recreation of dbspaces and chunks (with correct sizes and parameters) on another server.
• SQL Trace administration and statement analysis for IDS 11 upwards are now in Server Studio, so you no longer need Open Admin Tool for this.
• User session properties for IDS 11+ provides a list of historical SQL statements executed by the session as well as the last executed SQL statement.
• New SQL Reports provide information about SQL statements captured in SQL Trace cache of IDS 11+ servers.

AGS S2 E5 HD – Server Properties / Logs / Schema Audit

• Views of server properties were extended to include additional information, such as total & available OS memory and number of host CPUs.
• Enhanced IDS Logs Viewer for IDS 11+ servers displays Online Message Log, Administrative Commands Log and On-Bar logs.
• Recovery Logs folder in the Object Explorer tree allows viewing and managing logical and physical logs, checkpoints and recovery policies.
• Schema Audit jobs allow monitoring for changes on multiple Informix servers and issue notifications when differences are found. Email alerts can optionally contain an HTML difference report along with a synchronization script to roll back the changes. Schema snapshots can be taken for record keeping or manual reviewing.

AGS S2 E6 HD – Monitoring Blackouts / Command Line SQL Runner

• Maintenance Blackout can be scheduled for planned Informix server down time to avoid receiving false alerts from Sentinel monitors and jobs. Sentinel administrators can be notified whenever a blackout period starts and ends.
• Command-line SQL Runner allows using Server Studio with an external scheduler and to automate application deployment tasks without “dbaccess” installed.