KB Update SQL Tool - CI entry tables

Introduction

As explained in the previous section, in order to modify and update the Analysis schema, a set of entry tables are injected with data that defines how the Analysis schema will be altered.

Entry tables

The following is an exhaustive list of entry tables ordered by function. Each table and its function is described in more detail below.

CI_ERRORS

This table will be injected with data if any errors or data inconsistencies are found during the data check process (this takes place when the job is executed).

GUID_OBJECTS

When the results of a CAST analysis are stored in the Analysis schema, all objects (regardless of the analyzer type) are always given a unique numerical OBJECT_ID to identify the object – this OBJECT_ID is then used throughout the Analysis schema to identify the object. At the same time, some objects are also given a unique OBJECT_GUID to provide more details about the object, however, this is not the case for the results of all CAST analyzers. The results of the following CAST analyzers are awlays given an OBJECT_ID and an OBJECT_GUID:

• ABAP Analyzer
• C++ Analyzer
• Mainframe Analyzer
• J2EE Analyzer
• SQL Analyzer
• SSRS Analyzer
• SSIS Analyzer
• TIBCO Analyzer
• Universal Analyzer
• Universal Importer

You can query the CAST System View “CTV_GUID_OBJECTS” (providing the view is up-to-date) to see which objects have an OBJECT_GUID and which do not.

The KB Update SQL Tool always requires an object’s OBJECT_GUID to function correctly and to determine which object your update is being performed on. As a result, if any of the objects involved in the update do NOT have an OBJECT_GUID, then you must either:

• Manually create a unique OBJECT_GUID for the objects in question as part of your query that will enter data in the entry tables (i.e. enter values in the GUID_OBJECTS table) – modification based on the object’s ID.
• Or use the OBJECT_ID extracted from the CTV_GUID_OBJECTS view (in which case the KB Update SQL Tool will create the OBJECT_GUID itself and there is no need to enter values in the GUID_OBJECTS table) – modification based on the objects’ GUID.

Thus, to conclude, the GUID_OBJECTS table only needs to be filled with data when you want to modify objects that do NOT have an OBJECT_GUID and you want to create the OBJECT_GUID manually yourself. If you are using the OBJECT_ID (as oppose to the OBJECT_GUID), then you do not need to enter values in the GUID_OBJECTS table.

CI_OBJECTS

This table is used when you want to add a new object to the Analysis schema.

Note that with regard to defining the column OBJECT_TYPE, you must retrieve the existing type (use a CAST System View to do so):

select TYPE_NAME, TYPE_DESCRIPTION
from CTV_OBJECT_TYPES

CI_NO_OBJECTS

This table is used when you want to remove an existing object from the Analysis schema. You must enter either an OBJECT_ID or an OBJECT_GUID.

CI_PARENTS

This table is used when you want to add a parent type link (Belongs To) to an existing object. You must enter either a PARENT_ID or a PARENT_GUID.

CI_STR_PROPERTIES

This table is used when you want to modify/add an existing object’s alphanumeric property (for example object comments or business functions). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES

The column ORDER_NUMBER is used to split properties with a VALUE string size above 255 characters. For example if the VALUE of the property you wanted to add or modify was (for example) 389 characters, you would need to add two rows that contained the split comment as follows. The first row contains the first 253 characters of the property, the second row contains the last 135 characters, differentiated by the value in the ORDER_NUMBER column:

• ORDER_NUMBER = 1 VALUE = A System Administrator configures the upper limit to the degree of parallelism using server-wide configuration parameters. Session-wide and query-level options can further limit the degree of parallelism. These limits set both the total number of worker
• ORDER_NUMBER = 2 VALUE = processes that can be used in a parallel query and the total number of worker processes that can be used for hash-based access methods.

CI_INT_PROPERTIES

This table is used when you want to modify/add an existing object’s numeric property (for example the number of inner comment lines, number of code lines etc.). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES

CI_NO_PROPERTIES

This table is used when you want to delete an existing object’s property (for example the number of inner comment lines, number of code lines etc.). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES

CI_NO_LINKS

This table is used when you want to remove an existing link between two existing objects). You must enter either a CALLER_ID/CALLED_ID pair or a CALLER_GUID/CALLED_GUID pair.

CI_LINKS

This table is used when you want to add a link between two existing objects. You must enter either a CALLER_ID/CALLED_ID pair or a CALLER_GUID/CALLED_GUID pair.

CI_INT_LINK_PROPERTIES

This table is used when you want to modify/add an existing link’s numeric property (for example “the line number where the first column of a composite index is not used in a WHERE clause”). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES

CI_STR_LINK_PROPERTIES

This table is used when you want to modify/add an existing link’s alphanumeric property (for example “the line number where the first column of a composite index is not used in a WHERE clause”). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES

The column ORDER_NUMBER is used to split properties with a VALUE string size above 255 characters. For example if the VALUE of the property you wanted to add or modify was (for example) 389 characters, you would need to add two rows that contained the split comment as follows. The first row contains the first 253 characters of the property, the second row contains the last 135 characters, differentiated by the value in the ORDER_NUMBER column:

• ORDER_NUMBER = 1 VALUE = A System Administrator configures the upper limit to the degree of parallelism using server-wide configuration parameters. Session-wide and query-level options can further limit the degree of parallelism. These limits set both the total number of worker
• ORDER_NUMBER = 2 VALUE = processes that can be used in a parallel query and the total number of worker processes that can be used for hash-based access methods.

CI_NO_LINK_PROPERTIES

This table is used when you want to delete an existing object’s property (for example “the line number where the first column of a composite index is not used in a WHERE clause”). You must enter either an OBJECT_ID or an OBJECT_GUID.

Note that with regard to defining the column PROP_NAME, use a CAST System View to retrieve the existing property name you require:

select PROP_NAME, PROP_DESCRIPTION
from CTV_PROPERTIES