Full Db backup cbk file from Cache 2017.2.2 needs to be restored for SQL access

Question

Question

Mark Runyan · Jun 27, 2023

We are retiring a hosted application for an electronic health care records (EHR) system which stored the data on Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2.2 (Build 867_4_20245) Thu Oct 8 2020 16:58:40 EDT. The hosting company is providing me with a single CBK file. I need to install a database system to restore the database and provide occasional SQL access for reports when necessary. I'll need to maintain access to the data for an approximately 10 year retention period. Not sure how to approach restoring this old of a database and eventually upgrading it to a newer release, the IRIS platform, or other alternative. I've exported select CSV files via SQL for current data data and imported it into our new EHR, but need the complete Db for archival purposes. I would appreciate any advice! - Mark

Product version: Caché 2017.1

$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2.2 (Build 867_4_20245)

Discussion (36)1

Log in or sign up to continue

Danny Wijnschenk · Sep 26, 2023

Hi Mark,
After the upgrade from Caché to Iris, it is best to recompile and upgrade the classes, that should correct any new syntax.
Can you try the rebuild of indexes after following commands :

Do $system.OBJ.CompileAll()
and
Do $system.OBJ.Upgrade()

0 0

Robert Cemper · Sep 28, 2023

So I assume an id of 1 will suffice. WRONG

^DocM.DocumentImageD(1,1) =
indicates that the id is "1||1"

therefore after
AVCWS>Set obj = ##CLASS(DocM.DocumentImage).%OpenId(1)
write obj shows it is NOT and <OREF>
and obj.ID fails as a consequence:

But this is all visible in the class definition of
DocM.DocumentImage.cls

0 0

Danny Wijnschenk · Oct 1, 2023

Hi Mark, If you can do inserts but not selects, the class might have row security enabeled.
Is there a parameter ROWLEVELSECURITY = 1, and a method %SecurityPolicy present ? In that method, you can exclude rows based on e.g. $username and $roles. Even if your user has the %All role, you could be excluded to see a row in this security.
If you see that, you can or create the correct user/role, or set the parameter to 0 (and rebuild all indexes).
Can you send the class definition of one of the tables, so I can try the class on my server ?

0 0

Danny Wijnschenk · Oct 3, 2023

Hi Mark,
It is stated in the doc that %All is not enough : you have to have the exact role or be the exact username that matches row level security (in your case : the property FACILITY in each row).
Alternatively, you can disable it by setting the securitypolicy param to 0 (but then you need to recompile the classes that depend on it and rebuild the indices)
https://docs.intersystems.com/iris20232/csp/docbook/DocBook.UI.Page.cls?...

0 0

Danny Wijnschenk · Oct 3, 2023

Hi Mark,
I have tested this on my machine :
In the portal, add a role named 1 and give this role to your user besides the %All role.
Log in again in the portal and try the SQL, it should work now.
(In terminal, when you write $ROLES, it should say %All,1

0 0

score 0 · Answer 1 · 2023-06-27T16:42:34-04:00

Hi Mark,

Some thoughts on this:

Anticipate the online backup (CBK) may tie-in with operating system and Cache version to run the restore

It may be better to have "at rest" CACHE.DAT files:

They can be:

Renamed to IRIS.DAT
Mounted and "upgraded"
Endian converted if needed

Having md5 checksum of CBK / DAT files can help identify transfer issues to / from offline media.

An integrity check can also be a useful confirmation tool to trace back if the original backup had issue.

Having undeployed SQL table definitions maybe useful future proofing.

For older versions of Cache install media contact the wrc.intersystems.com

Anticipate IRIS will be here after another 45 years, so will easily meet the 10 year requirement.

score 0 · Answer 2 · 2023-06-27T17:51:36-04:00

Most important:: you run on x86-64 if Windows or Red Hat is not important to your CBK
If you still have access check the size of the original CACHE.DAT.
Check also the standard collation of the original DB to avoid unexpected surprise
To my experience, there should be no difference for backups between C1017 and C2018 (the latest)

score 0 · Answer 3 · 2023-09-16T04:10:35-04:00

Well I got the CACHE.DAT files from our hosting company and renamed them to IRIS.DAT. I also got hold of the cache.cpf file which I used to merged into a iris.cpf file. So using IRIS 2023.1 I was able to mount the databases and I can see the schema and table definitions. I cannot yet see any data. So not sure what is wrong. The IRIS.DAT files are large, 30GB and 53.4GB for each database. The hosted environment was Linux, and now I'm on Windows, but I believe the endian is the same (little). Using the Management Portal I don't see any options to upgrade or even verify the database. Let me know if you have any ideas. Thanks!

score 0 · Answer 4 · 2023-09-18T03:47:42-04:00

Can you check if the globals that are referred to by the table definitions are in any database ?
Can you look in the cpf file if they are mapped to another cache.dat.

score 0 · Answer 5 · 2023-09-18T20:32:53-04:00

Yes Danny good idea. When I look at the cpf file, I actually see that I'm missing 2 databases: AVCWSTMP and AVPMTMP, but since they appeared to be for the purpose of temporary storage, I just created blank databases to take their place. And since they never gave me the corresponding DAT files, I didn't think they were important. Here's a snippet of the cpf file. I commented out the original entries of the cache.cpf file and replaced them with what I'm using in the IRIS.cfg. There appears to be some mapping of globals into the AVCWSTMP and AVPMTMP databases, but most of the mappings are into AVCWS and AVPM.

[ConfigFile]
#Version=2017.226
Product=IRIS
Version=2023.1

#[Databases]
#CACHESYS=/opt/npc/cachesys/mgr/
#CACHELIB=/opt/npc/cachesys/mgr/cachelib/
#CACHETEMP=/opt/npc/cachesys/mgr/cachetemp/
#CACHE=/opt/npc/cachesys/mgr/cache/
#CACHEAUDIT=/opt/npc/cachesys/mgr/cacheaudit/
## Must mount at startup
#AVCWS=/npc/cachedb/yes_live/avcws/,,1
#AVCWSTMP=/npc/cachedb/yes_live/avcwstmp/,,1
#AVPM=/npc/cachedb/yes_live/avpm/,,1
#AVPMTMP=/npc/cachedb/yes_live/avpmtmp/,,1
## end must mount at startup
#DOCBOOK=/opt/npc/cachesys/mgr/docbook/
#SAMPLES=/opt/npc/cachesys/mgr/samples/
#USER=/opt/npc/cachesys/mgr/user/

[Databases]
IRISSYS=D:\InterSystems\IRISHealth\Mgr\
IRISLIB=D:\InterSystems\IRISHealth\Mgr\irislib\
IRISTEMP=D:\InterSystems\IRISHealth\Mgr\iristemp\
IRISLOCALDATA=D:\InterSystems\IRISHealth\Mgr\irislocaldata\
IRISAUDIT=D:\InterSystems\IRISHealth\Mgr\irisaudit\
AVCWS=D:\InterSystems\IRISHealth\Mgr\cws\,,1
AVCWSTMP=D:\InterSystems\IRISHealth\Mgr\avcwstmp\,,1
AVPM=D:\InterSystems\IRISHealth\Mgr\pm\,,1
AVPMTMP=D:\InterSystems\IRISHealth\Mgr\avpmtmp\,,1
ENSLIB=D:\InterSystems\IRISHealth\Mgr\enslib\
HSCUSTOM=D:\InterSystems\IRISHealth\Mgr\HSCUSTOM\
HSLIB=D:\InterSystems\IRISHealth\Mgr\hslib\
HSSYS=D:\InterSystems\IRISHealth\Mgr\hssys\
USER=D:\InterSystems\IRISHealth\Mgr\user\

[Namespaces]
%SYS=IRISSYS
#%SYS=CACHESYS
AVCWS=AVCWS
AVCWSTMP=AVCWSTMP
AVPM=AVPM
AVPMTMP=AVPMTMP
#DOCBOOK=DOCBOOK
#SAMPLES=SAMPLES
HSCUSTOM=HSCUSTOM
HSLIB=HSLIB
HSSYS=HSSYS
USER=USER

[Map.AVCWS]
Global_A=AVPM
Global_ACAP=AVPM
Global_ADT*=AVCWSTMP
Global_AERROR*=AVCWSTMP
Global_AGE*=AVCWSTMP
Global_APAY*=AVCWSTMP
Global_AREF=AVPM
Global_AREFCAP=AVPM
…
Global_CacheSql=AVCWSTMP
…
Global_csmsql=AVCWSTMP
...

[Map.AVPM]
Global_A=AVPM
Global_ACAP=AVPM
Global_ADT*=AVCWSTMP
Global_AERROR*=AVCWSTMP
...
Global_CacheSql=AVPMTMP
…
Global_csmsql=AVPMTMP
<End of Message>

score 0 · Answer 6 · 2023-09-20T03:51:05-04:00

Hi Mark,
Can you look in the definition of one of the classes/sql tables where you don't see any data : In Studio, when looking at the storage (bottom), which global is it using (usually name of the class + D) ? Where is this global mapped, and can you see this global in the database in management portal (System Explorer -> Globals).

score 0 · Answer 7 · 2023-09-20T04:06:51-04:00

Hi Mark,

Can you also check that you received the correct CACHE.DAT files by looking at the label inside the files (they might be copied from the wrong directories).
Do you still have the original .DAT files ? After renaming them from CACHE.DAT to IRIS.DAT, before you mount them in Iris, you can check the label stored inside the .DAT file.

when in terminal (%SYS) :
%SYS> Write $$ROOT^LABEL("c:\directory-unmounted-irisdat-file\")
It should display the original path of the CACHE.DAT from the Unix directory.

score 0 · Answer 8 · 2023-09-20T14:37:53-04:00

Wow! Using the Management Portal, under System > Globals > View Global Data, I can see data by looking at my ^DocM.DocumentImageD global! But when I use SQL: select ID, ... from DocM.DocumentImage (no where clause) I get: Row count: 0 Performance: 0.0660 seconds 327 global references 5390 commands executed 0 disk read latency (ms) Cached Query: %sqlcq.AVCWS.cls4 Last update: 2023-09-20 11:34:26.644

score 0 · Answer 9 · 2023-09-20T14:42:15-04:00

I don't seem to get any meaningful data from terminal queries on original unmounted DAT files.

%SYS>Write $$ROOT^LABEL("D:\Avatar\avatar\avcwsdat\CACHE.DAT")
-1
%SYS>Write $$ROOT^LABEL("D:\Avatar\avatar\avpmdat\CACHE.DAT")
-1

score 0 · Answer 10 · 2023-09-20T17:11:52-04:00

Hi Danny I did a head for the DAT files and they appear to be correct. I'm not sure how indexing works in IRIS, but I have a suspicion that data indexes are stored in the missing “TMP” databases. That might explain why I can see data by directly looking at the globals but not getting results with SQL queries. When I search the iris.cpf file for global mappings containing the word index, I can get the following:

Global_indexEp*=AVCWSTMP
Global_indexTX=AVPM
Global_indexCareFabricQuery=AVPMTMP
Global_indexEp*=AVPMTMP

Almost every instance is mapped to a TMP database. I’m asking our hosting company for these files now.

score 0 · Answer 11 · 2023-09-21T17:06:20-04:00

The exact index global for e.g. your class DocM.DocumentImage will by default be ^DocM.DocumentImageI (unless the class definition storage was changed).

You can rebuild the indices from the data by using the portal, click on the sql tabel and click on Actions->Rebuild indices.
Or go to terminal and Do ##class(DocM.DocumentImage).%BuildIndices()

(When doing an SQL query in the portal, you can view the query plan to see wheter an index was used.)

score 0 · Answer 12 · 2023-09-26T11:36:09-04:00

When I use the console to rebuild an index, I get this:
DO ##CLASS(DocM.DocumentImage).%BuildIndices()
^
<CLASS DOES NOT EXIST> *%Library.CacheStorage
I did a little research and found that %Library.CacheStorage was replaced by %Library.Persistent in IRIS.

score 0 · Answer 13 · 2023-09-28T01:59:54-04:00

Hi Danny, the recompile worked well (just a few errors logged). The Upgrade() command just said "No classes were modified". But after those commands I was able to rebuild the indexes with no errors! Still SQL queries come back empty. Will keep scratching my head! I increase the row limit of my view of the global values to 5K, and the data looks great, just can't get it via SQL yet.

score 0 · Answer 14 · 2023-09-28T04:50:01-04:00

Robert Cemper · Sep 28, 2023

Looks like old cached queries don't know new index ???
try drop cached queries from SMP

0 0

score 0 · Answer 15 · 2023-09-28T13:26:07-04:00

Hi Mark,

What can you see in the management portal (Explorer -> SQL) when you click on a table in Catalog Details -> Maps/Indices : should list your globals that are being used.
What can you see when you click on Open Table ?
How about the user that you use : does it have %All rights?

Have you tried opening an instance with Set obj = ##CLASS(DocM.DocumentImage).%OpenId( <some id> ) ?

score 0 · Answer 16 · 2023-09-28T14:56:21-04:00

Hi Danny, yes I see a list of 20 indexes and their hyperlinked globals. This is where I click on the DocM.DocumentImageD global I can clearly see the data. When I clicked on Open Table I get a separate window: http://localhost:8972/csp/sys/exp/UtilSqlOpen.csp?$NAMESPACE=AVCWS&$ID1=DocM&$ID2=DocumentImage but it remains a blank page. The table is huge, about 500K records, so wondering if Open Table will paginate.

score 0 · Answer 17 · 2023-09-28T16:44:50-04:00

In the DocM.DocumentImageD global, the first subscript is the id of each instance. Can you open such an id in terminal and look at the data 'the object way' by doing Set obj = ##CLASS(DocM.DocumentImage).%OpenId( id) and write he properties (like write obj.propxyz)

score 0 · Answer 18 · 2023-09-28T17:38:02-04:00

The first part of the 1st record of the DocumentImageD global is
1: ^DocM.DocumentImageD(1,1) = $lb("",1,"26179","PATIENT.USER_DEFINED",1,0,...

So I assume an id of 1 will suffice.
AVCWS>Set obj = ##CLASS(DocM.DocumentImage).%OpenId(1)

I assume a property will be any column name? But any writes I try come up with <INVALID OREF>.
AVCWS>write obj.ID
WRITE obj.ID
^
<INVALID OREF>

score 0 · Answer 19 · 2023-09-29T12:39:33-04:00

Yes, I got this to work. Viewing the class definition was another learning experience. So in the terminal opening and writing out the global data works.

AVCWS>Set obj = ##CLASS(DocM.DocumentImage).%OpenId("1||1")
AVCWS>write obj.DocumentDescription
Diagnostic Impressions (Non PHP)-Diagnostic Impressions
AVCWS>

score 0 · Answer 20 · 2023-09-29T16:59:13-04:00

Danny Wijnschenk · Sep 29, 2023

Hi Mark,
Can you post one of the class defintitions here ?

0 0

score 0 · Answer 21 · 2023-09-28T16:58:22-04:00

Can you do the following (assuming the namespace USER is empty):
- copy the class definition of one class to the USER namespace (take a class that does not refer to other of your classes)
- do a SQL Insert into that table in USER
- do an SQL Select of that table in USER
- look if the global ^...D exists in USER.
- now copy the ^...D global from your orignal namespace to USER (using Merge command, or export/import in mgmnt portal)
- Rebuild the indexes in USER of the class
- do the SQL Select again in USER

score 0 · Answer 22 · 2023-09-29T21:02:40-04:00

Hi Danny, Ok I went ahead and exported the DocumentImage class (and one other dependent class) and successfully imported them into my empty USER namespace. Then I inserted a row using SQL. But found myself in the same situation, no SQL query results, but I could see the data in the global DocM.DocumentImageD.

score 0 · Answer 23 · 2023-09-30T04:42:06-04:00

Robert Cemper · Sep 30, 2023

did you use SQL from SqlShell?
like this?

USER> Do $system.SQL.Shell()
......
SQL>>SELECT .....

0 0

score 0 · Answer 24 · 2023-09-30T13:27:15-04:00

Or in Terminal :
Do ##class(%ResultSet).RunQuery("DocM.DocumentImage","Extent")
(This should do the default query Select ID From table)

score 0 · Answer 25 · 2023-10-02T17:31:01-04:00

Yes a security parameter and method exists as such. That was the one dependent class I also had to import into the USER namespace. I executed SQL from the Manager Portal and all the techniques from the terminal. I have like 4 records now in the USER namespace DocM.DocumentImage. I'll attach the class file in XML format.

score 0 · Answer 26 · 2023-10-02T17:41:56-04:00

Yes, the security parameter is set to 1 inside the NTSTLIB.SecurityPolicy class.

<Method name="%SecurityPolicy">
<ClassMethod>1</ClassMethod>
<FormalSpec>FACILITY:%Integer</FormalSpec>
<ReturnType>%String</ReturnType>
<SqlName>SecurityPolicy</SqlName>
<SqlProc>1</SqlProc>
<Implementation><![CDATA[ q FACILITY
]]></Implementation>
</Method>

The account I've been using is my installation account. Maybe I need to assign addition roles. Thanks!

score 0 · Answer 27 · 2023-10-02T17:58:21-04:00

Strange, I'm a member of the %All role. And I added specific permissions (via Edit Role) to DocM.DocumentImage any way, but I still can't get query results.

score 0 · Answer 28 · 2023-10-03T12:03:00-04:00

Hi Danny, I see! I vaguely remember now there was a security setting to prevent personal from one facility seeing data generated from a different facility. But that wasn't important to us since we were setup as a single facility. As a result, the FACILITY column in every table is set to 1. It's not clear to me what IRIS role would allow pass this row level security, but it seems easier than re-compiling all the classes.

score 0 · Answer 29 · 2023-10-04T12:53:57-04:00

Mark Runyan · Oct 4, 2023

OMG! That worked! You have been so helpful. I'm so grateful and it's been a really fun experience.

0 0

score 0 · Answer 30 · 2023-10-04T15:09:00-04:00

Hi Mark,
Glad to be of help, it was also fun for me, like an escape room where you need to get all clues to find your way out !
Of course, now you have the difficult task to accept all my answers as the best answer ;)

score 0 · Answer 31 · 2023-09-21T17:03:45-04:00

Danny Wijnschenk · Sep 21, 2023

Hi Mark,
In Iris, you can only get the label for a IRIS.DAT file, so you have to rename the CACHE.DAT to IRIS.DAT first

0 0