Description of the Tables and Views

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: 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. 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)