Planning and Performing TSQL Migration
InterSystems TSQL is an implementation of the Transact-SQL procedural language which supports many of the features of both the Sybase and Microsoft implementations. Transact-SQL is used with Sybase Adaptive Server, Microsoft SQL Server (MSSQL), and other platforms.
Sybase Adaptive Server (ASE) is the implementation primarily described on this page, 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 Commands and System Stored Procedures.
This document will help you to effectively migrate your schemas, stored procedures and data from a Transact-SQL database application and it will provide you with an understanding of the TSQL (Transact-SQL) implementation for InterSystems IRIS® data platform to maintain and enhance it after the initial migration.
Why Migrate to InterSystems IRIS?
InterSystems IRIS provides an efficient and modern SQL implementation delivering speed, scale and security to mission-critical applications in many industries. These performance benefits, the vertical and horizontal scalability and enterprise-grade security also apply to TSQL applications migrated to InterSystems IRIS.
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 highly optimized ODBC and JDBC drivers that have been enhanced to fully support TSQL syntax. It also provides a SQL Gateway capability to facilitate accessing and importing data from other 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. ObjectScript is InterSystems object-oriented programming language. The compiled code is run on InterSystems IRIS and available for debugging purposes, if needed. This compilation step is fully automated and not a one-time migration task. The migration effort consists of loading the schema, data and TSQL code from the old environment to InterSystems IRIS. From then on you can continue to use and modify the TSQL application code, simply recompiling it after making any changes.
InterSystems IRIS provides many interfaces for running TSQL code. These interfaces provide a dialect option that specifies either Sybase or MSSQL.
Regardless of which of these interfaces or dialects is used, InterSystems IRIS will only execute the corresponding InterSystems SQL and ObjectScript code. InterSystems IRIS does not run TSQL natively.
Migrating off Sybase Products
The initial implementation of InterSystems TSQL was designed to support the migration of Sybase ASE code. It provides support for the vast majority of native ASE TSQL commands and system stored procedures.
It fully supports or accepts most ASE schema options, including datetime formats. It supports compiling ASE TSQL stored procedure code to InterSystems SQL and ObjectScript.
InterSystems IRIS performance is significantly higher than Sybase ASE when run on the same hardware. InterSystems Mirroring capability offers more robust resiliency than Sybase ASE database replication and InterSystems Enterprise Cache Protocol (ECP) and sharding offer more flexible options for scaling out.
Sybase's other products, Sybase ASA and Sybase IQ, involves slight differences in TSQL support and dialect. While some customers have migrated successfully from Sybase ASA, it is recommended that you work with InterSystems Support when planning such a migration.
While Sybase IQ is well-known for its high performance, customers that have moved their workloads from Sybase IQ to InterSystems IRIS have experienced even higher levels of performance than they did on their previous platform.
Migrating off Microsoft Products
Microsoft SQL Server (MSSQL) and Azure Database use a different TSQL dialect that has evolved significantly since it parted ways with Sybase TSQL. InterSystems IRIS supports a subset of the MSSQL dialect. However, InterSystems IRIS TSQL was principally designed to support migration of code in the Sybase dialect. It provides more limited support for Microsoft TSQL compatibility and migration.
Planning your Migration
While migrating a TSQL application means you will end up with the same schema, data and application code (but just on a different platform), it is still worthwhile planning this project carefully.
Planning the Infrastructure
InterSystems IRIS typically requires fewer hardware resources than other database platforms to run the same TSQL workload. This means migrating to InterSystems IRIS is an appropriate time to consider the infrastructure on which to run your TSQL application:
-
Leverage InterSystems IRIS efficiencies and vertical scalability to determine the minimal hardware for your TSQL application.
-
InterSystems IRIS also runs in containers. When deployed in the cloud, different Cloud Service Providers offer a wide range of options to right-size your environment.
-
Mirroring offers a robust and proven mechanism to implement your High Availability and Disaster Recovery requirements.
-
InterSystems IRIS can use Asynchronous Mirrored Reporting Servers to offload queries from the database server, providing significantly enhanced performance in high query usage systems. A Reporting Server can provide a subset of the data on the database server, if desired.
-
For the most demanding workloads, InterSystems IRIS also supports scaling out for user and data volume independently by deploying multiple machines in a cluster.
InterSystems Technology Architects are available for consultation to assist you in this process.
Reviewing your Application Schema and Code
InterSystems IRIS TSQL supports the vast majority of TSQL concepts and language elements and will accept most schema options. Still, it is appropriate to review your TSQL application to determine if it relies on any of the few constructs not currently supported. The easiest way to validate this is by simply importing the TSQL schema and code on IRIS and compile it. The InterSystems IRIS TSQL compiler will flag any issues it finds in the imported code so they can be addressed, either by changing the original TSQL code to use a supported alternative, or by choosing to re-implement specific procedures in ObjectScript.
TSQL features not supported on InterSystems IRIS
InterSystems IRIS coverage of TSQL features differs depending on the dialect the code was originally developed for. Due to differences in how schema and aliases are handled, there are some limitations with certain specific handling of SQL aliases as well as schema naming. These can be circumvented by appropriately matching schema names in the DDL, and careful use of mixing table names and aliases in SQL queries.
Many table properties and other schema options are platform-specific. These may not make a difference in the behavior of the table from the user or application point of view. This includes many storage-related properties such as partitioning and compression clauses, but also orthogonal features such as encryption. InterSystems IRIS TSQL will silently accept and ignore many properties that have no impact on behavior, but will report a compilation error when they do.
Client access to TSQL code on InterSystems IRIS
InterSystems IRIS TSQL supports a command-line SQL Shell which you can use to issue TSQL commands and queries directly. You must initially set the dialect for the current SQL Shell session to the preferred value. For example, SET DIALECT = Sybase.
TSQL commands can also be issued from Java-based applications through the JDBC or ODBC driver by setting the dialect accordingly.
InterSystems SQL provides an even broader set of accessibility options when using InterSystems SQL and ObjectScript, including ultra-fast, native access to the underlying storage paradigm. For further details, refer to InterSystems IRIS documentation.
Reviewing your Data
InterSystems IRIS is a full multi-model database and supports a broad variety of data types. Most data types available in TSQL are also available on InterSystems IRIS and the Data Definition Language (DDL) statements to create tables will automatically map those types appropriately. InterSystems IRIS also supports both traditional row-oriented storage models, as are typical for Sybase ASE, Sybase ASA, and Microsfot SQL Server, and an analytics-focused columnar storage model, as is the case for Sybase IQ.
Project Planning
The following are important consideration when determining the scope of your migration to InterSystems IRIS:
-
Availability of TSQL expertise for a general understanding of the code.
-
Availability of the original TSQL application developers for functional questions.
-
Availability of unit tests or other test scripts to validate the migration.
-
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 and ObjectScript.
-
Letting TSQL and IRIS SQL systems run side-by-side, leveraging the benefits of each and keeping the data in sync. InterSystems can assist you in determining what to migrate based upon an agreed-upon level of performance and service.
Testing the Migration
Unit tests are an essential part of any application development project and crucial for testing application code changes as well as upgrades of the underlying infrastructure. As such, they are also extremely valuable when migrating an application to a new platform, such as InterSystems IRIS. For TSQL applications, unit tests written in TSQL can be migrated along with the TSQL application code. Unit tests driven from an external facility, such as Java-based application code or frameworks such as Jenkins, can also be reused as-is.
In testing the migration of TSQL code and data sets the following are important additional considerations:
-
Performance tests. Are application response times as good or better than the previous platform?
-
Scalability tests. Can I support larger user and/or data volumes compared to the previous platform, optionally engaging additional hardware resources?
-
Correctness tests. Some semantics may differ between InterSystems TSQL and the previous platform (as they do between different TSQL implementations), such as the resolution of outer joins in SQL queries offered by Sybase ASE. Correctness tests are particularly important to guarantee users observe the same behavior and results after the migration.
Executing the Plan
To migrate existing TSQL applications to InterSystems TSQL, you need to perform four operations: configure InterSystems IRIS for TSQL, migrate the TSQL source code, migrate the metadata (DDL), and migrate the data. InterSystems and its implementation partners are available to assist in each of these tasks.
Setting Up the System
Some of InterSystems IRIS default system settings differ from the default configuration of Sybase ASE and other TSQL platforms. InterSystems and professional service partners with TSQL migration experience can provide scripts to prepare a freshly installed InterSystems IRIS instance for use with TSQL applications.
To configure your system for TSQL manually, using the InterSystems IRIS configuration utilities:
-
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.
-
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, such as table names and procedure names.
-
Set Equal Literal Replacement to OFF. This is the appropriate setting for Sybase ASE.
-
Set the default collation sequence to %SQLSTRING. Collation options perform string conversions only for the purpose of index collation; the stored data is not changed. The %SQLSTRING option corresponds to the default binary collation on Sybase ASE. It is important to verify that this setting matches the Sybase sort order. This step must be done before loading any data.
-
Truncation settings may be required for Sybase implementations where there are trailing spaces in the database at the end of strings. Truncating these strings will allow for proper string matching. See Data Collation and String Truncation.
-
Consider using Posix time instead of the default timestamp to increase performance. See Timestamp and Time Precision.
-
Temporary tables are fully supported. Their settings should be optimized for speed if they are heavily used. See Settings for Temporary Databases.
-
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:
-
Import the DDL: Import table and view definitions using either the %SYSTEM.SQL.Schema.ImportDDL()Opens in a new tab method (for single files) or the $SYSTEM.SQL.Schema.ImportDDLDir()Opens in a new tab 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()Opens in a new tab or $SYSTEM.SQL.Schema.LoadMSSQLServer()Opens in a new tab 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.
-
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.
-
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")
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.
-
Use the Data Migration Wizard. 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.
-
Use a bulk loader. InterSystems has utilities that understand the various formats in which Sybase stores datetime fields and can normalize these into a standard internal format in InterSystems IRIS. Without these transformations, data load may experience issues with datetime fields, depending on how these are stored in Sybase.
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 by turning on the TSQL Trace facility. See TRACE. This produces a log that records a timestamp for each operation, the elapsed time for each operation, a global references count and a %ROWCOUNT. The log provides detailed information by TSQL statement in processed stored procedures.
Additionally, consider retaining cached query source code as well as the generated cached queries to provide detailed information on the compiled code. See Cached Query Source.
Writing and Executing TSQL on InterSystems IRIS
You can write and execute TSQL by using InterSystems SQL interfaces or by using an integrated development environment.
Working with TSQL via SQL Interfaces
As an alternative to developing TSQL code within class definitions, you can use Data Definition Language (DDL) to create, replace, or drop TSQL stored procedures. You can enter DDL commands using the following set of interfaces:
-
Using the TSQL Shell
The InterSystems TSQL Shell can be used to execute Transact-SQL code directly on InterSystems IRIS. To use the TSQL Shell, invoke the TSQLShell()Opens in a new tab (or $SYSTEM.SQL.Schema.LoadTSQL()Opens in a new tab) 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.
-
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.
-
Using the Management Portal SQL Interface
In the Management Portal SQL interface, the 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.
-
Using Dynamic SQL
InterSystems IRIS Dynamic SQL, a feature of ObjectScript, can be used to execute TSQL code queries and a limited subset of other DML and DDL statements from ObjectScript code.
-
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()Opens in a new tab method which prepares an SQL command, or the %SYSTEM.SQL.Execute()Opens in a new tab method, which both prepares and executes an SQL command. Both of these methods provide a Dialect parameter.
See Using Dynamic SQL.
-
Working with TSQL via Class Definitions
Via an IDE, you can define InterSystems class definitions, which can include methods or class queries that you can write in TSQL and then easily project as SQL stored procedures.
A class method takes parameters and returns a single scalar result. A class 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.
To create a TSQL stored procedure in an IDE, create a class method marked as a stored procedure using the SqlProc keyword 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 ]
{
}
See the Language, SqlProc, and SqlName keywords for method definition in the Class Definition Reference.
You can write and maintain triggers in TSQL. A trigger is a set of instructions that appear in TSQL code that are executed in response to a specified SQL event. 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.