Using the Mossqlcli tool, you can create custom views that use more complex queries to include multiple tables. Views can even be combined to form more complex views. You can then use third-party tools to query just a single view to create reports.
Mossqlcli is based on Apache Derby ij, a third-party tool, which is installed with SQL Views. For information on how to use Derby ij and for a tutorial on the Apache Derby Web site, see http://db.apache.org/derby/integrate/plugin_help/ij_toc.html.
The following is a possible test query:
select * from MOSQL.SYSTEM_SERVICEMODELS_ELEMENTS;
If mossqlcli has not been installed, you can install it.
Run mossqlcli.
The following should dislpay:
Usage: mossqlcli username password [host:port] The port shown is the port for Managed Object SQL; the default is 1560.
Run the file again with valid arguments.
Verify a connection to SQL Views.
The Operations Center server and SQL Views must be running.
Install the latest patch bundle from Operations Center.
For more information, contact Technical Support.
Run the Operations Center Configuration Manager (such as customizer).
Verify that a new bin file called mossqlcli (Unix) or mossqlcli.bat (Windows) exists.
You can use mossqlcli to create a custom view based on one or more tables. For example, to create a view called MOSQL One containing the data from the system view called SYSTEM_ELEMENTS_ELEMENTS, which contains one record, you enter the following code:
create view MOSQLONE as select * from MOSQL.SYSTEM_SERVICEMODELS_ELEMENTS;
Mossqlcli returns the following:
0 rows inserted/updated/deteled
To see the contents of the view, enter the following code:
select * from MOSQLONE;
Mossqlcli returns the following:
NAME |DNAME |CLASSNAME |CONDITION |ID |PARENT |LASTUPDATE ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- Adapter: NOC Demonstration Adapter |demoMgr=Adapter%3A+NOC+Demonstration+Adapter/root=Elements |demoMgr |CRITICAL |1206626208 |1206624715 |2008-04-04 14:00:14.102 1 row selected
You can use the SQL Views syntax to write more complex queries to create more complex views, also.