Databases - User Guide

Introduction

This document provides details of how to set-up database in MySQL, PostgreSQL, Oracle, Sybase and SQL Server servers for use with Geneos.

Note: Geneos also supports MariaDB. All setup, configuration, etc. for MariaDB is identical to MySQL. Any references to MySQL can also be taken as referring to MariaDB.

The information included covers:

sizing
setting up user accounts with appropriate access
useful commands for each type of database
simple housekeeping scripts

It is assumed that the database server itself has already been installed. Please see Database Schema in Gateway Database Logging for information on the structure of the Geneos database.

The Geneos Database

In Sybase, MySQL, and SQLServer a particular server may contain a number of different databases. Tables are created within each database. Effectively each application uses a different database. Once connected to the server and using the database, tables can be accessed directly by table name.

In Oracle there is generally a single database instance per Oracle instance. Within each Oracle instance schemas are created which contain the tables for an application. Tables are referenced by schema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name.

For Oracle tables to be referenced without specifying the schema you must either be connected to Oracle as the user or use a synonym. Details on creating synonyms can be found in the Oracle section.

Database schemas - the creation scripts

The database schemas or creation scripts are packaged with the Gateway2 software and appear in the appropriate directory (mysql, oracle, Sybase or sqlServer) under resources/databases as schema-n.n.sql

Sizing

It is difficult to provide an accurate figure for the size of the database required as database sizing is affected by so many variables:

The number of metrics stored and the interval at which these are stored .
This itself is a function of the number of managed entities monitored, plug-ins used, etc.
The volatility of the metrics.
Housekeeping / archiving strategy.

A minimal size for a database in megabytes can be calculated using the following formula:

Size = Number of Managed Entities * Number of months data to be stored * 10

In practice the size of the database required can be minimised by:

Keeping only a limited number of days of data
Carefully considering what data is logged and only logging data which has a known use
Specifying the interval at which items are logged and using margins

Archiving Strategies

There is no automated archiving or housekeeping of data from the database.

All data logged to the database is written with a Unix timestamp making it a relatively simple process to remove all data prior to a certain date, i.e. it is possible to develop a set of routines which enable a rolling 3 or 6 months worth of data to be kept.

Example of housekeeping scripts can be found in the relevant database sections below. These scripts just purge old data from the database. They can be used as a basis for an archiving script in as much as the queries provided can be used for archiving as well as purging of data.

Required Access Levels

The Geneos user used by the gateway needs to have select, insert, update access to the tables

The logins used by the ActiveConsole only need select access to the various tables.

MySQL

Required client libraries

Ensure 64-bit MySQL client libraries (libmysqlclient_r.so.*) are installed on the host running the Gateway, and set up the MYSQL_LIB and LD_LIBRARY_PATH in the start_gateway script appropriately.

For example, if the client library is placed in the same directory as the Gateway, then the following entries will be required:-

MYSQL_LIB=.; export MYSQL_LIB
LD_LIBRARY_PATH=${MYSQL_LIB}:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

Windows supported libraries

Geneos supports the following libraries and loads the first available library, as listed:

libmysql.dll
libmariadb.dll

Geneos attempts to load libmySQL.dll first. If it does not find it, then it tries libmariadb.dll.

Using MySQL

mysql is a command line client utility which allows you to connect to and administer a mysql database server.

Entering mysql with no paramters will attempt to connect to the mysql server on the localhost as the user root.

To connect as user <user_name> to a database <database_name> on a remote host <host_name> use

mysql -u<user_name> -h<host_name> <database_name>

Note: There must be no space between -u and the user_name.

Setting up the Geneos database schema

Create a user

mysql> create user geneos_user;
Query OK, 0 rows affected (0.08 sec)
mysql>

Create a database

mysql> create database geneos_db;
Query OK, 1 row affected (0.01 sec)

mysql>

Give the user appropriate permissions

mysql> grant all on geneos_db.* to 'geneos_user'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on geneos_db.* to 'geneos_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

Note: Both grants (@'localhost' and '%') are needed if the user is going to be able to access the database from the localhost and remote hosts.

Adding identified by 'password', after the user on each grant entry, will give the user the password.

Giving the user full privileges (all) means that the user is a superuser and has full access to do anything within the geneos_db database except to grant privileges. To give the user the ability to grant privileges use " grant all on 'db_name' to 'user'@'host' with grant option; "

An example setting a password and giving grant privileges:

mysql> grant all on geneos_db.* to 'geneos_user'@'localhost' identified by 'geneos_passwd' with grant option;
Query OK, 0 rows affected (0.03 sec)

After all the grants have been completed issue a flush privileges command to ensure the privileges are reloaded from the grant tables

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

Load the schema

Assuming the schema is in the file schema_1.n.sql, check the first line of the schema sets the database to the required database in this case geneos_db.

$ more schema_1.n.sql

use geneos_db;

CREATE TABLE … …

Run in the schema

mysql -ugeneos_user < schema_1.n.sql

For more information on how to set up MySQL database in Linux, see SQL Toolkit Plug-in User Guide.

Note: A symbolic link or symlink must be created between the connector C library and the file libmysqlclient_r.so.

Useful Commands

Who am I?

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.02 sec)

mysql>

What databases exist?

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| geneos_cjt         |
| gwy2               |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

What is the current database?

mysql> select database();
+------------+
| database() |
+------------+
| geneos_db  |
+------------+
1 row in set (0.00 sec)

mysql>

What users exist?

mysql> select user,password from mysql.user;
+-----------+-------------------------------------------+
| user      | password                                  |
+-----------+-------------------------------------------+
| root      | *15ECE87A73876CA51D7444400EA7CD03BB8FA7B3 |
| root      | *15ECE87A73876CA51D7444400EA7CD03BB8FA7B3 |
| mysql     |                                           |
| sybase    |                                           |
| dmiller   |                                           |
| cjt       |                                           |
| *         |                                           |
| cjt2      |                                           |
| cjt2      |                                           |
| cjt_db    |                                           |
| cjt_db    |                                           |
| someuser  |                                           |
| dmiller   |                                           |
+-----------+-------------------------------------------+
13 rows in set (0.01 sec)

Switch to another database

mysql> use geneos_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

List the tables in the current database

mysql> show tables;
+----------------------+
| Tables_in_geneos_db  |
+----------------------+
| event_table          |
| net_table            |
| node_ref_table       |
| os_table             |
| processes_table      |
| text_table           |
| var_ref_table        |
| version_table        |
+----------------------+
8 rows in set (0.00 sec)

mysql>

Get the details of a table

Get details of the columns in a table

mysql> describe os_table;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| timestamp | int(11) | NO   |     | NULL    |       |
| node_ref  | int(11) | NO   | MUL | NULL    |       |
| var_ref   | int(11) | NO   |     | NULL    |       |
| value     | double  | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.29 sec)

mysql>

Null - if yes the column can contain nulls

describe <table_name> is a short cut for "show columns from table <table_name>

Show details of an index on a table

mysql> show index from var_ref_table;
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| var_ref_table |          0 | PRIMARY       |            1 | ref         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| var_ref_table |          0 | var_ref_index |            1 | varname     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| var_ref_table |          0 | var_ref_index |            2 | tablename   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
												mysql>

Non_unique - 0 if the index cannot contain duplicates, 1 if it can

On the var_ref_table in the example above, var_ref_index is a unique index consisting of varname and tablename columns.

Count the number of rows in a table

mysql> select count(*) from event_table;
+----------+
| count(*) |
+----------+
|  6319742 |
+----------+
1 row in set (0.12 sec)

mysql>

Limit the number of rows returned

mysql> select * from os_table limit 5;
+------------+----------+---------+-------+
| timestamp  | node_ref | var_ref | value |
+------------+----------+---------+-------+
| 1246981633 |        2 |       1 |    48 |
| 1246981633 |        3 |       1 |    48 |
| 1246981633 |        4 |       1 |  30.9 |
| 1246981633 |        5 |       1 |    48 |
| 1246981693 |        4 |       1 |  29.5 |
+------------+----------+---------+-------+
5 rows in set (0.00 sec)

mysql>

What access permissions does a user have?

Logged in as the user, use the show grants command:

mysql> show grants;
+-------------------------------------------------------------------------------------+
| Grants for geneos_user@localhost                                                    |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'geneos_user'@'localhost'                                     |
| GRANT ALL PRIVILEGES ON `geneos_db`.* TO 'geneos_user@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

To list all the permissions a user has been given select form the mysql.user table

mysql> select * from mysql.user where user = 'cjt';
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| %         | cjt  |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | cjt  |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
2 rows in set (0.00 sec)
mysql>

To list all the permissions a user has been given within a database, select from the mysql.db table

mysql> select * from mysql.db where user = 'cjt' and db = 'geneos_cjt';
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db         | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | geneos_cjt | cjt  | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| localhost | geneos_cjt | cjt  | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.07 sec)

mysql>

Handling Timestamps

To convert from a date and time to a Unix timestamp use UNIX_TIMETSTAMP(date)

mysql> select unix_timestamp('2009-12-24 09:24:10');
+---------------------------------------+
| unix_timestamp('2009-12-24 09:24:10') |
+---------------------------------------+
|                            1261646650 |
+---------------------------------------+
1 row in set (0.02 sec)

mysql>

To convert from a Unix timestamp to a normal time use FROM_UNIXTIME(timestamp)

mysql> select from_unixtime(1261581335);
+---------------------------+
| from_unixtime(1261581335) |
+---------------------------+
| 2009-12-23 15:15:35       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select from_unixtime(timestamp),value from os_table where var_ref = 151 and node_ref = 7;
+--------------------------+-------+
| from_unixtime(timestamp) | value |
+--------------------------+-------+
| 2009-12-23 15:15:35      | 42332 |
| 2009-12-23 15:15:44      | 42336 |
+--------------------------+-------+

Creating additional Geneos tables

To create additional tables for Geneos data use the following statements substituting appropriately for <name> in TABLE and KEY sections

For numeric data -

CREATE TABLE `<name>_table` (
`timestamp` int(11) NOT NULL,
`node_ref` int(11) NOT NULL,
`var_ref` int(11) NOT NULL,
`value` double default NULL,
KEY `<name>_index` (`node_ref`,`var_ref`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

For text data -

CREATE TABLE `<name>_table` (
`timestamp` int(11) NOT NULL,
`node_ref` int(11) NOT NULL,
`var_ref` int(11) NOT NULL,
`value` varchar(250) collate latin1_general_ci default NULL,
KEY `<name>_index` (`node_ref`,`var_ref`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Creating additional read-only users

The ActiveConsole connects directly to the database to extract information, for imports into the Event Ticker and to plot historic graphs. The ActiveConsole can use the same user as the gateway or another user but as it is only reading data the ActiveConsole should ideally use a read-only user. In addition, if you wish to extract information from the database using Express Reports, some other reporting tool or run ad-hoc queries on the database you should probably use a read-only user.

Creating a read-only user

To create a read-only user, create a user

mysql> create user geneos_ro;
Query OK, 0 rows affected (0.08 sec)

mysql>

Give the user appropriate permissions

mysql> grant select on geneos_db.* to 'geneos_ro'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on geneos_db.* to 'geneos_ro'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

Note: Both grants (@'localhost' and '%') are needed if the user is going to be able to access the database from the localhost and remote hosts.

After all the grants have been completed issue a flush privileges command to ensure the privileges are reloaded from the grant tables

Housekeeping

A simple housekeeping script for MySql. Replace all occurrences of <name> with the appropriate table.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than @num_days                                           */
/*                                                                            */
/******************************************************************************/

DROP procedure purge_<name>_table;

DELIMITER $$

CREATE procedure purge_<name>_table
(
in num_days numeric
)
BEGIN

delete
from <name>_table
where timestamp < unix_timestamp(date_sub(curdate(), interval num_days day));

END $$

DELIMITER ;

To load this stored procedure into the database use

mysql -h hostname -u username -D database_name < purge_script_name.sql

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines:

#!/bin/sh

HOST=redhatws
USER=geneos_user
PASSWORD=geneos_password
DBASE=geneos_db
NUM_DAYS=90

mysql -h ${HOST} -u ${USER} -D ${DBASE} -P ${PASSWORD} << EOF
call purge_<name>_table($NUM_DAYS);
EOF

Sybase

Required Client Libraries

Ensure the Sybase Server 15 client libraries are installed on the host running the gateway and set-up the SYBASE and LD_LIBRARY_PATH in the start_gateway script appropriately. Please see Sybase Software development kit for more details. Software Developer's Kit 15.7 > Open Client and Open Server Programmers Supplement for UNIX > Open Client Client-Library/C. This is available at http://infocenter.sybase.com/help/index.jsp.

setenv SYBASE /export/share/dev/SYBASE
setenv LD_LIBRARY_PATH $SYBASE/OCS-12_5/lib:$SYBASE/lib:${LD_LIBRARY_PATH}

Interfaces file

Sybase clients rely on the interfaces file to determine where Sybase servers are on the network. Find the interfaces file on the server on which the gateway is running and ensure it contains an entry for the Sybase server you are attempting to connect to.

% pwd
/export/share/dev/SYBASE
% more interfaces
LINUX165
master tli tcp /dev/tcp \x00021388c0a80aa50000000000000000
query tli tcp /dev/tcp \x00021388c0a80aa50000000000000000

ITRSSOL5
master tli tcp /dev/tcp \x00021004c0a80a290000000000000000
query tli tcp /dev/tcp \x00021004c0a80a290000000000000000
:
:

Using isql

isql (Interactive SQL parser to Adaptive Server) is a command line client utility

isql -S<server_name> -U<user_name> -P<password>

The server_name should match an entry in the interfaces file.

Setting up the Geneos database schema

Create a user

1> sp_addlogin geneos_user, geneospasswd
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1>

Create a database

1> create database geneos_db
2> go
CREATE DATABASE: allocating 25600 logical pages (50.0 megabytes) on disk
'bigger_data'.
1>

Switch to the database

1> use geneos_db
2> go

Change the database owner

1> sp_changedbowner geneos_user
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Database owner changed.
(return status = 0)
1>

Load the schema

Assuming the schema is in the file schema_1.n.sql, check the first line of the schema sets the database to the required database in this case geneos_db.

$ more schema_1.n.sql

use geneos_db
go

CREATE TABLE … …

Run in the schema

isql -S LINUX165 -U geneos_user -P geneospasswd < schema_1.n.sql

Useful Commands

Who am I?

1> select user, user_id()
2> go

------------------------------ -----------
geneos_user                              3

(1 row affected)
1>

What databases exist?

1> sp_helpdb
2> go
name                     db_size       owner                    dbid           created         status
------------------------ ------------- ------------------------ ------         --------------  ------------------------------------------------------------------------------------------------------
RJB                            50.0 MB  sa                            6         Nov 29, 2005    mixed log and data
geneos_db                      50.0 MB  sa                           16         Sep 14, 2009    mixed log and data
master                        126.0 MB  sa                            1         Jan 25, 2005    mixed log and data
model                           2.0 MB  sa                            3         Jan 25, 2005    mixed log and data
:
:

What is the current database?

1> select db_name()
2> go

------------------------------
geneos_db

(1 row affected)
1>

What users exist?

To find what users exist in a given database

1> use geneos_db
2> go
1> sp_helpuser
2> go
Users_name        ID_in_db    Group_name        Login_name
----------------- ----------- ----------------- -----------------
dbo                         1 public            geneos_user
(return status = 0)
1>

Switch to another database

1> use geneos_db
2> go
1>

List the tables in the current database

1> select name from sysobjects where type = "U"
2> go
name
------------------------------
version_table
node_ref_table
var_ref_table
event_table
os_table
net_table
processes_table
text_table
audit_table

(9 rows affected)
1>

Get the details of a table

sp_help <table_name> will return full details about a table in the current database, including the columns and indexes.

1> sp_help os_table
2> go
Name                           Owner                           Object_type
------------------------------ ------------------------------  --------------------------------
os_table                       dbo                             user table

(1 row affected)

Data_located_on_segment        When_created
------------------------------ --------------------------
default                               Sep 14 2009 12:24PM

Column_name     Type            Length      Prec Scale Nulls Default_name      Rule_name       Access_Rule_name               Identity
--------------- --------------- ----------- ---- ----- ----- ---------------   --------------- ------------------------------ --------
timestamp       int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
node_ref        int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
var_ref         int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
value           real                      4 NULL  NULL     0 NULL              NULL            NULL                                  0

index_name           index_description                                         index_keys                                     index_max_rows_per_page index_fillfactor index_reservepagegap    index_created
-------------------- -------------------------------------------------------   ---------------------------------------------- ----------------------- ---------------- --------------------    -------------------
os_index             nonclustered located on default                           node_ref, var_ref, timestamp                                         0                0                    0    Sep 14 2009 12:24PM

(1 row affected)

No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
1              0          0                 0            0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0                     0                   0
(return status = 0)
1>

Nulls - if 1 the column can contain nulls, if 0 then nulls are not allowed.

The default for a database when creating a table is not null, can be changed with sp_dboption.

To just see the details of the indexes on a table use sp_helpindex

1> sp_helpindex var_ref_table
2> go
index_name           index_description                                         index_keys                                      index_max_rows_per_page index_fillfactor index_reservepagegap   index_created
-------------------- --------------------------------------------------------  ----------------------------------------------  ----------------------- ---------------- --------------------   -------------------
var_ref_index        clustered, unique located on default                      varname, tablename                                                    0                0                    0   Sep 14 2009  3:38PM

(1 row affected)
(return status = 0)
1>

On the var_ref_table in the example above, var_ref_index is a unique index consisting of varname and tablename columns.

Count the number of rows in a table

1> select count(*) from event_table
2> go

-----------
10920

(1 row affected)
1>

Limit the number of rows returned

Set rowcount to the number of rows required. This affects all subsequent statements.

1> set rowcount 5
2> go

1> select * from os_table
2> go
timestamp   node_ref    var_ref     value
----------- ----------- ----------- --------------------
1239963328           1           1             0.000000
1239964471           1           1             0.000000
1239964527           1           1             0.000000
1239964585           1           2             5.130000
1239964590           1           2             4.620000

(5 rows affected)
1>

To remove the limit and have all rows returned, set rowcount to zero.

1> set rowcount 0
2> go

More user details

1> sp_displaylogin geneos_user
2> go
Suid: 11
Loginame: geneos_user
Fullname:
Default Database: master
Default Language:
Auto Login Script:
Configured Authorization:
Locked: NO
Date of Last Password Change: Sep 14 2009 11:19AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)
1>

What access permissions does a user have?

To list all the permissions a user has been given within a database, use sp_helprotect specifying the user

1> use geneos_db
2> go
1> sp_helprotect geneos_ro
2> go
grantor         grantee         type     action                        object          column     grantable
--------------- --------------- -------- ----------------------------  --------------- ---------- ---------
dbo             geneos_ro       Grant    Select                        audit_table     All        FALSE
dbo             geneos_ro       Grant    Select                        event_table     All        FALSE
dbo             geneos_ro       Grant    Select                        net_table       All        FALSE
dbo             geneos_ro       Grant    Select                        node_ref_table  All        FALSE
dbo             geneos_ro       Grant    Select                        os_table        All        FALSE
dbo             geneos_ro       Grant    Select                        processes_table All        FALSE
dbo             geneos_ro       Grant    Select                        text_table      All        FALSE
dbo             geneos_ro       Grant    Select                        var_ref_table   All        FALSE
dbo             geneos_ro       Grant    Select                        version_table   All        FALSE
dbo             public          Grant    Select                        sysalternates   All        FALSE
dbo             public          Grant    Select                        sysattributes   All        FALSE
dbo             public          Grant    Select                        sysobjects      ckfirst    FALSE
:
:

To list the permissions which a particular user has been given to a particular table use sp_helprotect specifying the table and user

1> sp_helprotect version_table, geneos_ro
2> go
grantor         grantee         type     action                        object          column     grantable
--------------- --------------- -------- ----------------------------  --------------- ---------- ---------
dbo             geneos_ro       Grant    Select                        version_table   All        FALSE

(1 row affected)
(return status = 0)
1>

Handling Timestamps

To convert from a date and time to a Unix timestamp use

1> select datediff(second,"January 1, 1970 00:00","24 Dec 2009 09:24:10")
2> go

-----------
1261646650

(1 row affected)
1>

To convert from a Unix timestamp to a normal time use

1> select convert(char(26),dateadd (second,1261646650,"January 1 1970 00:00"),109)
2> go

--------------------------
Dec 24 2009  9:24:10:000AM

(1 row affected)
1>

Creating additional Geneos tables

To create additional tables for Geneos data use the following statements substituting appropriately for <name> in table and index sections

For numeric data

CREATE TABLE <name>_table (timestamp int not null,node_ref int not null,var_ref int not null,value real);
go
CREATE INDEX <name>_index on <name>_table (node_ref,var_ref,timestamp)
go

For text data

CREATE TABLE <name>_table(timestamp int not null,node_ref int not null,var_ref int not null,value varchar(250));
go
CREATE INDEX <name>_index on <name>_table (node_ref,var_ref,timestamp)
go

Creating additional read-only users

Creating a read-only user

To create a read-only user, create a user

1> sp_addlogin geneos_ro, geneospasswd
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1>

Add the user to the database.

Note: You need to switch to the database first before issuing the sp_adduser command.

1> select user
2> go

------------------------------
dbo

(1 row affected)
1> use geneos_db
2> go
1> sp_adduser geneos_ro
2> go
New user added.
(return status = 0)
1>

As the dbo, give the read-only user access to the tables

1> grant select on version_table to geneos_ro
2> go

To give a user access to all the tables in a database the following approach can be used.

Note: This gives all permissions but all can be replaced by select.

Generate a list of the tables surrounded by appropriate text to return a command:-

1> select "grant all on " + name + " to geneos_user" from sysobjects
2> where type = "U"
3> go

----------------------------------------------------------
grant all on version_table to geneos_user
grant all on node_ref_table to geneos_user
grant all on var_ref_table to geneos_user
grant all on event_table to geneos_user
grant all on os_table to geneos_user
grant all on net_table to geneos_user
grant all on processes_table to geneos_user
grant all on text_table to geneos_user
grant all on audit_table to geneos_user

(9 rows affected)
1>

Then cut and paste the resulting output

1>  grant all on version_table to geneos_user
grant all on node_ref_table to geneos_user
grant all on var_ref_table to geneos_user
grant all on event_table to geneos_user
grant all on os_table to geneos_user
grant all on net_table to geneos_user
grant all on processes_table to geneos_user
grant all on text_table to geneos_user
grant all on audit_table to geneos_user

2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
12> go
1>

Housekeeping

A simple housekeeping script for Sybase. Replace all occurrences of <name> with the appropriate table. The loop and rowcount of 10000 avoids filling the transaction log.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than @num_days                                           */
/*                                                                            */
/******************************************************************************/
drop proc purge_<name>_table
go

create proc purge_<name>_table
(
@num_days int
)
as

declare @archive_tm      int,
@finished        char(1)

select @num_days = @num_days * -1
select @archive_tm = datediff(second,"January 1, 1970 00:00",
convert(char(15),
dateadd(dd, @num_days, getdate()),106))

set rowcount 10000

select @finished = 'N'

while @finished = 'N'
begin

delete
from <name>_table
where timestamp < @archive_tm

if @@rowcount = 0 select @finished = 'Y'

end

go

To load this stored procedure into the database use

isql -S hostname -U username -D database_name -P password < purge_script_name.sql

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines:

#!/bin/sh

DB_SERVER=SYBASE_SERVER
DB_DATABASE=geneos_db
DB_USERNAME=geneos_user
DB_PASSWORD=geneos_passwd

NUM_DAYS=90

isql -S${DB_SERVER} -U${DB_USERNAME} -D${DB_DATABASE} -P${DB_PASSWORD} << EOF
exec  purge_<name>_table  "$NUM_DAYS"
go
EOF

Oracle

Required Client Libraries

Ensure 64-bit Oracle client libraries (libclntsh.so) are installed on the host running the gateway and set-up the ORACLE_HOME and LD_LIBRARY_PATH in the start_gateway script appropriately. Below is an example of the shell commands to set-up the correct environment variables, these may vary depending on the details of your Oracle installation.

ORACLE_HOME=/usr/oracle/oracle/product/11.2.0/client; export ORACLE_HOME
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

tnsnames.ora file

Oracle clients use the tnsnames.ora file to determine where Oracle servers are on the network.

Find the tnsnames.ora file on the server on which the gateway is running and ensure it contains an entry for the Oracle database you are attempting to connect to.

EUMON01TCP, EUMON01,EUMON01.itrspc106.ldn.itrs =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP) (HOST = itrspc106.ldn.itrs) (PORT = 1521)
)
(CONNECT_DATA =
(SID = EUMON01)
)
)

When connecting to Oracle database, you might encounter the ORA-12154: TNS:could not resolve the connect identifier specified error message. This means that the value in the Gateway Setup Editor > Database logging > Database name parameter cannot be resolved.

To resolve this, do one of the following:

Ensure that the database name is correctly used in the tnsnames.ora file.
If you are not using the tnsnames.ora file, enter the following values in the Database name parameter:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<database server host/IP>)(PORT=<database server port>))(CONNECT_DATA=(SERVICE_NAME=database name/database global name)))

Using sqlplus

sqlplus is a command line client utility.

Before trying to use sqlplus you need to set-up ORACLE_HOME, e.g.

setenv ORACLE_HOME /usr/oracle/oracle/product/11.2.0/client

sqlplus <user_name>/<password>@<database_name>sqlplus geneos_user/geneos_passwd@db_name

To set the line length and page length within sqlplus to a sensible values -

SQL> set linesize 132
SQL> set pagesize 60

Setting up the Geneos database schema

Create a user

SQL> create user geneos_user identified by geneos_passwd default tablespace USERS temporary tablespace TEMP;

User created.

SQL>

Give the user appropriate access, connect and dba

SQL> grant connect, dba to geneos_user;

Grant succeeded.

SQL>

Load the schema, assuming the schema is in the file schema_1.n.sql

sqlplus geneos_user/geneos_passwd@TESTDB @schema_1.n.sql

Useful Commands

Who am I?

SQL> select user, uid from dual;

USER                                  UID
------------------------------ ----------
geneos_user                            58

SQL>

SQL> show user;
USER is "geneos_user"
SQL>

What users exist?

SQL> select username, default_tablespace, temporary_tablespace from dba_users order by username;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ANONYMOUS                      SYSAUX                         TEMP
CJT                            USERS                          TEMP
CTHOUMINE                      USERS                          TEMP
CTXSYS                         SYSAUX                         TEMP
DBSNMP                         SYSAUX                         TEMP

List the tables in the current database

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
NODE_REF_TABLE
VERSION_TABLE
VAR_REF_TABLE
EVENT_TABLE
OS_TABLE
NET_TABLE
PROCESSES_TABLE
TEXT_TABLE

8 rows selected.

SQL>

select * from user_objects where object_type = 'TABLE';

Get the details of a table

A list of the columns

SQL> desc OS_TABLE
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TIMESTAMP                                 NOT NULL NUMBER(38)
NODE_REF                                  NOT NULL NUMBER(38)
VAR_REF                                   NOT NULL NUMBER(38)
VALUE                                              FLOAT(63)

SQL>

A list of the indexes on the table

SQL> select distinct(INDEX_NAME) from all_ind_columns where table_name = 'OS_TABLE' and index_owner = 'geneos_user';

INDEX_NAME
------------------------------
OS_INDEX

SQL>

Full details of an index

SQL> select * from all_ind_columns where table_name = 'OS_TABLE' and index_name='OS_INDEX' and index_owner='CTHOUMINE';

INDEX_OWNER                    INDEX_NAME                TABLE_OWNER                    TABLE_NAME                      COLUMN_NAME                     COLUMN_POSITION COLUMN_LENGTH   CHAR_LENGTH     DESC
------------------------------ ------------------------- ------------------------------ ------------------------------  ------------------------------  --------------- -------------   -----------     ----
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      TIMESTAMP                       1               22              0               ASC
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      NODE_REF                        2               22              0               ASC
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      VAR_REF                         3               22              0               ASC

SQL>

Count the number of rows in a table

SQL> select count(*) from event_table;

COUNT(*)
----------
8977

SQL>

Limiting the number of rows returned

Add rownum <= <num_rows_required> to the where clause

SQL> select * from os_table where rownum <= 4;

TIMESTAMP   NODE_REF    VAR_REF      VALUE
---------- ---------- ---------- ----------
1251806387          1          1         11
1251806450          1          1         10
1251807151          1          1         11
1251807181          1          1         10

SQL>

What access permissions does a user have?

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
CREATE TABLESPACE
:
:

Handling Timestamps

To convert from a date and time to a Unix timestamp use

SQL> SELECT (to_date('24-dec-2009 09:47:08','dd-mon-yyyy hh24:mi:ss') - to_date('01-JAN-1970')) * (86400) AS dt FROM dual;

DT
----------
1261648028

SQL>

To convert from a Unix timestamp to a normal time use

SQL> select to_char(to_date('01-jan-1970') + 1261648028 / 86400,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(TO_DATE('01-
-------------------
24-dec-2009 09:47:08

SQL>

Creating additional Geneos tables

To create additional tables for Geneos data use the following statements substituting appropriately for <name>.

For numeric data

CREATE TABLE <name>_table (timestamp int not null, node_ref int not null, var_ref int not null, value real null) ;
CREATE INDEX <name>_index on <name>_table (node_ref, var_ref, timestamp) ;
ALTER TABLE <name>_table add constraint fk_<name>_table_node_ref  FOREIGN KEY (node_ref) REFERENCES node_ref_table (ref);
ALTER TABLE <name>_table add constraint fk_<name>_table_var_ref  FOREIGN KEY (var_ref) REFERENCES var_ref_table (ref);

For text data

CREATE TABLE <name>_table (timestamp int not null, node_ref int not null, var_ref int not null, value varchar(250)) ;
CREATE INDEX <name>_index on <name>_table (node_ref, var_ref, timestamp) ;
ALTER TABLE <name>_table add constraint fk_<name>_table_node_ref  FOREIGN KEY (node_ref) REFERENCES node_ref_table (ref);
ALTER TABLE <name>_table add constraint fk_<name>_table_var_ref  FOREIGN KEY (var_ref) REFERENCES var_ref_table (ref);

Creating additional read-only users

Creating a read-only user

To create a read-only user, create a user

SQL> create user geneos_ro identified by geneos_ro_passwd default tablespace USERS temporary tablespace TEMP;

User created.

SQL>

and give the user connect access,

SQL> grant connect to geneos_ro;

Grant succeeded.

SQL>

For user2 to access the tables of user1 you must have dba privileges and give user2 select access to the table of user1 and then create a synonym

grant select on "user1."table_name" to "user2";
create synonym "user2"."table_name" for "user1"."table_name"

e.g.

SQL> grant select on geneos_user.event_table to geneos_ro;

Grant succeeded.

SQL> create synonym geneos_ro.event_table for geneos_user.event_table;

Synonym created.

Listing synonyms

To list synonyms

SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from all_synonyms where owner = 'GENEOS_RO';

OWNER               SYNONYM_NAME                 TABLE_OWNER                    TABLE_NAME
------------------- ---------------------------- ---------------------------- --------------------
GENEOS_RO           EVENT_TABLE                  GENEOS_USER                  EVENT_TABLE
GENEOS_RO           NET_TABLE                    GENEOS_USER                  NET_TABLE
GENEOS_RO           NODE_REF_TABLE               GENEOS_USER                  NODE_REF_TABLE
GENEOS_RO           OS_TABLE                     GENEOS_USER                  OS_TABLE
GENEOS_RO           SOURCE_TABLE                 GENEOS_USER                  SOURCE_TABLE
GENEOS_RO           TEXT_TABLE                   GENEOS_USER                  TEXT_TABLE
GENEOS_RO           VAR_REF_TABLE                GENEOS_USER                  VAR_REF_TABLE
GENEOS_RO           VERSION_TABLE                GENEOS_USER                  VERSION_TABLE

Housekeeping

A simple housekeeping script for Oracle. Replace all occurrences of <name> with the appropriate table.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than num_days                                            */
/*                                                                            */
/******************************************************************************/

CREATE OR REPLACE PROCEDURE purge_<name>_table(num_days IN NUMBER)
IS
BEGIN
delete
from <name>_table
where timestamp < (trunc(sysdate) - num_days - to_date('01-JAN-1970','DD-MON-YYYY')) * 86400;
END;

To load this stored procedure into the database use

sqlplus user@schema@TESTDB @purge_script_name.sql

and once this has loaded into sqlplus enter /

for example

sqlplus cthoumine/cthoumine@TESTDB @ purge_<name>_table.sql
SQL*Plus: Release 11.2.0.1.0 - Production on Tue Dec 22 15:18:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

8  /

Procedure created.

SQL>

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines

#!/bin/sh

ORACLE_HOME=/usr/oracle/oracle/product/11.2.0/client
export ORACLE_HOME

USER=geneos_user
SCHEMA=geneos_user
DBASE=TESTDB

NUM_DAYS=90

/usr/oracle/oracle/product/11.2.0/client/bin/sqlplus ${USER}/${SCHEMA}@${DBASE} << EOF
exec  purge_<name>_table($NUM_DAYS);
EOF

SQL Server

Required Client Libraries

See MS SQL Server Database Setup using native client library - used by 64-bit Linux Gateway in the Database Logging Guide.

Setting up the database

Create a user (Windows authenticated)

Within Microsoft SQL Server Management Studio, open up the Security folder, and the Logins folder within it. Right click the Logins folder and choose New Login

databases1

Use a valid Windows NT application / user account for user credentials and enter the appropriate permissions.

databases2

databases3

databases4

Create a user (SQL Server authentication)

Within Microsoft SQL Server Management Studio, open up the Security folder, and the Logins folder within it. Right click the Logins folder and choose New Login

In the General tab enter a login name and assign a password to it.

databases5

Accept the defaults on other tabs as seen in previous slides.

Create a database

Right click the Database folder and choose New Database. Give the database a name and assign the database an owner.

databases6

Assign Collation to be Latin1_General_CI_AS. Keep other settings as default.

databases7

Create tables / schema

Choose New Query option

databases8

Within the Gateway's installation folder navigate to the directory resources/databases/sqlServer. Load the contents of the schema-<version_no>.sql file into the query window.

The script assumes geneos_db is the database. You will need to change this to name it otherwise.

Click on Execute

databases10

Right click the GENEOS database and choose Refresh and tables are now listed

databases11

Housekeeping

A simple housekeeping script for SQL Server. Replace all occurrences of <name> with the appropriate table.

/******************************************************************************/
/*                                                                            
/* Purging the <name>_table, nothing subtle, just remove all records with a   
/* timestamp earlier than num_days                                            
/*                                                                           
/******************************************************************************/

use geneos_db
GO
CREATE PROCEDURE dbo.purge_<name>_table
@num_days int
AS
Delete
from <name>_table
where @num_days < DATEDIFF(day,DATEADD(S,timestamp,'1970-01-01'),GETDATE())
GO

Below is a batch script to trigger the procedure:

set HOST=nysupsvr08
set USER=username
set PASSWORD=password
set DBASE=geneos_db
set NUM_DAYS=15

sqlcmd -S %HOST% -d %DBASE% -U %USER% -P %PASSWORD% -Q "Execute  purge_<name>_table %NUM_DAYS%"

Other

Renaming Gateways and the database

If the name of a gateway is changed, when the gateway is restarted it will fail to find the existing entries in the node_ref_table as these are linked to the old gateway name and as a result will create an entirely new set of node_refs. As a result whilst the historical information held in the database still exists it will not be available via the ActiveConsole and is effectively lost.

In order to avoid this, if changing the gateway name, the following procedure should be followed:-

Stop the gateway

Edit the gateway.setup.xml and change the gateway name

<gatewayName>old_gateway_name</gatewayName>

<gatewayName>new_gateway_name</gatewayName>

Change the gateway name in the node_ref_table

update node_ref_table
set gateway = "new_gateway_name"
where gateway = "old_gateway_name"

Restart the gateway

Renaming Managed Entities and the database

The name of a Managed Entity is stored in the node_ref_table as the node, a unique node_ref (number) is then assigned to this node and gateway combination. In other database tables the Managed Entity or node is referred to by this node_ref.

If the Managed Entity name is changed the gateway will not find the name of the managed entity in the node_ref_table and will automatically create a new row for the node / gateway combination and allocate it a new node_ref and will then store new information relating to the Managed Entity under the new node_ref. As a result the historic information held with-in the database for that Managed Entity will not be available via the ActiveConsole and is effectively lost.

In order to avoid this, if changing the name of a managed entity, it is necessary to perform the following procedure:

Stop the gateway
Rename the netprobe in the gateway.setup.xml

Change the node name in the node_ref_table

update node_ref_table
set node = "new_node_name"
where node = "old_node_name" and
gateway = "gateway_name"

Restart the gateway

Gateway Database Schema

The EER diagrams below are created using tools on MySQL. There might be slight differences on different databases in terms of variable types and sizes.

The links between tables shown are logical links. Foreign key constraints are not enforced by any of the Geneos database schema.

Schema 1.0

databases12

Schema 1.1

databases13

Schema 1.2

databases14

Schema 1.3

databases15

Schema 1.4

databases16

Schema 1.5

databases17

Schema 1.6

schema1_6

Schema 1.7

schema1_7

Schema 1.8

schema1_8

There is also an optional schema provided for those using time series. This needs to be added in addition to the normal Geneos schema in the same database.

Time series optional tables

databases18