Description of the Tables and Views
The following section gives an overview of the tables and views used by the RDB Archive Manager for the RDB compression created during installation.
Tables
In the following the tables, in which the process values for the RDB compression can be found, are described. They are responsible for proper operation of the RDB compression, logging processes, and managing the various compression calculations, intervals and steps.
The following tables are available:
The primary keys of the specific tables are highlighted bold.
CSCALCULATION table
The CSCALCULATION table contains the compression calculations for calculation of compressed values.
Column | Description | Data Type |
ELEMENT_ID | Primary key for data point element identification for the compression. This is a 25 digit number with the following syntax: STEP_ID<is filled with 0s if not 25 digits long>CSBASE_ID | NUMBER(25,0) |
STEP_ID | Provides the unique identification of the compression step in the CSSTEPS table. | NUMBER(5,0) |
CSBASE_ID | Provides the unique identification of the compression data point element in the ELEMENTS table. | NUMBER(25,0) |
LASTVAL | Stores the timely last vale of the previous intervals. NULL if this is not known (due to performance reasons it is used for average calculation). | NUMBER |
LASTVALPAST | Stores the timely last vale of the previous intervals. Applies for calculations performed afterwards - else the same applies as for LASTVAL. | NUMBER |
ACTIVE | ‘1’ or ‘0’. Specifies whether the compression function is active and thus it is calculated. Thereby should be noted that the appropriate compression step and interval are also active. | NUMBER(1,0) Default ‘1’ |
CSFUNCTION table
The CSFUNCTION contains the calculation functions for the calculation of the compressed values.
Column | Description | Data Type |
FUNCTION_ID | Primary key: fixed assigned. Unique identification number of the function. | NUMBER(2,0) |
FUNCTION_NAME | Name of the function like used in Oracle-SQL. | VARCHAR2(40 Byte) |
The content is fix-defined (for details on the available functions see Function paragraph).
FUNCTION_ID | FUNCTION_NAME |
1 | SUM |
2 | MIN |
3 | MAX |
4 | AVG |
5 | AVG_INTEGRAL |
6 | DIFF |
7 | DIFFABS |
CSHISTORY tables
The CSHISTORY tables result from the defined intervals. For every interval a history table is created (see also CSINTERVAL).
The syntax for the name of such a CSHISTORY table is as follows:
CSHISTORY_<interval>_<consecutive number>
Examples of possible history tables:
-
CSHISTORY_5MIN_03905014 -> 5 minutes interval
-
CSHISTORY_1HOUR_03905026 -> 1 hour interval
-
CSHISTORY_1DAY_03905018 -> 1 day interval
-
CSHISTORY_2WEEK_03905012 -> 2 weeks interval
-
CSHISTORY_1MON_03905015-> 1 month interval
-
CSHISTORY_1YEAR_03905009 -> 1 year interval
The layout of each CSHISTORY table is the same.
Column | Description | Data Type |
ELEMENT_ID | ID of the data point element, whose values have been compressed. | NUMBER(25,0) |
TS | Time stamp. Marks the end of the interval, for which the compression took place. | TIMESTAMP(9) |
VALUE | Compressed value. | NUMBER |
CSBASE_ID | Provides the unique identification of the compression data point element in the ELEMENTS table. | NUMBER(25,0) |
STATUS | Value status. The WinCC OA "_original.._status" attribute. | NUMBER(20,0) |
USER | The WinCC OA "_original.._user" attribute. | NUMBER(5,0) |
TEXT | The WinCC OA "_original.._text" attribute. | VARCHAR2(4000 Byte) |
MANAGER | The WinCC OA "_original.._manager" attribute. | NUMBER(20,0) |
SYS_ID | WinCC OA ID of the system from the SYSTEMS table. | NUMBER(20,0) |
BASE | Last value (dregs) from the last backed-up time range. | NUMBER(1,0) |
CSINTERVAL table
In the CSINTERVAL table the information regarding compression intervals is stored. For each interval the following information must be given:
-
An Oracle job, which initiates the calculation of the compression steps for the appropriate interval.
-
A synonym with the syntax CSHISTORY_FactorUnit_WRITE, whereby the factor is the length of the interval and the unit is the code from the CSUNIT table. The synonym points to the current table for the interval.
-
The current CSHISTORY table with the syntax CSHISTORY_ ValueUnit_consecutiveNumber.
-
The Oracle job is created with every interval as well as the synonym and the history table.
Column | Description | Data Type |
INTERVAL_ID | Primary key. Unique identification number of an interval. | NUMBER(5,0) |
STARTTIME | Time for the start time of the interval (in UTC). | TIMESTAMP(9) |
UNIT_CODE | Indicates the unit, which is used for the interval. Reference to the CSUNIT table. | VARCHAR2(4 Byte) |
GROUP_NAME | The interval name (results from the combination <UnitxFactor>). | VARCHAR2(7 Byte) |
JOB_ID | Internal job ID of the interval job (Syntax: J_<UnitxFactor>). | VARCHAR2(30 Byte) |
LENGTH | Indicates the factor, which is used for the interval. | NUMBER(3,0) |
JOBDELAY |
Delay in seconds. This is used in 2 cases:
Calculation: Logarithm with base 2 of the interval length in minutes produces delay in seconds. |
NUMBER(20,0) |
SIZE_ORDER | Indicates the size of the interval. This complies to the number of minutes in an interval. | NUMBER(10,0) |
ACTIVE | ‘1’ or ‘0’. Defines whether the interval is used ('1'). | NUMBER(1,0) Default ‘1’ |
LASTCALC | End time of the last calculation of compressions that has been performed (in UTC). | TIMESTAMP(9) |
DESCRIPTION | Description of the compression interval - composed of the factor and the unit (written out). | VARCHAR2(64 Byte) |
Examples of compression intervals (extract from the CSINTERVAL table):
INTERVAL _ID |
STARTTIME |
GROUP _NAME |
JOB_ID | LENGTH | JOBDELAY |
SIZE _ORDER |
ACTIVE | LASTCALC | DESCRIPTION |
1 |
18.11.09 13:48:00, 000000000 |
5MIN | J_5MIN | 5 | 2 | 5 | 1 |
23.11.09 11:58:00, 000000000 |
5 Minutes |
2 |
18.11.09 13:50:00, 000000000 |
6MIN | J_6MIN | 6 | 3 | 6 | 1 |
23.11.09 11:56:00, 000000000 |
6 Minutes |
3 |
18.11.09 14:00:00, 000000000 |
1HOUR | J_1HOUR | 1 | 6 | 60 | 1 |
23.11.09 11:00:00, 000000000 |
1 Hour |
4 |
18.11.09 23:00:00, 000000000 |
1DAY | J_1DAY | 1 | 10 | 1440 | 1 |
18.11.09 23:00:00, 000000000 |
1 Day |
5 |
29.11.09 23:00:00, 000000000 |
1WEEK | J_1WEEK | 1 | 13 | 10080 | 0 |
29.11.09 23:00:00, 000000000 |
1 Week |
6 |
30.11.09 23:00:00, 000000000 |
1MON | J_1MON | 1 | 15 | 43830 | 1 |
30.11.09 23:00:00, 000000000 |
1 Month |
7 |
31.12.09 23:00:00, 000000000 |
1YEAR | J_1YEAR | 1 | 19 | 525960 | 0 |
31.12.09 23:00:00, 000000000 |
1 YEAR |
CSSTEP table
In the CSSTEP table the information on compression steps are stored.
Column | Description | Data Type |
STEP_ID | Primary key. Provides the unique identification of the compression step. | NUMBER(5,0) |
NAME | Name of the compression calculation. Syntax: <name_of_the_compression_interval>_<compression_function>. | VARCHAR2(49 Byte) |
ACTIVE | ‘1’ or ‘0’. Defines whether the compression step is used ('1'). | NUMBER(1,0) Default ‘1’ |
INTERVAL_ID | The identification number of the interval from the CSINTERVAL table. | NUMBER(5,0) |
FUNCTION_ID | The identification number of the function from the CSFUNCTION table. | NUMBER(2,0) |
CSTIMELOG table
Table for performance checks . Activities (calculations, program cycles, interval cycles, etc.) are logged with start time, end time and duration.
Column | Description | Data Type |
STARTTS | Start time. | TIMESTAMP(3) |
ENDTS | End time. | TIMESTAMP(3) |
DURATION_IN_SEC | Duration of the activity (=end time - start time). | NUMBER |
DESCRIPTION | Description of the activity. | VARCHAR2(512 Byte) |
INFO | Additional information. | VARCHAR2(512 Byte) |
TRACE_LEVEL | Trace_Level of the specific activity. The higher the trace level the more important the specification. The trace level can be changed in the config (ARC_CONFIG table) and thus the number of defined activities can be controlled. | NUMBER(38,0) |
There are following trace levels available (defined in the ARC_CONFIG table; cs_tracelevel parameter):
0 | No tracing. |
1 | Time duration of an interval calculation cycle. |
2 | Occurrence times of alarms (ALERT table) and all entries of lower trace levels. |
3 | Occurrence of job delays (because of synchronization of "shorter" intervals) and all entries of lower trace levels, time needed for interval, steps and functions creation. |
4 | Duration of an individual compression calculation and all entries of lower trace levels. |
CSUNIT table
In the CSUNIT table the time units for the intervals are stored. The UNIT table has a fixed content.
Column | Description | Data Type |
UNIT_CODE | Defines the unit, which is used for the interval. | VARCHAR2(4 Byte) |
UNIT_ID | Unique identification number for the interval unit (fix assigned). | NUMBER(1,0) |
DESCRIPTION | Description of the time unit as used in Oracle in the time formatting. | VARCHAR2(400 Byte) |
FORMAT | Format of the time unit as used in Oracle in the time formatting. | VARCHAR2(4 Byte) |
FREQ | The frequency, in which the unit of the interval is present. | VARCHAR2(20 Byte) |
The available columns contain the following fixed values:
UNIT_ID | CODE | DESCRIPTION | FORMAT | FREQ |
1 | MIN | Minute | MI | MINUTELY |
2 | HOUR | Hour | HH24 | HOURLY |
3 | DAY | Day | DD | DAILY |
4 | WEEK | Week | null | WEEKLY |
5 | MON | Month | MM | MONTHLY |
6 | YEAR | Year | YYYY | YEARLY |
Views
The following default views for RDB compression are available for external access (e.g.: with Crystal Reports). They contain the WinCC OA attributes that are typically of interest for queries. Of course, own views can be created that allow custom groups of attributes.
This paragraph describes the following views:
CSHISTORY_<interval_name>
For each created interval group (interval names, factor multiplied by the unit) a history view is created, which logs all activities of the group.
Column | Description | Data Type |
ELEMENT_ID | Identification number of the data point element for the compression. A 25-digit number with the following syntax: STEP_ID<filled with zeros if not 25-digit long>CSBASE_ID | NUMBER(25,0) |
TS | Time stamp. Marks the end of the interval, for which the compression took place. | TIMESTAMP(9) |
VALUE | Compressed value. | NUMBER |
CSBASE_ID | Provides the unique identification of the compression data point element in the ELEMENTS table. | NUMBER(25,0) |
STATUS | Value statu. The WinCC OA "_original.._status" attribute. | NUMBER(20,0) |
USER | The WinCC OA "_original.._user" attribute. | NUMBER(5,0) |
TEXT | The WinCC OA "_original.._text" attribute. | VARCHAR2(4000 BYTE) |
MANAGER | The WinCC OA "_original.._manager" attribute. | NUMBER(20,0) |
SYS_ID | WinCC OA ID of the system from the SYSTEMS table. | NUMBER(20,0) |
BASE | Last value (dregs) from the last backed-up time range. | NUMBER(1,0) |
ARCHIVE | Sequence number of the appropriate interval from the CSHISTORY_<interval>_<sequence number>table. | NUMBER |
VCSCALCULATION
In this table all appropriate current values of a compression calculation are logged.
Column | Description | Data Type |
ELEMENT_ID | Identification number of the data point element for the compression. A 25-digit number with the following syntax: STEP_ID<filled with zeros if not 25-digit long>CSBASE_ID | NUMBER(25,0) |
ELEMENT_NAME | Name of the current compression calculation. | VARCHAR2(4000 BYTE) |
GROUP_NAME | The appropriate interval name (results from the combination <UnitxFactor>). | VARCHAR2(7 BYTE) |
FUNCTION_NAME | Name of the appropriate calculation function. | VARCHAR2(40 BYTE) |
STEP_ID | Identification number of the appropriate compression step. | NUMBER(5,0) |
CSBASE_ID | Identification number of the appropriate compression data point element from the ELEMENTS table. | NUMBER(25,0) |
LASTVAL | Stores the timely last value of the previous interval. NULL if it is not known (due to performance reasons it is used for average calculation). | NUMBER |
LASTVALPAST | Stores the timely last vale of the previous intervals. Applies for calculations performed afterwards - else the same applies as for LASTVAL. | NUMBER |
CALCULATION_ACTIVE | Indicates whether the appropriate compression calculation is active (1). | NUMBER(1,0) |
STEP_ACTIV | Indicates whether the appropriate compression step is active (1). | NUMBER(1,0) |
INTERVAL_ACTIVE | Indicates whether the appropriate compression interval is active (1). | NUMBER(1,0) |
SIZE_ORDER | Defines the size of the appropriate interval. This complies with the number of minutes in the interval. | NUMBER(10,0) |