How to create TADDM BIRT Reports using Detail Panel views

The TADDM database uses a highly normalised database schema, CI data is held across over 1500 tables within the TADDM database. Querying this data directly at a database table level can be challenging if you don’t have a detailed knowledge of the TADDM schema design! 

A much simpler approach is to use the database views IBM provide out-of-the-box or to create your own custom views to simplify the reporting process.

Three types of database view can be used to extract TADDM data:·

• Detail Panel Views – These database views replicate the CI data views displayed within the “Details Panel” section of the TADDM Product Console. IBM provide the reference file $COLLATION_HOME/etc/views/detail_panel_views.txt which allows TADDM administrators who are familiar with the TADDM Product Console to quickly locate the appropriate database view for each detail panel within the Product Console.

• Building Block Views – These database views replicate the structure used to organize CI data within the Common Data Model (CDM). IBM provide two reference files $COLLATION_HOME/etc/views/create_building_block_views_db2.sql and create_building_block_views_oracle.sql which allow TADDM administrators who are familiar with the CDM structure to quickly locate the corresponding view for each CDM object type. 

• Custom Views – TADDM reports can sometimes require CI attribute information that is spread across multiple building block views. On these occasions TADDM administrators require a good understanding of each building block view design and good SQL skills in order to formulate the necessary SQL join statements to extract their required data. Custom views have been designed to simplify this process. Rather than formulate complex SQL statements themselves, TADDM administrators can use the CDM to identify the attributes they require within a report, they then input this information (in a specific format) in $COLLATION_HOME/bin/user-views.xml and then run $COLLATION_HOME/bin/user_views.sh. The script automatically generates a new custom database view based on the supplied attribute information.

This tip will provide an example of using Detail Panel views to extract CI data in a custom TADDM BIRT Report.

Step 1 – Establish the report requirements

The first step in the process is to clearly define the information required within the BIRT report.
In our example we are required to create a BIRT report to capture information on all AIX and Windows servers running the IBM Sterling Connect:Direct® application.

Here’s the information we have been asked to display within a BIRT report:

Step 2 – Identify the object types and detail panel tab names in the TADDM Product Console

 Step 2 – Identify the object types and detail panel tab names in the TADDM Product Console In order to identify the database panel views required in the report, we must firstly identify the object type and the relevant tab names associated with each of the report attributes displayed in the TADDM Product Console.
In our example, we require server hardware and OS specific information (e.g. FQDN, IP address, OS, CPU, RAM) for both AIX and Windows machines as well as application object specific information (e.g. Connect:Direct version).
As you can see below, AIX computer system specific information is displayed in the Product Console using the object type IBM AIX Computer System.
FQDN, IP address, CPU and RAM attribute information are displayed on the General tab, OS name and version is displayed in the OS tab:

 Windows specific computer system information is displayed in the Product Console using the object type Windows Computer System.
FQDN, IP address, CPU and RAM attribute information are displayed on the General tab, OS name and version is displayed in the OS tab:

 Connect:Direct specific information is displayed in the Product Console using the object type App Server, product version information is displayed in the General tab:

Note: The Object Type attribute is only displayed within the General tab. All related tabs inherit this object type value. For example, the screen shot above shows the General, Runtime, Modules and Application Descriptor tabs. 
The App Server object type can also be used to identify the underlying database views associated with the data displayed in these tabs for our Connect:Direct servers.

Using the TADDM Product Console we are able to confirm that the following object types and tab names are required for our example report:

 

Step 3 – Identify the required database views in detail_panel_views.txt

Step 2 has established the object type, the attributes we require and the tabs they reside on within the TADDM Product Console detail panel.
We can now use this information to locate the appropriate views within  $COLLATION_HOME/etc/views/detail_panel_views.txt.

We will open detail_panel_views.txt in a standard text editor (e.g. Notepad) and search for the App Server object type:

The screen shot below shows the <Layout> stanza for the App Server object type:

The following diagram uses a colour key to illustrate how the Product Console details panel view for the App Server object type is described within detail_panel_views.txt:

Each <Layout> stanza lists each tab found within the Product Console details panel for a specific object type together with its associated database view.
As can be seen above, the database view DP_APP_SERVER_GENERAL_V can be used to return all App Server object attributes held with the General tab.

Using the same approach we can now search through detail_panel_views.txt to identify the database views associated with the IBM AIX Computer System and Windows Computer System object types:

As can be seen above, three database views will be required to populate the required attributes for each OS type:

AIX:

• DP_AIX_COMP_GENERAL_V
• DP_AIX_COMP_OS_V
• DP_APP_SERVER_GENERAL_V

Windows:

• DP_WINDOWS_COMP_GENERAL_V
• DP_APP_SERVER_GENERAL_V
• DP_WINDOWS_COMP_OS_V

 

Step 4 – Identify the Primary & Foreign Keys in each view

In order to construct SQL “JOIN” statements capable of spanning our required AIX and Windows based database views, common primary/foreign keys must be identified. The easiest way to identify primary and foreign keys is to use the database CLI to query each view using a simple SQL statement.  In our example our TADDM database is running on DB2 therefore we will connect to the DB2 database via the DB2 CLI and then run the “describe table <database view>” command to display the column names held within each view.  The same process can be achieved on an Oracle database using the “describe <database view>” command.

 The procedure for connecting to a DB2 TADDM database is as follows:

 1. Log onto to the TADDM database server as the database administrator.

2. Issue the following command to launch the DB2 CLI:

   db2

3. Issue the following command to connect to the CMDB database:

    db2 connect to <TADDM database name>

       e.g. to connect to a TADDM database called “CMDB”:

    db2 connect to cmdb

       A message similar to the following confirms a connection has been successfully established to the database:

 

4. For each database view issue the following command:

   describe table <database view>

      For example, to view the primary and foreign keys of DP_AIX_COMP_GENERAL_V :

As can be seen above, primary and foreign keys are denoted by the following naming convention:

PK_C<no>

Note: Primary keys are always denoted as PK_C1, all other PK_C* instances are foreign keys.

 

Step 5 – Identify the SQL join criteria

Step 4  allowed us to identify the primary and foreign keys within each of our required database views. We now need to determine how we can join these views within a SQL statement.

A useful method for identifying commonality between primary and foreign keys across multiple views is to query each view using a specific server as your reference.

In our example we have used the TADDM Product Console to identify an AIX Connect:Direct application server called “connect1”. 
We will use this machine to determine the join criteria between the DP_AIX_COMP_GENERAL_V,  DP_AIX_COMP_OS_V and DP_APP_SERVER_GENERAL_V views.

1. Within the DB2 interpreter we will issue the following SQL statement to view the primary and foreign keys for the DP_AIX_COMP_GENERAL_V  database view:

select PK_C1,PK_C2 from DP_AIX_COMP_GENERAL_V where NAME_C1=’connect1′

Note: In order to construct this query, the NAME_C1 column was identified by cross-referencing the information displayed in the General tab of the Details panel against the columns listed
when issuing “db2 describe table DP_AIX_COMP_GENERAL_V”:

 

 2. Within the DB2 interpreter we will now issue the following SQL statement to view the primary and foreign keys for the DP_AIX_COMP_OS_V view using the PK_C1 attribute discovered in step 1 as the SYSTEM_ID_C1 attribute:

select PK_C1,PK_C3,PK_C6,PK_C4 from DP_AIX_COMP_OS_V where SYSTEM_ID_C1=’08B7DAD7′

Note: In order to construct this query, the SYSTEM_ID value was identified by cross-referencing the information displayed in the OS tab of the Details panel  for the connect1 device against the columns listed when issuing “db2 describe table DP_AIX_COMP_OS_V”.

3. Within the DB2 interpreter we will now issue the following SQL statement to view the primary and foreign keys for the DP_APP_SERVER_GENERAL_V database view:

select PK_C1,PK_C2,PK_C3,PK_C4 from DP_APP_SERVER_GENERAL_V where SERVER_NAME_C1=’194.129.218.137:1363′

Note: In order to construct this query the SERVER_NAME_C1 value was identified by cross-referencing the information displayed in the General tab of the Details panel for the connect1 device
against the columns listed when issuing “db2 describe table DP_APP_SERVER_GENERAL_V”.

Now we have extracted the primary and foreign keys within each of our three views for our connect1 machine we should be able to identify commonality between each view.

The diagram below shows that the primary key (PK_C1) used by DP_AIX_COMP_GENERAL_V and DP_AIX_COMP_OS_V is also used as a foreign key (PK_C2) in DP_APP_SERVER_GENERAL_V:

 This commonality can be exploited in an SQL “JOIN” statement to return attribute data held for each AIX Connect Direct application server across each database view.

Before moving on to the next step we would use the same approach to identify commonality between DP_WINDOWS_COMP_GENERAL_V, DP_APP_SERVER_GENERAL_V and DP_WINDOWS_COMP_OS_V  by referencing a Windows based Connect:Direct server in the Product Console.

 

Step 6 – Build the required SQL queries

The steps performed in steps 3, 4 and 5 established the AIX, Windows and application server database views that contain the report attributes and SQL “JOIN” criteria which can be used to extract required information from each database view. 
This information can then be used by an experienced SQL user to develop a set of SQL statements to extract the required report data.

The following SQL statements (one for AIX and one for Windows OS types) can be used to populate our example Connect:Direct report:

Windows OS Connect:Direct SQL query:

select distinct
 gen.NAME_C1,
 os.OS_NAME_C3,
 os.SERVICE_PACK_C4,
 gen.ARCHITECTURE_C1,
 gen.CPU_SPEED_C1,
 gen.CPU_TYPE_C1,
 gen.MEMORY_SIZE_C1,
 app.VENDOR_NAME_C1,
 app.PRODUCT_NAME_C1,
 app.PRODUCT_VERSION_C1,
 app.IP_ADDRESS_C4
from 
DP_APP_SERVER_GENERAL_V app
join
 DP_WINDOWS_COMP_GENERAL_V gen
on
 gen.PK_C1 = app.PK_C2
left outer join  
 DP_WINDOWS_COMP_OS_V os
on  
 gen.PK_C1 = os.PK_C1
where 
 app.PRODUCT_NAME_C1=‘Connect:Direct’
order by
 gen.NAME_C1

 

AIX OS Connect:Direct SQL query:

select distinct
 gen.NAME_C1,
 os.OS_VERSION_C3,
 gen.ARCHITECTURE_C1,
 gen.CPU_SPEED_C1,
 gen.CPU_TYPE_C1,
 gen.MEMORY_SIZE_C1,
 app.VENDOR_NAME_C1,
 app.PRODUCT_NAME_C1,
 app.PRODUCT_VERSION_C1,
 app.IP_ADDRESS_C4
from 
 DP_APP_SERVER_GENERAL_V app
join
 DP_AIX_COMP_GENERAL_V gen
on
 gen.PK_C1 =app.PK_C2
left outer join  
 DP_AIX_COMP_OS_V os
on  
 (gen.PK_C1 =os.PK_C1)
where 
 app.PRODUCT_NAME_C1=‘Connect:Direct’
order by
 gen.NAME_C1

 

Step 7 – Create the BIRT report

In step 6 we  created the SQL statements we need to extract the report data. We now need to create a BIRT report which can generate this data.

On the report development workstation we will launch the Eclipse Report Designer and select an appropriate local user workspace location (a designated local file repository):

 

 

Once in the Eclipse editor we will Select  File -> New -> Report:

 

 

We will select the Report project folder as the parent folder, and then type a suitable report name e.g. ConnectDirect.rptdesign , then click Finish.

 

 

The next step is to define the data source. Eclipse BIRT reports can access data from the following types of data sources:

 

• JDBC data sources – The most common type of data source used within BIRT reports. BIRT reports can use the JDBC API to connect to remote databases and access these data sources using SQL queries and stored procedures. This is the preferred method for connecting to the TADDM CMDB database.
• Text files – Text files that BIRT reports use as data sources are typically created by older business systems and applications that create logs.
• XML data – BIRT reports can use XML data, either as a stream or a document.

In our example we are going to configure our report to use a JDBC data source to enable remote connectivity to the TADDM database. In order to successfully connect to the TADDM database, any JDBC data sources created within the Eclipse Report Designer application must use an appropriate JDBC driver. In our case we shall use the DB2 JDBC driver db2jcc.jar which has been downloaded from our TADDM database server onto our Eclipse workstation.

To create a data source to connect to our example DB2 TADDM database we need to perform the following steps:

1. Right-click on Data Sources and select New Data Source from the menu.

 

2. Select JDBC Data Source from the list of data source types and type a suitable data source name. Click the Next button.

 

 

Note: Steps 2.1 and 2.2 are only required if the db2jcc.jar file has not already been loaded within the Eclipse Report Designer. If db2jcc.jar is loaded we can proceed to step 3.

 

2.1. Click the Manage Drivers button in the NewJDBC Data Source Profile window, and then select the Add button to add a new JDBC driver.

 

 

2.2. Navigate to your local db2jcc.jar file then click OK.

 

 

3. Provide the following information to create a new JDBC data source, in our example we are using v3.3 of the DB2 JDBC driver:

 

• Driver Class:  Select com.ibm.db2.jcc.DB2Driver (v3.3) from the drop down list.
• Database URL: This value can be found by searching for com.collation.db.url in  $COLLATION_HOME/etc/collation.properties.
• User Name: This value can be found by searching for com.collation.db.user in $COLLATION_HOME/etc/collation.properties
• Password: The appropriate password for the com.collation.db.user user.
• JNDI URL: <leave blank>

For example:

 

4. Click the Test Connection button to confirm that the entries are correct. You should get a message box stating “Connection Successful”. Click the OK button to close the message box.

 

5. Click the Finish button to create the Data Source.

 

The BIRT data source establishes JDBC connectivity to the TADDM database. Once connectivity has been established BIRT “data sets” can be used to populate the report with TADDM data via SQL SELECT queries or stored procedures.

 

We will now perform the following steps to create the data sets for the Connect Direct report:

 

1. Right-click on Data Sets and select New Data Set from the menu.

 

 

2. We will now specify a logical  Data Set Name for the AIX query (e.g. AIX) and ensure the Data Source value is set to our newly created Data Source name and the Data Set Type is set to SQL Select Query.
Then click Next.

 

 

3. Paste the AIX SQL query created in step 6 into the highlighted pane and click Finish.

 

 

4. Right-click on the AIX data set and select Edit from the menu.

 

 

5. Within the Edit Data Set window click on the Preview Results link highlighted to confirm the SQL query correctly returns the required AIX data from the TADDM database.

 

 

Note: If the preview does not return the required data confirm data source database connectivity and/or modify the SQL SELECT query pane accordingly.

 

6. We now need to  extract the Windows based server information. Right-click on Data Sets and select New Data Set from the menu.

 

 

7. Specify a logical Data Set Name for the Windows query (e.g. Windows) and ensure the Data Source value is set to our newly created Data Source name and the Data Set Type is set to SQL Select Query. Then click Next.

 

 

8. Paste the Windows SQL query created in step 6 into the highlighted pane and click Finish.

 

 

9. Right-click on the Windows data set and select Edit from the menu.

 

 

10. Within the Edit Data Set window click on the Preview Results link highlighted to confirm the SQL query correctly returns the required Windows data from the TADDM database.

 

 

Note: If the preview does not return the required data confirm data source database connectivity and/or modify the SQL SELECT query pane accordingly.

 

At this point you may wish to format the report display to suit any specific presentation requirements you may have, e.g. text alignment, font type, bold, italic, underlining, table format etc.

 

Step 8 – Upload the BIRT report into the TADDM database

 

To upload BIRT reports into the TADDM database we will need to perform the following steps:

 

1. Log into the TADDM Domain Manager console  with an authorized TADDM account

 

 

2. Within the Domain Manager console, select the Analytics drawer highlighted below:

 

 

3. Within the Analytics drawer, select BIRT Reports, then select the Add button highlighted below:

 

 

4. In the Result Attributes window click the Browse button and navigate to your .rptdesign  report file, then input a suitable report Name and Description:

 

 

For example the following information has been provided above:

 

Name: Connect_Direct_Inventory
Description: Connect Direct Inventory Report
File Name: C:workspaceReport projectConnectDirect.rptdesign

 

Click OK to upload the file, the following confirmation message will be displayed to confirm the upload has completed, click Close to close this window:

 

 

5. Within the Domain Manager console, click the Refresh button highlighted below and confirm the custom report is displayed in the report list:

 

The BIRT report can then be run within the Domain Manager by highlighting it then click the RunReport button.

 

The custom TADDM BIRT report is then displayed:

 

Visits: 152