[SOLVED] Problems on Stat Server 8 / DB

Voice & Telephony
1

hi folks,

i’ve set up the stat server 8, including the new field STATUS_TABLE.IXNID allthoug i’ve also set the options ixn-id-in-status-table to “on”, respectively multimedia-activity-instatus-table to “yes”, the new field isnt populated.
SCI says:

Execution failure: SQL statement ‘ORA-00904_ “IXNID”: invalid identifier’, reason ‘’, error code -1

Now after undoin this configuration, i got another alert saying: Execution failure:

SQL statement ‘ORA-00001: unique constraint (STATSERVER.PK_STATUS_TABLE) violated’, reason ‘’, error code -1

Thus, maybe because i’ve undione the config in runtime… eek.

Can anyone help fixing this?

Cheers

Phil

2

Hi Phil,

The first error - ORA-00904 means that Oracle can’t find specified column name (IXNID). Please double check that STATUS_TABLE contains this column.

About the second error - this one is a bit odd as it means that StatServer uses primary key (column ID) that already exists in the table. If possible delete all records from the table and try it again. Please stop StatServer while working on the table.

R.

2 Likes
3

Hi René,

thanks for your feedback.

Yeah, i already triple-checked that: the column exists. the table looks like this:

DBMS_METADATA.GET_DDL(‘TABLE’,‘STATUS_TABLE’) ------------------------------------------------------------------
CREATE TABLE “STATSERVER”.“STATUS_TABLE”
( “STATUS_KEY” NUMBER(20,0) NOT NULL ENABLE,
“EXTRACT_KEY” NUMBER(20,0) NOT NULL ENABLE,
“PARTITION_KEY” VARCHAR2(10),
“AGENTDBID” NUMBER(,0) NOT NULL ENABLE,
“PLACEDBID” NUMBER(,0) NOT NULL ENABLE,
“STATUS” NUMBER(,0) NOT NULL ENABLE,
“STARTTIME” NUMBER(,0) NOT NULL ENABLE,
“DURATION” NUMBER(,0) NOT NULL ENABLE,
“ENDTIME” NUMBER(,0) NOT NULL ENABLE,
“CONNID” NUMBER(20,0) NOT NULL ENABLE,
“IXNID” VARCHAR2(16),
CONSTRAINT “PK_STATUS_TABLE” PRIMARY KEY (“STATUS_KEY”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “STATSERVER” ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “STATSERVER”

Yeah, really odd! Maybe cause i made some config changes in runtime (statserver), as i already mentionend. I’ try to delete.

Thansk for your effort, though the ORA-exception are quite clear - no harm meant :wink:

Cheers

:-Phil

4

Hi Phil,

I’m not sure your configuration is correct as your table is called STATUS_TABLE while StatServer expects STATUS. And structure of your table differs from the one expected by StatServer (based on SQL scripts provided with StatServer - see below).

create table STATUS
(
    ID         NUMBER(20)             not null,
    AgentDBID  INTEGER                not null,
    PlaceDBID  INTEGER                not null,
    Status     INTEGER                not null,
    StartTime  INTEGER                not null,
    Duration   INTEGER                not null,
    EndTime    INTEGER                not null,
    ConnID     NUMBER(20)             not null,
    IxnID      VARCHAR2(16),
    constraint PK_STATUS primary key (ID)
)

R.

5

Hi René,

When our DBA was about to drop and recreate the table he noticed, that there is only a view called “STATUS”. However, the field INXID has been added to the “STATUS_TABLE”- table as you were supposed to do, considering the Migration Guide:

The 8.0 release introduces a new column (IxnID) in the STATUS table for storing interaction IDs generated from Interaction Server.
—< Chapter 11: Stat Server Migration, Updating an Existing Stat Server Databse, p. 156 >—

Since there are dedicated StatServers for CCPulse and InfoMart and the view is the one, which is used by the StatServer for CCPulse, it was complaining rightly. After adding “IXNID” and “ENDTIME” to the view, there’s no complaining any more - except for the PK violation, of course :-/

Cheers

Phil

6

The PK Violation was induced by a StatServer which was wrongly configured to write in the same DB as another StatServer.

2 Likes