AGS Server Studio, Open Admin Tool (OAT), and Informix Server Administrator (ISA) are all graphical user interface (GUI) tools that are available to help you monitor and administer IBM Informix Dynamic Server (IDS). In this article, learn more about these tools—how they can be used to access IDS and how you can get started with them. Also, get an introduction to the UI tool DB-Access, which is provided along with the IDS server. As a bonus, learn about new features like ER plug-in and MACH 11 support now available in OAT.
DB-Access utility provides a user interface for typing, executing, and debugging SQL statements. You can execute both data definition language (DDL) and data manipulation language (DML) statements, and obtain and display the results using this tool. In addition, you can use DB-Access to display catalog tables and information schema. In DB-Access, you can either discard the queries executed once you obtain the results, or you can save your queries in a file for future use.
You can start DB-Access in Menu mode and then select options from menus, or you can invoke DB-Access from the command line. You can type the statements directly in the DB-Access text entry screen, or you can use your preferred editor.
To set up a DB-Access environment, perform the following steps:
- Install the database server and set the environment variables.
- Set up the Global Language Support (GLS) locale.
- Bring up the server online.
DB-Access is shipped with Informix. As soon as you install Informix, you will find DB-Access in the Informix installation directory (INFORMIXDIR) /bin. The path variable should be set with $INFORMIXDIR/bin to be able to use the DB-Access utility, where $INFORMIXDIR is the server installation directory.
You can invoke DB-Access functions in several ways:
- Displaying the main menu
- Displaying other menus or options
- Executing a command file
- Using DB-Access interactively in non-menu mode
Let's examine each method.
Displaying the main menu
You can start DB-Access from a command line using the following command, if the path includes $INFORMIXDIR/bin.
This command brings up the main menu for DB-Access, as shown in Figure 1. From here, you can reach other sub-menus and their options, with no database selected and no options automatically activated.
Figure 1. DB-Access main menu
Alternatively you can specify the options with the dbaccess command:
dbaccess [-ansi] [database_name] [-s]
- -ansi generates a warning whenever an Informix extension to ANSI-compliant syntax is encountered.
- -s goes to main menu and displays the information about the current session.
- database_name specifies the name of the current database.
You can select a sub-menu by pressing the Enter (return) key to choose the highlighted option. Or you can use the space bar or the left and right arrow keys to move the highlight, and then press the return key. Or you can type the shortcut letter in uppercase for the name of the option that you want to select.
Displaying other menus or options
You can directly obtain a sub-menu by issuing the following options along with dbaccess command:
dbaccess –c: Starts DB-Access with Connection menu as the top-level menu.
Figure 2. Connection menu
dbaccess –d: Starts DB-Access with Database menu as the top-level menu.
Figure 3. Database menu
dbaccess –q: Starts DB-Access with Query-Language menu as the top-level menu.
Figure 4. Query-Language menu
dbaccess –t: Starts DB-Access with Table menu as the top-level menu, but you must select the database first.
Figure 5. Table menu
Executing a command file
When you invoke DB-Access from the command line, you can specify a database as current and execute a file that contains one or more SQL statements. For example:
$dbaccess db_test sample
In the above line of code, db_test is the database and sample.sql is the file that has the SQL statements.
$dbaccess – test.sql
The command in Listing 1 executes the SQL statements in a file named test.sql. The database is specified in that file.
Listing 1. File test.sql
create database test; create table table_test(col1 int); insert into table_test values (1); insert into table_test values (2); insert into table_test values (3);
The output is shown in Listing 2:
Listing 2. DB-Access output - test.sql
"test.sql" 6 lines, 163 characters [drda_def]: /tmp >dbaccess - test.sql Database created. Table created. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. Database closed. [drda_def]: /tmp >
Using DB-Access interactively in non-menu mode
You can invoke DB-Access in non-menu mode as follows:
dbaccess - -
Listing 3 provides an example:
Listing 3. DB-Access in non-menu mode
[drda_def: /tmp >dbaccess -- > database test; Database selected. > select * from table_test; col1 1 2 3 3 row(s) retrieved. > ^D Database closed. [drda_def]: /tmp >
In Listing 3, DB-Access reads the input from the standard input device, processes the input, and writes the results to the standard output device.
DB-Access options in the main menu
There are six options in the DB-Access main menu:
From these options, let's take a look at the Query-language option and the Database option.
When you issue the dbaccess command, you get the main menu, where the Query-language option is highlighted, as seen in Figure 1. At this stage you have not selected any database. Let's say that you have selected the database test_db. By pressing the Enter key, you enter the Query-language option's sub-menus, shown in Figure 6:
Figure 6. DB-Access Query-Language menu
To run an SQL statement, press Enter. In this menu, you can type the SQL statement and then press the Esc (escape) key. When you press the escape key, the system will take you back to the previous menu, where you can select the Run option. This will execute the statement and displays the results.
Figure 7 shows that a user enters the SQL statement
select * from table_test; from the DB-Access
Figure 7. DB-Access query editor
Figure 8 shows the results of the query:
Figure 8. DB-Access query result
If there are any errors in the SQL statement, you can modify it using the Modify option. You can type your statements using an editor with the help of the Use-editor option. You can save this SQL statement in a file using the Save option. You can also direct the output to a printer, a new file, an existing file, or a pipe using the Output option.
You can select, create, drop, and obtain information, or drop the database using the Database option. For example, to create a database named test_db, select the Create option. You will be prompted for the name of the database to be created, which dbspace, and whether the database is to be created with a log or not. After selecting the required options, you can create a new database.
Figure 9 shows the initial DB-Access Database option menu:
Figure 9. DB-Access Database option
Next, you are prompted to enter the database, as shown in Figure 10:
Figure 10. DB-Access database creation
DB-Access thus provides a user interface that can be used to manage and monitor Informix database server. Since it comes bundled with IDS, you can easily use it to execute queries and retrieve information from the database.
AGS Server Studio
AGS Server Studio is a multi-platform suite that has a system of management tools for IDS. These tools help a database professional to be more effective and productive by simplifying the complexities of daily tasks. It maximizes Informix availability, performance, and maintainability. It improves the utilization of data storage resources by efficiently reorganizing vital databases with minimal application downtime. AGS Server Studio, which is a graphical development and management environment for Informix database server, provides wide range functionality, from initial design, development, and testing to production deployment and service, giving you a centralized point of control regardless of the location of your data servers. All versions of IDS, from 7.x to 11.xx, are supported in AGS Server Studio 6.5.
Creating a profile in Server Studio JE (SSJE)
After Server Studio is installed, when you open it, it asks you to create a user profile. But, before creating a profile, you need to generate a user certificate. Server Studio employs user profiles to securely store personalized information such as user connections, log-on parameters, data, preferences, system configurations, and more. Each user profile created is strongly encrypted with the personal user certificate, which acts as a master key. To generate a certificate, click on Request User Certificate. This opens a window, wherein you need to fill information such as name, address, e-mail ID, and so on. Once you have completed this and a have selected a certificate password, Server studio submits your request to the Software Asset Management System (SAMS). Later, a generated user certificate will be mailed to the e-mail id provided. You then have to save the certificate from the e-mail client and complete the user profile creation process.
Establishing a connection between SSJE and IDS
Connection Manager is an SSJE tool that is used to define connections between SSJE and IDS. It is used to configure parameters such as connection name, the Informix version is being used, connection groups, description, server name, host, user ID, and so on. To access the Connection Manager, select Tools > Connection Manager.
This tool has three tabs: General, Database, and Host Access.
- Use the General tab to specify the connection name and assign a group
to this connection, as shown in Figure 11:
Figure 11. Establishing a connection between SSJE and IDS
- On the Database tab, you enter server-specific JDBC connection
parameters, including server name, host where IDS is installed, port
number, and the database that you wish to connect to. User ID and
password are optional, and you can provide them at the time of
Figure 12. Server information
Specifying a database is optional. When you click on the Connect button, SSJE establishes a connection with the IDS Server.
- The Host Access tab allows you to provide configuration information for accessing IDS on the remote host using Telnet or SSH.
Once the SSJE is connected to IDS, the Object Explorer displays a list of databases, along with storage (spaces, chunks), sessions, virtual processors, memory pools, reports, E/R diagrams, and Data Loader.
Figure 13. Database Object Explorer Wizard
When you click on Databases in the Object Explorer, you get a summary of the databases created in the IDS server. This summary is displayed in the Properties window of the SSJE. The summary includes the database name, its owner, total size allocated, total size used, dbspace name, and date created.
You can find the following sections under each database in the Object Explorer:
- Routines section (SPL and external): You can use this section
to create a new routine, set filter to the routines, specify various
criteria, debug the routine, run update statistics, and export/copy
Figure 14. Database properties window
- Tables section: You can use this section to create new tables,
set a filter to tables, or run update statistics. You can edit the
data within the tables. Also, you can populate the table with
generated fake values specifying the table name and the number of the
rows to be populated. Figure 15 shows the properties of the table
Figure 15. Database table properties window
- System Tables section: In this section you can query existing system tables, set a filter for the tables, or display the system tables.
- Indexes section: Here you can create a new index or specify an option to either enable or disable the indexes on required fields of the table.
- Constraints section: Here you can create a new constraint on the table.
- Views section: In this section you can create a new view, set filter to the tables, or export or copy the views.
- Triggers section: In this section you can create a new trigger, set filter to the tables, or export/copy the trigger.
- Data types section: You can work on with various data types
using this section.
Figure 16. Supported data types
- Synonyms section: In this section you can create a new synonym, set filter to the tables, or export/copy the synonyms.
- Security section: You can use this section to create a new user
or a new role using the role and user options specifying the required
inputs. Figure 17 shows the privileges of the various users for the
Figure 17. Security options
- E/R diagrams section: Here you can create a new diagram using the diagram option and specifying the required input. You can also open the existing E/R diagram to analyze and gather information.
There are two options under the storage section of the Object Explorer:
- Spaces section: The spaces section describes the existing
DB-Spaces. When one of the spaces is selected, the properties of that
DB-Space (dbspace number, name, owner, type, number of chunks, page
size, total size, and free size) are displayed. A pie chart, showing
the utilization of the dbspace, is also displayed. Figure 18 shows the
properties of one such dbspace:
Figure 18. Storage spaces wizard
- Chunks section: This section describes the chunk details that currently exist. The chunks mentioned in this section and the chunks mentioned as a part of the spaces section point to the same page.
Virtual processors section
This section of the Object Explorer lists the various virtual processors provided by the Informix database server. You can create new virtual processors (VP), view statistics of the VPs, or get information on Ready Queue and I/O Queue. The following figure shows the various classes of virtual processors displayed on the statistics tab.
Figure 19. Virtual processor summary
Server Studio thus provides a GUI infrastructure that helps both seasoned database professionals and novices alike manage Informix database servers environment complexities with ease, and ensure that critical databases remain up and perform at peak levels.
The IDS OpenAdmin Tool (OAT) is a Web-based administration tool for IDS. OAT supports IDS 11 and later. OAT provides full range of administration support for IDS. Installing and configuring the OAT tool with IDS server has been described in the developerWorks article "Manage your Informix database with the IDS OpenAdmin Tool, Part 1: Configuring and using the OpenAdmin Tool with IDS" (July 2008), which you should first go through before starting on OAT.
This article mainly focuses on MACH 11 support with OAT and the new feature ER plug-in support that have been added with the recent release of OAT.
Figure 20. The OpenAdmin Tool for IDS
MACH (Multi-Node Active Cluster for High Availability) 11 support
OAT provides GUI interface for all MACH 11 setup, which eases the administrator's job. This section explains in detail the GUI help that is provided with OAT (shown in Figure 21):
- System validation
- User privileges
- Virtual processors
- Auto update statistics
These operations are explained later in the article.
Other topics discussed in this section:
- Creating an SD secondary server
- Managing service-level agreements and failover configurations with the Connection Manager Wizard
- Starting and stopping servers in the cluster
Figure 21. Open Admin Tool for IDS – Replication views
Components of OAT on MACH 11 setup
- Clusters: This list displays all clusters that are found in an OAT group. To display the topology and the status of the servers in the cluster, select a cluster.
- Cluster topology: This topology of the selected cluster shows the relationships between the primary server and the secondary servers, including high-availability data replication (HDR), shared disk secondary (SDS), and remote standalone secondary (RSS) servers.
- Server list: This list displays the following information for
the servers in the selected cluster:
- Server type: Primary, HDR, RSS, or SDS
- Server status: Active or inactive
- Connection status: Connected or not connected
- Workload: The percentage of the workload the server is carrying
- Lag time: The time the secondary server requires to confirm communication from the primary server
- Find clusters: Finds and displays all the high availability clusters that are available in the OAT connections database and displays them in the clusters list.
Only the clusters on servers that are part of the OAT group are displayed on the High Availability Clusters page. If needed, add the servers in the high availability clusters to an OpenAdmin Tool group. To add a server to an OAT group, on the Admin menu, expand Manage Connections, and then click Add Connection.
Creating an SD secondary server
Click on Add SDS to add an SDS server connection to the selected cluster. In the SDS Wizard, provide the following server information:
- Server Name: The name identifying the server.
- Group: The name of the server group to which the SDS server belongs.
- Server Number: The IDS-defined number of the server.
- Host: The name of the machine hosting the server.
- Port: A valid, unique port number.
- User: The user name Informix (required).
- Password: The valid password for the user Informix.
- IDS Protocol: The NETTYPE parameter for the connection to the server.
Figures 22 and 23 show the shared disk secondary wizard:
Figure 22. SDS Wizard
Figure 23. SDS Wizard
The connection manager accepts client connection requests and redirects the requests to the appropriate client server. You can create and modify service-level agreements (SLAs) and failover configurations (FOCs) for high availability clusters.
An SLA is a contract between specified client applications and their service providers; IDS servers, in this case. An FOC specifies the secondary server that takes over the role of the primary server if the primary server fails.
Figure 24. Connection Manager Wizard
Figure 25. Connection Manager Wizard
To create an SLA, on the SLA tab, click on Create SLA, and enter the requested information.
To modify an SLA, on the SLA tab, click on Modify SLA, and change the information.
To create or modify the FOC, on the FOC tab, enter or change the information.
Figure 26. Connection Manager, failover setup
Figure 27. Connection Manager, failover setup
Configuring the IDSD daemon
Install and configure the IDSD daemon on the IDS server. Reference the OAT help document for creating and running IDSD daemon at database server end. The IDSD daemon helps start and stop the servers.
You can stop/start secondary servers using OAT GUI interface.
If you are using the OpenAdmin Tool for remote administration tasks such as starting or stopping servers in a high-availability cluster, or adding a secondary server, OAT provides a GUI interface to do the same. But you must set up IDSD connectivity with the database server.
Once you have done the setup at the server end, you're able to start and stop the server using the modify button, as shown in Figures 28 and 29:
Figure 28. Stop/start server
Figure 29. Stop/start server
ER plug-in support
The Enterprise Replication plug-in for the OpenAdmin Tool for IBM Informix Dynamic Server provides a graphical interface for setting up, monitoring, and administering Enterprise Replication (ER). OAT supports Informix ER model from IDS 11 11.50 XC4 and later. This helps you to monitor ER servers and domains. OAT ER plug-in supports plenty of ER model GUI interfaces that include viewing topology of the ER domain and all known nodes, creating ER administrative tasks, defining and modifying the ER servers, creating ER setup using the template, managing the state of the servers, replicating sets and replicates, resynchronizing data among the replication servers, and modifying the ER server parameter using the GUI.
Setting up the ER plug-in for OAT
Install the Enterprise replication plug-in. Follow the OpenAdmin Tool help if need be. From the OAT menu, click on Help > How do I? For more information on creating and setting up the Enterprise Replication, follow the IBM IDS ER guide.
Monitoring Enterprise Replication
Use the Enterprise Replication plug-in for the OpenAdmin Tool (OAT) to monitor Enterprise Replication servers and domains.
Viewing the topology of an ER domain
This page helps to view the topology, or routing map, of an Enterprise Replication domain. To view the topology of the current domain, on the OAT menu, expand Enterprise Replication, and then click on Domain. The domain displays all the ER domain nodes that are known by the current node. For example, a leaf node knows itself and its parent. Nodes are denoted by the group name that is defined in the Informix SQLHOSTS file.
Note: If you are connected to a non-root node or a leaf node, the information that you can view and the actions that you can take are limited. For fuller access, switch to a root node in the domain.
On the domain page, you can perform visual alerts to help you identify the nodes that need attention. Node name is displayed as soon as you place the mouse cursor on the node icon. Node sub-trees are displayed when you click on the node icon.
Figure 30. ER routing topology
Refresh rate is applied by clicking the refresh icon. To change the refresh rate, click on the clock icon, and then use the slider to specify a refresh rate from 0 seconds to 300 seconds. To display the connection lines between the nodes, click on the Normal View icon. To hide the connection lines, click on the No Connection View icon. To view the servers in the domain in a list, select the Server List tab. For each server, the list displays the status, member servers, type, parent (if applicable), IDS version, and the date and time that the server was last monitored. The Actions button helps you to make the server and a new node in an existing ER domain or to create a new ER domain. To perform this task, you need select the server from the server list and then click on the Actions button. It lists a set of options that is allowed with that server.
The Action button allows you to perform the following operations, as illustrated in Figures 31 and 32:
- Define new server
- Modify server
- Delete server
- Check the particular node status
- View the node details
- Edit thresholds
- Connect to server
- Disconnect from server
- Suspend replication
- Resume replication
- Start replication
- Stop replication
- Clean start replication
Figure 31. Domain page with actions options
Figure 32. Domain page with actions options
Defining a server for Enterprise Replication
This page helps you make the server a new node in an existing ER domain or create a new ER domain.
Figure 33. Adding the server on Domain page
Figure 34. Adding new server to ER domain
To edit the node details, you have to select a sever and then choose Node Details from the Action menu. This page helps you to view a high-level summary of all of the activity:
Figure 35. Node details summary
There are also detailed pages/tabs that provide information about the various aspects of the node:
- The Capture tab shows the state of data capture and location in the logical log.
- The Send Queue tab shows which transactions are waiting to be replicated.
- The Network tab displays the network I/O for the node.
- The Disk Usage tab shows information about the spaces utilized by the
send and receive queues to store replicated transactions:
Figure 36. Disk Usage tab
- The Apply tab shows the information about replicated transactions being applied at the current node.
- The Aborted Transaction Spooling (ATS) Files tab contains details about these error files.
- The Row Information Spooling (RIS) Files tab contains details about these error files.
- The Errors tab lists the errors occurring at the node, as well as the node that sent the command causing the error.
- The Configuration tab shows the Enterprise Replication configuration parameters and their values.
Use the edit threshold action to define thresholds (shown in Figure 37) that, when met or exceeded, generate visual alerts for servers on the Routing Topology page. To prevent a threshold value from generating alerts on the Routing Topology page, disable the threshold by deselecting the enabled check box.
Figure 37. Editing threshold
To define a replicate template, on the OAT menu, expand Enterprise Replication, and then click on Replicates. To create a template, name the template, specify the tables to use for the template, the database and Enterprise Replication (ER) server in which the tables are located, and the template options, as illustrated in Figure 38:
Figure 38. Creating a replicate template
Figure 39. Creating a replicate template, continued
A template defines a group of master replicates and a replicate set. A template consists of schema information about a database, a group of tables, column attributes, and the primary keys that identify rows.
Use templates to set up and deploy Enterprise Replication
A template uses schema information about a database, a group of tables, columns, and primary keys to define a group of master replicates and a replicate set. First, you define a template, and then you instantiate it on the servers where you want to replicate data.
Figure 40. Summary of replicate templates
A replicate set combines several replicates to form a set that can be administered together as a unit. You can use a single command to start, stop, suspend, or resume all the replicates in the set.
Defining a replicate
Define the replication participants and replication attributes, including how frequently to replicate the data (see Figure 41) and how to handle conflicts. With the define replicate action, you define a strict master replicate and its participants. A master replicate verifies that replicated tables on different servers have consistent column data types. A strict master replicate also verifies that the replicated tables have identical column names. All database servers containing master replicates must be able to establish a direct connection with the master replicate database server. The Actions button also allows you to perform the following operations with replicate sets:
- Modify replicate set
- Start replicate set
- Stop replicate set
- Suspend replicate set
- Resume replicate set
- Check or sync replicate set
- Delete replicate set
Figure 41. Defining new replicate set
The task status button helps you to monitor the progress of the task and to see the consistency report. This section discusses the various tasks that can be monitored using OAT:
- Monitoring task status: Monitor the progress and results of check, check and repair, and synchronize tasks for replicates and replicate sets.
- Monitoring replicate tasks: Monitor the progress and results of check, check and repair, and synchronize tasks for replicates. After you run a consistency check, a check and repair, or a synchronization task on a replicate, the status of the task and the results are displayed and updated on the Task Status page.
- Monitoring replicate set tasks: Monitor the progress and results of check, check and repair, and synchronize tasks for replicate sets. After you run a consistency check, a check and repair, or a synchronization task on a replicate set, the status of the task and the results are displayed and updated on the Task Status page.
OpenAdmin Tool provides support for \most of the IDS 11 features MACH 11, Auto update statistics. It also provides user-friendly GUI interface for Enterprise Replication Model.
Informix Server Administrator (ISA)
The Informix Server Administrator (ISA) is also a Web-based administrative tool for IDS 10 and earlier. It also provides a set of administrative GUI environments, which simplifies user or administrators tasks.
After installing and configuring the ISA with IDS, you can place the URL in a browser that will fetch the following ISA GUI interface:
Figure 42. Informix Server Administrator (ISA)
It also has a help page that can guide you to perform various operations with ISA.
To monitor IDS using ISA, ISA must be installed in the same machine where IDS is installed. ISA runs the daemon process on the server machine to monitor the IDS.
Configuring ISA with IDS
After installing the ISA, open up the Web URL that was given during the installation in the browser.
Figure 43. Installed ISA tool on Web
Select to edit the configuration on the Web page. Add ISA apache port number, Informix server name, and installation directory, and then save it.
Figure 44. Configuring IDS with ISA
Now you are allowed to monitor the IDS using ISA.
Figure 45. IDS on ISA tool
To learn more about the ISA, read through the developerWorks article series Manage your Informix database with the IDS OpenAdmin Tool.
ISA provides an elegant GUI interface for IDS 10 and earlier, which also includes IDS HDR support. XPS server can also be configured and monitored using ISA.
This article has explained various utilities that are available to manage and monitor Informix. You can manage IDS with a command interface using DB-Access, or take advantage of the easy-to-use graphical tools AGS Server Studio, OpenAdmin Tool, or Informix Server Administrator. Whatever you choose, any of these tools will make your job as a DBA much easier.
- Manage your Informix database with the IDS OpenAdmin Tool (developerWorks): In this series of articles, get an introduction to IDS OAT and learn how to migrate from the Informix Server Administrator. Learn also how to analyze IDS performance using the IDS OAT.
- In the developerWorks Informix page: Read articles and tutorials and connect to other resources to expand your Informix skills.
- "Using IBM Informix Dynamic Server on Microsoft Windows" (developerWorks, December 2007): View a demo showing you how to install Apache, PHP, and the OpenAdmin tool.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Apache HTTP server: Download the Apache HTTP server.
- PHP: Download the PHP source code.
- OpenAdmin Tool: Download the OpenAdmin Tool.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.