Technical Blog Post
Abstract
ITNM 4.2 Make ncim.managedStatus part of your networkView
Body
Your network View is typically a filter of entities from mainNodeDetails or Interfaces. Maybe even activeevent. Can the network view also filter from other tables in your ncim database? The answer is yes. And I suspect there are several ways to do this. I'm going to show one way to do this, and part of the reason I'm using this method is that it is easy to take a slow approach in a lab if you like and make sure your table drop and create is done right.
For this demonstration I am going to make the 'status' field of ncim.managedStatus part of ncim.mainNodeDetails.
This will be done with DB2, but a similar approach could be used for Oracle.
In our cautious approach first step is to get a count for ncim.mainNodeDetails
login as: itnmdb
[itnmdb@ bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to ITNM
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = ITNMDB
Local database alias = ITNM
db2 => select count(*) from ncim.mainNodeDetails
1
-----------
35859
1 record(s) selected.
db2 =>
In the event of a disaster in my testing I want to be able to replace ncim.mainNodeDetails if I make a mistake. It is a 'view' and not a true 'table', so it will immediately populate if it's dropped and added again.
[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ grep mainNodeDetails create*
createPrecisionIPDb.sql:-- mainNodeDetails VIEW
createPrecisionIPDb.sql:CREATE VIEW mainNodeDetails AS
This sql contains the table create for mainNodeDetails.
I cut out the part I want and create a small sql file
[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ cat mndgood.sql
CREATE VIEW mainNodeDetails AS
SELECT
c.accessIPAddress AS ipAddress,
c.className,
e.description,
e.entityName,
s.sysObjectId,
e.entityId,
s.sysName,
x.classType,
e.displayLabel,
d.domainMgrId,
s.sysDescr,
s.sysLocation,
s.sysContact,
c.upTime AS sysUpTime,
c.services AS sysServices,
c.isIpForwarding AS ipForwarding,
c.vendorType AS entPhysicalVendorType,
e.description AS entPhysicalDescr,
c.serialNumber,
g.locationId,
g.locationDescription,
g.latitude,
g.longitude,
g.altitude,
g.timezoneOffset,
g.altitudeUnits,
e.manual,
dm.domainName
FROM entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);
[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $
At the top I add two lines to connect to the database and to set schema to ncim
connect to ITNM;
set schema=ncim;
CREATE VIEW mainNodeDetails AS
SELECT
c.accessIPAddress AS ipAddress,
c.className,
e.description,
e.entityName,
s.sysObjectId,
e.entityId,
s.sysName,
x.classType,
e.displayLabel,
d.domainMgrId,
s.sysDescr,
s.sysLocation,
s.sysContact,
c.upTime AS sysUpTime,
c.services AS sysServices,
c.isIpForwarding AS ipForwarding,
c.vendorType AS entPhysicalVendorType,
e.description AS entPhysicalDescr,
c.serialNumber,
g.locationId,
g.locationDescription,
g.latitude,
g.longitude,
g.altitude,
g.timezoneOffset,
g.altitudeUnits,
e.manual,
dm.domainName
FROM entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);
So now a test so I can recover in case I do something wrong.....
[itnmdb@bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to ITNM
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = ITNMDB
Local database alias = ITNM
db2 => drop view ncim.mainNodeDetails
DB20000I The SQL command completed successfully.
db2 => select count(*) from ncim.mainNodeDetails
SQL0204N "NCIM.MAINNODEDETAILS" is an undefined name. SQLSTATE=42704
db2 => quit
DB20000I The QUIT command completed successfully.
[itnmdb@ bin]$ ./db2 -tmf /opt/IBM/netcool/core/precision/scripts/sql/db2/mndgood.sql
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = ITNMDB
Local database alias = ITNM
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
[itnmdb@bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to ITNM
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = ITNMDB
Local database alias = ITNM
db2 => select count(*) from ncim.mainNodeDetails
1
-----------
35859
1 record(s) selected.
db2 =>
So in the event I make a mistake I can drop the view/table and start again if I need to.
Here is my new sql where I make two changes to accommodate for status.
[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ cat mnd1.sql
connect to ITNM;
set schema=ncim;
CREATE VIEW mainNodeDetails AS
SELECT
c.accessIPAddress AS ipAddress,
c.className,
e.description,
e.entityName,
s.sysObjectId,
e.entityId,
s.sysName,
x.classType,
e.displayLabel,
d.domainMgrId,
s.sysDescr,
s.sysLocation,
s.sysContact,
c.upTime AS sysUpTime,
c.services AS sysServices,
c.isIpForwarding AS ipForwarding,
c.vendorType AS entPhysicalVendorType,
e.description AS entPhysicalDescr,
c.serialNumber,
g.locationId,
g.locationDescription,
g.latitude,
g.longitude,
g.altitude,
g.timezoneOffset,
g.altitudeUnits,
e.manual,
dm.domainName,
ms.status
FROM entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
LEFT JOIN managedstatus ms ON ms.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);
Two lines are new
ms.status
and
LEFT JOIN managedstatus ms ON ms.entityId = e.entityId
So I drop the view/table and add my new one mnd1.sql with status information
db2 => drop view ncim.mainNodeDetails
DB20000I The SQL command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
[itnmdb@ bin]$ ./db2 -tmf /opt/IBM/netcool/core/precision/scripts/sql/db2/mnd1.sql
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = ITNMDB
Local database alias = ITNM
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
[itnmdb@ bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => select count(*) from ncim.mainNodeDetails
1
-----------
35859
1 record(s) selected.
db2 =>
That count must still continue to match! If not there is an error in your new sql and you should put the mndgood.sql back in!
At this point I'm finished with the 'core' portion of my change in the database. So now I need to change the GUI or visual portion of this change.
I make a backup of my ncimMetaData.xml and I add one line in my mainNodeDetails section. The 'status' line
[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $ pwd
/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm
[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $ less ncimMetaData.xml
[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $
<dataField tableAlias="m" dataType="str" column="altitudeUnits"/>
<dataField tableAlias="m" dataType="str" column="timezoneOffset"/>
<dataField tableAlias="m" dataType="str" column="status"/>
<fromTables>
FROM _ncim_.mainNodeDetails m
WHERE e.entityId = ?
</fromTables>
That's it!
Now let's take a look in the GUI for network view creation with 'status'
Subscribe and follow us for all the latest information directly on your social feeds:
|
|
|
Check out all our other posts and updates: | |
Academy Blogs: | https://goo.gl/eZjStB |
Academy Videos: | https://goo.gl/kJeFZE |
Academy Google+: | https://goo.gl/HnTs0w |
Academy Twitter : | https://goo.gl/DiJbvD |
UID
ibm11082475