Cache is a database management system developed by Intersystems. If you have an application based on Cache, you may need to extract data from it into another database system like Oracle, SQL Server, PostgreSQL, or MySQL. Cache is a very flexible database, so sometimes, it is difficult to predict how the data is stored. For instance, some developers may keep all their data in globals while normal tables will be empty. Here is a brief guide to Cache database extraction.
The Challenge With Global Storage
If the data you want to extract is stored in classes and tables, you can access it through ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity).
But if all data is stored in Globals, and you retrieve the data, you may find that the data looks strange.
Globals lack typical database table schema, so the closest you can get to retrieving data in a human-readable form is to go through the System Management Portal. The main options are to open a terminal and use the d^%G command or the * zw command.
However, it is possible to define classes with custom-mapped storage to make them appear as if they are stored in tables through SQL.
Exporting Data Through Cache Monitor
Cache Monitor is a utility that can export data in different formats. You can use it to export data to HTML, XML, JSON, comma-delimited text files, Excel files, and SQL INSERT statements.
You can use the Save Results or the Export Table feature to do the data export. Save Results is available in any results table. For instance, you can use the Query Analyzer to create a SQL query to view a dataset and then export the results using Save Results.
The Export Table option works well for large result sets. It allows you to direct the results of your query to a file. You may use it to export many tables to a file with a single command.
In the Server Navigator, select your desired Namespace and choose the Tables node. Highlight the tables to export and then pick Export Table from the context menu.
Complete the export by selecting a folder (or directory) and filename for the exported data.
You may also make use of the Export Wizard when you want to do a bulk export. This tool is ideal for exporting a large table. All you have to do is select the table and click on Export. You may also save the selection for future use.
Selective Data Export
The tools and methods listed earlier are suitable for extracting data from individual tables and views. But sometimes, you may need to export data from multiple tables through a complex SQL statement.
For this, you can use the IRIS Management Portal. This tool allows you to pull out information with any SQL statement.
Within the IRIS Management Portal, navigate to System Explorer, then select SQL. Inside the SQL Explorer, you can change your Namespace to view a list of your tables. This will help you to type your table names correctly.
This method is suitable for handling simple query joins with two or three tables. But if you have to join several tables to create your dataset, you may end up with a timeout message.
The message comes up when the response time expires before the database completes the query on large datasets.
If the query does not finish before the browser timeout occurs, you will get a timeout error message.
A good workaround is to start a terminal session and run your SQL query from the shell prompt. You can also use other tools like DBeaver, DataGrip, DbVisualizer, or Cache Monitor to extract large datasets. Combine them with JDBC so you can display the results in a custom application.
Extracting extensive datasets from a Cache database can become quite complicated. If you have a data migration project involving data extraction from Cache to another database like SQL Server or Oracle, contact a data migration specialist.
Get Expert Help for Cache Database Extraction
Contact MediQuant at 844.286.8683 today to discuss your data migration needs. If you need to migrate healthcare data, visit our contact page to book a free consultation.