DPSync: Justice Is Served

Batman and Robin are a lot busier these days, and that makes Captain Mark Dickerson of the Sacramento County Sheriff’s Department quite happy.

Captain Dickerson’s Batman and Robin don’t wear capes and masks. They are the dynamic duo of machines hosting the County’s Integrated Justice Information System (IJIS) databases on Microsoft SQL Server 2000. The databases are populated in near-real-time with data propagated from ADABAS in the mainframe-based Criminal Justice Information System (CJIS) using Treehouse Software’s DPSync product.

Timely, Accurate Information Improves Public Safety

The IJIS Program is a complex, multi-agency initiative sponsored by the Sacramento County Criminal Justice Cabinet. Program Manager Brian Richards explains that one of the program’s key goals is to identify and implement projects that are consistent with the technical recommendations outlined in the IJIS Information Strategy Plan (published in 2002). Each project must meet one or more of the following IJIS Program goals:

• improve data sharing among stakeholder organizations;
• reduce redundant activities; or
• improve the ability to perform ad-hoc or trend analysis reporting.

“The purpose of the Program is to improve public safety through the timely and accurate sharing of critical criminal justice information among the stakeholder organizations in the criminal justice community,” says Mr. Richards. “IJIS is helping us deal with a number of important issues, including the difficulty of sharing critical information between agencies, cost avoidance of implementing projects that duplicate capabilities, concerns with the accuracy of redundant data entry, and difficulty in performing effective crime trend analysis activities.”

Besides the County’s Sheriff’s Department, IJIS stakeholder organizations include the County’s Conflict Criminal Defenders, Coroner’s Office, County Executive’s Office, District Attorney’s Office, Department of Health and Human Services, Department of Human Assistance, Probation Department, Public Defender’s Office, and the Department of Revenue Recovery. Additionally, the Superior Court and Sacramento City Police Department participate in the IJIS Program.

"...it was clear that tRelational/DPS was the leading product for ADABAS-to-RDBMS data transfer.”      - Doug Kauffroath        MIS Director for the Sacramento Superior Courts

Making Information Available

Like many state and local governments, Sacramento County has had a long history with ADABAS, dating back to the late 1970s when development of the Criminal Justice Information System (CJIS) commenced. CJIS is the County’s core operational information system that supports multiple stakeholders in the areas of arrests, warrants, criminal history, Court calendaring, and jail management. Mike Gilliam, an ADABAS Database Administrator for the County’s Office of Communications and Information Technology (OCIT) notes, “We run a 24x7 shop on behalf of our hundreds of clients. ADABAS’s reliability and performance is critical to meeting our service standards.”

However, beginning in the 1980s, and increasingly in the mid-to-late 1990s, stakeholders started developing their own departmental systems. They needed to do ad-hoc reporting and analysis, and lacking easy access to ADABAS data from the mainframe, they depended on OCIT programmers to write reports and extracts. This led to a backlog of requests and ongoing difficulty with obtaining sufficiently current data.

During 2000 and 2001, County staff began investigating means to improve access to CJIS data by developing a data warehouse on the Microsoft SQL Server platform. Deploying the data on a standard RDBMS would enable querying and analysis using a variety of inexpensive, off-the-shelf tools. Doug Kauffroath, now MIS Director for the Sacramento Superior Courts, was the user champion for that initiative and describes the approach:

“Our investigations found that Treehouse’s tRelational/DPS offered the capabilities we needed to efficiently get CJIS data from ADABAS into our new JBSIS (Judicial Branch Statistical Information System) data warehouse. We looked at other solutions, but it was clear that tRelational/DPS was the leading product for ADABAS-to-RDBMS data transfer. Funding for the project was partially to be gained through a grant, and to qualify for the grant we had to validate that certain assumptions about ability to produce reports would be met. To that end, we engaged Treehouse to deliver a turnkey tRelational/DPS implementation.”

The consulting effort was led by Wayne Lashley, now Treehouse Director of Technical Operations. “The productivity and efficiency of tRelational/DPS made it possible for us to deliver a complete turnkey application over the course of only three short site visits,” Wayne notes. The project was completed successfully and on time and proved that tRelational/DPS would meet the requirements for the Courts data warehouse.

IJIS and Data Currency

Among the continuing projects is the implementation of the IJIS Database Environment. Through 2002-2003, the IJIS Program established a Microsoft SQL Server-based reporting repository as part of the initial deliverables set out in its Information Strategy Plan. However, IJIS users required the data to be updated more frequently than once a day. OCIT responded by modifying operational procedures to create ADABAS protection log (PLOG) archives four times per day. PLOG archives are the data source tRelational/DPS uses for propagation?proven to be the most efficient, reliable, and effective approach.

By 2004, the demand was growing from IJIS users for even more frequent propagation. “Like many of our customers, the County is becoming a real-time enterprise,” Wayne observes. “We knew we had to respond with an extension to tRelational/DPS that would allow customers to circumvent the operational issues around frequent PLOG switching, yet remain as robust, scalable, and high-performing as tRelational/DPS. Sacramento County is a great example of the quality and effectiveness of DPSync as a product and of Treehouse services.”

"DPSync allows us to take a big step forward without replacing CJIS.”      - Albert Locher        Assistant Chief in the District Attorney’s Office / IJIS co-chair

It was evident that bringing the IJIS Database Environment current to within a few minutes of any updates to mainframe CJIS would enable the Sheriff’s Department and other stakeholders to make a quantum leap forward in exploiting IJIS information. Mr. Richards brokered discussions between Treehouse and the County and helped secure funding from the Sheriff’s Department to acquire DPSync: “I was pleased to find that Treehouse could meet the County’s requirements for near-real-time data through the DPSync product.”

The new near-real-time version of IJIS, using DPSync version 1.1, went live in October 2004 and has been propagating without a hitch since then.

Technical Notes

The IJIS Database Environment involves two separate Windows-based servers, each running Microsoft SQL Server 2000. One server, known as “Robin”, is utilized by individual stakeholder Report Writers for ad-hoc reporting, testing, and development based on their departmental security view of the data. The second server, “Batman”, is more powerful and supports scheduled downloads of data and replicated views and data. Based on request, a stakeholder organization’s security view of the data can be replicated to their departmental server using SQL Server’s powerful homogeneous replication features.

"In our deployment of field identification capabilities, having current data available on criminal histories as part of that process is critical.”      - Captain Dickerson        Commander of Technical Services        Sacramento County Sheriff's Department

On an average day, updates to the CJIS database generate over 500,000 PLOG images. These images are captured by DPSync and those that pertain to the 32 ADABAS files used by IJIS are transformed into SQL Server-native SQL statements according to the specified mappings. Batman and Robin each host 125 target SQL Server tables, and these tables comprise over 1400 data elements on each server. The required service level is to have any update to CJIS be propagated to IJIS on both servers within 7 minutes, which DPSync delivers easily. This service level is in support of the Sheriff’s Department victim notification commitment, where a prisoner’s release must be communicated to his or her victims within 15 minutes of that release.

Cornerstone for New Justice Applications in the Real-Time Enterprise

With near-real-time data currency in IJIS, what new opportunities are within reach? Brian Richards explains:

“Potential follow-up projects include an application for law enforcement officers to use handheld devices querying the IJIS Database Environment via wireless connection to instantly obtain up-to-date information on suspects in the field. Another potential application is a website where custody status and bail information for an individual can be obtained by families and concerned parties.”

In his role on the Board of Directors of the Justice Information Sharing Professionals (www.jisp.us), Mr. Richards is in frequent contact with his counterparts in other jurisdictions. “Everywhere I go, I talk about the IJIS Program, which I believe should be a model for other jurisdictions. In my discussions with colleagues from other jurisdictions that are also working on integrated justice projects, I let them know how DPSync is one more of the puzzle pieces that we have put in place to make our CJIS data more easily available to our end users.”

Albert Locher, an Assistant Chief in the District Attorney’s Office and IJIS co-chair, notes, “As agencies have greater needs to share information, they will not always have the ability to replace a legacy system such as CJIS, which uses older technology at least not in the short term. This project demonstrates that it is not always necessary. DPSync allows us to take a big step forward without replacing CJIS.”

Captain Dickerson, Commander of the Technical Services Division, says, “Our goal is to be able to deliver information where it is needed, when it is needed, and in the format it is needed. When you are dealing with an officer’s encounter with a suspect, the safety of that officer and the protection of the public are paramount. In our deployment of field identification capabilities, having current data available on criminal histories as part of that process is critical. When assessing the immediate impacts to the Sheriff’s Department and the long term benefit for system integration at both the Sheriff’s Department and other justice agencies, it became clear that funding this project was the right thing to do.”

Treehouse Home Page | tRelational/DPS Interactive Home