New post
Question
· Apr 24, 2016

Import/export data from InterSystems Caché

#Databases #Data Import and Export #Object Data Model #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!

Discussion (11)1
Log in or sign up to continue
Jenna Poindexter · Apr 24, 2016

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

1 0
Eduard Lebedyuk · Apr 25, 2016

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.

2 0
Evgeny Shvarov · Apr 24, 2016

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.

0 0
Stefan Wittmann · Apr 25, 2016

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.

1 0
Confused Developer · May 4, 2017

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.

0 0