Skip to main content
Previous sectionNext section

Planning and Performing TSQL Migration

InterSystems TSQL is an implementation of Transact-SQL which supports many of the features of both the Sybase and Microsoft implementations. Transact-SQL is used with Sybase Adaptive Server and Microsoft SQL Server (MSSQL).

Note:

Sybase Adaptive Server (ASE) is the implementation primarily described in this chapter, though much of this information is relevant to any Transact-SQL implementation.

InterSystems TSQL also contains a few proprietary extensions not found in either of these implementations. These are described in the Commands and System Stored Procedures chapters.

This document will help you to quickly migrate schemas and stored procedures from Sybase or Microsoft databases and it will provide you with an understanding of the TSQL (Transact-SQL) implementation for InterSystems IRIS® data platform.

Why Migrate to InterSystems IRIS?

InterSystems IRIS provides an efficient and modern SQL interface for storing and retrieving data that provides massive scalability and supports many features to optimize performance.

All data in an InterSystems IRIS database is stored in efficient, tree-based sparse multidimensional arrays called globals. Because they are accessed directly with no file system layer needed, InterSystems IRIS globals provide very fast, flexible storage and retrieval. Globals underlie the InterSystems IRIS object and SQL interfaces, support the Key/Value and No SQL paradigms, and allow InterSystems IRIS to easily handle dynamic data types such as XML or JSON.

InterSystems SQL provides high-performance access through ODBC and JDBC (using a pure Java-based driver). It also provides a Relational Gateway to facilitate importing data from relational databases.

Running your TSQL Applications on InterSystems IRIS

When you run TSQL code on the InterSystems IRIS platform, the code is compiled into the corresponding InterSystems SQL and ObjectScript code. This compiled code is run on InterSystems IRIS.

InterSystems IRIS provides many interfaces for running TSQL code. These interfaces provide a dialect option that specifies either Sybase or MSSQL.

Regardless of which InterSystems IRIS interface is used, TSQL code is used to generated corresponding IRIS SQL executable code. InterSystems IRIS does not provide system-level support for native TSQL.

Migrating off Sybase Products

InterSystems TSQL supports the migration of Sybase Adaptive Server Enterprise (ASE). It provides much more limited support for Sybase ASA and Sybase IQ.

Migrating off Sybase ASE

InterSystems TSQL is designed to support the migration of Sybase ASE code. It provides support for native ASE TSQL commands and stored procedures. InterSystems implementation of JDBC fully supports TSQL syntax. JDBC provides significant performance enhancements, including Fast Insert. ODBC support is also provided.

It is not necessary to convert an ASE environment to InterSystems ObjectScript to enjoy the performance and scalability benefits of InterSystems IRIS. Support for the ASE environment was designed to be permanent, not transitional. Migration is all about loading the schema and then the data, then compiling the existing stored procedures, which can then be run as if they were native stored procedures.

InterSystems IRIS TSQL fully supports ASE schema, datetime and other issues when loading data, Java code using JDBC, and stored procedures.

InterSystems IRIS performance is significantly higher than Sybase ASE. It supports mirroring, which provides reporting server capability as well. InterSystems IRIS fully supports ECP servers.

Migrating off other Sybase products

Migrating Sybase ASA involves slight differences in TSQL support and dialect. While migration from ASA is possible, it should not be undertaken without close cooperation with InterSystems Support.

Migrating Sybase IQ to InterSystems TSQL is limited because IQ implementations typically involve columnar storage as a physical property of IQ tables. InterSystems TSQL does not support columnar storage.

These migrations are not further described in this documentation. Refer to InterSystems Support for further details.

Migrating off Microsoft Products

Microsoft SQL Server (MSSQL) and Azure Database use a different TSQL dialect. InterSystems IRIS SQL interfaces support the MSSQL dialect. However, InterSystems IRIS TSQL was principally designed to support Sybase migration, and provides more limited support for Microsoft TSQL compatibility and migration.

Microsoft provides good TSQL reference material at: https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15.

Planning your Migration

Planning the Infrastructure

Because of the efficiencies of InterSystems IRIS, most implementations of TSQL should run on InterSystems IRIS with smaller hardware requirements and superior performance.

Note that the InterSystems IRIS implementation of TSQL supports mirroring, but does not support sharding.

Reviewing your Application Schema and Code

InterSystems IRIS TSQL supports common features of SQL and many extensions to the SQL standard.

TSQL features not supported on InterSystems IRIS

InterSystems IRIS TSQL does not support most proprietary extensions to Transact-SQL.

Note that InterSystems IRIS TSQL features and compatible work-arounds are continually expanding. Contact InterSystems Support for further details.

Client access to TSQL code on InterSystems IRIS

InterSystems IRIS TSQL supports JDBC access for TSQL. Support for ODBC for TSQL is being developed and will be fully supported in the near future.

InterSystems SQL provides a much broader set of accessibility options. For further details, refer to InterSystems IRIS documentation.

Reviewing your Data

InterSystems IRIS TSQL supports a large variety of data types. However, not all Sybase data types are supported or can be supported. Similarly, most but not all data conversions are supported.

Sybase IQ uses different storage paradigms that are not supported by InterSystems IRIS.

Project Planning

The following are important consideration when determining the scope of your migration to InterSystems IRIS:

  • Availability of TSQL expertise.

  • Availability of the original TSQL application developers for functional questions.

  • Availability of unit tests or other test scripts.

  • Initial assessment of schema and code. Determining complexity and presence of unsupported features.

  • Assessing the size of the data set.

  • Agreement on your requirements for switching over, either an all-at-once conversion or a phased conversion.

In the process of migration you may use any combination of the following approaches:

  • Migrating all TSQL code and then fixing incompatible code.

  • Migrating compatible TSQL code and then augmenting with new development in InterSystems SQL.

  • Letting TSQL and SQL systems run side-by-side, leveraging the benefits of each.

Testing the Migration

In testing the migration of TSQL code and data sets the following are important considerations:

  • Unit tests. TSQL unit tests can be migrated along with TSQL application code.

  • Performance tests.

  • Scalability tests.

  • Correctness tests. Because semantics may differ between TSQL and InterSystems SQL, these are particularly important.

Executing the Plan

To migrate existing TSQL applications to InterSystems TSQL, you need to perform three operations: configure InterSystems IRIS for TSQL, migrate the TSQL source code, and migrate the data.

Setting Up the System

To configure your system for TSQL:

  • Go into the InterSystems IRIS Management Portal. Select System Administration, Configuration, SQL and Object Settings, then select TSQL Compatibility. Here you can specify the dialect (Sybase or MSSQL), and turn on or off the ANSI_NULLS, CASEINSCOMPARE, and QUOTED_IDENTIFIER settings. The default for all three is “off”, which is the appropriate setting for Sybase ASE. These values are used to set the ^%SYS(“tsql”,”SET”,...) global array values.

  • From the Management Portal, select System Administration, Configuration, SQL and Object Settings, then SQL. From here, you can set the Default Schema. This is the default schema name (which maps to a package) for all unqualified DDL entities.

  • Set Equal Literal Replacement to OFF. This is the appropriate setting for Sybase ASE.

  • Set the default collation sequence to %SQLSTRING. This is the appropriate setting for Sybase ASE. Collation options perform string conversions only for the purpose of index collation; the stored data is not changed.

  • From the Management Portal, select System Administration, Configuration, SQL and Object Settings, then User DDL Mappings. You can use this option to map any needed user-defined data types.

Migrating the Code

The initial application migration is simple:

  1. Import the DDL: Import table and view definitions using either the %SYSTEM.SQL.Schema.ImportDDL() method (for single files) or the $SYSTEM.SQL.Schema.ImportDDLDir() method (for multiple files in a directory). Within these methods, you set the DDLMode parameter to either "MSSQLServer" or "Sybase". These methods import DDL statements, as well as DML statements such as INSERT, convert them to equivalent InterSystems IRIS SQL, and execute them. For further details, see Importing SQL Code.

    Alternatively, you can invoke the $SYSTEM.SQL.Schema.LoadSybase() or $SYSTEM.SQL.Schema.LoadMSSQLServer() method to import the schema. For further details, see Importing SQL Code.

    If the TSQL source contains CREATE PROC statements, then a class method containing the CREATE PROC source is created. InterSystems IRIS places this class method in either an existing class or in a new class whose name is based on the schema and procedure name. If the procedure already exists, then the existing version is replaced by the new version. If a class matching the class name generated from the schema and procedure already exists, then this class name is used — if it was previously generated by the TSQL utility. If not, then a unique class name is generated, based on the schema and procedure name. The resulting class is compiled once the procedure has been successfully created. If logging is requested then the source statements are logged along with the name of the containing class, class method, and the formal arguments generated. Any errors encountered by the process are also reported in the log. If an error is detected during CREATE PROC processing, InterSystems IRIS deletes any new class that was generated for that procedure.

  2. Inspect the log file for errors: Search by Error #. A summary count of errors and successful imports will appear at the end of the log. In most cases, errors can be worked around or addressed by using information found in this document.

  3. Compile: When you import DDL, table and view definition compilation is automatically performed. To compile other TSQL source code, it is best to use the command as follows:

      DO $SYSTEM.OBJ.CompileAll("-l")
    Copy code to clipboard

    The lowercase “L” qualifier flag specifies that locking is not applied for the duration of the compile. For a full list of flag qualifiers, call DO $SYSTEM.OBJ.ShowFlags().

Migrating the Data

The following are options for migrating data:

  • Set up an SQL Gateway Connection, using either JDBC or ODBC for data export/import.

    In the Management Portal select System Administration, Configuration, Connectivity, SQL Gateway Connections, then select the Create New Connection button to define an SQL Gateway Connection.

  • In the Management Portal select System Explorer, SQL, then from the Wizards drop-down list select Data Migration To configure a Data Migration Wizard. Select an existing SQL Gateway Connection from the drop-down list. This runs a wizard to migrate data from an external source and creates an InterSystems IRIS class definition to store it.

  • For a large volume of data, use a bulk loader that provides support for reading Bulk Copy Program (BCP) files, or use your preferred Extract Transform and Load (ETL) utility. InterSystems Support can provide tools for bulk data ingestion. Contact InterSystems Support for further details.

Troubleshooting

Inspect the compile log

Use the TRACE functionality.

Writing and Executing TSQL on InterSystems IRIS

  • Writing TSQL class methods and stored procedures

    Create a class method stored procedure and enter the language as tsql. You can use the following template as a starting point:

    ClassMethod MyTestMethod() As %Integer 
       [ Language = tsql, ReturnResultSets, SqlName=name, SqlProc ]
    {
    }
    Copy code to clipboard

    See the Language keyword for method definition in the Class Definition Reference.

    You can write and maintain TSQL stored procedures (SPs) in Studio. A TSQL SP can be either a class method or a query. A class method takes parameters and returns a single scalar result, a query takes parameters and returns rows. If you put plain SELECT statements into a class method they will be executed but you won't be able to get the rows.

    TSQL procedures are converted to InterSystems IRIS methods or queries with a Language type equal to TSQL. Use the following command:

    DO ##class(%TSQL.Manager).load("sybase",<filename>,<logname>)
    Copy code to clipboard

    When compiling TSQL methods, ObjectScript code is generated. There is no system-level support for native TSQL. It is best to maintain the methods in TSQL to retain the familiar look of the original stored procedures.

  • Using Dynamic SQL

    InterSystems IRIS Dynamic SQL can be used to execute TSQL code queries and a limited subset of other DML and DDL statements.

    • You can create a Dynamic SQL statement class instance, then set the %Dialect property to Sybase or MSSQL. You then prepare and execute a TSQL command within that object instance.

    • You can execute Dynamic SQL without creating a statement class instance by invoking the %SYSTEM.SQL.Prepare() method which prepares an SQL command, or the %SYSTEM.SQL.Execute() method, which both prepares and executes an SQL command. Both of these methods provide a Dialect parameter.

    See “Using Dynamic SQL” in the Using InterSystems SQL manual.

  • Using the Management Portal SQL Interface

    The SQL interface Dialect option allows you to set the SQL dialect to IRIS, Sybase, or MSSQL. The default is IRIS. Note that the dialect you select becomes the user customized default the next time you access the Management Portal. See “Using the Management Portal SQL Interface” in the Using InterSystems SQL manual.

  • Using the TSQL Shell

    The InterSystems TSQL Shell can be used to execute Transact-SQL code from InterSystems IRIS. To use the TSQL Shell, invoke the TSQLShell() (or $SYSTEM.SQL.Schema.LoadTSQL()) method from the Terminal as follows: DO $SYSTEM.SQL.TSQLShell(). This invokes the InterSystems SQL Shell and sets its DIALECT configuration parameter to the currently configured TSQL dialect (MSSQL or Sybase). The initial configuration default is MSSQL.

    When entering SQL code interactively, the TSQL Shell supports, but does not require, the semicolon (;) statement delimiter at the end of each SQL statement.

    You can use the Shell’s RUN command to execute a TSQL script file. The RUN command displays a series of prompts, including Please enter the end-of-statement delimiter (Default is 'GO'): GO=>. This enables you to specify the TSQL semicolon (;) as the statement delimiter in your script file, rather than the InterSystems IRIS default GO statement. See “Using the SQL Shell Interface” in the Using InterSystems SQL manual.

  • Using the InterSystems SQL Shell

    The InterSystems SQL Shell can be used to execute lines of TSQL code by using the SET DIALECT command to set the Shell’s dialect to Sybase or MSSQL.

    When the Shell’s dialect is set to Sybase or MSSQL, the SQL Shell supports, but does not require, the semicolon (;) statement delimiter at the end of each SQL statement. When the Shell’s dialect is set to IRIS, a semicolon (;) statement delimiter results in an SQLCODE -25 error.

    You can use the Shell’s RUN command to execute a TSQL script file. The RUN command displays a series of prompts, including Please enter the end-of-statement delimiter (Default is 'GO'): GO=>. This enables you to specify the TSQL semicolon (;) as the statement delimiter in your script file, rather than the InterSystems IRIS default GO statement. See “Using the SQL Shell Interface” in the Using InterSystems SQL manual.

  • Using Triggers

    You can write and maintain triggers, which are sets of instructions that appear in TSQL code and that are executed in response to certain SQL events. You can use the Language=tsql class definition keyword to specify that a trigger is written in TSQL. The UpdateColumnList class definition keyword is only supported for TSQL. Row-level triggers are not supported for TSQL. See “Using Triggers” in the Using InterSystems SQL manual.