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:

  1. If a job cannot be run, because "shorter" intervals are not ready yet or because not all values have been written to the event history yet.

  2. As delay time for the interval job - is created on initialization and cannot be changed afterwards. If a change is desired, the interval job has to be deleted by hand and the jobs have to be updated (via refresh job).

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)