Which app is generating this message ????

[seanh]

Hi all,

Can anyone advise on the following please.

I am seeing "ORA-00942: table or view does not exist" messages (see below !!!) in my dbserver_routing log but for GenesysLabs to investigate further, they are asking "which app" is generating them.

I have turned ALL traces on for all other apps but cannot see the messages anywhere else.

They are being generated every 15 mins so i guess its a stats based source but as i say, i cannot prove what specific app is generating them.

Many thanks,
Sean

Thu Nov 06 10:30:03 2008.259  Debug  sul7it10  dbserver_routing  GCTI-46-10741  'App: 1' has put request id='5347' into queue
Thu Nov 06 10:30:03 2008.259  Debug  sul7it10  dbserver_routing  GCTI-46-10737  Forwarding request '5347' from 'App: 1' to 'dbclient_oracle: 1.1'
Thu Nov 06 10:30:03 2008.260  Debug  sul7it10  dbserver_routing  GCTI-46-10743  'dbclient_oracle: 1.1' is spare
Thu Nov 06 10:30:03 2008.309  Debug  sul7it10  dbserver_routing  GCTI-46-10749  Forwarding request '5347.1' from 'dbclient_oracle: 1.1' to 'App: 1'
+0000  execute sp: CALLSMON.SPOC_INSERTCALL VAR02='',VAR03='59322',VAR04='009d01a066eb3c2c',VAR05='20081106103003',VAR06='',VAR07='015',VAR08='',VAR09='',VA
R10=''
+0049  SP fetch record failure (no data)
+0049  MSG_PROCCOMPLETED status='DBM_SUCCESS' msg='The procedure completed ok.'

Thu Nov 06 10:30:08 2008.207  Debug  sul7it10  dbserver_routing  GCTI-46-10741  'App: 1' has put request id='5348' into queue
Thu Nov 06 10:30:08 2008.207  Debug  sul7it10  dbserver_routing  GCTI-46-10737  Forwarding request '5348' from 'App: 1' to 'dbclient_oracle: 1.1'
Thu Nov 06 10:30:08 2008.207  Debug  sul7it10  dbserver_routing  GCTI-46-10743  'dbclient_oracle: 1.1' is spare
[color=red] +0000  SQL: SELECT CURR_BIAS.era_timezone_bias_id, CURR_BIAS.era_timezone_id, to_char(CURR_BIAS.era_start_datetime,'Mon DD YYYY HH:MIPM'), to_char(CURR_BIAS
.era_end_datetime,'Mon DD YYYY HH:MIPM'), PREV_BIAS.era_bias, CURR_BIAS.era_bias, NEXT_BIAS.era_bias ...
+0001  MSG_ERROR status='14' msg='ORA-00942: table or view does not exist[/color] '

Thu Nov 06 10:30:08 2008.209  Debug  sul7it10  dbserver_routing  GCTI-46-10749  Forwarding request '5348.1' from 'dbclient_oracle: 1.1' to 'App: 1'
Thu Nov 06 10:30:15 2008.390  Debug  sul7it10  dbserver_routing  GCTI-46-10741  'App: 1' has put request id='5349' into queue
Thu Nov 06 10:30:15 2008.390  Debug  sul7it10  dbserver_routing  GCTI-46-10737  Forwarding request '5349' from 'App: 1' to 'dbclient_oracle: 1.1'
Thu Nov 06 10:30:15 2008.390  Debug  sul7it10  dbserver_routing  GCTI-46-10743  'dbclient_oracle: 1.1' is spare
Thu Nov 06 10:30:15 2008.633  Debug  sul7it10  dbserver_routing  GCTI-46-10749  Forwarding request '5349.1' from 'dbclient_oracle: 1.1' to 'App: 1'
+0000  execute sp: CALLSMON.SPOC_UPDATECFT VAR1='OTHER',VAR2='009d01a066eb3c28'
+0242  SP fetch record failure (no data)
+0242  MSG_PROCCOMPLETED status='DBM_SUCCESS' msg='The procedure completed ok.'

[Steve]

These look like WFM 6.5 tables to me. So I would be looking at Data Aggregator when it tries to write the 15 minute timestep data to the database.

[quote]era_timezone_bias 28 One record per timezone bias, per timezone.
era_timezone_bias_id int 4 NO Key, ID.
era_timezone_id int 4 NO Join to era_timezones.
era_start_datetime datetime 8 NO Beginning of period in which the bias is in effect.
era_end_datetime datetime 8 NO End of period in which the bias is in effect.
era_bias int 4 NO Value for bias. Typically 60, indicates # of minutes.[/quote]

[Steve]

I forgot to mention that this table hold the data for Daylight saving time adjustments, when it starts, when it stops and how much of an offset is applied.

[seanh]

Hi Steve,

thanks for your reply, but when you say WFM, you mean Work Force Manager right ?
If so, we dont run it !

Its a pretty basic setup here.

I know the messages are being issued every 15 mins so its a "scheduled" type of activity so it obviously points me in the region of statistics but until i can find the app, GenesysLabs cant (or wont) help.

I have asked for guidance from them as to how to find the application but they have offered no advice.

All help is gratefully received.

Sean

[Luk]

Maybe ETL (Extraction, Transition and Loading) which is a process that runs for reporting. It takes data from ODS and puts them in DataMart/InfoMart...? I don't know a lot about reporting :s

[seanh]

I've upped the tracing on these also but on the 15 minute interval, nothing appears !

[René]

Hi Sean,

I would try following to determine what application issues the query

- list of applications and DAPs connected to this DBServer in Genesys configuration
- list of TCP/IP connections to DBServer using some tools (TCPView from Sysinternals)

The logs says that the request was received by the application 'App: 1'. I assume that 'App: 1' means a client connected to DBServer. If it does then this client issued previous request "execute sp: CALLSMON.SPOC_INSERTCALL VAR" as well. Do you know what client uses the stored procedure "CALLSMON.SPOC_INSERTCALL"?

R.

[arunkumar]

This message can be from the etl and can you tell what are the application which are using this particular dbserver_routing

[Steve]

Sean

Rene is right in that all 3 of these requests came from App: 1, so start by looking for CALLSMON.SPOC_INSERTCALL and CALLSMON.SPOC_UPDATECFT in your application logs, especially URS as strategies tend to be the main way to call procedures, although GVP and other apps can do it too.

I still believe that the "era_timezone_bias" table is Workforce Manager 6.5. Could you have copied a strategy from another system? one which did have WFM?  Has someone used the WFM table to work out if daylight saving is on or off?

[seanh]

Hi again,

Thanks for the input once again.

The stored procs were written by myself and the strategy is all my handy work too 
The stored procs run at various stages through my strategy and push various pieces of info out to a database table so we can see what options were selected, what agent was targetted, the connid etc etc, but there is nothing to do with WFM....we dont have the product as its such a small helpdesk.

I designed the strategy from scratch so again, there is no possibility of anything being from a former life ! 

We have no timezone-ing built into the scripts either.

The thing that is puzzling, is the frequency......every 15 mins so surely that rules out URS ?

[seanh]

Here is a little more info that hopefully may help.

My strategy calls a subroutine called SPOC_CALLSMON.
This subroutine parses date variables etc (nothing too clever), but then at the end, runs a stored proc SPOC_INSERTCALL to push the data out into a database table.

Upon checking, this element shows as using a DB Server of "dbserver_routing" with a database acces point of "DAP_routing"

Upon checking in Config Mgr, the "Options tab" for application "DAP_routing" has a "Sections / URServer" option, which when opened, shows the following variable......

Name = using
Value = "wfm"

Could this be the route cause behind the wierd SQL statements that are being issued ?

I have no idea whethere this should be in here or how long its been here as, as i say, we dont have WFM.

Thanks again,

Sean

[Steve]

None of our DAPs have anything in the Options Tab, and we do have WFM!

[Jason Neuman]

Check the DAPs URS is connected to
- For each DAP URS is connected to check the option tab for an option called "using" configured to WFM.

If you're not using WFM, remove this option and the querying will go away.