Demystifying the BMC Remedy AR System Database: A Deep Dive for Technical Professionals

In the intricate world of IT Service Management (ITSM) and business process automation, the BMC Remedy Action Request System (AR System) stands as a robust and enduring platform. For many organizations, it’s the engine that drives critical workflows, from incident management to change control. At its core, the AR System database is the unsung hero, meticulously storing and managing the data that powers these processes. Understanding its architecture, how data is organized, and the underlying principles is crucial for administrators, developers, and anyone looking to leverage its full potential.

This article aims to provide a detailed, human-like technical exploration of the AR System database. We’ll delve into its historical roots, architectural layers, data storage mechanisms, and key configurations, all while keeping practical application and real-world scenarios in mind.

A Brief History and the “Remedy” Identity

Before we dive into the database itself, it’s important to clarify the nomenclature. Many seasoned professionals still refer to it simply as “Remedy.” The full, current name is BMC Remedy Action Request System. The Action Request System was originally developed by Remedy Corporation, founded in 1990. After a series of acquisitions – first by Peregrine Systems in 2001, and then by BMC Software in 2002 following Peregrine’s bankruptcy – the platform became a cornerstone of BMC’s offerings, particularly for ITSM solutions. So, when you hear “Remedy” or “Action Request System,” know they’re referring to the same powerful platform, now under the BMC umbrella.

Understanding the AR System Architecture: A Layered Approach

The AR System is built on a multi-tiered architecture, designed for scalability, flexibility, and efficient data handling. Each tier plays a specific role:

1. Client Tier

This is the user-facing layer. It encompasses various clients that interact with the AR System. This includes:

• BMC Remedy User: The primary client for end-users to interact with applications, submit requests, and manage their tasks.
• BMC Remedy Developer Studio: The powerful integrated development environment (IDE) used by developers and administrators to build, customize, and manage AR System applications.
• Web Browsers: Through the Mid Tier, users can access AR System applications via web browsers.
• API-based clients: Custom applications or integrations using AR System APIs to communicate with the server.

These clients present information and capture input, acting as the gateway for human interaction with the system.

2. Mid Tier

The Mid Tier acts as a crucial intermediary, bridging the gap between web clients and the AR System server. It’s essentially a web server component that hosts a set of services and plugins. Its key responsibilities include:

• Translating Requests: It translates HTTP requests from web browsers into AR System API calls.
• Interpreting Responses: It converts AR System server responses back into formats suitable for web browsers (like HTML).
• Handling Web Services: It manages incoming web service requests, enabling integration with other systems.
• Executing Server-Side Processes: It can run specific server-side processes to provide AR System functionality to web clients.

The Mid Tier is typically deployed on a web server like Tomcat or JBoss and requires a servlet engine. While IIS can be used, BMC generally recommends Tomcat or JBoss for optimal performance and compatibility.

3. Server Tier

This is the brain of the AR System. The AR System server is responsible for:

• Workflow Execution: It controls the execution of business logic defined by active links, filters, and escalations.
• Data Access: It manages access to the underlying databases and other data sources in the Data Tier.
• Core Components: It hosts critical server-side applications like the Approval Server, Email Engine, and the Flashboards server, which provide specialized functionalities.
• Plugin Servers: It utilizes C and Oracle Java plugin servers to extend its capabilities, often for custom integrations or advanced processing.

The AR System server is the central processing unit, orchestrating all the complex operations that make the platform function.

4. Data Tier

This tier comprises the actual data storage mechanisms. Primarily, this involves:

• Database Servers: These are the relational database management systems (RDBMS) where AR System stores its schema, configuration data, and application data. Common choices include Oracle, Microsoft SQL Server, and IBM DB2.
• Other Data Sources: In some advanced configurations, AR System can integrate with other external data sources through Vendor Forms.

The database server is the persistent storage layer, ensuring data integrity and availability.

The Heart of the Matter: The AR System Database

The AR System database is where all your application data, configuration settings, and workflow definitions reside. Let’s break down some key aspects of its structure and management.

Database Naming and Default Credentials

By default, the AR System database is typically named Arsystem. The default administrative user for interacting with the database is usually ARAdmin, with a default password of AR#Admin#. It’s a security best practice to change these default credentials immediately upon installation.

Table Creation Sequence During Installation

When you install the AR System server, a specific sequence of tables is created in the database to establish the foundational structure. Understanding this sequence can be insightful for troubleshooting or deep-diving into the system’s core components:

1. control: Contains system-wide configuration and metadata.
2. controlRecordIds: Manages record IDs for various system objects.
3. arschema: Stores definitions of all AR System forms (schemas).
4. schema_index: Manages indexing for forms to optimize query performance.
5. schema_group_ids: Associates group IDs with schema objects for permission management.

Key Database Tables for Workflow Objects

The AR System uses specific tables to store definitions for its core workflow objects:

• Active Links: Stored in tables like dbo.actlink and its variations (e.g., dbo.actlink_ followed by an action name).
• Filters: Defined in tables such as dbo.filter and its related extensions (e.g., dbo.filter_).
• Escalations: Configuration resides in tables like dbo.escalation.

These tables are fundamental to how AR System automates processes. When you create or modify an active link, filter, or escalation, these underlying database tables are updated.

Understanding Form Types and Their Database Representation

AR System categorizes forms based on their function, and this categorization has implications for how they are handled in the database:

• Regular Forms (Form ID 1): Standard forms used to store data.
• Join Forms (Form ID 2): Combine data from two or more regular forms.
• View Forms (Form ID 3): Provide a specific view of data, often used for reporting or read-only access.
• Display Forms (Form ID 4): Used for presentation purposes, not directly for data storage.
• Vendor Forms (Form ID 5): Allow AR System to interact with external data sources.

When a form is deleted, associated active links might also be deleted if they are directly tied to that specific form and no other form references them. This highlights the interconnectedness of AR System objects.

Data Storage and Data Types

Dates and Times: The Epoch Standard

AR System handles date and time values in a standardized way. Dates are stored as the total number of seconds that have elapsed since 00:00:00 GMT on January 1, 1970. This is commonly known as Unix time or epoch time. This method allows for efficient storage and calculation across different time zones. This format is generally supported for dates between January 1, 1970, and January 18, 2038.

Character vs. Diary Fields

A common point of confusion for beginners is the difference between Character and Diary fields. Understanding this is key for data integrity:

• Character Fields: These are your standard alphanumeric fields. Data entered into a character field can be overwritten. This is useful for simple text input. However, AR System has a maximum size for character strings, and attempting to save multi-byte data beyond this limit can result in errors like ARERR [559] Character string exceeds maximum size allowed.
• Diary Fields: These fields are designed for logging and auditing. When you add data to a diary field, it’s appended with a timestamp, the user who made the modification, and the data itself. This ensures that all historical entries are preserved, providing a clear audit trail. This append-only behavior means diary fields are excellent for tracking changes and maintaining a complete history of information within a record.

Attachments and Attachment Pools

AR System provides robust capabilities for handling file attachments.

• Attachment Fields: These fields are specifically designed to store binary data, essentially pointers to files.
• Attachment Pool: This is a more structured way to manage attachments, often used for larger or multiple files. When you use attachment fields or pools, AR System typically creates related tables in the database to store the binary data and its metadata. For an attachment field, you might see tables that store the file path, original size, and compressed size. For an attachment pool, you’ll find tables linking request IDs to the binary data.

It’s worth noting that web reports generally do not support diary fields, attachment fields, or attachment pools. While they can display currency fields, they don’t support currency values or types directly.

Workflow Automation: Active Links, Filters, and Escalations

The power of AR System lies in its ability to automate business processes through workflow. These are triggered by specific events and can perform a series of actions:

1. Active Links

Trigger: Client-side operations or user interactions within the current form window.

Functionality: Active links execute based on what the user is doing directly in the application. This can include displaying messages, changing field colors, showing/hiding fields, or pushing data to other forms. They are executed on the client side or at the edge of the server-side processing.

Key Points:

• Cannot be triggered via API programs.
• If you create an active link with the same name as an existing one, AR System will typically append _c to the new name.
• An active link must contain at least one action to be functional.
• When you “Save As” a form, its associated active links are not automatically copied. However, if you rename a form, the system may preserve its associated active links.

2. Filters

Trigger: Server-side processing of form transactions (e.g., Submit, Modify, Delete). Any operation that interacts with the data on a form triggers filter execution.

Functionality: Filters are the workhorses of data validation, manipulation, and routing within AR System. They check form transactions as they are processed by the server. This is where you’d implement business rules, validate data, assign tasks, or trigger notifications based on record changes.

Key Points:

• Filters execute *after* the initial data is committed to the database for certain operations, making them ideal for post-transaction processing.
• They can trigger other workflows or perform complex logic.

3. Escalations

Trigger: At a predetermined time interval or condition.

Functionality: Escalations are scheduled tasks that periodically check requests in the database for specific conditions. They are commonly used for tasks like:

• Sending overdue notifications.
• Automatically changing the status of tickets if they remain unresolved for too long.
• Performing cleanup tasks.

They operate independently of direct user interaction, focusing on time-based or condition-based automation.

User and Access Management: Licenses, Groups, and Permissions

Controlling who can access what and do what within AR System is paramount. This is managed through licenses, groups, and permissions.

Understanding Licenses: Fixed vs. Floating

AR System uses a license model to govern user access and concurrency. The two primary types are:

• Fixed Licenses: A fixed license is assigned to a specific user login. That user can log in as many times as they wish, but only one instance of their license is consumed at a time. This is generally more cost-effective for users who have consistent access.
• Floating Licenses: Floating licenses are pooled and shared among a group of users. When a user with a floating license logs in, a license is consumed from the pool. When they log out, the license becomes available for another user. Floating licenses are typically more expensive and are best suited for users who only need occasional access or for shift-based working environments where licenses can be shared.

The Unlimited Login Scenario: If you have 5 fixed and 5 floating licenses, an unlimited number of users *can log in*. However, they will have to *wait* for an available license if the licensed slots are all occupied. Users without a license will likely experience read-only access or be blocked, depending on the specific configuration and license type availability.

Group Management: Explicit vs. Implicit

AR System categorizes groups into two main types, each with distinct operational characteristics:

• Explicit Groups: These are groups to which you manually assign users via the User form. When a user joins an explicit group, they inherit all permissions granted to that group. Examples include admin, sub-admin, and customizer. Explicit groups are server-specific, meaning if you move objects to a new server, you might need to re-map permissions. Deployable applications with role-based permissions can help mitigate this.
• Implicit Groups: Users belong to implicit groups based on specific circumstances or conditions, often derived from data within a request (e.g., the Submitter or Assignee of a ticket). You do not manually assign users to these groups. Dynamic groups created in AR System are also considered implicit. Examples include Public, Submitter, Assignee, and Assignee Group.

Group ID Ranges and Categories

Group IDs are integers used to uniquely identify groups. Their ranges often indicate their purpose:

• 0-1000: Typically for core AR System and current application groups.
• 1000-13004 & 13007-14999: For regular and computed groups (often explicit).
• 13005-13006: Reserved for CMDB groups.
• 14999-59999: For future AR System applications.
• 60000-60999: For dynamic groups (often implicit).

Group Types (Permissions): Within a group, you can define specific permission levels on objects:

• View: Allows users to see the object (forms, fields).
• Change: Allows users to modify the object.
• None: Denies access.

Overlay Groups and Customization Modes

AR System supports a powerful customization model using overlays. Overlay Groups define how users interact with customized objects versus base (out-of-the-box) objects:

• Overlay Group field set to 1: Users are restricted to working on overlay and custom objects. In Developer Studio, they operate in “Best Practice Customization” mode.
• Overlay Group field set to 0: Users are restricted to working on base mode objects. In Developer Studio, they operate in “Base Developer” mode.
• Overlay Group field set to (clear): No restrictions; users can access base, overlay, or custom objects.

Granular Overlays: This advanced feature allows you to customize specific subcomponents of an object. This minimizes merge conflicts during upgrades. The three types of granular overlays are:

• Additive Overlay (Extensions): Adds custom information to the origin object. Additions are appended during upgrades.
• Overwrite Overlay: The overlaid object is used entirely, ignoring the origin object. Useful for removing permissions or making significant changes.
• No Overlay (Inheritance): The default. Inherits properties from the origin object without changes.

Menus: Enhancing User Interaction

Menus in AR System are associated with character fields and provide users with selectable options, simplifying data entry and navigation.

Types of Menus:

• Static Menus: Options are hardcoded.
  • Character: Simple list of options.
  • Form data dictionary: Populates from data on another form.
  • Field data dictionary: Populates from the distinct values of a field on another form.
• Dynamic Menus: Options are retrieved at runtime.
  • Search: Populates from data based on a search query.
  • SQL: Populates directly from an SQL query against a database.

File menus can be of both static and dynamic types.

Menu Refresh Rates:

The refresh rate determines how often menu content is updated. This is critical for ensuring users see the latest options without impacting performance:

• On Connect: Retrieves the menu when the user first connects to a form. Requires reopening the form for updates.
• On Open: Retrieves the menu *every time* it’s opened. Can impact performance if used excessively.
• On 15 Minute Interval: Retrieves the menu on open and then again after 15 minutes have passed. Balances currency with performance.

Note: Character menus are static and do not refresh.

Menu Permissions:

You cannot directly assign permissions to menus. Instead, the permissions of the character field to which the menu is attached will govern access to the menu options.

Core Fields and Data Storage Details

Every record in an AR System form has a set of core fields that are essential for its management and identification.

The 9 Core Fields:

1. Request ID (1): The unique identifier for a record. Can be prefixed and have its length restricted via field properties.
2. Submitter (2): The login name of the user who initially created the record.
3. Create Date (3): The date and time the record was created.
4. Assigned To (4): The user or group currently assigned to the record.
5. Last Modified By (5): The login name of the user who last modified the record.
6. Modified Date (6): The date and time the record was last modified.
7. Status (7): The current workflow status of the record (e.g., New, Assigned, Resolved, Closed).
8. SD (8): Likely “Status Description” or similar, related to the status.
9. Status History (Hidden Field ID 15): Tracks changes to the Status field, providing an audit trail. You can view this through the “View” menu or by pressing Ctrl+H on a form in modify mode.

`ar.cfg` File: Server Configuration

The ar.cfg file (on Windows) or ar.conf (on Unix/Linux) is where you define AR System server configuration changes. This file is dynamically created during installation and contains various settings, including some password information. It’s crucial for server behavior and troubleshooting.

Database Configuration and Port Numbers

Proper network configuration and understanding of port usage are vital for AR System to communicate effectively with its various components and external systems.

Common Port Numbers:

• DB2: 50,000
• Oracle: 1521
• SQL Server: 1433
• JAVA Plugin Port: 9999
• Flashboards: 1150
• SMTP: 25
• Email Engine: (Specific port varies, often configured separately)
• DIS Server Port: 2000
• Flashboards (alternative): 9998 (though 1150 is more commonly cited for general Flashboards communication). Always verify with your specific version and documentation.

The hosts file and services file (typically found in C:\Windows\System32\drivers\etc on Windows) are essential for mapping hostnames to IP addresses and service names to port numbers, respectively. AR System relies on these for proper network communication.

Installation and Log Files

When installing or troubleshooting AR System, log files are your best friends. The installation log files are typically found in a temporary directory, often something like: C:\Users\Administrator\AppData\Local\Temp\1\arsystem_install_log.txt.

Advanced Concepts and Scenarios

Vendor Forms: Integrating External Data

Vendor Forms are a powerful feature that allows AR System to interact with external data sources (like flat files, other databases, or even web services) as if they were regular AR System forms. This enables you to pull data from disparate systems into your AR System workflows without complex data migration.

GUID (Global Unique Identifier)

A GUID is essential for uniquely identifying a form across different AR System servers, especially in distributed environments or during application deployment. You can implement a GUID for a form by using a character field and setting its database ID to 179.

Table Management: T, B, and H Tables

After saving a form, the AR System database creates a set of tables to manage its data:

• T (Transaction Table): Stores the actual data for the form’s records. For example, a form named ‘Incident’ might have a table like `T100` where `100` is the form’s ID.
• B (Binary Table): Stores binary data associated with records, such as attachments, active links, and some workflow data.
• H (History Table): Stores the history of changes, particularly for the ‘Status’ field, providing an audit trail.

When you have attachments, the binary table will have additional columns to store file path, original size, and compressed size.

Joining Forms: Combining Data Streams

Join Forms allow you to combine data from multiple forms. BMC recommends joining up to 6 forms for optimal performance, though more are technically possible. The join criteria, once defined, cannot be changed in versions like 8.1 without recreating the join form. Join forms can also be created from other form types like view, vendor, archive, and audit forms.

Conclusion: The Enduring Power of the AR System Database

The BMC Remedy AR System database is a sophisticated and powerful component that underpins a vast array of critical business processes. From its layered architecture to its granular control over data and workflow, understanding its intricacies is not just about technical knowledge, but about unlocking the full potential of your ITSM and business process automation initiatives.

Whether you’re a seasoned administrator managing complex environments or a developer building the next generation of AR System applications, a solid grasp of its database foundation, workflow mechanisms, and access controls is indispensable. By continuing to explore, experiment, and stay updated with its capabilities, you can ensure your organization effectively leverages the enduring power of BMC Remedy AR System.