User defined archive groups

Until now the archive tables in the WinCC OA database had a fixed structure. A table always comprised the same specific columns. Using the user defined archive groups, you can create your own archive groups.

You can create archive table structures according to your needs as well as create flexible and faster database queries.

The archive table structure is created with the help of templates. A template again comprises a data point node that specifies the structure of an archive table. A template is used to create a user defined archive group. This chapter explains how to create and use user defined archive groups.

A license is required for the use of RDB. There are three different options for the license key rdb_archive:

- RDB can be used with archive groups

- RDB can be used without archive groups

- RDB cannot be used at all

Figure 1. Data point Node "Node1" comprising the elements "A" and "C"
Figure 2. Database table comprising the Columns "A" and "C"

A table always contains the columns ELEMENT_ID and TS (Primary keys), as well as the SYS_ID (for partitioning) and BASE (for copying base set from exported archives to the oldest online archive) columns. The individual data point elements are single columns of the database table.

You cannot change user defined archive groups. You can only delete and recreate them. This also applies to the underlying DP structure.

User Defined Archive Groups can only be used when the data is written using Oracle Call Interface (OCCI). For more information on OCCI, see chapter Requirements and installation).

The column names (DP elements) used for user defined archive groups must start with an alphanumeric character (from a to z upper or lower case letters).

The entry "writeWithBulk = 1" has to be set in order to use the user defined archive groups (see also Requirements and installation).

Since a table always contains the elements ELEMENT_ID and TS (primary keys) as well as SYS_ID and BASE you cannot add columns (data point elements) with these names. Additionally, no keywords may be used (a list of words that cannot be used as table names is included below). The database is not case sensitive. Therefore, you cannot add, for example, two columns (two data point elements) called "Id" and "ID" (even though one word is written in upper and the other in lower-case letters).

Initial Letter Keyword
A ACCESS, ADD, ADMIN, AFTER, ALL, ALLOCATE, ALTER, ANALYZE, AND, ANY, ARCHIVE, ARCHIVELOG, AS, ASC, AUDIT, AUTHORIZATION, AVG
B BACKUP, BECOME, BEFORE, BEGIN, BETWEEN, BFILE, BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, BLOB, BLOCK, BODY, BOOLEAN, BY
C

CACHE, CANCEL, CASCADE, CHANGE,

CHAR, CHARACTER, CHECK, CHECKPOINT, CLOB, CLOSE, CLUSTER, COBOL, COLUMN, COMMENT, COMMIT, COMPILE, COMPRESS, CONNECT, CONSTRAINT, CONSTRAINTS, CONTENTS, CONTINUE, CONTROLFILE, COUNT, CREATE, CURRENT, CURSOR, CYCLE
D DATABASE, DATAFILE, DATE, DBA, DEC, DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISABLE, DISMOUNT, DISTINCT, DOUBLE, DROP DUMP
E EACH, ELSE, ENABLE, END, ESCAPE, EVENTS, EXCEPT, EXCEPTIONS, EXCLUSIVE, EXEC, EXECUTE, EXISTS, EXPLAIN, EXTENT, EXTERNALLY
F FETCH, FILE, FLOAT, FLUSH, FOR, FORCE, FOREIGN, FORTRAN, FOUND, FREELIST, FREELISTS, FROM, FUNCTION
G GO, GOTO, GRANT, GROUP, GROUPS
H HAVING
I IDENTIFIED, IMMEDIATE, IN, INCLUDING, INCREMENT, INDEX, INDICATOR, INITIAL, INITRANS, INSERT, INSTANCE, INT, INTEGER, INTERSECT, INTO, IS
K KEY
L LANGUAGE, LAYER, LEVEL, LIKE, LINK, LISTS, LOCK, LOGFILE, LONG
M MANAGE, MANUAL, MAX, MAXDATAFILES, MAXEXTENTS, MAXINSTANCES, MAXLOGFILES, MAXLOGHISTORY, MAXLOGMEMBERS, MAXTRANS, MAXVALUE, MIN, MINEXTENTS, MINUS, MINVALUE, MLSLABEL, MODE, MODIFY, MODULE, MOUNT
N NATURAL, NATURALN, NCHAR, NCLOB, NEW, NEXT, NOARCHIVELOG, NOAUDIT, NOCACHE, NOCOMPRESS, NOCYCLE, NOMAXVALUE, NOMINVALUE, NONE, NOORDER, NORESETLOGS, NORMAL, NOSORT, NOT, NOTFOUND, NOWAIT, NULL, NUMBER, NUMERIC, NVARCHAR2
O OF, OFF, OFFLINE, OLD, ON, ONLINE, ONLY, OPEN, OPTIMAL, OPTION, OR, ORDER, OWN
P PACKAGE, PARALLEL, PCTFREE, PCTINCREASE, PCTUSED, PLAN, PLI, PLS_INTEGER, POSITIVE, POSITIVEN, PRECISION, PRIMARY, PRIOR, PRIVATE, PRIVILEGES, PROCEDURE, PROFILE, PUBLIC
Q QUOTA
R RAW, READ, REAL, RECORD, RECOVER, REFERENCES, REFERENCING, RENAME, RESETLOGS, RESOURCE, RESTRICTED, REUSE, REVOKE, ROLE, ROLES, ROLLBACK, ROW, ROWID, ROWLABEL, ROWNUM, ROWS
S

SAVEPOINT, SCHEMA, SCN, SECTION, SEGMENT, SELECT, SEQUENCE, SESSION, SET, SHARE, SHARED, SIGNTYPE, SIZE, SMALLINT, SNAPSHOT, SOME, SORT, SQL, SQLBUF, SQLCODE, SQLERROR, SQLSTATE, START, STATEMENT_ID, STATISTICS,

STOP, STORAGE, STRING, SUCCESSFUL, SUM, SWITCH, SYNONYM, SYSDATE, SYSTEM
T TABLE, TABLES, TABLESPACE, TEMPORARY, THEN, THREAD, TIME, TO, TRACING, TRANSACTION, TRIGGER, TRIGGERS, TRUNCATE
U UID, UNDER, UNION, UNIQUE, UNLIMITED, UNTIL, UPDATE,UROWID, USE, USER, USING
V VALIDATE, VALUES, VARCHAR, VARCHAR2, VARRAY, VIEW
W WHEN, WHENEVER, WHERE, WITH, WORK, WRITE

How to create a user defined archive group

  1. Check that the useRDBArchive = 1 is set in the config file. User defined archive groups can only be configured for archiving to a database, not for using value archives.

  2. Consider the data point structure and create it via the PARA module. In this example, a data point type "Test" is created. The data point structure contains a root node and two sub elements "Element1" and "Element2". The data point "ArchiveGroup_DP" is created.

The elements of a node containing an archive config cannot be dyn or blob elements and the node must not contain any sub nodes.

User defined archive groups can only be used for DP types containing the same type of DP structure as the template.

  1. Create a template. Open the RDB file selection panel (see below) through System Management -> Database -> RDB archive groups and click on the Manage Templates button.

Figure 3. RDB File Selection Panel

The panel for creating templates opens:

Figure 4. Panel for creating User defined Templates

A template name can contain up to 21 characters.

Create a new template by clicking the New template button. The panel for creating new templates opens. Select the root node of the data point created earlier via the data point selector. The sub elements are shown in the column Table columns and the element types in the Type column. The table index can be used to make the database queries faster. Click on Create. In this example, the template ARCHIVEGROUP_DP_ROOTN has been created.

Note that a maximum of nine indexes can be created for a history table.

Figure 5. Panel for creating a new Template

  1. Open the panel for creating new archive groups from the New archive group button of the RDB file selection panel.

Figure 6. RDB File Selection Panel

The panel for creating new archive groups opens.

A group name can contain up to seven characters.

Figure 7. Panel for creating a new Archive Group

Specify the name for the group. The manager number is the number of the RDB archive manager in the console. Activate the User Defined Archive Group checkbox and select the created template from the combo box. Click on OK. The archive group "Group1" is created and shown in the RDB file selection panel (see figure earlier on).

  1. Open the PARA module. Add an archive config to the root element of the created data point "ArchiveGroup_DP". Select the created archive group "Group1" from the combo box and activate the Store original value checkbox. Click on OK. Check that the RDB archive manager with the number 99 is running in the console.

Figure 8. Data point "ArchiveGroup_DP" with an Archive config

User defined archive groups can only be added to sub nodes (not to main nodes) that do not contain any nodes but only elements (leaves). (see figure above).

_corr.._value or _online.._value cannot be saved since correction values do not exist for user defined archive groups.

  1. A database table with the desired structure is created.

Using the config entry sendUDAGNullValues you can specify whether the Data manager should send

a null (sendUDAGNullValues =1) or the latest value (sendUDAGNullValues =0) to the RDB database when

a single value is changed. This means that when there are several data point elements and the value of

only one element is changed, the value of the other elements is set to the latest value of the element or to zero. Add the sendUDAGNullValues entry to the [data] section of the config file.

The following figure shows values of individual elements (of the group1.History view) for when the config entry sendUDAGNullValues has been set to 0 and for when it was set to 1.

Figure 9. Database Table

The rows 3 and 4 reflect a value change for when the sendUDAGNullValues was set to 0 (the value of a single element has been changed and the value of the other element has been set to the latest value). Row 5 again shows a value change for a single element when sendUDAGNullValues was set to 1. The value of the other element has been set to zero.

When using User Defined Archive Groups note that _original.._status and _original.._text do not exist in the RDB since the columns do not exist in the Oracle table.

Queries

For external queries of the RDB (not through WinCC OA), all time stamps are saved in UTC.

Note that the query functions such as dpGetPeriod do not work for user defined archive groups. To query

the data, use either ADO or the function runRealSQLQuery as shown below. If you query data

of user defined archive groups, the history view [archiveGroupName].History has to be selected.

Alarms with or without alarm configuration can not be shown in the alert or event panel.

The function runRealSQLQuery uses the config entries of the valueArchiveRDB section ("DbUser", "DB" and "DbType". See chapter Possible config entries for RDB archiving). The password has to be entered using the RDB panels.

main()
{
  dyn_dyn_anytype ret;
  DebugN(runRealSQLQuery("select
  el.element_name,h.element_id,h.ts, h.Element1, 
  h.Element2 from elements el, GROUP1HISTORY h where 
  el.element_id = h.element_id order by h.ts,h.element_id desc",ret));
  for (int i = 1; i <= dynlen(ret); i++)
  {
    string val = ret[i];
    DebugN(val);
  }
}

/* Selects the element name, element ID, the time stamp as well as the elements "Element1" and "Element2" (Data point elements of the data point that was used for the template. See the beginning of this chapter) from the elements table "el" and [archiveGroupName].h table = "GROUP1HISTORY h" where the element ID of the elements table = element ID of archiveGroupName table. Order by time stamp, element Id). The "GROUP1HISTORY" is the name of the user defined archive group.

The elements table contains all data point elements of the WinCC OA project including additional information. For detailed information of the elements table, see chapter Description of tables and views.

The structure of the [archiveGroupName] h table depends on the structure of the template and on the data point that was used to create the template. The table, however, always contains the columns
ELEMENT_ID and TS (time stamp), which are
                                        primary keys as well as the columns SYS_ID (for
                                        partitioning) and BASE (for copying base set from exported
                                        archives to the oldest online archive). Additionally, the
                                        table contains the columns:
A
RCHIVE# = The number of the archive set
ROWID = The ID of the row

The function runRealSQLQuery works like dpGetPeriod. The advantage is that

you do not need to enter a password to identify yourself like when using ADO

*/

In order to use the runRealSQLQuery function, you have to add the CtrlRDBArchive.dll to the [ctrl] section of the config.level file.

You cannot use the query "SELECT *".

Clean the RDB schema after failed UDAG creation manually

  1. 0 is returned for a successful operation: The group has been created and also the first archive in the group.

  2. The first archive in a group gets the default start time 1.1.1970.

  3. If the group cannot be created at all a -1 is returned.

  4. If the group was created but not the first archive, a -2 is returned. Check the ARC_LOC table. Detailed error messages are shown in the table.

  5. If you want to delete the group you can do so by running the DB function "ArchiveControl.DeleteArcGroup" directly from the database.

  6. If the initial time of the first archive was not set to 1.1.1970 a -3 is returned. This could cause problems later when entering or reading values. Check the ARC_LOC table. Detailed error messages are shown in the table.

  7. If you want to delete the archive you can do so by running the DB-function "ArchiveControl.DeleteArcGroupTable" directly from the database.

  8. If the whole group should be deleted, run "ArchiveControl.DeleteArcGroup"

Save Strings containing more than 4000 characters in the Database

Requirements

  • You can save strings that contain ore than 4000 characters only in user defined archive groups.

  • You have to set the entry maxStringUDAG in the ARC_CONFIG table. The default value is 4000. Set the value to a multiple of 4000 (for example 8000, 20000, 32000, …)

  • The maximum string size is 64000 characters.

  • You cannot convert existing UDAG tables.

In order to configure strings, open the RDB Setup and configuration of User Defined Templates panel through the System management panel -> Database tab -> RDB Archive Groups -> Manage Templates button -> New Template

Figure 10. User Defined Templates with the Option LongString

You can specify for each database column of type string whether it should be an extended string by checking the check box "LongString". In the database the following columns will be created for maxStringUDAG=12000:

LONGTEXT Varchar2(4000),

LONGTEXT_1 Varchar2(4000) and

LONGTEXT_2 Varchar2(4000)

The length of the column name is restricted to 27 characters. The LongString will be divided into 4000 byte columns.

Since there is no RDB query function for the UDAG's, you have to query the columns yourself (for example „Select LONGTEXT || LONGTEXT_1 from UDAG where ….“).