Managing Your System
Managing the Installation with Actian Director
Director is an easy-to-use graphical interface that lets you interact with Vector installations.
Director is available as a separate download from
esd.actian.com, under Administration Tools.
Using Director, you can:
• View the started and stopped components and servers in the installation
• Start or stop the entire installation or individual components
• View Vector system and user environment variables
• View the contents of the various logs
• Administer security (users, groups, roles, and profiles)
• Manage databases and tables
• Create, store, and execute queries
For more information, see the Director help.
Managing the Installation with Visual Manager on Windows
Visual Manager is part of the Visual Tools suite available on Windows. The Visual Tools suite is included in the DBA Tools package, which is a separate download.
Visual Manager (IVM) provides a global view into the Vector installation. It serves as a system console from which you can manage Vector components and access other utilities. This utility captures events that are occurring in the system and allows them to be filtered for emphasis, based on your preferences.
IVM allows you to monitor (and start and stop) the different servers in the installation (DBMS, X100, Name, Communications, JDBC, Data Access, Bridge, and Recovery) as well as Remote Command, the Logging and Locking systems, and the Primary Transaction Log and Archiver process. It shows events occurring in the system both at the installation level and the level of each server. Using IVM, you can also edit the Vector system and user environment variable settings.
Visual Manager lets you:
• View the started and stopped components and servers in the installation as well as the history of start/stop events
• Start or stop the entire installation or individual components
• View and edit Vector system and user environment variables
• Control how types of Vector error log messages are handled
• Set preferences for event monitoring and notification
• View and filter events that occurred both at the installation level and the component or server level
• View statistics on these events
• View explanations for error log messages
• Access all Vector visual tools
For more information, see the IVM help.
Grant Access to Remote Users
To allow a user other than the installation owner to execute remote commands, you can use the following methods:
• In Actian Director, use the Create User or Modify User dialog, select Options, Remote Command Privileges
• In VDBA, use the Create or Alter User dialog. Enable the Remote Command (rmcmd) Privileges checkbox.
• You can execute the following SQL statements, while connected to the imadb database as the installation owner:
grant select,insert,update,delete on $ingres.remotecmdinview to user
grant select,insert,update,delete on $ingres.remotecmdoutview to user
grant select,insert,update,delete on $ingres.remotecmdview to user
grant execute on procedure $ingres.launchremotecmd to user
grant execute on procedure $ingres.sendrmcmdinput to user
grant register, raise on dbevent $ingres.rmcmdcmdend to user
grant register, raise on dbevent $ingres.rmcmdnewcmd to user
grant register, raise on dbevent $ingres.rmcmdnewinputline to user
grant register, raise on dbevent $ingres.rmcmdnewoutputline to user
grant register, raise on dbevent $ingres.rmcmdstp to user
Note: Grants must not be made directly to the underlying tables.
Session Management
You can perform the following session management tasks:
• See how long a query has been running
• List all sessions
• Remove a session
Determine How Long a Query Has Been Running
Query start time is stored in the imadb database.
To see when a query started
1. Connect to the imadb database:
sql imadb
2. Run this query:
SELECT server, session_id, real_user, db_name, session_terminal, VARCHAR(_DATE(query_start_secs)) + ' ' + VARCHAR(_TIME(query_start_secs)) + VARCHAR(':') + VARCHAR(MOD(query_START_secs,60))
FROM ima_server_sessions
WHERE db_name NOT IN ('', 'imadb')\g
To see how long a query has been running
1. Connect to the imadb database:
sql imadb
2. Run this query:
SELECT server, db_name, session_query, (BIGINT((CURRENT_TIMESTAMP - TIMESTAMP_WITH_TZ('1970-01-01 00:00:00+00:00'))/INTERVAL '1' second) - query_start_secs) AS elapsedsecs
FROM ima_server_sessions
WHERE db_name NOT IN ('', 'imadb')\g
Note: The longest running query in the system is shown.
Note: If a query is finished, it will still be returned in the list. In this case, we can join another table and ignore queries in BIO CS_EVENT_WAIT state. The following query will show all queries that are actually running (rather than ran, finished, and waiting for user input, such as \q):
SELECT db_name, session_query, (BIGINT((CURRENT_TIMESTAMP - TIMESTAMP_WITH_TZ('1970-01-01 00:00:00+00:00'))/INTERVAL '1' second) - query_start_secs) AS elapsedsecs
FROM ima_server_sessions s,ima_server_sessions_extra e
WHERE s.session_id = e.session_id and s.effective_user != ''
AND (e.session_state != 'CS_EVENT_WAIT' OR e.session_wait_reason != 'BIOR')
AND db_name NOT IN ('', 'imadb')
Notes:
• The start time is the start of execution, not parsing/optimization.
• The start time is seconds since 1-Jan-1900 00:00:00. The functions _DATE4 and _TIME can be used to perform time calculations; or use an ANSI interval in seconds.
• The time is not set to zero when the query ends.
Remove a Vector Session
You can list or remove Vector sessions, for example, if a query is taking longer than expected to complete. These tasks can be performed using SQL, the iimonitor utility, or the Interactive Performance Monitor.
To remove a session using SQL
1. Connect to the imadb database:
sql imadb
2. Execute the following SQL, which will return a list of active sessions (queries):
SELECT s.server, s.session_id, db_name, session_query,
(BIGINT((CURRENT_TIMESTAMP - TIMESTAMP_WITH_TZ('1970-01-01 00:00:00+00:00'))/INTERVAL '1' second) - query_start_secs) AS elapsedsecs
FROM ima_server_sessions s,ima_server_sessions_extra e
WHERE s.session_id = e.session_id and s.effective_user != ''
AND (e.session_state != 'CS_EVENT_WAIT' or e.session_wait_reason != 'BIOR')
AND db_name NOT IN ('', 'imadb') \g
Results similar to the following (truncated here to fit the space) are returned. Elapsed time is shown further to the right in the actual result.
|server |session_id |db_name|session_query
+-------------------------------+----------------------+-------+-------------
|phantom.vectorwise.com::/@34131|0000000139656236786176|z | select a.col
Note: Alternatively, execute this SQL:
EXECUTE PROCEDURE show_session_list \g
3. To remove a session, use the session_id and server_id values returned in Step 2 as parameters on the ima_remove_session procedure. For example:
EXECUTE PROCEDURE ima_remove_session(session_id='0000000139656236786176',server_id='phantom.vectorwise.com::/@34131') \g
Note: Alternatively, execute this SQL, for example:
EXECUTE PROCEDURE kill_session(session_id='0000000139656236786176') \g
4. To remove all sessions, use this statement:
EXECUTE PROCEDURE ima_drop_all_sessions \g
To remove a session using iimonitor
1. Start the iinamu utility:
iinamu
2. At the IINAMEU> prompt, enter this command to display the server number (GCF address) of your Vector Server:
show ingres
The running servers and their associated server numbers are shown.
3. Start iimonitor using the Vector Server number shown in Step 2:
iimonitor server_number
4. At the IIMONITOR> prompt, list sessions:
show sessions formatted
Session ID and other information are shown. For example:
Session 00007F04412D0200:1093457680
5. Remove the session by using the remove command with the hex number that precedes the colon (:). For example:
remove 00007F04412D0200