How to get access and use the Proclaim database  [SOLVED]

Questions around Server admin, DB admin, email, printers, scanners etc related to Proclaim.
coastal_living
Posts: 16
Joined: Mon Sep 27, 2021 11:58 am
Has thanked: 8 times

How to get access and use the Proclaim database

Post by coastal_living »

I have just joined a company which has Proclaim. I normally connect SSMS to a database and write my own SQL queries to pull out data. What would be the equivalent software/app/tool for getting access to the Proclaim backend database? :?:

steve
Posts: 470
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 120 times

Re: How to get access and use the Proclaim database  [SOLVED]

Post by steve »

Hi,
you've got a number of options for extracting data using SSMS and SQL-92 queries.

1) Use an ODBC/JDBC driver to access the underlying database,
Casedata is not stored in a normalised structure, and there are some caveats around schema field widths, transaction isolation etc.
This gives you the most 'live'/raw view of the underlying data but can be tricky to query.

2) use the Proclaim "Dynamic Database" to present another Progress database for query, but with tables and indexes to suit your own design. ODBC driver access and SQL-92 queries are still used, but data is stored in a more logical format / custom tables as you need for your reporting needs. Queries can be quicker since you a) define your own indexes and b) only populate a subset of data.

3) Use the Proclaim "Management Information Warehouse" tool to keep an MSSQL database in sync with selected fields from your Proclaim database.
Useful if your existing MI or skillset is MSSQL based You can then query using T-SQL in SSMS. The downside is that it requires a scheduled synchronisation job to push changes from the Proclaim database(s) to MSSQL, and can on occasion fall out of sync.

There are other options for data extraction, such as using Proclaim tools to scheduled exports to csv/xml, Pro2 replication to 'mirrored' MSSQL databases. CDC modules to keep MI databases in sync, but perhaps beyond the needs of all but the heaviest of reporting workloads.

This is just a summary. If you intend to perform large amounts of queries against the live DB then there are a number of considerations for configuration, but for light use it can be quite straightforward to set up

coastal_living
Posts: 16
Joined: Mon Sep 27, 2021 11:58 am
Has thanked: 8 times

Re: How to get access and use the Proclaim database

Post by coastal_living »

Thank you Steve, that information is really helpful. Which one of your suggestions will give me access to history, audit, diary (tasks), accounts / ledger, as well as case fields, options and case tables? I will be wanting to access the data in near real-time too.

steve
Posts: 470
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 120 times

Re: How to get access and use the Proclaim database

Post by steve »

I would therefore use option 1 - namely create and ODBC connection to the underlying Proclaim / accounts databases.

There are some considerations / gotchas, as Progress is not an MSSQL database. Have a read around this link:
https://docs.progress.com/bundle/opened ... ments.html

Broker / read-only user
Whilst you don't necessarily need to get Eclipse/Access involved in the setup process, I would suggest you do, and ask them to create a secondary login broker for SQL connections on your proclaim and proacc databases , and create a new DB user with read-only access to all Proclaim and proacc tables, which you then use for reporting.
https://knowledgebase.progress.com/arti ... popup=true

Field Widths
You will need to also handle the fact that field widths in a Progress DB are for display only; a Progress field can store any amount of data up to 32k, however when querying via SQL-92 the driver will report an error if a stored field exceeds the published SQL-92 schema width.

To get around this you can either:
a) run a utility periodically to update the SQL schema field widths to match the data held in your DB, plus an expansion margin
https://docs.progress.com/bundle/opened ... ility.html
b) enable a Progress startup parameter to autonomously update the schema on-the-fly, or automatically truncate overflowed fields (OE11.6+)
https://d117h1jjiq768j.cloudfront.net/d ... 58e47109_0
c) truncate overflow fields via the ODBC connection configuration
https://knowledgebase.progress.com/arti ... connection
d) write your SQ-92 query to only select characters up to the schema width. Progress recommend e.g. :

Code: Select all

SELECT col1, substring(col2,1,255) FROM pub.table1
Transaction isolation
You don't want your MI queries to lock records in use by Proclaim client app, or users will see errors / blocking messages.
Consider setting the default isolation level as necessary to prevent locking of records by your reporting query. This can be done at query level, or in the ODBC connection.
https://docs.progress.com/bundle/opened ... evels.html

Performance
General SQL performance guides still apply - namely use indexes where possible, SELECT only the columns/tables you require etc.
There are some other tweaks you can perform at the database level, but altering e.g. indexes isn't supported (this is where the dynamic database or MIWH come in). See below:
https://knowledgebase.progress.com/arti ... le/P117623