Unpacking the Data Tier: The Backbone of Your AR System

In the intricate architecture of any robust IT service management (ITSM) platform, the Data Tier often plays the unsung hero. It’s the silent workhorse, the vast repository where all your critical information resides, is managed, and from which it’s retrieved to power your entire application. For those working with BMC Remedy AR System (or its modern iterations), understanding the Data Tier is not just about knowing where the data is, but how it’s structured, accessed, and secured. This article dives deep into the AR System’s Data Tier, demystifying its components, storage mechanisms, and the nuances that make it a crucial aspect of your system’s health and performance.

The Foundation: Database Servers and Beyond

At its core, the Data Tier in AR System is all about the database. This isn’t just a simple file cabinet; it’s a sophisticated engine responsible for the persistent storage and efficient retrieval of all your ITSM data. Think of it as the brain and memory of your AR System. When a user interacts with a form, submits a request, or triggers an automation, it’s the Data Tier that receives, stores, and later serves up that information.

Key Components of the Data Tier

• Database Server: This is the primary engine. AR System typically relies on relational database management systems (RDBMS) like Microsoft SQL Server, Oracle, or PostgreSQL. The database server hosts the AR System database and executes queries to fetch or store data.
• AR System Database: By default, this database is named Arsystem. It’s within this database that all AR System-specific schemas and tables are created and managed.
• Database User: To interact with the database, AR System uses a dedicated database user. The default for this is ARAdmin, which is granted the necessary privileges to manage the Arsystem database.
• Default Password: For the ARAdmin user, the default password is AR#Admin#. It’s crucial for security best practices to change this default password immediately upon installation or as part of your security hardening efforts.

The Blueprint: Understanding Database Table Creation

When you install AR System, a specific sequence of table creation occurs within the Arsystem database. Understanding this sequence can be invaluable for troubleshooting database-related issues or for developers trying to pinpoint where certain types of data are stored. This foundational structure ensures that AR System can properly manage its metadata and operational data.

The Installation Table Sequence: A Logical Flow

1. control: This table likely holds overarching configuration settings and system-level parameters for the AR System instance. It’s the first point of contact for the system’s operational state.
2. controlRecordIds: This table probably manages the unique identifiers or primary keys that AR System uses to reference records across various tables. Maintaining sequential and unique IDs is critical for data integrity.
3. arschema: This is a fundamental table that defines the metadata for all AR System forms, views, and other schema objects. It stores information about form names, IDs, types, and relationships.
4. schema_index: This table is related to indexing strategies for AR System schemas. Efficient indexing is vital for query performance, ensuring that searches and data retrieval are fast and responsive.
5. schema_group_ids: This table likely manages groupings or classifications of schemas, perhaps for organizational purposes or to assign specific permissions or behaviors to sets of forms.

Mapping AR System Objects to Database Tables

Beyond the initial setup tables, AR System maps its core functional objects to specific database tables. Knowing these mappings is essential for advanced troubleshooting, data analysis, or when working with direct database queries.

Key AR System Objects and Their Database Residences

• Active Links: These are the automation rules that execute on the client side. They are stored in the dbo.actlink table. When an action name is appended, it becomes dbo.actlink_actionname.
• Filters: These are server-side automation rules that execute when specific conditions are met. They reside in the dbo.filter table, similar to active links, with the table name potentially including an action suffix.
• Escalations: These are scheduled automation processes. They are stored in the dbo.escalation table.
• Menus: Menus provide dropdown lists or selection options. When you attach a menu to a character field, the menu definitions are stored in dbo.field_enum (for the menu ID) and dbo.field_enum_value. If you’re inspecting menus attached to character fields directly, you might look at dbo.char_menu.

The Nuances of Data Storage: Dates, Times, and Attachments

AR System handles certain data types in unique ways, which can sometimes be a source of confusion if not understood. Dates, times, and attachments, in particular, have specific storage mechanisms.

Date/Time: The Epoch and Beyond

AR System stores date and time values not as human-readable strings, but as the integer number of seconds elapsed since 00:00:00 GMT on January 1, 1970 (the Unix epoch). This epoch-based storage is a common practice in computing for its efficiency and consistency. However, it comes with a significant limitation:

• The Year 2038 Problem: AR System’s date storage method is constrained by the maximum value a signed 32-bit integer can hold. This means it can accurately store dates only up to January 18, 2038. Systems requiring data beyond this date will need custom solutions or upgrades.

When AR System calculates time, it converts the duration from January 1, 1970, up to the current date, including all leap year days, into a total number of seconds. This precise conversion ensures that time-based calculations and comparisons are accurate.

Attachment Pool: Storing Binary Data

Handling file attachments within AR System involves a dedicated mechanism to manage binary data efficiently. This is managed through the Attachment Pool.

• Data Types: The attachment pool data type is used for the pool itself, and an attachment data type is used for individual attachment fields within a form.
• Table Structure: When attachments are involved, AR System creates two primary tables:
  1. BSchema_id: This table, where Schema_id is the ID of the form, acts as a binary data container. For every attachment associated with a record in this form, three columns are created in this table:
     • C (Path): Stores the path to the file.
     • O (Original Size): Records the original size of the file in bytes.
     • C (Compressed Size): Stores the size of the file after any compression applied by AR System.
  2. Battachpoolid: This table links request IDs to their associated binary data, essentially storing the actual attachment content or references to it.

When you examine the attachment pool in the database, you’ll often find two entries per attachment: one representing the metadata (path, sizes) and another for the actual data or its reference.

The Trio of Tables: T, B, and H

For every form you create and save in AR System, a specific set of three tables is generated in the database to manage its data, attachments, and historical records.

• T (Transaction Table): This is the primary data table for a form. It holds all the transactional data entered into the form’s fields, excluding attachments and audit history.
• B (Binary Table): This table is used to store binary data associated with the form. This includes attachments, but also can store other binary objects like Active Links if they are configured to be stored in the database.
• H (History Table): This table is dedicated to storing the history of changes to records, particularly status changes. It allows you to track the lifecycle of a request through different statuses.

Understanding this T, B, H convention is key when navigating the database schema for a particular AR System form.

Special Forms: Vendor, Join, and Archive

AR System offers specialized form types that interact with data in unique ways, extending its capabilities beyond simple data entry.

Vendor Forms: Bridging External Data

Vendor Forms are a powerful feature that allows AR System to access and present data from external sources without migrating that data into the AR System database itself. This is achieved through the use of BMC’s vendor technology. When you create a Vendor Form, it’s essentially a “view” that points to an external data source. The “Form type of server information” for these forms often indicates that they extract data from text files, but this can extend to other structured data sources through adapters.

Join Forms: Merging Data Streams

Join Forms allow you to combine data from two or more existing AR System forms into a single, unified view. This is incredibly useful for creating reports or unified interfaces that pull information from different sources without duplicating data. However, it’s important to note that there are some limitations. For instance, the join criteria you define when creating a Join Form cannot be altered once set (this was noted as a bug in version 8.1, and while fixes may exist, it’s a historical point to be aware of).

Join Forms can be created over various form types, including regular forms, view vendor forms, archived forms, and even audit forms.

Archive and Audit Forms: Data Lifecycle Management

AR System provides mechanisms for managing data growth and historical records through archiving and auditing.

• Archive Forms: When you archive data, a new archive form is created. The process of archiving involves moving older, less frequently accessed data from the primary form to this archive. While the archive form is created immediately, the actual data transfer happens according to scheduled times.
• Audit Forms: Audit forms are designed to log changes made to data in specific fields or entire forms. Importantly, you cannot directly audit an audit form itself.
• Interactions: You can audit an archive form. Conversely, you cannot archive an archive form (you would typically manage data retention for archives through external archiving or cleanup processes). However, the archive of an audit form (meaning, the data *in* the audit form can be archived) is possible.

Data Representation: The Core Fields and Field Types

Every record in AR System is built upon a set of core fields that provide essential metadata about the record itself. Beyond these, understanding different field types is crucial for designing effective forms and managing data integrity.

The Essential 9 Core Fields

Every AR System record, regardless of the form it belongs to, inherently possesses these core fields, often identified by their field IDs:

1. Request ID (1): The unique identifier for each record in a form. This is often the primary key in the underlying database table.
2. Submitter (2): The user who originally created the record.
3. Create Date (3): The timestamp when the record was created.
4. Assigned To (4): The user or group currently assigned to manage the record.
5. Last Modified By (5): The user who last modified the record.
6. Modified Date (6): The timestamp of the last modification.
7. Status (7): The current status of the record (e.g., New, In Progress, Resolved, Closed).
8. SD (8): This field typically represents the “Submitter Department” or similar organizational identifier, though its exact purpose can be configured.
9. Status History (Hidden – 15): While not directly visible on the form, this field often stores a serialized representation of the record’s status changes over time, linking to the history table.

Character vs. Diary Fields: A Matter of Appendage

The distinction between Character and Diary fields is a subtle but important one for data management:

• Character Fields: These are standard text fields that can hold alphanumeric characters. Data entered into a Character field can be overwritten. If you try to save multi-byte data exceeding its defined limit, you’ll encounter an ARERR [559] “Character string exceeds maximum size allowed.” You can attach menus to Character fields to provide controlled input options.
• Diary Fields: These fields are designed for logging purposes. When you modify a Diary field, the new data is not simply overwritten. Instead, it’s appended to the existing content in a specific format: [modified timestamp in seconds -| User -| Data]. This ensures that a complete history of entries is maintained within the single field, providing an audit trail of sorts for that specific field’s content.

Important Note: Web reports do not support Diary Fields, Attachment Fields, or Attachment Pools. They do, however, support Currency fields (though not currency values or types directly in the same way). This is a critical consideration when designing reporting strategies.

Data Access and Permissions: The Gatekeepers

Controlling who can see and do what within AR System is paramount. The Data Tier’s security is managed through a robust permission system, leveraging Permission Groups and Roles.

Permission Groups and Roles: The Pillars of Access Control

These are the primary mechanisms for defining access rights:

• Form Permissions: Control access to entire forms. This dictates whether a user can view, modify, or interact with a form at all. Examples might include vyomgroup or wiproGroup, indicating specific teams or roles that have access.
• Field Permissions: These permissions are more granular, controlling access to individual fields within a form. This allows for scenarios where a user can see a form but only certain fields are visible or editable to them. “Personal groups” can be used for field-level permissions.

Permissions for Menus: An Indirect Approach

You cannot directly assign permissions to menus themselves. Instead, the permissions of the character field to which the menu is attached will dictate whether the user can see and interact with the menu options. If a user doesn’t have permission to access a character field, they won’t be able to use the associated menu.

Bulk Permission Assignment: Efficiency is Key

For administrators managing a large number of forms, assigning permissions individually can be time-consuming. AR System provides a way to grant common permissions to multiple forms efficiently:

1. Locate and select the group to which you want to assign permissions.
2. Right-click on the group name.
3. Choose the “Assign Permissions” option.
4. Select “Add Forms” and then choose the forms you wish to grant access to.

This bulk assignment feature is a significant time-saver and helps maintain consistency in your security policies.

Troubleshooting Common Data Tier Issues

When things go wrong with your AR System, the Data Tier is often implicated. Here are some common issues and how to approach them:

Interview Relevance: What You Should Know

For anyone looking to excel in AR System administration, development, or support roles, a solid understanding of the Data Tier is non-negotiable. Here’s what interviewers often look for:

Conclusion: The Unseen Powerhouse

The Data Tier is the bedrock upon which your entire AR System application is built. From the fundamental table structures created during installation to the intricate ways dates, times, and attachments are managed, a deep appreciation for its components and functionality is crucial for effective system administration, development, and troubleshooting. By understanding these technical underpinnings, you gain the power to optimize performance, ensure data integrity, and build more robust and responsive ITSM solutions.