Working with OM Databases

The measurements collected by dsTest are stored in an SQLite database in the directory from which dsTest was launched. The file om.db is always the most current database file and when filled or closed is rotated to om_0.db and a new om.db file is started. This process continues until the maximum number of files is reached, after which the oldest will be discarded. The maximum number of OM files can be specified when launching dsTest (the default is 10). The current database will be closed, and the OM files will rotate, when dsTest is stopped, when the last OM-generating object is deleted, or when the command diag om close is executed.

When an OM database is closed a dsTest utility performs a statistical analysis of the measurement values. While this process is running you will see a <database file name>.lck file in that directory. Copying or downloading the database either before it has been closed or while the lock file is present will corrupt the database. If a database is corrupted, you can attempt to recover it by following the instructions in this FAQ.

SQLite provides both Tcl, Java, and C/C++ interfaces that will allow you to integrate measurement analysis into your automation tool. Refer to the SQLite web site for API details and SQL syntax. SQLite Studio is a cross-platform visual tool that you can use to view the measurement database and execute queries. This topic focuses on the database structure and provides some sample queries.

omIndex - master table of all possible OMs for all applications and interfaces, including the minimum, maximum, and variance of the OMs collected

omObjects - provides unique identifiers (id) for the objects present in the OMs collected and associated with a particular parent (node name)

omIntervals - the timestamps for all OM collection intervals in text and numeric formats

As you can see from the diagram, foreign keys in the omCounts table allow you to join it with any or all of the other tables when executing queries.

omInfo - information regarding the origin of the database file and its sequence if a test spans multiple databases

SQLite supports all of the options available with the SQL SELECT command except for RIGHT OUTER JOIN and FULL OUTER JOIN. If you are new to SQL, the example queries shown below may help to get you started.

In the first interval, all possible measurements for the current configuration are written with their initial values, typically 0. In subsequent intervals, only the measurements with values different from the previous interval are written.

In the examples, SQL keywords are shown in GREEN CAPS and the following aliases are used for table names: t = omIntervals, m = omIndex, c = omCounts, and o = omObjects. The first expression in the WHERE clause (t.id=c.interval...) defines the table relationships and should not be altered, assuming that all tables are used in your query. The other expressions in the WHERE clause may be altered to suit your purpose, and so may the columns selected and the ordering.

SELECT t.time, o.objectType, m.name, c.value FROM omIntervals AS t, omObjects AS o, omIndex AS m, omCounts AS c WHERE (t.id=c.interval AND m.id=c.countId AND o.id=c.object) AND o.name="diameter" ORDER BY t.time, m.name;

Returns the time stamp, object, measurement name, and value for all Diameter object OMs (local socket and peer ) sorted by time stamp and measurement name.

SELECT t.time, o.objectType, m.name, c.value FROM omIntervals AS t, dsObjects AS oomdsIndex AS momdsCounts AS c WHERE (t.id=c.interval AND m.id=c.countId AND o.id=c.object) AND m.type LIKE "ERROR%" ORDER BY t.time, o.objectType, m.name;

Returns the time stamp, object, measurement name, and value for all ERROR_ACCUMULATOR OMs (type begins with "ERROR") sorted by time stamp, object type, and measurement name.

SELECT t.time, o.objectType, m.name, c.value FROM omIntervals AS t, dsObjects AS o, omIndex AS m, omCounts AS c WHERE (t.id=c.interval AND m.id=c.countId AND o.id=c.object) AND (m.type LIKE "EVENT%" AND (t.id BETWEEN 101 AND 200)) ORDER BY t.time, o.objectType, m.name;

Returns the time stamp, object, measurement name, and value for all EVENT_ACCUMULATOR OMs collected in the 101st-200th intervals (the BETWEEN operator is inclusive) sorted by time stamp, object type, and measurement name.

Returns the time stamp, object, measurement name, and value for the Transaction Attempts and Transaction Successful OMs of the Diameter Sh Transaction object sorted by time stamp and measurement name.

SELECT t.id AS Interval, t.time, o.objectType, m.name, c.value FROM omIntervals AS t, omObjects AS o, omIndex AS m, omCounts AS c WHERE (t.id=c.interval AND m.id=c.countId AND o.id=c.object) AND (m.id=245760 OR m.id=245761) AND (t.id IN (8000, 16000, 24000, 32000, 40000)) ORDER BY t.id, m.name;

Returns the interval (in a column titled "Interval"), time stamp, object, measurement name, and value for the Transaction Attempts and Transaction Successful OMs of the Diameter Sh Transaction object collected in the specified intervals (8000, 16000, ...) sorted by interval and measurement name.

A convenience utility is provided with dsTest, db2csv (in /usr/local/devsol/bin/), that converts database measurements to CSV format and calculates the final values for duration and variance OMs. The files will be saved in the directory from which you executed the utility.

db2csv [-d <database>] [-i <starting interval>] [-s <starting object ID>] [-e <ending object ID>] [-l {0-10}] [-m <maximum rows>] {[-c <object>] [-c <object>]...}

The first interval to be converted, relative to the specified database file (default is 0).

Example: -m128000 sets the maximum number of rows to 128000; -m0 sets the maximum number of rows allowed of 262144 (256K).

Identifies an object and/or counter for export. When one of more -c options are specified, only those counters are exported. OMs will be exported to a file named 'db2csv.csv'. Format:

-c :xxxx - summary of all server summary counters in the database for counters with ID xxxx

-c elem:name:elem:name:xxxx - hierarchical identifier of an object (e.g., agw:agw_1:wg:wg:622592)

-c elem::elem::xxxx - hierarchical identifier of an object where object name is defaulted (e.g., aaa::wg::622594)