Import/export data from InterSystems IRIS
Hi!
Suppose I have full access to InterSystems IRIS database instance A and want to export a consistent part of the data and import it into another InterSystems IRIS instance B. Classes are equal.
What are the most general and convenient options for me?
TIA!
Comments
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 .... ?
%XML.Reader and %SQL.Import.Mgr respectively.
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.
Renamed post to IRIS as the content is relevant to IRIS as well.
This could be useful to the other versions of IRIS as well. Folks who use IRIS for Health, Health Connect, and HealthShare might find this useful.
Indeed, thank you @Scott Roth!
If the two systems are at network reach you could use ECP and Remote Database(s).
Then the logic could be rather easily scripted with all the power of ObjectScript at hand.
I wish ECP setup could be as useful as it sounds
I wouldn't know why setting up ECP isn't easy.
Super easy by the Management Portal, but it can also be scripted.
For 'useful' I'd say: just skim through your globals and copy or merge whatever you need.
In 2021 I published my article IRIS easy ECP workbench
with a related a Docker based demo on OEX.
All you need is the ECP enabled license for containers or Platform Independent
Community version is not ECP enabled