Class Reference
IRIS for UNIX 2019.4
InterSystems: The power behind what matters   
Documentation  Search
  [%SYS] >  [%SQL] >  [Migration] >  [Import]
Private  Storage   

class %SQL.Migration.Import extends %RegisteredObject, Util

Imports Objects from Relational Databases

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
18


Summary

Properties
Debug DebugOutFile DeferIndices DeleteExistingData
GTWUnicode MaxErrors NoCheck NoJournal
NoTrigger PreservePKName

Methods
%%OIDGet %AddToSaveSet %BindExport %BuildObjectGraph
%ClassIsLatestVersion %ClassName %Close %ConstructClone
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Extends
%GetParameter %IncrementCount %IsA %IsModified
%New %NormalizeObject %ObjectModified %OriginalNamespace
%PackageName %RemoveFromSaveSet %SerializeObject %SetModified
%ValidateObject AccessGetPKey AddOwnerMap ClearError
ClearErrors ClearOwnerMap ClearStatusMsg Connect
CopyData CopyDataJ CopyOneView CopyOneViewJ
CopyTableFKeys CopyTableFKeysJ CopyTableStruct CopyView
CopyViewJ CopyViewRecursive CopyViewRecursiveJ DSNClose
DSNExecute DSNFetch DatatypeName Disconnect
DropForeignKeys DropTable DropView ErrorCount
ErrorLogClose ErrorLogExecute ErrorLogFetch ExecSql
FindIdentity FindIdentityJ FindViewDeps FindViewDepsJ
GetCGateHandle GetLastErrorMsg GetLastStatusMsg GetOwnerMap
GetPKeyStr GetPKeyStrJ Gethdbc IFXFindNotNull
InitDLL LogError LogStatusMsg OraDefault
ShowAllErrors ShowAllStatusMsg SqlTablesClose SqlTablesExecute
SqlTablesFetch isaView isaViewJ


Methods

• method Connect(dsn As %String, localuser As %String = "", isJDBC As %Boolean = 0) as %Status
Connect to the client system via an SQL Gateway Connection (ODBC or JDBC), and initializes some structures

Arguments:
  • dsn - is the name of the SQL Gateway Connection to connect to
  • localuser - obsolete
  • isJDBC - 1/0 flag. If this SQL Gateway connection is via JDBC use 1, otherwise use 0. 0 (ODBC) is the default parameter value
  • • method CopyData(TbOwner As %String, TbName As %String, ScrubRoutine As %String) as %Status
    CopyData (for ODBC connection) - Copies the data from the source system to this system
  • 1 - get list of fields on source table (through gateway)
  • 2 - construct select list for source
  • 3 - construct update list for dest
  • 4 - select loop on source
    * for each, insert into destination
    * for each Blob on source:
    * while whole blob not fetched
    * fetch blob block from source
    * add blob to destination blob
  • 5 - create indices, ....or anything

    Arguments:
  • TbOwner - Name of the owner/schema of the target table
  • TbName - Name of the target table
  • ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

    DATA SCRUBBING:
    For each data import process, a Routine can be speficied to modify field values prior to inserting them into InterSystems IRIS. This also allows to skip specific rows of data, preventing their insertion into InterSystems IRIS. The user-written Data scrubbing routine must provide 2 entry points.
  • Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)
  • Data(colData)

    Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
    Arguments:
  • TbOwner - The SQL Schema in the origin datasource
  • TbName - The SQL Table name
  • colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"
  • colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:
    #define SQLCHAR 1
    #define SQLBIGINT -5
    #define SQLBINARY -2
    #define SQLBIT -7
    #define SQLDATE 9
    #define SQLDECIMAL 3
    #define SQLDOUBLE 8
    #define SQLFLOAT 6
    #define SQLGUID -11
    #define SQLINTEGER 4
    #define SQLLONGVARCHAR -1
    #define SQLLONGVARBINARY -4
    #define SQLNUMERIC 2
    #define SQLREAL 7
    #define SQLSMALLINT 5
    #define SQLTINYINT -6
    #define SQLTIME 10
    #define SQLTIMESTAMP 11
    #define SQLVARBINARY -3
    #define SQLVARCHAR 12
  • colStreamTypes - An array 1..N. One element per column in the table. The value is
    0: if the column data is not a stream
    1: if the column data is contained in a %CharacterStream
    2: if the column data is contained in a %BinaryStream
    Return value: none (Quit with no argument)

    Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into InterSystems IRIS.
    Arguments:
  • colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in InterSystems IRIS as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.
  • • method CopyDataJ(TbOwner As %String, TbName As %String, ScrubRoutine As %String) as %Status
    CopyDataJ (for JDBC connection) - Copies the data from the source system to this system
  • 1 - get list of fields on source table (through gateway)
  • 2 - construct select list for source
  • 3 - construct update list for dest
  • 4 - select loop on source
    * for each, insert into destination
    * for each Blob on source:
    * while whole blob not fetched
    * fetch blob block from source
    * add blob to destination blob
  • 5 - create indices, ....or anything

    Arguments:
  • TbOwner - Name of the owner/schema of the target table
  • TbName - Name of the target table
  • ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

    DATA SCRUBBING:
    For each data import process, a Routine can be speficied to modify field values prior to inserting them into InterSystems IRIS. This also allows to skip specific rows of data, preventing their insertion into InterSystems IRIS. The user-written Data scrubbing routine must provide 2 entry points.
  • Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)
  • Data(colData)

    Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
    Arguments:
  • TbOwner - The SQL Schema in the origin datasource
  • TbName - The SQL Table name
  • colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"
  • colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:
    #define SQLCHAR 1
    #define SQLBIGINT -5
    #define SQLBINARY -2
    #define SQLBIT -7
    #define SQLDATE 9
    #define SQLDECIMAL 3
    #define SQLDOUBLE 8
    #define SQLFLOAT 6
    #define SQLGUID -11
    #define SQLINTEGER 4
    #define SQLLONGVARCHAR -1
    #define SQLLONGVARBINARY -4
    #define SQLNUMERIC 2
    #define SQLREAL 7
    #define SQLSMALLINT 5
    #define SQLTINYINT -6
    #define SQLTIME 10
    #define SQLTIMESTAMP 11
    #define SQLVARBINARY -3
    #define SQLVARCHAR 12
  • colStreamTypes - An array 1..N. One element per column in the table. The value is
    0: if the column data is not a stream
    1: if the column data is contained in a %CharacterStream
    2: if the column data is contained in a %BinaryStream
    Return value: none (Quit with no argument)

    Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into InterSystems IRIS.
    Arguments:
  • colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in InterSystems IRIS as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.
  • • method CopyTableFKeys(FkTbOwner As %Library.String, FkTbName As %Library.String) as %Status
    Copy Foreign Keys from remote DB via ODBC to InterSystems IRIS for the given FK Table.
    Arguments:
  • FkTbOwner - Name of the owner/schema of the foreign key table
  • FkTbName - Name of the target table to get Foreign Keys from
  • • method CopyTableFKeysJ(FkTbOwner As %Library.String, FkTbName As %Library.String, verbose As %Library.Boolean = 0) as %Status
    Copy Foreign Keys from remote DB via JDBC to InterSystems IRIS for the given FK Table.
    Arguments:
  • FkTbOwner - Name of the owner/schema of the foreign key table
  • FkTbName - Name of the target table to get Foreign Keys from
  • • method CopyTableStruct(TbOwner As %String, TbName As %String, verbose As %Boolean = 0) as %Status
    Cleanup previous version of table (Table,Index,Foreign Keys), re-create table with Fields, Unique and Primary Key constraints, and Index Definitions
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • verbose - 1/0 flag. If 1 display output to screen. Default os 0.
  • • method CopyView(VOwner As %String, VName As %String) as %Status
    Copy One View and its dependencies via ODBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
  • • method CopyViewJ(VOwner As %String, VName As %String) as %Status
    Copy One View and its dependencies via JDBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
  • • method CopyViewRecursive(VOwner As %String, VName As %String, hstmt As %String)
    Find dependencies. Copy each dependency View via ODBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
  • • method CopyViewRecursiveJ(VOwner As %String, VName As %String)
    Find dependencies. Copy each dependency View via JDBC
    Arguments:
  • VOwner - Name of the owner/schema of the view
  • VName - Name of the target view
  • • private method CpTableIndex(TbOwner As %Library.String, TbName As %Library.String) as %Status
    Copied Indices from remote DB via ODBC to InterSystems IRIS for the specified table. Note: currently, all Indices, be they user or system defined, are copied as normal indices. Oracle Bitmap Indices and Sql Server Clustered Indices are copied as normal Indices. Indices on Primary Keys are not created, as those are automatically generated.
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • • private method CpTableIndexJ(TbOwner As %Library.String, TbName As %Library.String, ByRef pkColumns As %String, verbose As %Boolean = 0) as %Status
    Copied Indices from remote DB via JDBC to InterSystems IRIS for the specified table. Note: currently, all Indices, be they user or system defined, are copied as normal indices. Oracle Bitmap Indices and Sql Server Clustered Indices are copied as normal Indices. Indices on Primary Keys are not created, as those are automatically generated.
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • pkColumns - Passed by Reference. returns primary key columns
  • verbose - 1/0 flag. If 1 display output to screen. Default os 0.
  • • private method CpTableSchema(TbOwner As %Library.String, TbName As %Library.String) as %Status
    Copy the table definition via ODBC to InterSystems IRIS: Fields, Datatypes, Not Null Columns, Defaults columns, and table Primary Key.
    Not copied by this function are: Foreign Key constraints, Check Constraints, table Triggers, Autoincrements.
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • • private method CpTableSchemaJ(TbOwner As %Library.String, TbName As %Library.String, ByRef pkColumns As %String) as %Status
    Copy the table definition via JDBC to InterSystems IRIS: Fields, Datatypes, Not Null Columns, Defaults columns, and table Primary Key.
    Not copied by this function are: Foreign Key constraints, Check Constraints, table Triggers, Autoincrements.
    Arguments:
  • TbOwner - Name of the owner/schema of the table
  • TbName - Name of the target table
  • pkColumns - Passed by Reference. returns primary key columns
  • • private method Datatypes() as %Status
    Build Array of Datatypes for source Database
    • method Disconnect() as %Status
    Disconnect from the SQL Gateway (ODBC)
    • method ErrorCount() as %Integer
    Returns the current number of import errors.
    • method Gethdbc() as %String
    Returns Database handle for current connection ("" or 0 if not connected)


    Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.