Schema Tables: The Backbone of Your Application’s Data

In any robust application, especially those that manage complex business processes and data, understanding how that data is structured and stored is paramount. This is where the concept of Schema Tables comes into play. Think of them as the blueprints and the foundational building blocks that define the organization, relationships, and constraints of your application’s data. For users of platforms like BMC Remedy or BMC Helix, delving into schema tables isn’t just an academic exercise; it’s a practical necessity for effective administration, troubleshooting, and customization.

This article aims to demystify schema tables, particularly within the context of BMC’s offerings, by exploring their creation, purpose, and the intricate relationships they hold. We’ll break down the essential tables, their roles, and how they contribute to the overall functionality of your application.

Understanding the Foundation: Installation Sequence and Core Tables

When you install or upgrade an application like BMC Remedy Action Request System (ARS), the database schema isn’t built haphazardly. There’s a deliberate sequence to ensure that dependencies are met and the database is correctly initialized. This sequence is critical for a stable and functional environment.

The typical sequence for creating essential schema tables during an ARS installation is as follows:

1. control: This table often serves as a master table, holding crucial configuration information and system-level settings. It’s the first to be established, setting the stage for subsequent components.
2. controlRecordIds: This table likely manages unique identifiers for records within the control system or other core components. It ensures that each critical piece of metadata has a distinct identity.
3. arschema: This is a pivotal table. It stores metadata about all the forms (or schemas, in ARS terminology) defined within the application. Every form you create, every field you add, and their properties are cataloged here. It’s the central registry for your application’s data structures.
4. schema_index: As the name suggests, this table is crucial for performance. It stores information about indexes created on various schema tables, enabling faster data retrieval and query execution. Efficient indexing is key to a responsive application.
5. schema_group_ids: This table likely handles the grouping of schemas, potentially for organizational purposes, access control, or licensing. It helps in managing collections of related forms.

Database User and Credentials:

It’s important to note that these operations are typically performed by a dedicated database user. In a default BMC ARS setup, this user is often named ARAdmin, with a default password like AR#Admin#. While these are defaults, it’s a best practice to change these credentials for security reasons in a production environment. This user has elevated privileges within the Arsystem database, allowing it to manage these critical schema tables.

Note: Always refer to the official BMC documentation for the most up-to-date and specific information regarding database users and their roles for your particular version of ARS or Helix.

Schema Tables for Core Application Components

Beyond the foundational tables, ARS creates specific schema tables to manage the metadata for its core application components. Understanding these is vital for administrators and developers who interact with these components.

Active Links: Driving Client-Side Logic

Active Links are the workhorses of client-side automation in ARS. They trigger actions based on user interactions or data changes within the client application. Their metadata is stored in dedicated tables:

• dbo.actlink: This table contains the core definitions of your active links. It stores information such as the active link name, its conditions, and the actions it will perform.
• dbo.actlink_: This table acts as a repository for appending specific action details. The underscore suffix indicates that it’s a related table, often holding more granular information about the actions associated with an active link. For instance, if an active link has multiple actions, their specifics might be detailed here.

Filters: The Heart of Server-Side Automation

Filters are the server-side equivalent of active links, executing logic and actions when data is submitted, modified, or viewed. They are crucial for enforcing business rules, performing calculations, and integrating with other systems.

• dbo.filter: Similar to actlink, this table stores the fundamental definitions of your filters, including their names, conditions, and the execution order.
• dbo.filter_: This table complements dbo.filter by storing more detailed information about the actions performed by the filters. It allows for a comprehensive definition of complex filter logic.

Escalations: Automating Time-Based Actions

Escalations are designed to perform actions based on time. They can be used to send reminders, reassign tasks after a certain period, or trigger other automated processes. Their configuration is managed in:

• dbo.escalation: This table holds all the metadata for your escalations, including their names, scheduling, conditions for execution, and the actions they will perform.

Interview Tip: When asked about how ARS handles automation, mentioning Active Links, Filters, and Escalations and their corresponding database tables (actlink, filter, escalation) demonstrates a strong understanding of the system’s architecture.

The T, B, and H Tables: Managing Transactional Data and Attachments

When you save a form in ARS, the system doesn’t just store the data in a single location. It often leverages a tripartite table structure (T, B, H) to manage different aspects of the saved data. This design offers flexibility and efficiency in handling various data types and requirements.

• T – Transaction Table: This is where the primary transactional data of your form resides. When you enter information into fields on a form, the core data is stored in this table. Think of it as the main repository for your records.
• B – Binary Table: This table is dedicated to handling binary data, most commonly associated with attachments. When users upload files (documents, images, etc.) to a form, these files are stored here. Additionally, it can also store information related to active links (though the primary definition resides in actlink) and other binary-related metadata.
• H – History Table: This table is invaluable for auditing and tracking changes. It stores the status history of records. Whenever a record’s status changes, or significant updates occur that are configured for history tracking, the relevant details are logged in the H table. This allows you to trace the lifecycle of a record and understand how it evolved over time.

Schema tables like T, B, and H are fundamental to understanding how ARS manages data persistence and evolution.

Managing Menus and Field Enumerations

Interactive applications often rely on menus and predefined lists to guide user input and ensure data consistency. ARS uses specific schema tables to manage these features.

Menu Table: The Source of Dropdowns and Lists

Menus in ARS provide users with selectable options, often displayed as dropdown lists in form fields. The definition of these menus is stored in:

• dbo.char_menu: This table holds the definitions of your character menus. Each entry likely contains the menu name and the associated menu items or SQL queries used to populate the menu.

Menu Associations: Linking Values to Fields

When you associate a menu with a particular field, or when you define enumerated values directly for a field (e.g., a Status field with options like “New,” “Assigned,” “Closed”), ARS stores this relationship in specific tables:

• dbo.field_enum (id): This table is instrumental in defining enumerated values for fields. It stores the unique identifier (ID) for each enumerated value.
• field_enum_value: This table holds the actual values for the enumerations. It’s linked to field_enum and provides the displayable text (e.g., “New,” “Assigned”) and potentially internal values associated with each enumerated option for a field.

By examining these tables, you can understand how dropdowns and predefined lists are populated and linked to your forms, ensuring data integrity and a user-friendly experience.

Form IDs: Differentiating Form Types

In ARS, not all forms are created equal. The system categorizes forms based on their functionality and purpose. Each type is assigned a unique numerical identifier, which is often used internally by the system and can be referenced in various configurations and logs.

• Regular (ID = 1): These are standard forms used for everyday data entry and display.
• Display (ID = 4): These forms are typically used for read-only purposes or for presenting aggregated information without allowing direct modification.
• Join (ID = 2): Join forms are a powerful feature that allows you to present data from multiple underlying forms as if it were a single form. They are essential for creating consolidated views of related information.
• View (ID = 3): View forms are often used to present data from a database view, providing a way to access structured data without directly interacting with the underlying tables.
• Vendor (ID = 5): These forms are typically associated with vendor-specific integrations or functionalities, allowing for specialized data handling.

Understanding these Form IDs is crucial when interpreting system logs, debugging issues, or writing custom scripts that interact with form metadata. The arschema table would store this Form ID for each defined form.

Global Unique Identifiers (GUIDs): Ensuring Uniqueness Across Servers

In distributed environments or when migrating data between different ARS servers, ensuring that each form has a unique identity is critical. This is where GUIDs (Globally Unique Identifiers) come into play.

A GUID is a 128-bit number used to uniquely identify information in computer systems. In ARS, a GUID can be generated and assigned to a form to ensure it can be distinguished from any other form, even if they have the same name, on different servers or at different times.

How to Create a GUID for a Form:

To implement GUIDs for your forms, you can leverage a character field and set its property to use database ID 179. This property designation signals to the system that this field should store a GUID for the form.

Official Documentation Link: For detailed information on GUIDs and their management in BMC Remedy, refer to BMC’s official documentation. A good starting point would be searching for “BMC Remedy ARS GUIDs” on docs.bmc.com.

Diary Field: Capturing Rich Text and Audit Trails

The Diary field in ARS is a versatile field type that allows users to enter rich text, notes, and comments. It’s often used for logging interactions, providing detailed explanations, or capturing audit information. The internal representation of Diary field data in the database is quite structured:

When a Diary field is modified, its value in the database is formatted as:

[modified timestamp in seconds -| User -| Data]

If the field is modified further, new entries are directly appended to this string, maintaining a chronological log of all entries within that single Diary field.

This format is crucial for administrators and developers who might need to parse this data for reporting, auditing, or custom processing. It provides a built-in history of entries within the field itself.

Attachment Pool: Managing File Storage

Handling attachments efficiently is a common requirement for business applications. ARS employs a sophisticated mechanism for managing file attachments, utilizing dedicated database tables to store and link these files to specific records.

When attachments are used, ARS typically creates two primary tables to manage the attachment pool:

1. BSchema_id: This table is associated with the binary data of attachments for a specific schema (form). When an attachment is added to a record, three columns are created or populated in the binary table (often referred to as the ‘B’ table for that schema):
   • C: This column depicts the path of the file. It tells the system where the file is stored on the server or in the attachment repository.
   • CO: This column stores the Original size of the file, typically in bytes.
   • CC: This column stores the Compressed size of the file, indicating how much space it occupies after any compression is applied.
2. Battachpoolid: This table is more focused on the association between a record’s identifier (Request ID) and the actual binary data of the attachment. It links a specific request or record to its associated files, allowing the system to retrieve the correct attachment when viewing the record.

This two-tiered approach allows for efficient storage and retrieval of attachments, separating metadata about the file’s location and size from the actual binary content and its linkage to a specific record.

Database tables for attachments are critical for understanding how file storage is managed within ARS.

Troubleshooting Common Schema Table Issues

Working with database tables, especially in a complex system like ARS, can sometimes lead to issues. Here are a few common problems and how to approach them:

1. Performance Degradation: Slow Queries and Application Unresponsiveness

Problem: The application is sluggish, and queries take a long time to complete.

Cause: This is often due to missing or poorly designed indexes on frequently queried schema tables (like arschema, actlink, filter, and the T, B, H tables). Large tables without proper indexing can lead to full table scans, which are very inefficient.

Troubleshooting Steps:

• Analyze your queries: Use database performance tools (e.g., SQL Server Management Studio’s execution plan analysis) to identify slow-running queries.
• Check existing indexes: Review the indexes defined on critical schema tables. Are they appropriate for the common query patterns?
• Create or modify indexes: Based on your analysis, create new indexes or modify existing ones on relevant columns. For example, if you frequently search for forms by name, ensure there’s an index on the name column in the arschema table.
• Monitor index fragmentation: Over time, indexes can become fragmented. Regularly rebuild or reorganize indexes to maintain optimal performance.

Official Documentation Link: Refer to BMC’s documentation on performance tuning and database indexing for ARS/Helix for specific guidance.

2. Data Inconsistency or Missing Data

Problem: Records are not appearing as expected, or data seems to be missing from forms.

Cause: This could be due to issues with relationships between tables, incorrect configuration of filters, or problems with attachment handling.

Troubleshooting Steps:

• Examine filter logs: If filters are involved in data manipulation or creation, check their execution logs for errors.
• Verify join form configurations: If you’re using join forms, ensure the underlying forms are correctly linked and that the join conditions are accurate.
• Check attachment table integrity: If attachments are involved, ensure the `BSchema_id` and `Battachpoolid` tables are consistent and that file paths are valid.
• Review the arschema table: Ensure the form definition itself is intact and hasn’t been corrupted.

3. Attachment Issues: Files Not Uploading or Downloading

Problem: Users cannot upload new attachments, or existing attachments cannot be downloaded.

Cause: This can stem from issues with the database tables responsible for attachments, file system permissions, or network connectivity.

Troubleshooting Steps:

• Verify BSchema_id and Battachpoolid tables: Check for any corruption or missing entries. Ensure the tables are accessible to the ARS service account.
• Check file system permissions: Ensure the ARS server has read/write permissions to the directory where attachments are stored.
• Review ARS server logs: Look for specific error messages related to file I/O or attachment handling.
• Test network connectivity: If attachments are stored on a network share, ensure connectivity and permissions are correctly configured.

Schema Tables and Interview Relevance

Understanding schema tables is not just for day-to-day administration; it’s a common topic in interviews for roles such as:

• BMC Remedy Administrator
• BMC Helix Administrator
• AR System Developer
• Database Administrator (DBA) specializing in application databases
• IT Service Management (ITSM) Consultant

Interviewers might ask questions like:

• “Can you describe the purpose of the arschema table?”
• “How does ARS handle attachments at the database level? Which tables are involved?”
• “What is the difference between a Filter and an Active Link, and where is their metadata stored?”
• “Explain the T, B, and H tables and their roles.”
• “Why is the installation order of schema tables important?”
• “How would you troubleshoot slow performance related to schema tables?”

Being able to articulate the relationships between forms, fields, and their corresponding database tables demonstrates a deep understanding of the application’s architecture and operational intricacies.

Conclusion

Schema tables are the unsung heroes of any application, providing the structure and definitions that allow data to be stored, managed, and interacted with. For users of BMC Remedy and BMC Helix, a thorough understanding of these tables – from the foundational installation sequence to the specific tables for active links, filters, escalations, and data storage – is indispensable for effective administration, troubleshooting, and customization. By demystifying these components, you gain a more profound insight into how your application functions and how to optimize its performance and reliability.

Whether you’re a seasoned administrator or just starting your journey, investing time in understanding schema tables will undoubtedly pay dividends in your ability to manage and leverage your application’s data effectively.

For further in-depth information, always consult the official BMC documentation at bmc.com and its associated documentation portals.