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.
 NOTE
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: Data point Node "Node1"
comprising the elements "A" and "C"
Figure: 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.
 CAUTION
You cannot change user defined archive groups.
You can only delete and recreate them. This also applies to the
underlying DP structure.
CAUTION
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).
CAUTION
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).
CAUTION
The entry "writeWithBulk = 1" has
to be set in order to use the user defined archive groups (see
also Requirements and installation).
The user defined archive groups cannot be used under Solaris.
CAUTION
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).
"
ACCESS ADD ADMIN AFTER ALL ALLOCATE ALTER ANALYZE "
"
AND ANY ARCHIVE ARCHIVELOG AS ASC AUDIT AUTHORIZATION
"
"
AVG BACKUP BECOME BEFORE BEGIN BETWEEN BFILE BINARY_DOUBLE
"
"
BINARY_FLOAT BINARY_INTEGER BLOB BLOCK BODY BOOLEAN "
"
BY 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 DATABASE DATAFILE DATE DBA DEC DECIMAL "
"
DECLARE DEFAULT DELETE DESC DISABLE DISMOUNT DISTINCT
"
"
DOUBLE DROP DUMP EACH ELSE ENABLE END ESCAPE EVENTS "
"
EXCEPT EXCEPTIONS EXCLUSIVE EXEC EXECUTE EXISTS EXPLAIN
"
"
EXTENT EXTERNALLY FETCH FILE FLOAT FLUSH FOR FORCE "
"
FOREIGN FORTRAN FOUND FREELIST FREELISTS FROM FUNCTION
"
"
GO GOTO GRANT GROUP GROUPS HAVING IDENTIFIED IMMEDIATE
"
"
IN INCLUDING INCREMENT INDEX INDICATOR INITIAL INITRANS
"
"
INSERT INSTANCE INT INTEGER INTERSECT INTO IS KEY "
"
LANGUAGE LAYER LEVEL LIKE LINK LISTS LOCK LOGFILE "
"
LONG MANAGE MANUAL MAX MAXDATAFILES MAXEXTENTS MAXINSTANCES
"
"
MAXLOGFILES MAXLOGHISTORY MAXLOGMEMBERS MAXTRANS "
"
MAXVALUE MIN MINEXTENTS MINUS MINVALUE MLSLABEL MODE "
"
MODIFY MODULE MOUNT 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 OF OFF OFFLINE OLD ON ONLINE ONLY OPEN "
"
OPTIMAL OPTION OR ORDER OWN PACKAGE PARALLEL PCTFREE "
"
PCTINCREASE PCTUSED PLAN PLI PLS_INTEGER POSITIVE "
"
POSITIVEN PRECISION PRIMARY PRIOR PRIVATE PRIVILEGES "
"
PROCEDURE PROFILE PUBLIC QUOTA RAW READ REAL RECORD "
"
RECOVER REFERENCES REFERENCING RENAME RESETLOGS RESOURCE
"
"
RESTRICTED REUSE REVOKE ROLE ROLES ROLLBACK ROW ROWID
"
"
ROWLABEL ROWNUM ROWS 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 TABLE TABLES TABLESPACE TEMPORARY "
"
THEN THREAD TIME TO TRACING TRANSACTION TRIGGER TRIGGERS
"
"
TRUNCATE UID UNDER UNION UNIQUE UNLIMITED UNTIL UPDATE
"
"
UROWID USE USER USING VALIDATE VALUES VARCHAR VARCHAR2
"
"
VARRAY VIEW WHEN WHENEVER WHERE WITH WORK WRITE "; |
How to create a user defined archive group
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. 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.
CAUTION
The elements of a
node containing an archive config cannot be dyn or blob
elements and the node must not contain any sub nodes.
CAUTION
User defined archive
groups can only be used for DP types containing the same type
of DP structure as the template.
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: RDB File Selection Panel
The panel for creating templates opens:
Figure: Panel for creating User
defined Templates
CAUTION
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.
CAUTION
Note that a maximum of nine indexes can be created
for a history table.
Figure: Panel for creating a new
Template
Open the panel for creating
new archive groups from the
New archive group 
button of the RDB file selection panel.
Figure: RDB File Selection Panel
The panel for creating new archive groups opens.
CAUTION
A group name can contain up to seven characters.
Figure: 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).
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: Data point "ArchiveGroup_DP"
with an Archive config
CAUTION
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).
CAUTION
_corr.._value
or _online.._value cannot be
saved.
A database table with the
desired structure is created.
NOTE
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: 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.
CAUTION
When using User Defined Archive Groups, _original.._status and _original.._text
do not exist in the RDB.
Queries
CAUTION
For external queries of the RDB (not through
WinCC OA),
all time stamps are saved in UTC.
NOTE
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.
CAUTION
Alarms are not shown (the alert panel cannot
be used to view alarms).
NOTE
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:
ARCHIVE#
= 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
*/
|
|
CAUTION
In order to use the runRealSQLQuery function,
you have to add the CtrlRDBArchive.dll to the CTRL section of
the config.level file.
CAUTION
You cannot use the query SELECT
*.
Clean the RDB schema after failed UDAG creation manually
0 is returned for a successful
operation: The group has been created and also the first archive
in the group. The first archive in a group
gets the default start time 1.1.1970. If the group cannot be created
at all a -1 is returned. If the group was created
but not the first archive, a -2 is returned. If you want to delete the
group you can do so by running the DB function "ArchiveControl.DeleteArcGroup"
directly from the database. If the initial time of the
first archive was not set to 1.1.1970 a -3 will be returned.
This could cause problems later when entering or reading values. If you want to delete the
archive you can do so by running the DB-function "ArchiveControl.DeleteArcGroupTable"
directly from the database. 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: 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 ….“). |
