ORACLE DATABASE TABLES | CNCNETPDM MANUAL

Top

This section describes the steps to setup required and optional database tables for CNCnetPDM in case you're using an Oracle database.

• Note: Starting with CNCnetPDM Ver. 6.3.3.1 the names of all database tables can be configured on a per-service basis, see chapter Connect of CNCnetPDM INI file for details.

For an Oracle database the information in section SQL Server Database tables applies also but the scripts to create the tables look slightly different.

• MTH_RAW_DATA Stores all data received from devices
• MTH_ONLINE_DATA Contains only the most recent data received from devices
• MTH_USER_CONTEXT Enables you to combine machine-data with work-order, operation and part-number information
• MTH_EQP_SHIFT_AVAIL_V Enables you to acquire data only during an active shift

MTH_RAW_DATA

The following code can be used to create MTH_RAW_DATA. CNCnetPDM stores all data acquired in every reading cycle in this table. This Alternatively you can use MTH_RAW_DATA_ORACLE.sql from this zip archive. Here user is MTH and tablespace is USERS, please change that according to your needs.

 CREATE TABLE "MTH"."MTH_RAW_DATA" 
   (	"GROUP_ID" NUMBER, 
	"READING_TIME" DATE NOT NULL ENABLE, 
	"DEVICE_NUMBER" NUMBER NOT NULL ENABLE,
	"TAG_CODE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"TAG_DATA" VARCHAR2(255 BYTE), 
	"CREATION_DATE" DATE, 
	"PROCESSING_FLAG" NUMBER DEFAULT 2 NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."GROUP_ID" IS 'Tag context group identifier';
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."READING_TIME" IS 'Reading time for the tag data';
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."DEVICE_NUMBER" IS 'Device number sending tag data';
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."TAG_CODE" IS 'Tag code';
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."TAG_DATA" IS 'Tag data';
   COMMENT ON COLUMN "MTH"."MTH_RAW_DATA"."CREATION_DATE" IS 'Creation time';
   COMMENT ON TABLE "MTH"."MTH_RAW_DATA"  IS 'This table contains the unprocessed tag data collected from devices'; 

FIG 1:    Setup script (ORACLE) for database table MTH_RAW_DATA

MTH_ONLINE_DATA

To only store the most recent data from your machines you can use table MTH_ONLINE_DATA. CNCnetPDM updates all existing records for every device in this table when new data is queried. The update interval depends on the value for PollInterval in section [GENERAL] of CNCnetPDM.ini. To use this table please make sure that you have set WriteOnlineData = 1 in section [Connect] of CNCnetPDM.ini. You can create table MTH_ONLINE_DATA manually by using the following code or file MTH_ONLINE_DATA_ORACLE.sql from this zip archive. Here again user is MTH and tablespace is USERS, please change that if needed.

 CREATE TABLE "MTH"."MTH_ONLINE_DATA" 
   (	"GROUP_ID" NUMBER, 
	"READING_TIME" DATE NOT NULL ENABLE, 
	"DEVICE_NUMBER" NUMBER NOT NULL ENABLE,
	"TAG_CODE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"TAG_DATA" VARCHAR2(255 BYTE), 
	"CREATION_DATE" DATE, 
	"PROCESSING_FLAG" NUMBER DEFAULT 2 NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."GROUP_ID" IS 'Tag context group identifier';
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."READING_TIME" IS 'Reading time for the tag data';
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."DEVICE_NUMBER" IS 'Device number sending tag data';
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."TAG_CODE" IS 'Tag code';
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."TAG_DATA" IS 'Tag data';
   COMMENT ON COLUMN "MTH"."MTH_ONLINE_DATA"."CREATION_DATE" IS 'Creation time';
   COMMENT ON TABLE "MTH"."MTH_ONLINE_DATA"  IS 'This table contains the unprocessed tag data collected from devices'; 

FIG 2:    Setup script (ORACLE) for database table MTH_ONLINE_DATA

MTH_USER_CONTEXT

For table MTH_USER_CONTEXT you can use the following code or alternatively MTH_USER_CONTEXT_ORACLE.sql from this zip archive. Here again user is MTH and tablespace is USERS, please change that if needed.

  CREATE TABLE "MTH"."MTH_USER_CONTEXT" 
   (	"USER_CONTEXT_PK_KEY" NUMBER NOT NULL ENABLE, 
	"SCAN_TIME" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"EQUIPMENT_FK" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"WORKORDER_FK" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"SEGMENT_FK" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"ITEM_FK" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	"EQUIP_DOWN_REASON" VARCHAR2(255 BYTE), 
	"SYSTEM" VARCHAR2(20 BYTE), 
	 CONSTRAINT "MTH_USER_CONTEXT_PK" PRIMARY KEY ("USER_CONTEXT_PK_KEY")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

FIG 3:  Setup script (ORACLE) for database table MTH_USER_CONTEXT

MTH_EQP_SHIFT_AVAIL_V

To only acquire data within an active shift for a device you can use the following code or alternatively file MTH_EQP_SHIFT_AVAIL_V_ORACLE.SQL from this zip archive.

  CREATE TABLE "MTH"."MTH_EQP_SHIFT_AVAIL_V" 
   (	"EQUIPMENT_FK" VARCHAR2(255 BYTE) NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

FIG 4:  Setup script (ORACLE) for database table MTH_EQP_SHIFT_AVAIL_V

For testing you can insert the 4 digit device number into field EQUIPMENT_FK and adjust the .ini file for CNCnetPDM to only collect data within a shift. In a production environment MTH_EQP_SHIFT_AVAIL_V should be a view that looks up if there’s an active shift for the equipment in a shift table and, if yes, returns the 4 digit device number.