Question
· Jun 27

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

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 (12)1
Log in or sign up to continue

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.

 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!

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>

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.

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

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. 

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.)