Database Metric Visualization

The Automic DB Metric Visualization allows you to have a graphical representation of the performance metrics that you gather for your system. For more information, see System Performance.

It consists of a .zip file that you can download from our download center and import into your Automic Automation environment, thus allowing you to have a Dashboard in the Automic Web Interface which displays charts of your performance metrics. This function can help when troubleshooting possible performance issues.

Prerequisite: Before importing the relevant files into your Automic Automation environment, make sure that your system allows the use of SQLI variables. You can do so in Client 0 by setting the SQLVAR_INTERNAL key of the UC_SYSTEM_SETTINGS variable to YES.

For more information, see:

This page includes the following:

Data Displayed

Once installed, the Dashboard displays the following information:

  • A chart each for:

    • How long the overall performance check takes

    • The time it takes for the INSERT statements

    • The time it takes for ROLLBACKS on the database

    • The time it takes for the FETCH/CLOSE to happen

    • The time it takes for BINDCOLUMN/BINDVALUE to happen

  • The benchmark values that are used for this check displayed as:

    • Ideal Value (green): values near green show that performance is good

    • Must be Reached (yellow): values near yellow are okay, but not optimal

    • System Will Not Scale (red): values beyond the red value show that the system will likely not scale or perform in an optimum fashion

  • Historical information on the performance check that can be used to see trends over time when the performance may have been better or worse.

  • The information icon on each chart shows:

    • What that particular metric looks at

    • What api call is used to reach out to the database

    • What to check if the value is not within the expected range

Downloading the DB Metric Visualization Tool

Follow the instructions below to download the tool:

  1. Go to our download center at https://downloads.automic.com/

  2. In the menu bar at the top of your screen, expand Tools.

  3. Select Automic DB Metric Visualization.

    Note: You can also access the page directly at Automic DB Metric Visualization.

  4. Click the Download DB Metric Visualization button to start the download process. The db-metrics-<version>.zip file is downloaded.

Installing the DB Metric Visualization Tool

Follow the instructions below to install the tool:

  1. If you have not done so yet, log into Client 0 and open the UC_SYSTEM_SETTINGS variable.

  2. Set the SQLVAR_INTERNAL key to YES.

  3. Unzip the db-metrics-<version>.zip file. It contains an XML file (db-metrics-<version>.xml) and a folder containing a Web UI plugin (webui-plugin-analytics.jar), once for every version supported.

  4. Making sure you pick the correct version, move the webui-plugin-analytics.jar to the autoinstall folder in your Automic Automation installation directory.

    Where you can find the directory depends on whether the Automic Web Interface uses Tomcat or Jetty:

    • For Tomcat: <webserver>/webapps/awi/WEB-INF/autoinstall

    • For Jetty: /WebInterface/plugins/autoinstall

    There is no need to restart AWI.

  5. Log into a Client other than Client 0, select the relevant folder and import the db-metrics-<version>.xml file. For detailed information on how to import an XML object, see Importing/Exporting Objects.

  6. In the same folder, run the _AE.DBOPEN.GET.VALUES.FOR.PERF.METRIC.CHARTS script to fill the dashboard charts with data.

    In this version:

    • It is recommended to schedule the script to run hourly two minutes after the hour.

    • The system performs the check precisely on the hour.

    • The (i) icon on each chart provides detailed information on the values presented.

If you want to schedule the script to run hourly, follow these instructions:

  1. Right-click on the script and select Execution Options > Execute Recurring.

  2. In the Period section, define when the execution should start and specify it should never end (End: never).

  3. In the Frequency section, do the following:

    1. Select the Execute: in intervals of every option and set it to one hour.

    2. Set the Between time to 0:58 and 23:59 (FOR 24) and 0:2 and 23:59 (FOR 21).

    3. Select the Initial Start Time - Adjust initial start to a clock-time interval checkbox.

  4. In the Days section, select Execute: weekly and select all days of the week.

For more information about recurring executions, see Executing Objects - Recurring.

What does the Test Perform?

The performance test runs the same four database operations (INSERT / SELECT / ROLLBACK / SELECT) 1000 times and checks how long it took to perform each operation.

Example

These are the SQL's looped in sets of 1000 executions.

Bind parameter used for insert:

DIVDB_PK = 1
DIVDB_String = std::string string_check_utf8 = u8"abcxyz_ABCXYZ_\u00E4\u00F6\u00FC_\u00DF_\u00C4\u00D6\u00DC_\u00E7\u0153_\u00C7\u0152_\u6C34\u51E8\u571F_\U0001F606_\U0001F34C_0123456789";
DIVDB_Memo = std::u16string string_check_utf16 = u"abcxyz_ABCXYZ_\u00E4\u00F6\u00FC_\u00DF_\u00C4\u00D6\u00DC_\u00E7\u0153_\u00C7\u0152_\u6C34\u51E8\u571F_\U0001F606_\U0001F34C_0123456789";
DIVDB_Date = 2001.10.20 23:45:58

Looks within a trace:

20241102/104401.005 - U00009909 TRACE: (DB-DATA: divdb_) 0x7ffcbb396ab0 000255
00000000 61626378 797A5F41 42435859 5A5FC3A4 >abcxyz_ABCXYZ_..<
00000010 C3B6C3BC 5FC39F5F C384C396 C39C5FC3 >...._.._......_.<
00000020 A7C5935F C387C592 5FE6B0B4 E587A8E5 >..._...._.......<
00000030 9C9F5FF0 9F98865F F09F8D8C 5F303132 >.._...._...._012<
00000040 33343536 37383900 00000000 00000000 >3456789.........<
00000050 00000000 00000000 00000000 00000000 >................<
000000F0 00000000 00000000 00000000 000000 >...............<
20241102/104401.005 - Length date :16
20241102/104401.005 - U00009909 TRACE: (DB-DATA: date) 0x7ffcbb396aa0 000016
00000000 D1070A00 14001700 2D003A00 00000000 >........-.:.....<
20241102/104401.005 - Length divd :100
20241102/104401.005 - U00009909 TRACE: (DB-DATA: divd) 0x7ffcbb396bb0 000256
00000000 61006200 63007800 79007A00 5F004100 >a.b.c.x.y.z._.A.<
00000010 42004300 58005900 5A005F00 E400F600 >B.C.X.Y.Z._.....<
00000020 FC005F00 DF005F00 C400D600 DC005F00 >.._..._......._.<
00000030 E7005301 5F00C700 52015F00 346CE851 >..S._...R._.4l.Q<
00000040 1F575F00 3DD806DE 5F003CD8 4CDF5F00 >.W_.=..._.<.L._.<
00000050 30003100 32003300 34003500 36003700 >0.1.2.3.4.5.6.7.<
00000060 38003900 00000000 00000000 00000000 >8.9.............<
00000070 00000000 00000000 00000000 00000000 >................<
000000F0 00000000 00000000 00000000 00000000 >................<

Test's logical flow:

Start performance check loop. Number of iterations: 1000
cnt = 0
StartTimeMain
while cnt LT 1000
- StartTimeBind
-- BindValues for INSERT
- EndTimeBind
- StartTimeInsert
-- INSERT INTO DIVDB (DIVDB_PK,DIVDB_Memo,DIVDB_String,DIVDB_Date) VALUES(1, ?, ?, ?)
-- UCUDB32 INSR RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00054 DB: 0.00054 ODBC: 0.00000 UDB: 0.00000
- EndTimeInsert
- StartTimeCloseStatementHandle
-- UCUDB32 CLST RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00001 DB: 0.00001 ODBC: 0.00000 UDB: 0.00000
- EndTimeCloseStatementHandle
- StartTimeSelect
-- SELECT DIVDB_String, DIVDB_Date, DIVDB_Memo FROM DIVDB where DIVDB_PK = 1
-- UCUDB32 SLCT RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00024 DB: 0.00024 ODBC: 0.00000 UDB: 0.00000
- EndTimeSelect
- StartTimeFetch
-- UCUDB32 READ RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000000000001 ALL: 0.00000 DB: 0.00000 ODBC: 0.00000 UDB: 0.00000
- EndTimeFetch
- StartTimeCloseStatementHandle
-- UCUDB32 CLST RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000000000001 ALL: 0.00001 DB: 0.00001 ODBC: 0.00000 UDB: 0.00000
- EndTimeCloseStatementHandle
- StartTimeRollBack
-- UCUDB32 RBCK RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000000000001 ALL: 0.00033 DB: 0.00033 ODBC: 0.00000 UDB: 0.00000
- EndTimeRollBack
- StartTimeSelect
-- SELECT DIVDB_String, DIVDB_Date, DIVDB_Memo FROM DIVDB where DIVDB_PK = 1
-- UCUDB32 SLCT RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00015 DB: 0.00015 ODBC: 0.00000 UDB: 0.00000
- EndTimeSelect
- StartTimeFetch
-- UCUDB32 READ RET 0001 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00000 DB: 0.00000 ODBC: 0.00000 UDB: 0.00000
- EndTimeFetch
- StartTimeCloseStatementHandle
-- UCUDB32 CLST RET 0000 HSTMT: 0x00000001ff0b90 VALUE: 0x00000001ff0b90 ALL: 0.00001 DB: 0.00001 ODBC: 0.00000 UDB: 0.00000
- EndTimeCloseStatementHandle

- cnt = cnt++

endwhile
EndTimeMain

The result is shown in the LogFile / StandardOut (if ucdbconntest is used). The same is shown every time a c-based CP/WP is started or the hourly build check is executed:

U00003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '100411155'/'2791 (1000/0.358228 s)'

The value is calculated with:

1000 / (EndTimeMain - StartTimeMain (0,358228)) = DbValue (2791)