T1.6: Deploys SQL Applications

Knowledge Review - InterSystems IRIS SQL Specialist

1. Understands SQL application deployment mechanisms

Key Points

  • LOAD SQL Command: Recommended method for importing DDL scripts from any SQL dialect
  • %SYSTEM.SQL.Schema Methods: ImportDDL(), Run(), and ExportDDL() for programmatic deployment
  • Multiple SQL Dialects Supported: IRIS, FDBMS, Informix, InterBase, MSSQLServer, MySQL, Oracle, Sybase
  • Deployment Approaches: DDL script files, embedded SQL compilation, dynamic SQL execution, ODBC/JDBC
  • Class Compilation: %SYSTEM.OBJ.Compile() for deploying persistent class definitions as SQL tables

Detailed Notes

Overview

InterSystems IRIS provides multiple mechanisms for deploying SQL applications, enabling seamless migration from other database platforms and flexible deployment strategies.

LOAD SQL Command

The LOAD SQL command is the primary recommended approach for importing schema definitions and table structures.

  • Source: Text files (single files or entire directories)
  • Dialect support: InterSystems IRIS SQL and external vendor SQL dialects
  • Automatic translation: Converts vendor-specific SQL into IRIS-compatible syntax

Programmatic Deployment Methods

The %SYSTEM.SQL.Schema class offers:

  • ImportDDL(): Batch operations for importing DDL
  • Run(): Interactive deployments
  • Error logging: Both methods generate error logs for troubleshooting failed statements

Supported SQL Dialects

InterSystems IRIS supports importing SQL from major database vendors:

  • Oracle, MySQL, SQL Server (MSSQL), Sybase, Informix, InterBase, FDBMS
  • Conversion: Automatic conversion of compatible commands and clauses

DDL Deployment Methods

Execute CREATE TABLE, CREATE INDEX, CREATE VIEW, and other schema definition statements through:

  • Embedded SQL
  • Dynamic SQL
  • DDL script files
  • JDBC/ODBC connections

Object-Oriented Deployment

For object-oriented applications:

  • %SYSTEM.OBJ.Compile(): Deploys persistent classes
  • Automatic generation: Creates corresponding SQL tables, views, and stored procedures

Deployment Characteristics

  • Syntax validation: At compilation time
  • Entity existence checking: Deferred until runtime execution (allows flexible deployment sequencing)
  • Target: Current namespace
  • Character encoding: Options to specify via I/O translation tables for international data sets

Documentation References

2. Identifies deployment requirements and dependencies

Key Points

  • Schema Dependencies: Tables must exist before views, indexes, triggers, and foreign keys referencing them
  • Security Requirements: Users need DDL privileges (%DB_DBNAME role) for CREATE/ALTER/DROP operations
  • Class Dependencies: Compiler automatically resolves and compiles dependent classes in correct order
  • File Format Requirements: SQL scripts require delimiters (GO for IRIS, semicolon for others) between statements
  • Namespace Context: All deployments target current namespace; requires proper namespace configuration

Detailed Notes

Overview

Successful SQL application deployment requires careful attention to dependencies, security privileges, and environmental prerequisites.

Schema Object Dependencies

Schema object dependencies must be deployed in proper sequence:

  • Base tables before dependent views
  • Parent tables before child tables with foreign key constraints
  • Tables before indexes and triggers defined on them
  • Stored procedures before code that calls them

Class Compiler Dependency Management

The class compiler automatically manages dependencies when deploying persistent classes:

  • Generates a dependency list
  • Compiles prerequisite classes first
  • Ensures inherited properties and methods are available before compilation

Security Requirements

DDL operations require specific privileges:

  • Privileges: Typically granted through the %DB_DBNAME role for the target database
  • SQLCODE -99: Privilege Violation error indicates insufficient permissions
  • Authentication: Use $SYSTEM.Security.Login() for proper authentication

SQL Script File Format Requirements

SQL script files must conform to specific format requirements:

  • File type: Unformatted text files (.txt or .sql extensions)
  • Statement placement: Each SQL statement beginning on its own line
  • Delimiters: GO for InterSystems SQL, semicolon for most vendor formats
  • Formatting: Optional multi-line formatting with indentation permitted

Namespace Configuration

Namespace configuration is a key dependency:

  • All import operations execute within current namespace context
  • Requires proper namespace selection before deployment
  • Appropriate database mappings needed for package distribution across namespaces

Character Encoding

Character encoding requirements may necessitate I/O translation table specifications:

  • Required when importing SQL scripts from systems using different character sets
  • Important for international deployments with Unicode or multi-byte character data

Error Logging

The deployment process generates error logs documenting failed statements:

  • LOAD SQL: Uses %SQL_Diag.Result
  • ImportDDL(): Uses custom error log files
  • Information captured: Timestamps, user names, namespace names, detailed error messages

Documentation References

Exam Preparation Summary

Critical Concepts to Master:

  1. Deployment Methods: Know LOAD SQL, ImportDDL(), Run(), ExportDDL(), and %SYSTEM.OBJ.Compile()
  2. SQL Dialect Support: Understand vendor-specific import capabilities and conversion limitations
  3. Dependency Management: Recognize proper deployment sequencing for schema objects
  4. Security Requirements: Identify privileges needed for DDL operations and authentication methods
  5. File Format Rules: Know delimiter requirements and formatting conventions for SQL scripts

Common Exam Scenarios:

  • Selecting appropriate deployment method for given migration scenarios
  • Identifying missing dependencies causing deployment failures
  • Troubleshooting privilege violations during DDL execution
  • Determining correct import method for vendor-specific SQL scripts
  • Sequencing deployment of tables, views, indexes, and triggers

Hands-On Practice Recommendations:

  • Import SQL scripts using LOAD SQL command with various dialects
  • Deploy applications using ImportDDL() and examine error logs
  • Practice %SYSTEM.OBJ.Compile() for persistent class deployment
  • Test deployment with insufficient privileges to understand SQLCODE -99
  • Export DDL using ExportDDL() and re-import to different namespace
  • Create deployment scripts with proper dependency ordering

Report an Issue