Possible config entries for RDB archiving

This section describes the possible config entries in connection with archiving values and alerts from WinCC OA to a relational database. In order to make the RDB Archive Manager fully functional, you must configure entries in the sections [general] and [ValueArchiveRDB].

In addition, optional entries in the [ui] and [ctrl] sections can accelerate database queries.

Entries in the config file in the [general] section

Entry Type Default Range Description
useRDBArchive int 0 0|1
  • 0 = RDB Archive Manager not functional
  • 1 = RDB Archive Manager activated (RDB write/read)

Entries in the config file in the [ValueArchiveRDB] section

Entry Type Default Range Description
alertUpdateDelay int 300 0 - 600

If alarms where the pre-alarm is included in an already exported archive set occur, the database tries to reach the old alarm for five minutes. Thereby, alarms are possibly held back and the alarms are not written to the database. To prevent this and to reduce this time e.g. to 30 seconds use [ValueArchiveRDB] "alertUpdateDelay = 30".

Note: The value should not be higher than 300
bufferToDisk int 1 0 - 2

Specifies the storage mode, with which the data blocks are buffered, before they will be written into the database.

  • bufferToDisk = 0

    Without BufferToDisk: in case of a disconnect to the RDB, the data blocks are buffered in RAM and accordingly they are written into the database.

    Note: Data will get lost in case no free memory is available
  • bufferToDisk = 1

    BufferToDiskMin (default)

  • bufferToDisk = 2

    BufferToDiskMax

bufferToDiskDir string "<project_path>\db\buffer" -

Absolute path to the directory, in which the data blocks are buffered onto the HDD using bufferToDisk = 1|2.

If the default directory is used the directory buffer will be created automatically.

If you use an individual storage place, you have to enter the absolute path, e.g. "D:\BufferToDisk\Files".

DbUser string "" - The database user
DbType string "ORACLE", "Access", "SQL" "ORACLE" Type of database; currently only "ORACLE" is implemented.
Db string "" - The database instance
DbPass string "" -

Database password.

Note: It is to consider that this config entry will only be used for the first start. Afterwards the password will be get only from a corresponding data point.
delayAfterDBRestart int 30 -

Timely delay of the RDB initializing process in seconds after database start.

If the database was stopped and is restarting again, it may happen that although the connection between RDB and the database has been established, the database start-up process has not been complemented yet. With this config entry RDB awaits the defined time before initialization of the connections and thus begins to write data into the database.

By default this config entry is set to 30 seconds.

In case of a database connection loss or if a connection will be opened/closed via the "closeDBConnection" and "openDBConnection" internal data point elements, this delay is taken into account.

In case of a RDB manager start this delay is not used.

queryOverId bool 1 0|1

Indicates the mode of database read queries:

  • 0 = data point name or DPE name; although this option impacts on performance, it does allow you to access the data in the Oracle database from a different WinCC OA project.
  • 1 = IDs (faster)
initialEntriesInBlock int 50 - Specifies the size of the first block in the buffer after a reconnect of the RDB to the Oracle DB. All further blocks in the buffer possess the defined size from the data buffer in the RDB manager panel.
lostConnectionReportInterval float 15.0 >= 0.0 Specifies that a flag is set when the connection to the database is lost and RDB tries to build a connection to the database. In this way (via the flag), the user knows that the connection is lost. The flag is set after "lostConnectionReportInterval (seconds) ".
maxRequestLineCount int 0 0 - maxInt Limits the size of queried data (dpGetPeriod, alertGetPeriod, dpQuery) to max. "x" return lines (0 = no limit). If this limit is exceeded an error (and no data) will be returned.
maxRequestThreads int 4 0 - 4

Number of threads (and also connections to the database) the RDB manager uses to (parallel) read (query) operations in the database.

When using queryRDBdirect = 1, the read connection for the direct DB access is not affected by this setting (maxRequestThreads can be set to 0 in this case).

openConnOnDemand bool 0 0|1

RDB manager only opens a single write connection to the database, all other connections (update, deletion, info) will be opened (and closed afterwards) when necessary. Performance is worse in comparison to leaving all connections open all the time (openConnOnDemand = 0).

Number of read connections is not affected by this setting (use maxRequestThreads instead). When using queryRDBdirect = 1 the read connection for the direct DB access is not affected by this setting. The openConnOnDemand entry is required for large, distributed systems when several RDB managers write into the same database. Otherwise Oracle requires too much memory since too many connections are open simultaneously.

updateConnCloseDelay float 180 0 - 32767

An additional DB connection is established for updates. Is only used if openConnOnDemand = 1 (see above) ,

This update connection is closed again updateConnCloseDelay seconds after its last use. Default = 180 (3 minutes).

oracleClientVersion int 11 >= 11 Specifies the Oracle client version.
queryOverBounds int 1 0|1

Specifies whether the function dpGetPeriod() should query outside the specified period (the parameter "Count" of the function dpGetPeriod ) or not. If the parameter "count" of the function is bigger than 0, also values outside (this means before and after) the queried period are queried. This may sometimes take longer. queryOverBounds = 1 means that values are queried outside the specified period.

This works with queryRDBdirect = 1 only then, when one data point element is queried at the same time. queryOverBounds = 0 queries only values within the period. Thus, the query is faster.

queryTimeout int 0 0 - 32767

Aborts database queries after <queryTimeout> seconds.

If you set the value to 0, the queries are not cancelled.

queryFunction bool 0 0|1

With the config entry queryFunction = 1 a database function is used for dpGetPeriod() instead of a query. Therefore, the limitations such as the number of tables and the length of the SQL statement cease to exist. With queryFunction = 0, a query is used as so far.

The entry can be used in ValueArchiveRDB and UI sections.

Note:

Please note that this entry is not intended for use in regular projects, but can only be used for specific, proprietary database schemas.

Use in different projects means that correct functioning can no longer be guaranteed.

redirectArcGroup string - <AR_FROM> :<AR_TO>

The Config entry allows to specify, if an archive group is mapped to an other archive group to prevent the creation of a new archive group and therefor improving the overall performance.

Example

redirectArcGroup = VA10 :QPS

The archive group VA10 is redirected to the archive group QPS

redirectArcGroup = VA* :EVENT

The archive groups with a name beginning with "VA" are redirected to the EVENT archive group.

sendMaxTS bool 1 0|1
  • sendMaxTS = 1

    The RDB manager gets the highest time stamp from the database and synchronizes the data with the Data manager. Thus, the Data manager sends all value changes that are newer than the time stamp from the database, to the RDB manager.

  • sendMaxTS = 0

    The RDB manager does not get the time stamp from the database and does not synchronize the data. This improves the performance but the last value change until the RDB start is not saved in the Oracle database.

  • sendMaxTS = 2

    The latest time stamp per system is saved in the SYSTEMS table and updated with every block insert. When the RDB manager is started, the time stamp is read from the SYSTEMS table. This is a quick read operation that allows you to query the last time stamp of your system.

SQLPreFetchCount int 1000 >= 0 Sets the number of rows to be buffered by the Oracle Client libraries after a successful query call and for each subsequent internal fetch request to the database. For queries returning a large number of rows, performance can be significantly improved by increasing the prefetch count.
writeTimeout int 15 0 - 32767 When executing an INSERT or UPDATE statement in the database, the RDB manager waits writeTimeout seconds for the completion of the command. A reconnect to the database will be done when this timeout expires.
writeWithBulk bool 1 0|1
  • 1 = Write data to RDB archives using OCI Oracle Call Interface (OCCI). To activate the OCI bulk writing, set this config entry to 1. This improves the performance.
  • 0 = Do not use OCI.
APMDbUser string 0 0|1

Additionally, there are own APM query functions, which can be used to query another RDB database. The functions use the DPE name instead of the element ID for the query. For the APM query functions see chapter Direct read functions.

APMDbUser = User of the database.

APMDbType string "" "ORACLE", "Access", "SQL" Type of database; only "ORACLE" implemented at the moment.
APMDbDb string "" - The database instance.

Optional entries in the config file in the [ui] and [ctrl] sections

Entry Type Default Range Description
queryRDBdirect bool 0 0|1

Indicates the mode of database write queries:

0 = Standard CTRL read functions (dpGet...()) via the WinCC OA Event Manager and the WinCC OA Data Manager.

1 = The standard CTRL read functions (dpGet...()) are redirected to the direct read functions in the Control add-on.

Note that the two required CTRL DLLs must also be loaded in order to be able to use the RDB read functions (see next line).

CAUTION:

If you use queryRDBdirect = 1 and query DPs using the event screen, enter the DPs or *.* for the query. Otherwise the event screen does not show the DPs.

CtrlDLL="CtrlRDBArchive"

CtrlDLL="CtrlRDBCompr"

Note: To use queryRDBdirect inside a distributes system, the database schemata must be configured with additional database links (see Configuration of DB Links).

Entries in the [data] section of the config file

Entry Type Default Range Description
statFctInitInterval uint 7200 0 - 7200

Specifies (when using RDB) the time how long after an archive is finished, value changes of static data points that are dated back (can be detected via time stamp milliseconds = 0) are still sent to the archive when the archive is restarted. If an archive is e.g. finished at 14:30 , the Data Manager normally sends all last values with a time stamp after 14:30 to a new starting archive.

For statistical data points, however, all last values are sent with the time stamp after 12:30 (7200) and thus sent to the old archive (since statistical functions are calculated delayed and the last calculated value could otherwise get lost.).

Maximum value: 2 hours (7200) old values are sent to the old archive.

Possible error messages when using RDB archiving

WCCOArdb (97), 2005.02.16 01:06:24.322, PARAM,SEVERE, 0 , Could not find a valid Archive Set in DB for MCC:10_DS007_TT_6101.In.disvalue.value (Type: 207 Sys: 1 Dp: 65296 El: 6 Conf: 0 Det: 0 Attr: 0x0) 

In this case, the RDB manager does not find a valid archive group to which the data can be written.

WCCOArdb (97), 2005.02.16 01:43:05.911, SYS, INFO, 0, , No group found for , MCC:23_DS001_PT_6101.In.disvalue.value WCCOArdb (97), 2005.02.16 01:43:05.912, SYS, SEVERE, 0, , MCC:23_DS001_PT_6101.In.disvalue.value :Could not insert this one! I will try it again.

This is a subsequent error of the error above. The archive group is not known. Values cannot be added.

WCCOArdb (97), 2005.02.26 08:29:48.262, SYS, INFO, 0, , Couldn´t find a valid Archive Set in the DB for , > MCC:10_LVS121_COP01S01.In.state:_original.._value, createEventStatement

In this case, the RDB manager does not find a valid archive group and cannot read data from a group.

WCCOArdb (97), 2005.02.23 16:28:28.957, SYS, INFO, > 0, , DpName:

WCCOArdb (97), 2005.02.23 16:28:28.957, SYS, INFO, > 0, , DpName: MCC:10_PS004_PT_1201.In.disvalue.value, ElementID: 772305585921.000000, Time stamp: 2005.02.23 16:21:36.955, DB Error: ORA-02291:  ¥·´ÍêÕûÔ¼ÊøÌõ¼þ (WESTEAST.FK_TRENDLASTVAL_ELEMEN) - δÕÒµ½¸¸Ïî¹Ø¼ü×Ö, Check the values in the table Elements. Maybe the Datamanager did not send the DP Info to me.

This message describes that an entry is missing in the Element table. This could be the reason for the error above.

writeBlockfailed - Fehler -300 -400 etc. (z.B. Bulk data NOT written into database!, ErrorCode: -300)

Errors -300 -400 etc: In error case (not in case of unique constant violations), the return code is multiplied by 100. This means that if, for example, 3 rows could not be imported correctly, the return value is -300 and in case of 21, it is -2100. The error source always remains the same but the number of lines that are not OK changes with each bulk insert.

Debug and Report Flags

Debug Flag Description
RDB_CONN Outputs information about opening and closing a connection to the database.
RDB_INPUT Debug outputs are triggered for every received value.
Report Flag Description
CONNLIST Outputs information whether a connection to the database is opened or closed.