Analyzing Metadata and Procedures

Analyzing Metadata and Procedures

Describes how to analyze procedures using Impact Analysis, and how to view Data Profiling for the columns in a synonym.
Describes how to analyze procedures using Impact Analysis, and how to view Data Profiling for the columns in a synonym.

Impact Analysis is accessible only through the DB2^® Web Query Developer Workbench.

Parent topic:
IBM DB2 Web Query for System i Overview

Analyzing Procedures With the Impact Analysis Tool

You can use the Impact Analysis tool to generate a report that identifies the procedures that access a specific Master File or field within a Master File. This tool helps you analyze the potential impact of modifying or deleting Master Files or fields. The Impact Analysis tool enables you to analyze data, control search criteria, save reports, and interactively open and edit procedures based on search results.
Impact Analysis searches Business Views in addition to FOCUS procedures. This enables you to see if changes in the original Master File will impact fields used in the Business View. For more information about Business Views, see Creating Business Views in Developer Studio.
The Impact Analysis tool is accessible from Developer Workbench or from within the tools in the Synonym Editor and Business Views. When Impact Analysis is launched from the Synonym Editor or Business Views, it searches files based on the application path of the reporting server. From the interface, it enables you to select applications/domains to be searched.

View Impact Analysis Results from the Synonym Editor

When Impact Analysis is launched from within the Synonym Editor, it searches files based on the application path of the reporting server.

From the Projects or Data Servers area, double-click the Master File or select Edit in Synonym Editor from the File menu.
The Master File opens to the Tree View tab in the Synonym Editor.

Select the Synonym File name, or a column field, right-click and select Impact Analysis from the context menu.
The following image shows the context menu used to launch the Impact Analysis tool.

Impact Analysis is also available from the Business View tab.
The Impact Analysis results are displayed in a report spreadsheet.

You may use the results toolbar to view server messages, print the report, copy data as text, and export the report.

Click Save from the File menu to save the Impact Analysis results.

Click Close from the File menu to close the Impact Analysis results window and return to the Synonym Editor.

Use the Impact Analysis Tool From the Interface

From the Projects or Data Servers area, highlight a Master File and select Impact Analysis from the File menu.

You may also select a Impact Analysis from the right-click context menu of a Master File. This interface enables you to select applications or domains to search.
The Impact Analysis tool opens with the New Report tab displaying the selected (Master) File Name and default Search Paths.

To search for all procedures that access a specific Master File or field, perform one of the following:

For the Master File you selected when opening the tool, it will be searched by default. (Go to the next numbered step.)

For a different Master File, click the ellipsis button to the right of the File Name search field and select a different Master File in the Open dialog box that opens.

For a single field within a Master File, after you select the desired Master File, click the ellipsis button to the right of the Field Name search field and double-click a field name in the Master File pop-up box.

Optionally, to search for procedures in directory paths not listed by default in the Search Paths pane, add more search paths by clicking the folder icon above the Search Paths area and selecting one or more folders in the Browse for Folder dialog box that opens.

You can also delete search paths by highlighting a search path and clicking the Delete icon above the Search Paths area.

Click Analyze to display a report in the Impact Analysis Results pane.
The following image shows the New Report tab of the Impact Analysis dialog box populated with File Name search criteria, multiple Search Paths, and a report displayed in the Impact Analysis Result pane.

You have options to edit procedures, print the report, delete an item in the report, and export a report.
Exported reports are XML formatted, have an .IAR extension, and are saved in the following default directory (unless you specify a different location):
drive:\ibi\DevStudioreleasenumber\bin

Click the Saved Reports tab to access all previously created reports.
All Impact Analysis reports are automatically saved in the following XML formatted file (unless you manually delete a report):
drive:\ibi\DevStudioreleasenumber\bin\IARepository.dita
Information is appended to this file as new analysis reports are performed. You have options to view reports, import previously exported reports, and delete reports.

Viewing Data Profiling Characteristics

Data Profiling provides data characteristics for the columns in a synonym. You can display the characteristics for all the columns in a synonym or segment, or for an individual column.

Data Profiling is not available if your adapter is not configured correctly.
For alphanumeric columns, Data Profiling provides the segment, format, count of distinct values, total count, patterns count, maximum, minimum, and average length, minimum and maximum values, and number of nulls. Patterns count shows the number of patterns found in each alphanumeric column.
For numeric columns, Data Profiling provides the segment, format, count of distinct values, total count, maximum, minimum, and average value, and number of nulls.
Data Profiling for an individual column provides access to Statistics, Patterns, Values, and Outliers reports.

Data Profiling a Synonym or Segment

Data Profiling provides information on all the columns in a synonym or segment. You can also drill down to the Values or Patterns reports for an individual column from a synonym or segment’s Data Profiling report.

View Data Profiling for a Synonym or Segment

To view the data profiling information for a synonym or segment’s columns:

From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.
The Synonym Editor opens to the Tree View tab.

Right-click the synonym or segment name and, select Data Profiling.

The Data Profiling information displays in the workspace.

You may use the data profiling results toolbar to view server messages, print the report, copy data as text, and export the report.

Optionally, you can click a column name or patterns count (for alphanumeric columns) to drill down to the Values or Patterns reports, respectively.
This is a partial Values report produced by clicking a column name.

This is a Patterns report produced by clicking a Patterns Count value.
The image below is an example of an address column.

For pattern analysis, a "9" represents a digit, an "A" represents any upper case letter, and an "a" represent any lower case letter. All printable special characters are represented by themselves, and unprintable characters are represented by an "X".

Data Profiling a Single Column

Data Profiling for an individual column provides access to four reports:

Statistics shows the same information as a Data Profile report for a synonym or segment.
For alphanumeric columns, the Statistics report provides the segment, format, count of distinct values, total count, patterns count, maximum, minimum, and average length, minimum and maximum values, and number of nulls.
For numeric columns, the Statistics report provides the segment, format, count of distinct values, total count, maximum, minimum, and average value, and number of nulls.

Patterns, which is only available for alphanumeric columns, shows patterns of letters, digits, and special characters, as well as counts and their percent.

Values shows unique values and their percents.

Outliers shows the ten highest and lowest distinct values and their counts.

These reports are available by right-clicking a column in the Synonym Editor and selecting Data Profiling.

The Patterns report shows the number of patterns found for each alphanumeric column.

Clicking on a patterns count displays the actual patterns.
The image below is an example of an address column.

For pattern analysis, a "9" represents a digit, an "A" represents any upper case letter, and an "a" represent any lower case letter. All printable special characters are represented by themselves, and unprintable characters are represented by an "X".

View Data Profile Statistics

To view the statistical data profiling information for a single column:

From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.
The Synonym Editor opens to the Tree View tab.

Right-click a column and, select Statistics from the Data Profiling submenu.

The Statistical Data Profiling information displays in the workspace.

Optionally, you can click a column name or patterns count (for alphanumeric columns) to drill down to the Values or Patterns reports, respectively.

View Data Profile Patterns

Data Profile Patterns shows patterns of letters, digits, and special characters, as well as counts. This is only available for alphanumeric columns.
To view the patterns data profiling information for a single column:

From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.
The Synonym Editor opens to the Tree View tab.

Right-click a column and, select Patterns from the Data Profiling submenu.

The Patterns Data Profiling information displays.

For pattern analysis, a "9" represents a digit, an "A" represents any upper case letter, and an "a" represent any lower case letter. All printable special characters are represented by themselves, and unprintable characters are represented by an "X".

View Data Profile Values

Data Profile Values shows unique values.
To view the values data profiling information for a single column:

From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.
The Synonym Editor opens to the Tree View tab.

Right-click a column and, select Values from the Data Profiling submenu.

The Values Data Profiling information displays.

View Data Profile Outliers

Data Profile Outliers shows the 10 highest and lowest distinct values.
To view the outliers data profiling information for a single column:

From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.
The Synonym Editor opens to the Tree View tab.

Right-click a column and, select Outliers from the Data Profiling submenu.

The Outliers Data Profiling information displays.

Outliers produce a maximum of 10 highest and lowest distinct values, if they exist.