Genesys CTI User Forum

Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: phil on October 26, 2010, 12:30:09 PM

Title: [SOLVED] Problems on Stat Server 8 / DB
Post by: phil on October 26, 2010, 12:30:09 PM

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

Title: Re: Problems on Stat Server 8 / DB
Post by: René on October 26, 2010, 04:19:53 PM

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.

Title: Re: Problems on Stat Server 8 / DB
Post by: phil on October 27, 2010, 08:07:59 AM

Hi René,

thanks for your feedback.

[quote author=René link=topic=5973.msg25954#msg25954 date=1288109993]
The first error - ORA-00904 means that Oracle can't find specified column name (IXNID). Please double check that STATUS_TABLE contains this column.
[/quote]

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"


[quote author=René link=topic=5973.msg25954#msg25954 date=1288109993]
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.
[/quote]

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 ;-)

Cheers

:-Phil

Title: Re: Problems on Stat Server 8 / DB
Post by: René on October 27, 2010, 09:31:35 AM

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

[code]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)
)[/code]

R.

Title: [SOLVED]: Problems on Stat Server 8 / DB
Post by: phil on October 27, 2010, 09:44:24 AM

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:

[i]The 8.0 release introduces a new column (IxnID) in the STATUS table for storing interaction IDs generated from Interaction Server.[/i]
[color=navy][size=8pt]---< Chapter 11: Stat Server Migration, Updating an Existing Stat Server Databse, p. 156 >---[/size][/color]

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

Title: Re: Problems on Stat Server 8 / DB
Post by: phil on October 29, 2010, 07:52:42 AM

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