Server Studio - Advanced DBMS Tools for IBM Informix Servers

Object Explorer

Object Explorer is the integrated command center for all Server Studio™ Win32 tools and serves as a convenient central point for managing all connected database servers. It offers an easy-to-use interactive, function-rich system management interface that provides in-depth IBM Informix database catalog navigation by displaying and interpreting objects in the catalog, enabling you to quickly understand the structure of the entire database. Object Explorer displays the properties of database objects and lets you manage them with ease.

Object Explorer provides several different views:

Physical View — is organized by physical object types such as PROCEDURES, TABLES, VIEWS, TRIGGERS, UDTs, DATABLADES. Table objects display attached triggers for fast access to the trigger’s code.

Properties View — displays tables and views structure, table indexes, and SQL syntax for routines, triggers, tables, views and UDTs. Table columns' datatypes can be explored down to basic types using Datatypes Explorer tab. SQL templates can also be generated for selected tables and views. SQL templates are very useful when you have to write CREATE, SELECT, INSERT or UPDATE SQL statements based on the given table structure and you do not want to retype column names or data types. Generated templates can easily be pasted into the SQL Editor. Back and Forward operations allow easy navigation between recently analyzed properties of objects.

SQL Editor

SQL Editor is a powerful full-featured, color-coded editor, customized specifically to support Informix SPL and SQL languages. It allows you to write, edit, analyze and execute SQL and SPL statements and is able to display multiple servers' result sets in grid-style tab folders. SQL Editor provides complete syntax checking and supports nested objects cross-referencing. It enables you to apply SPL code templates, to execute individual code statements and provides standard block text operations such as comment/uncomment and indent/outdent. Another important and useful feature of the SQL Editor is its ability to estimate the cost of SQL statements execution. This capability enables you to tune SQL statements performance without the need to execute them on the server. Multiple SQL Editor sessions can be initiated with several databases using distinct user logins. Long running queries can be interrupted by the user at any time.

LOAD / UNLOAD — SQL Editor provides support for LOAD and UNLOAD statements, which allows to import and export a table data. It implements extended syntax, which allows advanced data import and export features, such as ability to export column header, specify error log files and other options. Load and Unload Wizards allows to visually build statements and paste them into SQL Editor script.

Syntax Checking — SQL Editor provides complete syntax checking of code scripts without requiring their actual execution on the server. The whole script or just the highlighted portion can be checked for syntax errors. During the checking process, if an error is found, its position is highlighted in the source code to assist you in locating and fixing it fast.

SPL Templates — For developers new to SPL language, or for those of you who do not work with stored procedures on a daily basis, SPL Templates window allows you to preview the entire syntax of the command and paste either the entire syntax or any portion of it directly into SQL Editor. This quick reference feature will help you write error-free code and will save you a lot of precious development time.

Nested Objects Cross-Referencing — A very helpful feature of SQL Editor is it's ability to directly launch another instance of SQL Editor window for the object referenced in the highlighted source. For example, it allows you to look up the syntax of a view, trigger or nested procedure, that the code currently being edited invokes. This will help you to quickly analyze the entire logic of the your application.

Table Editor

Table Editor allows you to visually create new database tables or edit existing ones. With it you can create and edit all table attributes such as columns, datatypes, table and index fragmentation, referential integrity constraints, primary and foreign keys. It also allows you to create or edit table indexes.

Dynamic CREATE and ALTER — A very helpful feature of the Table Editor is it's ability to dynamically generate SQL CREATE and ALTER statements, as the user performs modifications to the table or index structure in a visual designer. All modifications are immediately applied to the database when the user executes "Update in Database" command or saved into a script file for later use.

Foreign Key Wizard — helps you create Foreign Keys, by visually linking corresponding columns of two tables. It substantially simplifies the task of managing Referential Integrity constraints by automatically taking into consideration existing primary key and column datatype definitions.

Data Editor

Data Editor enables you to interactively view and edit data in database tables and views using a "spreadsheet-style" grid with resizable and movable columns. Data can be visually filtered, sorted and exported into a variety of file formats including ASCII, Excel, SQL, etc. Data Editor supports editing data of any datatype including ROW TYPES, UDTs and COLLECTIONS.

Cell Editor — Build-in Cell Editor allows to view, create and modify data of complex datatypes using a convenient hierarchical tree view. You can use Data Editor to instantly create test data sets or to analyze and modify data during active SPL Debugger sessions. Multiple instances of Data Editor window can launched simultaneously to compare data in several tables and views.

Permissions Editor

Permissions Editor is an easy to use visual permission editor that hides the complexities of managing database security. It enables you to add new or edit the existing Users and Roles to a database, grant or revoke permissions for Users, Roles and databases objects, as well as assign Users to Roles and Roles to databases objects. Permissions Editor's flexibility makes it equally easy to manage permissions for a group of databases objects, Users or Roles as it is for a single one. Editing an existing database object, such as a stored procedure or a view, requires first dropping it, then re-creating it anew, which results in a loss of previously defined permissions for that object. Recovering permissions after each object modification is a tedious and an error-prone job. Permission Editor solves this problem by automatically recovering and applying all prior permissions to any object after it has been modified.

SPL Debugger

SPL Debugger is the only interactive debugger for Informix database servers' stored procedures and triggers available on the market and contains powerful capabilities usually found only in high level programming languages. It enables you to execute stored procedures and triggers line-by-line, to step into nested procedures or fired triggers, to set breakpoints, to watch and to modify values of procedures' variables and calling arguments, break infinite loops, etc. Simultaneous debugging sessions can be initiated from any workstation on the network. SPL Debugger can be invoked from any client application or development tool, such as Visual Basic, C++, Informix-4GL, etc. and can "hook" into a stored procedure or trigger already executing on the server, enabling you to identify the cause of an infinite loop or to pinpoint the slowest portion of the code. SPL Debugger supports watching and editing all types of variables, including UDT, OPAQUE and COLLECTION datatypes. Typical uses for SPL Debugger are:

Verification that a particular stored procedure or trigger was actually executed as a part of application logic and that correct set of parameters was passed.

Analysis of a complex SPL code execution logic, by means of executing it line-by-line and monitoring values of variables.

Interactive editing of variables' value so r tables data to force correct execution of the logic when the logical error is found during debugging session, without the need to interrupt the debugging process.

Verification that a stored procedure operates with correct data, by examining values in data tables and views that the procedure’s logic depends on, when execution has been paused on a breakpoint.

Breaking of an infinite loop during execution of a stored procedure and proceeding with its execution in a step mode, to analyze conditions that lead to the loop's occurrence.

Reference Analyzer

Reference Analyzer is a powerful impact and reverse analysis tool, which enables you to determine the effect that the changes in one database object will have on other server-side application objects. It performs powerful searches for all references to a specific database object across one or multiple databases. Reference Analyzer returns all stored procedures, triggers and views that contain references to the selected object of interest and identifies each line of source code where such references were found. You can view and edit any of the found object's references by clicking on it's associated line of source code, which will automatically launch in the SQL Editor. Typical uses for the Reference Analyzer are:

Determining, prior to making any changes to a table, which stored procedures, triggers and views will have to be modified to reflect the change in that table's structure.

Finding all references to the use of a given stored procedure, trigger or view across one or multiple databases.

Determining the cause of wrong values in a data table after your application is executed. Reference Analyzer can assist you in finding quickly which stored procedures and triggers perform UPDATE, INSERT or DELETE operations on that table, thus allowing you to focus on the collection of database objects that might be the source of wrong data values.

Difference Analyzer

Difference Analyzer is a powerful change management tool that allows you to perform comparative syntax analysis of stored procedures, triggers, views or any script file, regardless of whether they are located on the same or on different servers. You can easily compare individual database objects, files or text in an open SQL Editor.

Difference Analyzer employs color coding, auto aligning and synchronous scrolling of the "old" and "new" sources to make it easy for you to visually compare the differences in the source code. It is seamlessly integrated into Server Studio, enabling you to call it from any other tool (such as Object Explorer), and to switch immediately to editing mode once the difference in the code has been found.

Dependencies Analyzer

Dependencies Analyzer enables you to examine all complex source and data dependencies of any selected database object with a single click of the mouse. The generated dependency tree is created to the required levels of nesting and contains all database objects (such as stored procedures, triggers, tables and views) that are dependant on the selected object of interest. Such dependency tree gives you complete picture of the entire application logic associated with the database object being examined. This capability is especially useful when analyzing legacy code, or code written by other developers. Dependencies Analyzer is also very helpful in documenting server-side application code.

For example, you run a stored procedure and it produces an unexpected or incorrect result. By running a dependencies analysis on this procedure, you can immediately find out that your procedure inserts some data into the table which fires a long forgotten trigger, which in turn executes yet another procedure that is the actual culprit of your data corruption. Trying to resolve this type of a problem by conventional means might have taken you hours of tedious debugging work, however, with the help of the Dependencies Analyzer you can accomplish it in minutes.

Execution Plan Analyzer

Execution Plan Analyzer graphically displays complete IBM Informix optimizer execution plan for a given SQL statement or executed stored procedure. It allows you to visually identify bottlenecks and tune the performance of your SQL application. You can set up a custom highlighting criteria based on Estimated Cost of an SQL statement to bring attention to potentially problematic segments of code.

Execution Plan Analyzer is seamlessly integrated with the SQL Editor and you can configure it to automatically run performance analysis for every statement executed from the SQL Editor. Execution Plan Analyzer makes it very easy to run execution plan analysis on any statement or a script and it enables you to try different tuning options and compare produced performance results. You can also open and view off-line explain plan files on the server, using a convenient tree-view visual presentation.

Server Studio™ Win32