Import/export data from InterSystems Caché

Hi!

Suppose I have full access to Caché database instance A and want to export consistent part of the data and import it into another Caché instance B. Classes are equal.

What are the most general and convenient options for me?

TIA!

  • 0
  • 0
  • 4023
  • 6
  • 5

Answers

You could use %GOF for export and %GIF for import from Terminal.   These tools export block level data.   The ultimate size of the export will be much less than other tools 

Is this a one-time migration of data from instance A to instance B?

If so, create a new database on instance A and then use GBLOCKCOPY to copy from the existing database to the new one.   Then just move the new database to instance B

Thank you Kenneth!

But what if you need a part of data? Say the records only from current year or from particular customer?

And what if you need not all the classes, but part of them - what globals should I choose to export?

I believe in this cases we should use SQL to gather data. The question is how to export/import it.

 

One approach would be to use %XML.DataSet to convert SQL results into XML:

Set result=##class(%XML.DataSet).%New()                                  
Do result.Prepare("SELECT TOP 3 ID, Name FROM Sample.Person")
Do result.Execute()                                                      1
Do result.WriteXML("root",,,,,1) 

Outputs:

<root>
<s:schema id="DefaultDataSet" xmlns="" attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <s:element name="DefaultDataSet" msdata:IsDataSet="true">
    <s:complexType>
      <s:choice maxOccurs="unbounded">
        <s:element name="SQL">
          <s:complexType>
            <s:sequence>
              <s:element name="ID" type="s:long" minOccurs="0" />
              <s:element name="Name" type="s:string" minOccurs="0" />
            </s:sequence>
          </s:complexType>
        </s:element>
      </s:choice>
    </s:complexType>
    <s:unique name="Constraint1" msdata:PrimaryKey="true">
      <s:selector xpath=".//SQL" />
      <s:field xpath="ID" />
    </s:unique>
  </s:element>
</s:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DefaultDataSet xmlns="">
  <SQL diffgr:id="SQL1" msdata:rowOrder="0">
    <ID>96</ID>
    <Name>Adam,Wolfgang F.</Name>
  </SQL>
  <SQL diffgr:id="SQL2" msdata:rowOrder="1">
    <ID>188</ID>
    <Name>Adams,Phil H.</Name>
  </SQL>
  <SQL diffgr:id="SQL3" msdata:rowOrder="2">
    <ID>84</ID>
    <Name>Ahmed,Edward V.</Name>
  </SQL>
</DefaultDataSet>
</diffgr:diffgram>
</root>

There is also %SQL.Export.Mgr class, which does SQL export.

Thank you, Ed. And I can import the result on Instance B with class .... ?

If so, create a new database on instance A and then use GBLOCKCOPY to copy from the existing database to the new one.   Then just move the new database to instance B.

That can help sometimes. Thank you. Just move - you mean unmount and download cache.dat file?

Is this a one-time migration of data from instance A to instance B?

My question is a request for general  approaches.  But my task now is to extract some part of consistent data from the large database to use it as test data in my local database for development purposes.

 

 

If it is more complex to determine the data set, because you have specific parameters in mind it makes sense to select the data via SQL and insert the selected record into the other instance via SQL. You can either use linked tables, which allows you to write this simple logic in Caché Object Script, or you can write a simple Java application and go directly via JDBC. Obviously, any supported client-side language can solve this challenge, Java is just one option.

In case you have to migrate data where the model includes foreign-key constraints, you have to use the %NOCHECK keyword in your SQL INSERT statement: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

This approach is definitely more work than just exporting/importing the data, but it allows to easily add simple logic with some benefits, e.g. anonymization, batch-loading and parallelization. Depending on your use case some of these topics may be relevant.

Hi,

Another option is try to use the SQL Data Migration Wizard. You can copy just the data and or create the schema as well.

To select the data from a specific Year, Customer, etc. you can create a view on the source side and then use the migration wizard to migrate to importe the data.

I hope it helps.

Fábio.

Cannot make an answer on my own question. 

Anyway, here are some answers from Russian forum:

DbVisualizer and Caché Monitor can export/import InterSystems Caché data partially via SQL queries.

There is also %Global class wrapper for %GI, %GIF,..etc routines which can help to export/import global nodes partially. Documentation.

Hi All,

 

I need urgent help,

I want to export the values from Global to CSV file.

Values are in global are :

^Global1(1)="1,2,3,4"
^Global1(2)="5,6,7,8"
.
.
.
^Global1(n)="n,n,n,n"

I want output in CSV File as:
1,2,3,4
5,6,7,8
.
.
.
n,n,n,n

I made a class:

ClassMethod ExportNewSchemaGlobals(pFile)
{
    Set ary("^Global1")=""
    Set pFile = "C:/Test.csv"
      
    Set ary = ##class(%Library.Global).Export(,.ary,pFile)
}

But its not giving expected Output.

I am glad to see that you subsequently posted this as a new question here and it has already received an answer.