BMC Remedy AR System stored procedures, functions, and tables

Function

FN_ADJUSTED_DATE

Invoked as required by ITSM class and object formulas. The script that inserts the function is FN_ADJUSTED_DATE_ORA.SQL. BMC Analytics for BSM uses these converted dates for reporting and trending metrics. The function supports Daylight Saving Time (DST) for multiple time zones.

Package

PKG_BSM_RELS_PARENT

Contains procedures for populating child data for a root level business service

Package

PKG_BSM_RELS_CHILD

Contains procedures for populating parent data for a root level business service

Package

PKG_ANA_POPULATE_ENUMS

Contains the ANA_Populate_Enums procedure to populate the ANA_ALL_ENUM_VALUES table

Stored procedure

POPULATE_FISCAL_CALENDAR

Populates the ANA_FISCAL_CALENDAR view. This stored procedure is invoked by AR Workflow on the ANA:Fiscal Calendar Form when the form is updated in BMC Remedy Mid Tier. The script that inserts the stored procedure is PC_POPULATE_FISCAL_CALENDAR_ORA.SQL.

Stored procedure

DENORMALIZE_BSM_RELS_PARENT

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every root-level business service under the specified dataset ID.

Stored procedure

DENORMALIZE_BSM_RELS_CHILD

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every leaf-level business service under the specified dataset ID.

Stored procedure

PC_BSM_RELS_TRAVERSAL_PARENT

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every child or grandchild of a root-level business service under the specified dataset ID.

Stored procedure

PC_BSM_RELS_TRAVERSAL_CHILD

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every parent or grandparent of a leaf-level business service under the specified dataset ID.

Stored procedure (Package body)

ANA_POPULATE_TIMEZONE_DST

The procedure queries the ANA_TZ_DST_VIEW view and inserts data into the  NA_TZ_DSTTIME table.

Stored procedure (Package specifications)

PKG_ANA_TZ_DST

The procedure queries ANA_TZ_DST_VIEW view and inserts data into the ANA_TZ_DSTTIME table.

Stored procedure

ANA_Populate_Enums

A database procedure that reads the enumerated field values from the BMC Remedy AR System Field_Dispropp table and populates the ANA_ALL_ENUM_VALUES table. The script that is run to install this procedure is ANA_ALL_ENUM_VALUES_DDL_ORACLE.SQL.

Table

DENORMALIZE_BSM_REL ATIONSHIPS

A database procedure reads the business service models and the relationships in BMC Atrium CMDB and populates the business service model relationships in the DENORMALIZE_BSM_RELATIONSHIPS table. The table is created and populated during the installation, but you can update the data in the table using the procedures DENORMALIZE_BSM_RELS_PARENT (for every root-level business service under the specified dataset ID) and DENORMALIZE_BSM_RELS_CHILD (for every leaf-level business service under a given dataset ID).

Table

ANA_TIMEZONE_INFO

Lists the time zones used in the reports and the related information. This table is created by the following scripts:

• ANA_TIMEZONE_INFO_DDL_ORA.SQL
• ANA_TIMEZONE_INFO_DML_ORA.SQL

Table

ANA_ALL_ENUM_VALUES

Captures the data for enumerated values resolved English strings and corresponding localized strings and locale information. This table is created by the ANA_Populate_Enums procedure.

Table

ANA_REPORT_LOCALE

Stores the user-specified locale settings for the reports. This table is created by the following scripts:

• ANA_REPORT_LOCALE_DDL_ORA.SQL
• ANA_REPORT_LOCALE_DML_ORA.SQL

Table

ANA_REPORT_TRANSLATION

Stores the report definition settings for the reports in various languages. This table is created by the following scripts:

• ANA_REPORT_TRANSLATION_DDL_ORA.SQL
• ANA_REPORT_TRANSLATION_EN_DML_ORA.SQL (English)
• ANA_REPORT_TRANSLATION_JA_DML_ORA.SQL (Japanese)
  (This script is not executed, but it is stored in the installation directory.)

Table

ANA_TZ_DSTTIME

Stores the precalculated data for the DST start time and end time of the preferred time region, which is used for converting Epoch time to the preferred time in the date conversion function. This table requires the ANA_TIMEZONE_INFO table, which is also created during installation and contains the timezone code.

View

ANA_TZ_DST_VIEW

This view is used to populate the ANA_TZ_DSTTIME table. The view query reads the data from the ANA_TIMEZONE_INFO table and precalculates the DST start time and end time. This view requires the ANA_TIMEZONE_INFO table, also created during installation, that stores the time zone region code.

View

ANA_FISCAL_CALENDAR

Used by the Populate_Fiscal_Calendar procedure, invoked by workflow on the ANA:Fiscal Calendar form

Function

FN_ADJUSTED_DATE

Invoked as required by IT Service Management class and object formulas. The following scripts  nsert the function:

• FN_ADJUSTED_DATE_MSSQL.SQL
• DROP_FN_ADJUSTED_DATE_MSSQL.SQL
  BMC Analytics for BSM uses these converted dates for reporting and trending metrics. The function supports Daylight Saving Time for multiple time zones.

Function

FN_ANA_DENORM_WITH_FN_
MSSQL.SQL

Retrieves parent/child data for a given business service

Function

FN_ANA_DENORM_UNODENAME_
MSSQL.SQL

Called by FN_ANA_DENORM_WITH_FN_MSSQL.SQL function to retrieve the ultimate parent/child business service of a given business service

Stored Procedure

ANA_POPULATE_TIMEZONE_DST

Populates the ANA_TZ_DSTTIME table. This procedure reads the data from ANA_TIMEZONE_INFO table and precalculates the DST start time and end time for the preferred time region, which is used for converting Epoch time to the preferred time in the date conversion function. This stored procedure requires the ANA_TIMEZONE_INFO table, which stores the time region code.

Stored Procedure

PC_POPULATE_FISCAL_CALENDAR

Populates the ANA_FISCAL_CALENDAR view. This stored procedure is invoked by AR Workflow on the ANA:Fiscal Calendar Form when updated in BMC Remedy AR System. The script that inserts the stored procedure is PC_POPULATE_FISCAL_CALENDAR_MSSQL.SQL.

Stored Procedure

PC_DENORMALIZE_BSM_RELS_
PARENT_MSSQL.SQL

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every root level business service under a given dataset ID. The following scripts install the stored procedure:

• DROP_PC_DENORMALIZE_BSM_RELATIONSHIPS_
  MSSQL.SQL
• PC_DENORMALIZE_BSM_RELATIONSHIPS_
  MSSQL.SQL

Stored Procedure

PC_DENORMALIZE_BSM_RELS_
CHILD_MSSQL.SQL

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. This procedure is run for every leaf level business service under a given dataset ID. The following scripts install the stored procedure:

• DROP_PC_DENORMALIZE_BSM_
  RELATIONSHIPS_MSSQL.SQL
• PC_DENORMALIZE_BSM_RELATIONSHIPS_
  MSSQL.SQL

Stored Procedure

PC_BSM_RES_PARENT_
TRAVERSAL_MSSQL.SQL

A database procedure called from PC_DENORMALIZE_BSM_RELATIONSHIPS that traverses one business service at a time in a recursive mode. This procedure is run for run every child or grandchild of a root-level business service. The following scripts are run to install the procedure:

• DROP_PC_BSM_RELATIONSHIPS_TRAVERSAL_
  MSSQL.SQL
• PC_BSM_RELATIONSHIPS_TRAVERSAL_
  MSSQL.SQL

Stored procedure

PC_BSM_RES_PARENT_
TRAVERSAL_MSSQL.SQL

A database procedure called from PC_DENORMALIZE_BSM_RELATIONSHIPS that traverses one business service at a time in a recursive mode. This procedure is run for run every parent or grandparent of a leaf-level business service. The following scripts are run to install the procedure:

• DROP_PC_BSM_RELATIONSHIPS_TRAVERSAL_
  MSSQL.SQL
• PC_BSM_RELATIONSHIPS_TRAVERSAL_
  MSSQL.SQL

Stored procedure

ANA_POPULATE_ENUMS

A database procedure that reads the enumerated field values from the BMC Remedy AR System Field_Dispropp table and populates the ANA_ALL_ENUM_VALUES table. The script that is run to install this procedure is ANA_ALL_ENUM_VALUES_DDL_MSSQL.SQL.

Table

ANA_TZ_DSTTIME

Stores the precalculated data for the DST start time and end time of the preferred time region; used for converting Epoch time to the preferred time in the date conversion function. This table requires the ANA_TIMEZONE_INFO table, which is also created during installation and contains the timezone code.

Table

ANA_TIMEZONE_INFO

Lists the time zones used in the reports and the related information. This table is created by the following scripts:

• DROP_TABLE_ANA_TIMEZONE_INFO_MSSQL.SQL
• ANA_TIMEZONE_INFO_DDL_MSSQL.SQL
• ANA_TIMEZONE_INFO_DML_MSSQL.SQL

Table

DENORMALIZE_BSM_
RELATIONSHIPS

A database procedure reads the business service models and the relationships in BMC Atrium CMDB from the BMC_CORE_BMC_BASERELATIONSHIP table and populates the DENORMALIZE_BSM_RELATIONSHIPS table. Relationships for any business service are included, either at the root level or leaf level for a specified dataset ID. The installation utility creates and initially populates this table. The following scripts are run to install the table:

• DROP_TABLE_DENORMALIZE_BSM_RELATIONSHIPS_
  MSSQL.SQL
• ANA_DENORMALIZE_BSM_RELATIONSHIPS_DDL_
  MSSQL.SQL

Table

ANA_ALL_ENUM_VALUES

Captures the data for enumerated values resolved English strings and corresponding localized strings and locale information. This table is created by the  NA_ALL_ENUM_VALUES_DDL_MSSQL.SQL script.

Table

ANA_REPORT_LOCALE

Stores the user-specified locale settings for the reports. This table is created by the following scripts:

• DROP_TABLE_ANA_REPORT_LOCALE_MSSQL.SQL
• ANA_REPORT_LOCALE_DDL_MSSQL.SQL
• ANA_REPORT_LOCALE_DML_MSSQL.SQL

Table

ANA_REPORT_TRANSLATION

Stores the report definition settings for the reports in various languages. This table is created by the following scripts:

• DROP_TABLE_ANA_REPORT_TRANSLATION_MSSQL.SQL
• ANA_REPORT_TRANSLATION_DDL_MSSQL.SQL
• ANA_REPORT_TRANSLATION_EN_DML_MSSQL.SQL (English)
• ANA_REPORT_TRANSLATION_JA_DML_MSSQL.SQL (Japanese)

View

ANA_Fiscal_Calendar

Used by the Populate_Fiscal_Calendar procedure, invoked by workflow on the ANA:Fiscal Calendar form