Introducing IDB Schema

The Interaction Database (IDB) stores in its tables all reporting data that the Interaction Concentrator server (ICON) provides. Logically, IDB tables can be divided into groups that correspond to the classes of information that ICON writes. This topic provides a summary of the precalculated call and party metrics that are available in the statistical tables.

This page describes the following groups of tables:

• Operational Tables
• Configuration Tables
• Data Source Session Control Tables
• Service and Dictionary Tables
• Log Tables

Important

For a complete table structure and description of all IDB tables and fields, see the Interaction Concentrator Physical Data Model document for your particular RDBMS.

The data filtering feature limits the data stored to IDB. See Filtering IDB Data for more information.

Operational Tables

There are eight subgroups of tables that contain operational data:

• Interaction-related and Party-related tables
• Agent State–related and Login Session–related tables
• Custom State–Related tables
• Attached Data–related tables
• Outbound-related tables
• Active Call and Active Interaction tables
• Virtual Queue–related tables

Interaction-Related and Party-Related Tables

There are three subgroups of tables that contain data about interactions and parties:

Core Tables

The core tables contain current (the most up-to-date) information about interactions and parties, as well as all related information. The five tables in this subgroup are:

• G_CALL—Contains information about telephone calls (both completed and active), SIP chat, eServices, and 3rd Party Media interactions.
• G_PARTY—Contains information about parties to the interaction.
• G_IR—Contains information about the data that is common to all of the interactions in a particular scenario.
• G_IS_LINK—Contains information about inter-site interaction links in a multi-site scenario.
• G_ROUTE_RESULT—Contains information about the results of routing for the interaction.

History Tables

The history tables contain intermediary (history) states of the data that was previously stored in the corresponding core tables. The history tables are named the following:

• G_CALL_HISTORY
• G_PARTY_HISTORY
• G_IR_HISTORY
• G_IS_LINK_HISTORY

They correspond to the G_CALL, G_PARTY, G_IR, and G_IS_LINK core tables, respectively.

Statistical Tables

The two statistical tables, G_CALL_STAT and G_PARTY_STAT, contain statistical information about interactions and parties, respectively.

Interaction Metrics

The Available Interaction Metrics table, below, lists the Interaction Concentrator metrics that are stored in the G_CALL_STAT table, in the order of their appearance in that table. This table also indicates the media type for which the metric is calculated.

Available Interaction Metrics

Metric Name	Description	Media Type Supported
		Voice	eServices
			Email	Chat	3rd Party Media
F_CONN	Flag CONNECTED.	Yes	Yes	Yes	Yes
F_CONN_EXTN	Flag CONNECTION ON EXTENSION.	Yes	Yes	Yes	Yes
F_TE_ABND	Flag EVENT ABANDONED.	Yes	No	No	Depends on the media type and media server.
CNT_HOLD	Number of times that the call was placed on hold.	Yes	No	No	No
CNT_DIVERT	Number of times that the interaction was diverted (for example, from a Queue or Routing Point).	Yes	Yes	Yes	Yes
CNT_TRANSFER	Number of times that the interaction was transferred, given that the transfer was completed.	Yes	Yes	Yes	Yes
CNT_TRANSFER_LGIN	Number of times that the interaction was transferred by a party associated with a device that had a logged-in agent.	Yes	Yes	Yes	Yes
CNT_CONFERENCE	Number of times that the interaction was conferenced.	Yes	No	Yes	No
T_DURATION	Duration of the interaction.	Yes	Yes	Yes	Yes
T_CONN	Time until CONNECTED with the first party in the interaction. This metric’s value can be considered the time to answer on any device (either by an agent or IVR).	Yes	Yes	Yes	Yes
T_CONN_EXTN	Time until CONNECTED ON EXTENSION.	Yes	Yes	Yes	Yes
T_TE_ABND	Time until ABANDONED.	Yes	No	Yes	Yes
TT_ALERTING	The sum of all the time interval durations if there was at least one internal party in a call in the ALERTING state.	Yes	Yes	Yes	Yes
TT_CONNECTED	The sum of all the time interval durations when all parties in a call were in a CONNECTED state.	Yes	Yes	Yes	Yes
TT_HOLD	The sum of all the time interval durations when there was at least one internal party in a call in the HOLD state.	Yes	No	No	No
TT_QUEUED	The sum of all the time interval durations when there was at least one internal party in a call in the QUEUED state.	Yes	Yes	Yes	Yes
CM_EXT_1–CM_EXT_10	Reserved.	n/a	n/a	n/a	n/a

Party Metrics

The Available Party Metrics table, below, lists the Interaction Concentrator metrics that are stored in the G_PARTY_STAT table, in the order of their appearance in that table. The table also also indicates the media type for which the metric is calculated.

Available Party Metrics

Metric Name	Description	Media Type Supported
		Voice	eServices
			Email	Chat	3rd Party Media
TT_ALERTING	Total time that a party spent in the ALERTING state.	Yes	Yes	Yes	Yes
TT_CONNECTED	Total time that a party spent in the CONNECTED state. The states of other parties in the call do not affect this metric.	Yes	Yes	Yes	Yes
TT_HOLD	Total time that a party spent in the HOLD state.	Yes	No	No	No
TT_QUEUED	Total time that a party spent in the QUEUED state.	Yes	Yes	Yes	Yes
TT_ACW	Total time that a party spent in after-call work (ACW).	Yes	No	No	No
CNT_ALERTING	Number of times that a party changed its state to ALERTING.	Yes	Yes	Yes	Yes
CNT_CONNECTED	Number of times that a party changed its state to CONNECTED (for example, from the Alerting or HOLD state).	Yes	Yes	Yes	Yes
CNT_HOLD	Number of times that a party changed its state to HOLD.	Yes	No	No	No
CNT_QUEUED	Number of times that a party changed its state to QUEUED.	Yes	Yes	Yes	Yes
CNT_ACW	Flag of ACW presence for this party.	Yes	No	No	No
TT_ON_ALERT	Total time that another party in the interaction spent in the ALERTING state.	Yes	Yes	Yes	Yes
TT_ON_HOLD	Total time that another party in the call spent in the HOLD state.	Yes	No	No	No
TT_ON_QUEUE	Total time that another party in the interaction spent in the QUEUED state.	Yes	Yes	Yes	Yes
TT_ON_CONNECTED	Total time that all parties in the call were simultaneously in the CONNECTED state.	Yes	Yes	Yes	Yes
T_DURATION	Duration of a party’s existence.	Yes	Yes	Yes	Yes
PM_EXT_1–PM_EXT_10	Reserved.	n/a	n/a	n/a	n/a

Agent State and Login Session Tables

There are three subgroups of tables that contain historical data about contact center agents.

Core Tables

The core tables contain information about agent states, login sessions, and the association of sessions with endpoints (DNs). The tables in this subgroup are:

• G_LOGIN_SESSION—Contains information about agent login sessions.
• GX_SESSION_ENDPOINT—Contains information about the association between a login session and an endpoint (DN).
• G_AGENT_STATE_RC—Contains information about changed or terminated reason codes for agent states.
• G_AGENT_STATE_RC_A—Contains information about active reason codes for agent states.

History Tables

The history tables contain historical data relating to agent states and login sessions at a DN. The two tables in this subgroup are:

• G_AGENT_STATE_HISTORY—Stores the history of agent states within a given login session.
• G_DND_HISTORY—Stores the history of DND (Do Not Disturb) feature activation and deactivation on a device (DN).

Statistical Tables

The two statistical tables contain several statistics related to agent states and login sessions. The two tables in this subgroup are:

• GS_AGENT_STAT—Stores durations of agent states.
• GS_AGENT_STAT_WM—Stores durations of work modes for agent states.

Custom State–Related Tables

Interaction Concentrator supports customer-defined states and attached data in UserEvents for compatibility with Call Concentrator. The three tables related to custom states are:

• G_CUSTOM_DATA_P—This is a flat table with key values delivered in UserEvents associated with voice calls.
• G_CUSTOM_DATA_S—This is a generic table. Information about key values are delivered in UserEvents associated with voice calls.
• G_CUSTOM_STATES—Stores detailed information about an agent’s state changes during the agent login session.

Attached Data–Related Tables

The tables related to attached data (also sometimes referred to as UserData) contain data that T-Server and, if applicable, Interaction Server attach to an interaction. ICON selects the data from TEvents or multimedia reporting events. The exact data that ICON selects depends on the way in which ICON has been configured. The data in these tables can include the current state of attached data or the history of attached data changes, if so configured.

For more information, see Processing Attached Data.

There are three subgroups of attached data–related tables:

Attached Data State Tables for Voice (flat tables)

The Attached Data State (flat) tables store the current (latest received) state of the attached data attributes that are associated with calls. The four tables in this subgroup are:

• G_CALL_USERDATA—Stores predefined attached data attributes.
• G_CALL_USERDATA_CUST—Stores custom attached data attributes.
• G_CALL_USERDATA_CUST1—Stores custom attached data attributes.
• G_CALL_USERDATA_CUST2—Stores custom attached data attributes.

Attached Data History Tables for Voice (generic tables)

The Attached Data History (generic) tables store historical information about attached data for voice and multimedia interactions. The two tables in this subgroup are:

• G_USERDATA_HISTORY—Stores information about the attached data fields that require no security protection.
• G_SECURE_USERDATA_HISTORY—Stores information about the sensitive attached data fields that do require security protection (for example, a customer’s Social Security number).

Multimedia Attached Data Tables

The Multimedia Attached Data tables store information about multimedia-specific attached data. The two tables in this subgroup are:

• GM_L_USERDATA—Stores the values of attached data keys for suggested and auto responses and acknowledgements, customer IDs, and reasons for stopping processing.
• GM_F_USERDATA—Stores metadata information about e-mail and chat interactions (for example, the sender’s name and e-mail address, the subject, and the type).

For more information about how ICON populates these tables, see Attached Data Processing for Multimedia.

Outbound-Related Tables

The outbound-related tables include either the GO_ or GOX_ prefix in their names. These tables store information about the entities and attributes that are related to the processing of outbound calls and campaigns as reported by Outbound Contact Server (OCS).

For more detailed information about stored outbound data, see Available Outbound Data.

Active Call and Active Interaction Tables

The G_CALL_ACTIVE and G_IR_ACTIVE tables store data about currently active calls and interactions, respectively. When a call or an interaction terminates, the record is removed from the G_*_ACTIVE tables.

These tables are used to help more efficiently clear calls that become stuck after Interaction Concentrator stops. When Interaction Concentrator restarts, it checks the G_*_ACTIVE tables, and terminates all listed calls with the termination timestamp being the time that Interaction Concentrator restarted.

For details on these tables, see the relevant sections of the Interaction Concentrator Physical Data Model document for your RDBMS.

Virtual Queue Tables

The table that stores information related to interaction processing at virtual queues is called G_VIRTUAL_QUEUE. It stores the history of associations between interactions and virtual queues, as reported by T-Server, provided that Universal Routing Server (URS) provides this information to T-Server.

Data about virtual queue IDs are provided in the GSYS_EXT_VCH1 field of the G_ROUTE_RESULT table for interactions that have been cleared or abandoned from the virtual queue. The G_ROUTE_RES_VQ_HIST table also contains information about the use of virtual queues in interaction processing.

For details on these tables, see the relevant sections of the Interaction Concentrator Physical Data Model document for your RDBMS. For more detailed information about stored virtual queue data, see Monitoring Virtual Queues and Routing Points.

Configuration Tables

There are two subgroups of tables that contain configuration data:

Object tables
The object tables include the GC_ prefix in their names. These tables contain current (the most up-to-date) information about the configuration objects that ICON tracks. These tables also preserve information about the configuration objects that have been deleted from the Configuration Database.

Object Links tables
The object links tables include the GCX_ prefix in their names. These tables contain information about the associations (links) between configuration objects in the Configuration Database. Examples of associations between configuration objects include assignments of Skills or Logins to Agents and assignments of Agents to Agent Groups. These tables also preserve information about terminated associations, such as information about the fact that an Agent was removed from an Agent Group.

Data Source Session Control Tables

The Interaction Concentrator IDB schema contains five control tables, one for each ICON provider. Provider refers to the ICON functionality that provides data for a particular ICON database schema. The provider functionality corresponds to the ICON role. Each provider can be considered as an independent ICON process, with its own set of IDB tables.

The provider control tables are:

• G_DSS_CFG_PROVIDER—The control table for the cfg role, which stores configuration-related information. The data source is Configuration Server.
• G_DSS_GCC_PROVIDER—The control table for the gcc role, which stores interaction-related and party-related information. The data sources are T-Server and Interaction Server.
• G_DSS_GLS_PROVIDER—The control table for the gls role, which stores data that pertains to agent states and agent login sessions. The data sources are T-Server and Interaction Server.
• G_DSS_GOS_PROVIDER—The control table for the gos role, which stores data that pertains to outbound calls and campaigns. The data source is Outbound Contact Server (OCS).
• G_DSS_GUD_PROVIDER—The control table for the gud role, which stores data that pertains to attached data associated with interactions. The data sources are T-Server and Interaction Server.

ICON populates a particular provider table only if the corresponding role option has been defined for the ICON Application object.

Important

When assigned the lrm role, ICON creates records in the G_DSS_GLS_PROVIDER and G_DSS_GOS_PROVIDER tables.

For each ICON Application instance that performs a particular provider role, the provider table stores information about:

• Data Sources

  The identity of the data sources for that particular provider—for example, the identifier that Configuration Server assigns to the primary and backup data source applications (such as DS_DBID and DS_DBID_PRIM).

• Connection

  The connection between the data source and ICON—for example, the timestamp when the connection was established, and the timestamp when a disconnection was detected.

• Connection Type

  An important connection-related field, DSCONN_TYPE, describes the type of connection. This indicates the reason that a new record was created in the provider table:

  • 1—Indicates that this is the first connection following a restart of the ICON server.
  • 2—Indicates that this is a reconnection to the data source.
  • 3—Indicates that this is a reconnection to the data source following a detected restart of the data source application (information about the restart was received).
  • 4—Indicates that a reconnection of the data source to the switch was detected.
  • 5—Indicates that a switchover between primary and backup data sources was detected.
• ICON Server

  The identity and status of the ICON server—for example, the DBID of the ICON server (ICON_DBID), and the server startup or shutdown time.

• Data Source Events

  Events from the data source—for example, the timestamp of the first and last saved events.

For full details about the fields in the G_DSS_*_PROVIDER tables, see the Interaction Concentrator Physical Data Model document for your particular RDBMS.

Populating the Provider Control Tables

On startup (start or restart), each ICON reads its configuration (connection list) from Configuration Server, and determines the DBIDs of all the applications connected with that ICON. However, simply establishing a connection between ICON and a data source does not trigger the creation of a record in any provider table.

The first record in a provider table is created the first time an ICON instance processes an event from an identified connection, and prepares data for storage in IDB for that provider.

Thereafter, every time a transaction for that provider is being written, the record for the connection is updated to include data pertaining to the last stored event on the connection. The provider table record is updated before the new transaction is committed.

In some cases, ICON updates particular records in active provider control tables when it detects disconnection or shutdown of a data source application that is connected with that ICON.

Handling Very Short Connections

When ICON establishes a connection to a data source, it saves all necessary data in memory and waits for next event on this connection. If the next event is a data event, the data is stored in IDB.

However, if the data source provides no operational data, ICON waits until the expiration of the NoData timeout before processing the NoData event.

If the lifetime of the connection is less than the NoData timeout, no information about this short-lived, inactive, connection is written to IDB.

If two or all three provider roles (gcc, gls, and gud) are assigned to ICON, when the NoData condition becomes true for any one provider, NoData records are created for all other providers from that particular data source. This occurs because all providers use the same DSS connection.

Data Source Session ID

A data source session ID (DSS_ID) uniquely identifies the connection between the ICON application, the data source application (for example, T-Server), and the switch, as well as the timeframe during which the connection was active. The DSS_ID is obtained from the value of a system field (GSYS_DOMAIN) in all operational tables, which identifies the session that was active when the data was processed by ICON.

If any of the applications that are part of the connection restarts, the value of the DSS_ID changes, and this triggers the creation of a new record in the provider table.

Limitations

Interaction Concentrator does not support dynamic changes to HA configuration. In deployments in which the data source for the ICON instance is an HA pair, ICON might incorrectly identify the data source in the provider table record if the relationship between the primary and backup data sources is changed in the Configuration Layer while ICON is running.

When ICON restarts after a shutdown, it does not store data about the previous data source session in the applicable provider control table under the following circumstances:

• For configuration data (the G_DSS_CFG_PROVIDER table), if the cfg-sync.db file was deleted or the cfg role was disabled before ICON restarted.
• For OCS data (the G_DSS_GOS_PROVIDER table), if the persistent queue (.pq) file was deleted or the gos role was disabled before ICON restarted.
• For interaction-related, agent-related, and attached data–related data (the G_DSS_GCC_PROVIDER, G_DSS_GLS_PROVIDER and G_DSS_GUD_PROVIDER tables), if the persistent queue (.pq) file was deleted before ICON restarted.

Important

When ICON starts up, the gcc, gls, and gud providers start automatically in order to perform certain internal functions, regardless of the role that has been configured in the ICON Application object. The respective provider control tables might therefore contain partial records (for example, containing information about the start time or end time of the ICON session), even if that ICON instance does not perform the particular provider role. However, the ICON provider stores data in IDB only if the ICON instance has been configured to perform the applicable role. Full records in the corresponding provider table are created and updated only in connection with data storage activities.

Purpose of the Provider Control Tables

The provider control tables support data integrity analysis. They provide a mechanism to determine the availability and reliability of various types of data in IDB. In ICON HA deployments, the downstream reporting application can use this information to determine which IDB is the better source from which to extract data for a particular time period.

For more information about using the provider control tables, Determining Data Availability and Reliability and Extracting Data in an HA Deployment.

Service and Dictionary Tables

The service and dictionary tables are used for ICON internal purposes or describe fields in other tables. The six tables in this group are:

• G_DICTIONARY and G_DICT_TYPE—Contain dictionary information for certain enumerator fields in other tables (for example, the STATE, STATUS, and CAUSE fields).
• G_DB_PARAMETERS—Contains general information about the database schema (for example, the schema version).
• G_SYNC_CONTROL and G_PROV_CONTROL—Are used by ICON to implement internal transaction control.
• G_TIMECODE—Expands the timecode values that are referenced in other tables (for example, CREATED_TCODE and DELETED_TCODE) into specific time value entities such as month, day of the week, day of the month, and so on.

Log Tables

The log tables are internal tables used by ICON’s system procedures. The five tables in this group are:

• G_LOG_ATTRS—Stores attributes about the messages stored in the G_LOG_MESSAGES table.
• G_LOG_MESSAGES—Stores messages from the stored procedures about merge operations, purge operations, and stuck calls.
• G_LOG_GETIDRANGEREQ—Stores information that Solution Control Interface (SCI) uses internally for selecting log records.
• GSYS_DNPREMOTELOCATION—Stores information about the remote locations involved in an interaction.
• GSYS_SYSPROCINFO—Stores information that ICON uses internally for processing.