Database Requirements

Aplication relies on following tables and columns:

• soe
  • ikz - as primary signal identifier in the system (see IKZ requirements)
  • time_stamp - as a time stamp in UTC
  • bin1_val - as a signal value
  • sig_quality - while only value All Okay is considered to be relevant record for the system
  • event_type - as a column classifier
• kks
  • ikz - should match the soe.ikz identifier
  • kks - as a KKS code (forms left part of the Signal Name which is presented to the end-users)
  • sgn - as a signal code (forms right part of the Signal Name which is presented to the end-users)
  • fb_name - as a code of related Functional Area
  • description1 - as a short info shown to the user in the detail page of the app
  • fup_page - as manual page indicator (for matching signal descriptions and documents)
  • tbl_name - as additional Id
  • range_comming - description of signal range (for analog signals) or event coming description (for binary signals)
  • range_going - description for event going (binary signals). Used since v2.0.3 (=43). If not present, column range_comming is used (parenthesis added)
  • cabinet - cabinet info (optional)
  • rack_slot - cabinet&rack info (optional)
  • channel - channel info (optional)
  • ityp_na - HW info (optional)
• bpr_signal - additional signal descriptions, joined with kks using tbl_name
  • units - description of measure units
  • tbl_name - additional signal Id
• ***tbl_**** - tables for particular signals
  • time_stamp - as a time stamp in UTC
  • sig_quality - while only value All Okay is considered to be relevant record for the system
  • event_type - as a column classifier
  • bin1_val- as signal value for binary signals
  • ana_val- as signal value for analogue signals
• imported_tav - this table is not required, but since version 22 (v1.7), the system may use it to read information about new data in database. disk_nr, tav_nr and tav_period are joint into one string and used as unique identifier. See Server App Configuration for more details.
  • disk_nr - text describing the disk#
  • tav_nr- text describing the TAV file#
  • tav_status- any text
  • tav_period- the most important value describing the timerange covered by particular TAV. Should be in format: DD.MM.YYYY HH:mm:ss->DD.MM.YYYY HH:mm:ss
  • conv_time- time of TAV file processing, should be in format YYYY-MM-DD HH:mm:ss.ffffff
  • conv_status- any description
• bpr_typea - since version 22 (v1.7) the system may use this table to gain more infromation about signals. For the moment this info is used only for the purpose of the signalStats calculation command.
  • typea_id1, typea_id2, typea_id3, typea_id4, typea_id5 - this info is matched to the IKZ field of particular signals using following pseudocode: ikz.Contains(".TYPEA_ID2].TYPEA_ID3.")
  • typea_text2 - expected to contain signal description (eg. YU new greater YO)
  • typea_id7 - expected to contain signal type (eg. XQ01)
• bpr_fb - can be used to initialize file groups.xml if this file is missing during Historian startup (otherwise not used)
  • fb_id - signal group id
  • fb_name - signal group name
  • fb_sgn - signal group (short) code

The soe table is required to have an index for the ikz column (performance reasons). On the other hand, for the purpose of this application there is no need to have an index for the column tbl_name (which is being replaced by the aforementioned index.

During some performance tests we have also included a index for the sig_quality column in combination with the time_stamp and ikz. Influence of these indexes on the overall system performance should be evaluated later.

In order to evaluate some queries effectively, the mysql server should be allowed to utilize more resources than by default. Therefore please update MySQL configuration (Debian supposed) as follows:

Into file /etc/mysql/my.cnf - below sections [mysqld] and [mysqld_safe] please insert:

open_files_limit=10000
thread_stack=1048576

Into file /lib/systemd/system/mysql.service - below section [Service] please insert:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

This modification is required for example for queries that searches for boundary values of large number of signals at once (100+ signals). In order to apply the changes, do the following:

sudo systemctl daemon-reload
sudo service mysql restart