Delving Deep into Database Tables in AR System

Delving Deep into Database Tables in AR System: A Technical Deep Dive

Welcome to a comprehensive exploration of database tables within the BMC Remedy Action Request System (AR System). For anyone working with AR System, understanding how its components translate into database structures is crucial for effective administration, troubleshooting, and development. This article aims to demystify these table structures, offering practical insights and real-world relevance.

The Foundation: Understanding AR System Database Tables

At its core, AR System relies on a relational database to store all its configuration, metadata, and transactional data. When you install AR System, a specific sequence of tables is created to lay the groundwork for the entire system. Understanding this order is like knowing the blueprint of a building – it helps you appreciate how everything fits together.

Sequence of Table Creation During AR System Installation

The initial setup of your AR System environment involves the creation of several fundamental tables. Here’s the typical order, which is vital for system integrity:

control: This is the very first table created, acting as a foundational element. It typically holds core system parameters and configuration settings.
controlRecordIds: Essential for managing unique identifiers across the system, this table ensures that records are uniquely identifiable.
arschema: This is a critical table that stores metadata about all the forms (schemas) defined within your AR System. It’s the central repository for form definitions.
schema_index: As the name suggests, this table is involved in indexing the schema information, which is key for efficient data retrieval and querying.
schema_group_ids: This table manages group identifiers associated with schemas, playing a role in access control and permissions.

Interview Relevance: Knowing this sequence can demonstrate a fundamental understanding of AR System architecture and installation processes. Interviewers often probe on these foundational aspects to gauge a candidate’s depth of knowledge.

Core System Components and Their Table Mappings

AR System is more than just forms; it’s a powerful engine for workflow automation. Let’s look at how some key workflow objects map to database tables:

Active Links: These client-side workflow components, responsible for user interactions, are typically stored in the dbo.actlink table. You might notice a pattern where the action name is appended, leading to tables like dbo.actlink_.
Filters: These server-side workflow engines, triggered by data changes, reside in the dbo.filter table, often with action-specific variations like dbo.filter_.
Escalations: Time-based workflow automation is managed in the dbo.escalation table.

Troubleshooting Tip: If you’re experiencing unexpected behavior with active links or filters, checking the corresponding tables in your database can sometimes reveal inconsistencies or corruption, although direct manipulation is generally discouraged.

The AR System Database Location

For those working with Microsoft SQL Server, the AR System database files are typically found in a standard location. While this can vary based on installation specifics, a common path is:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

This directory contains the actual database files (.mdf and .ldf) for your AR System instance.

Deep Dive into Specific Data Storage

Beyond the core workflow objects, AR System handles various types of data, each with its own storage mechanism within the database. Understanding these mechanisms is key to managing data integrity and performance.

The TBH Structure: Transaction, Binary, and History Tables

When you save a form, AR System doesn’t just create one table; it often creates a set of three tables to manage different aspects of the data:

T – Transaction Table: This is where the primary data for your records resides. Think of it as the main repository for the fields defined on your form.
B – Binary Table: This table is used to store binary or file-based attachments, as well as the logic for active links.
H – History Table: For tracking changes to the status of a record, the history table is used, preserving a log of status changes over time.

Real-world Example: If you have a Change Request form with an attachment and a status history, the core data will be in the ‘T’ table, the attachment will be in the ‘B’ table, and status updates will be logged in the ‘H’ table.

Attachment Pool Management

Handling attachments is a common requirement. AR System utilizes specific mechanisms for this:

Attachment Pool: When you configure an attachment pool with two entries, you’re setting up a system to manage multiple files associated with a single record.
Attachment Field: The data type for an attachment field is simply ‘Attachment’.
Table Structure: AR System typically creates two tables for attachment pools. The first, often named like BSchema_id, stores metadata. Every attachment entry in the transaction’s binary table will have three columns: one depicting the file path, another for the original file size, and the third for the compressed file size (all in bytes). The second table, potentially named Battachpoolid, links request IDs with binary data.

Troubleshooting Tip: If attachments are not saving or retrieving correctly, examining these attachment-related tables can help diagnose the issue. Ensure file paths are accessible and data types are consistent.

Storing Menu Data

Menus are a fundamental part of AR System forms, providing users with selectable options. Their storage is also quite specific:

Menu Storage Tables: Menus, particularly those associated with character fields, are stored in tables like dbo.char_menu. For enumerated fields (like dropdowns), the menu items themselves are typically found in dbo.field_enum (storing the enum ID) and dbo.field_enum_value (storing the associated display values).
Menu Levels and Children: Character and file menus have defined limits. You can typically have up to 15 levels and 99 child entries for each menu item.

Practical Explanation: When you create a dropdown list on a form, AR System stores the individual options in these enumeration tables, linking them to the field definition.

Diary Field Values in the Database

Diary fields are designed to capture a chronological log of entries. Their storage format is distinct:

The format in the database is generally: [modified timestamp in seconds -| User -| Data]. When modified, new entries are appended to this existing string, preserving the history of updates.

Real-world Example: A diary field on a ticket might show multiple entries like “1678886400-| John Doe -| Initial assessment completed.” followed by a new entry on a later date. This allows for a complete audit trail of text-based interactions.

Form Types and Their Database Implications

AR System offers various form types, each serving a different purpose and having implications on how data is accessed and managed. Understanding these types is crucial for designing efficient applications.

Regular, Join, View, and Vendor Forms

Regular Forms: These are the most common forms, directly mapped to a database table. Their Form ID is typically 1.
Join Forms: These forms allow you to combine data from multiple underlying forms based on defined join criteria. They have a Form ID of 2. A critical point to remember is that you generally cannot change the join criteria once a join form is defined. This is a known behavior and a point of consideration during development.
View Forms: These forms provide a read-only view of data, often from other forms or data sources. They have a Form ID of 3. View forms can be created on other forms, including regular, join, vendor, archive, and even audit forms.
Vendor Forms: These forms are unique in that they allow AR System to access arbitrary external data sources. They are often used to extract data from text files or other external systems. Their Form ID is typically 5.

Interview Relevance: Discussing the differences between these form types and their database implications (especially the immutability of join criteria) showcases practical AR System knowledge.

Form IDs and Their Significance

Each form type is associated with a unique Form ID, which is important for system processes and database interactions:

Regular Forms: 1
Join Forms: 2
View Forms: 3
Display Forms: 4
Vendor Forms: 5

Troubleshooting Tip: While you rarely interact directly with Form IDs in day-to-day operations, understanding them can be helpful when troubleshooting advanced issues or analyzing database logs.

Workflows and Their Database Footprint

Workflows are the heart of AR System, automating business processes. Understanding how these workflows are stored provides insight into system behavior.

Active Links, Filters, and Escalations in the Database

As mentioned earlier:

Active Links: Stored in dbo.actlink (and variants).
Filters: Stored in dbo.filter (and variants).
Escalations: Stored in dbo.escalation.

Key Distinction: A crucial difference lies in their triggers. Active links are client-side and react to user interactions. Filters are server-side and react to form transactions (submit, modify, delete). Escalations, while server-side like filters, are time-based, executing at predetermined intervals.

When a Form is Deleted: Active Links and Workflow

A common question is what happens to associated workflow when a form is deleted. If you delete a form, any active links, filters, or escalations that are *exclusively* associated with that form will also be deleted. However, if a workflow object is associated with multiple forms, deleting one form will not delete the workflow object itself; it will simply be disassociated from that specific form.

Practical Explanation: This means that while AR System is generally good at cleaning up orphaned workflow, it’s wise to manually review and clean up workflow if you’re performing complex form deletions or migrations.

User Management and Permissions in the Database

Access control is paramount, and AR System manages user permissions through groups. These concepts have direct database representations.

Database Users: ARAdmin

The default administrative user is typically ARAdmin, with a default password often being AR#Admin#. This user has full access to the Arsystem database.

Troubleshooting Tip: If you lose administrator credentials, recovery can be complex and may involve direct database intervention (with extreme caution and proper backups).

Groups: Implicit vs. Explicit

AR System categorizes groups into two main types:

Explicit Groups: These are groups to which users are manually assigned. They are server-specific. Examples include Admin, Customise, and Struct Admin.
Implicit Groups: Users are automatically members of these groups based on certain conditions or system roles. Examples include Public, Submitter, and Assignee.

Interview Relevance: Understanding the distinction between explicit and implicit groups is fundamental for AR System security and administration discussions.

Group Categories and Ranges

Group IDs are allocated within specific ranges, which can be informative:

Regular/Computed Groups: Typically range from 1000 - 14999 (Explicit).
Dynamic Groups: Range from 60000 - 60999 (Implicit).

Practical Explanation: When you create custom groups, AR System assigns them IDs within these defined ranges, which helps in managing and identifying group types.

Assigning Permissions to Forms and Menus

Form Permissions: To grant common permissions to a large number of forms, you can search for the relevant group, right-click on it, select “Assign permissions,” and then “Add forms.” This is an efficient way to manage bulk access.
Menu Permissions: You cannot directly assign permissions to menus. Instead, the permissions of the character field to which the menu is attached will be inherited and applied to the corresponding menu options.

Real-world Example: If a “Support Staff” group has read access to a form, any menu attached to a character field on that form will effectively only be usable by members of the “Support Staff” group when interacting with that form.

Configuration and Technical Notes

Understanding AR System’s configuration files and underlying architecture is key to effective management.

The `ar.cfg` File

The ar.cfg file (on Windows) contains AR System server configuration settings. It’s dynamically created during installation and holds critical parameters. Be aware that it does save password information, so securing this file is important.

Port Numbers

Knowing the default port numbers for various AR System components is essential for network configuration and troubleshooting:

Database Ports:

DB2: 50,000
Oracle: 1521
SQL Server: 1433

Java Plugin Server TCP Port: 9999
Flash/Board Port: 1150 (Note: Some sources suggest 9998, always verify your specific configuration).
SMTP Port: 25

Troubleshooting Tip: Firewall issues are a common cause of connectivity problems. Verifying that these ports are open between your AR System server, mid-tier, and database server is a crucial first step.

Supported Platforms and Web Servers

AR System 8.1.00 supports a range of operating systems and web servers:

Supported OS: Windows, Linux.
Supported Web Servers (for Mid-Tier): Tomcat, Jboss, Servlet:Exec, and others. IIS is supported but not officially recommended by BMC.

AR System Architecture: The Three Tiers

The AR System architecture is typically described in three tiers:

Client Tier: This includes all AR System clients, from end-user tools like the User Tool and browsers to developer tools like Developer Studio.
Mid Tier: This component runs on a web server and translates client requests for web users. It handles web service requests and server-side processes to deliver AR System functionality to browsers.
Server Tier: This is the core AR System server, managing workflow, database access, and other server-side applications (like Approval Server and Email Engine).
Data Tier: This encompasses the database servers and other data sources that the AR System server interacts with.

Interview Relevance: Being able to articulate the AR System architecture and the role of each tier is a standard expectation for AR System administrators and developers.

Core Fields and Their Database IDs

AR System has several “core” fields that are fundamental to record management. While not directly visible as distinct tables, their underlying IDs are important:

Request ID: 1
Submitter: 2
Create Date: 3
Assigned To: 4
Last Modified By: 5
Modified Date: 6
Status: 7
SD (Short Description): 8
Status History (Hidden): 15

Troubleshooting Tip: Sometimes, data corruption or unexpected behavior can be traced back to issues with these core fields. Understanding their IDs can be helpful for deeper analysis.

Practical Examples and Troubleshooting

Let’s walk through some practical scenarios and troubleshooting tips related to database tables.

Scenario: Changing Status and Database Reflection

When you change the status of a ticket through its complete lifecycle (from “New” through to other statuses), the updates are reflected within the same row of the respective ticket’s table. This often involves specific columns like T0 U0, T1 U1, etc., which map to status transitions.

Scenario: Diary Field Modifications

As discussed, diary fields append data. After modifying a diary field, you’ll see the new entry added to the existing log, preserving the timestamp, user, and the entered data.

Scenario: Tree Table Output

For tree tables, setting “Refresh on entry change” to true ensures that the tree structure dynamically updates as underlying data changes, providing a real-time view.

Giving Prefix to Request ID

To add a prefix to the Request ID field:

Open the Request ID field’s properties.
Set the Default Value to your desired prefix (e.g., “ABC”).

To restrict the length of the Request ID:

Open the Request ID field’s properties.
Navigate to the Database tab.
Set the Input Length.

GUID (Global Unique Identifier)

A GUID is crucial for uniquely identifying a form across different AR System servers. You can implement this by taking a character field and changing its property with Database ID 179.

When Form Deletion Affects Active Links

As noted earlier, if you delete a form, associated active links are deleted only if they are exclusively tied to that form. If an active link serves multiple forms, it will persist.

Conclusion

Understanding the intricacies of AR System database tables is fundamental for any professional working with the platform. From the initial sequence of table creation to the specific storage mechanisms for workflows, attachments, and menus, this knowledge empowers you to troubleshoot effectively, optimize performance, and build robust applications. By delving into these technical details, you gain a deeper appreciation for the power and complexity of the BMC Remedy Action Request System.