Error while running SBH - ORA: 00904 Invalid Identifier

Technote (troubleshooting)


Problem(Abstract)

Some Busy Hours fail with the Oracle Error ORA-00904: Invalid Identifier

Symptom

Error Message:

An example of the error is running the Stored BusyHour with the following command:
sbh_admin -r -n Nokia_Busy_Hour -start 07/10/12 -end 07/10/12

returns the following error:

sbh_admin -r -n Nokia_ Busy_Hour -start 27/10/12 -end 27/10/12
[echo] Licensed Materials - Property of IBM
[echo] 5724W86
[echo] (C) Copyright IBM Corp. 2004, 2009 All Rights Reserved.
[executestoredbusyhour] Executing Busy Hour(s): Nokia_ Busy_Hour
[executestoredbusyhour] Name = Nokia_ Busy_Hour
[executestoredbusyhour] Status = Enabled, priority = 99
[executestoredbusyhour] DAILY
[executestoredbusyhour] First Available = None
[executestoredbusyhour] Last Available = None
[executestoredbusyhour] Last Successful Run = 18 Oct 2012 12:11:05
[executestoredbusyhour] Last Run = 26 Oct 2012 19:43:19
[executestoredbusyhour] Result = An exception occurred - com.ibm.tivoli.tnpmw.pde.api.engine.ex.PDEEnvironmentBuildException: java.sql.SQLException: ORA-00904: "NC_SCCP_SUBSYSTEM"."BSC_ID": invalid identifier
[executestoredbusyhour]
[executestoredbusyhour] Duration = 607 seconds
[executestoredbusyhour]
[executestoredbusyhour] WEEKLY
[executestoredbusyhour] First Available = None
[executestoredbusyhour] Last Available = None
[executestoredbusyhour] Last Successful Run = 18 Oct 2012 12:11:22
[executestoredbusyhour] Last Run = 26 Oct 2012 17:24:42
[executestoredbusyhour] Result = No calculation required
[executestoredbusyhour] Duration = 22 seconds
[executestoredbusyhour]
[executestoredbusyhour] MONTHLY
[executestoredbusyhour] First Available = None
[executestoredbusyhour] Last Available = None
[executestoredbusyhour] Last Successful Run = 04 Oct 2012 13:32:25
[executestoredbusyhour] Last Run = 26 Oct 2012 17:24:58
[executestoredbusyhour] Result = No calculation required
[executestoredbusyhour] Duration = 16 seconds


The following error statements appear in the {WMCROOT}/logs/as/default/as-busyhour.log file:

11:03:17,969 ERROR [service.SBHServiceManager] (ThreadPool(4)-1) Failed to run SBH for given range
com.ibm.tivoli.tnpmw.pde.api.engine.ex.PDEEnvironmentBuildException: java.sql.SQLException: ORA-00904: "NC_SCCP_SUBSYSTEM"."BSC_ID": invalid identifier

Caused by: java.sql.SQLException: ORA-00904: "NC_SCCP_SUBSYSTEM"."BSC_ID": invalid identifier

NOTE: The table name and column name in the error message can vary and the above is just an example “NC_SCCP_SUBSYSTEM"."BSC_ID"


Diagnosing the problem

Problem and Impact Statement

This problem occurs in TNPM Wireless 1.2.1 onwards when Stored Busy Hours are running on PDE Mode. The busy hours fail with the error

com.ibm.tivoli.tnpmw.pde.api.engine.ex.PDEEnvironmentBuildException: java.sql.SQLException: ORA-00904: "<tablename>"."<column_name>": invalid identifier

Root Cause

The root cause of this issue is that the entity record is missing in the wmn_entity_context table because of which the hierarchy relationship is not established in the wmn_entity_map table. The output of the following query will return no records.

select b.entity_id, b.entity_name parent, c.entity_id, c.entity_name infant, distance,
taba.table_name, cola.col_name, tabb.table_name, colb.col_name
from wmn_entity_map a, wmn_entity b, wmn_entity c, wmd_col_def cola, wmd_col_def colb, wmd_dict_table taba, wmd_dict_table tabb
where a.parent_entity = b.entity_id
and a.child_entity = c.entity_id
and taba.table_id = cola.table_id
and a.column_id = cola.field_id
and a.column_id1 = colb.field_id (+)
and colb.table_id = tabb.table_id (+)
and taba.table_name = 'NC_SCCP_SUBSYSTEM'
order by parent, infant;

no rows selected

NOTE: Replace the table name in red with the table for which the error is observed.
.


Resolving the problem

Workaround


The workaround is to insert the missing entity in the wmn_entity_context table and run the kpicache_admin command to build the entity hierarchy relationship.

Below are the steps to perform the workaround:

1. Login to the database server and run the following commands.

2. sqlplus virtuo@vtdb. Enter the password when prompted

3. Get entity_id: Remove the leading NC_ from the table name in the error message and run the following query
Eg select entity_id from wmn_entity where upper(entity_name) = 'SCCP_SUBSYSTEM';

4. Get context_id: Run the following query by using the name of the Techpack for which the failing Busy Hour:
Eg select context_id from wmn_context where context_name = 'Nokia_UMTS_UTRAN';

5. Get parent_entity_id:
a. vi $WMCROOT/admin/techpacks/<tp name>/<tp version>/metalayer/main.xml

b. Search for the table name with the error string eg SCCP_Subsystem in the above file and get the parent entity name:

<Child name="SCCP_Subsystem">
<Parent name="SCCP" technology="UMTS" subsystem="UTRAN" vendor="Nokia"/>
</Child>

c. Get the entity_id of the parent:
select entity_id from wmn_entity where upper(entity_name) = 'SCCP';

6. Run the following command using the entity_id, context_id and parent_entity_id from the above steps:
insert into wmn_entity_context values (<entity_id>, <context_id>, 'y', <parent_entity_id>, null)

7. Commit your changes: commit;

8. On the app server run kpicache_admin to sync the KPIs using following command:
kpicache_admin -u <user> -p <password> -d <filename> <dsname>

The dsname can be obtained by running the command:
ds_admin -u <user> -p <password> -list

9. Now run the query to confirm the fix:

select b.entity_id, b.entity_name parent, c.entity_id, c.entity_name infant, distance,
taba.table_name, cola.col_name, tabb.table_name, colb.col_name
from wmn_entity_map a, wmn_entity b, wmn_entity c, wmd_col_def cola, wmd_col_def colb, wmd_dict_table taba, wmd_dict_table tabb
where a.parent_entity = b.entity_id
and a.child_entity = c.entity_id
and taba.table_id = cola.table_id
and a.column_id = cola.field_id
and a.column_id1 = colb.field_id (+)
and colb.table_id = tabb.table_id (+)
and taba.table_name = 'NC_SCCP_SUBSYSTEM'
order by parent, infant;

The query should return more than one record. This means that the entity hierarchy has been established and the Busy Hour run will be successful.

Product Alias/Synonym

TNPM

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Tivoli Netcool Performance Manager
IBM Tivoli Netcool Performance Manager (TNPM Wireless) Platform

Software version:

1.3, 1.4

Operating system(s):

AIX, Linux, Solaris

Reference #:

1615693

Modified date:

2014-08-25

Translate my page

Machine Translation

Content navigation