raincode rect

Home

Stored Procedures

Version 6.0.136.0

Raincode Crossbow

Raincode Crossbow is a consistent release of the entire Raincode product line, covering compilers, emulators and ancillary tools.

In practice, Raincode Crossbow is designed with three driving forces:

  • A focus on performance across the board:

    • The internal computation engine for non-binary numeric data types (display numeric or packed decimals) has been entirely overhauled, resulting in performance improvements that can go up to a factor of 5 (depending on the level of dependence on these non-binary data types)

    • The file I/O layer has been optimized to ensure the best possible performance when dealing with indexed or sequential files

    • RadaR is a revolutionary solution to run batch steps 5 to 10 times faster, without changing the application source code or even the JCL they are called from

    • The Visual Studio plugin now runs the heaviest debugging operations asynchronously, thereby dramatically improving response time and the overall user experience, even when debugging programs with hundreds of active variables

    • The views generated to access VSAMSql and IMSql data using plain SQL DML statements have been optimized dramatically

  • Updated .NET platform support running both on Linux and on Windows, on virtual machines or in containers, on the cloud, on laptops or servers managed on premise.

  • Consolidation of the product suite, allowing all Raincode software to be built and upgraded synchronously, thereby avoiding the headaches that come from multiple products that follow different (and sometimes, incompatible) release cycles.

Version 6.0

In addition to the above description, Raincode Crossbow v6.0 (see release notes) is a technical release that includes the following:

  • Support for .NET 10.0 and SQL Server 2025 across the board.

  • The Visual Studio plugin now supports Visual Studio 2022 and 2026, with improved support for JCL debugging.

  • Support for the File-AID JCL utility.

  • Support for TLS encryption on QIX and IMS TN3270 terminal servers.

  • Removal of support for .NET Framework and .NET 6.0.

  • Removal of support for Microsoft Host Integration Server (HIS) for Db2.

  • Removal of 32-bit support.

1. Introduction

The Raincode Stored Procedure Runner allows running a Raincode COBOL stored procedure on SQL Server. This feature can be used when migrating a Db2® stored procedure written in COBOL to SQL Server without rewriting it to T-SQL.

To achieve this, the stored procedure runner relies on .NET C# language extensions and the Raincode Legacy compilers.

This document will guide us through all the necessary steps required to install the Raincode Stored Procedure Runner and deploy a COBOL stored procedure.

2. Installation

The Raincode Stored Procedure Runner is part of the Raincode Legacy Compilers package, but additional setup steps are required before using it:

  • The RAINCODE_STORED_PROCEDURE external language must be created

  • StoredProcedureRunner.dll must be installed

  • Appropriate .dll files must be copied to your SQL Server installation directory

The setup process has been automated as much as possible, but still requires a few manual steps. Once installation is complete, a sample program will run to verify that everything was installed correctly

2.1. Prerequisites

The prerequisites are the same as for RadaR. On the SQL Server that hosts the database, the following prerequisites must be met:

  • Windows OS and PowerShell.

  • SQL Server 2019 CU3+ Database Engine instance with the extensibility framework (Machine learning Services and Language Extensions).

  • In the SQL Server network configuration, have named pipes, shared memory and TCP/IP enabled. Note that this is not the default configuration.

  • The SQL Server Launchpad service is up and running. Note that this is not the default configuration.

  • An installation of the Raincode runtime on the SQL Server machine.

  • sqlserver module for PowerShell (Install-Module sqlserver)

2.2. Steps to install

Follow these steps on the machine where SQL Server is running.

  1. Start by editing file $env:RCDIR\scripts\StoredProcedureRunner\config.xml. This is the configuration file where the Raincode Stored Procedure runner looks for COBOL programs, helpers files, and the database connection string. For installation and validation purposes, you only need to set the ProgramsPath and HelpersPath values (the test program does not require a database connection).

  2. Next, as an administrator, run script $env:RCDIR\scripts\StoredProcedureRunner\install.ps1 with the following argument:

    • ConnectionString: (mandatory) the SQL Server connection string that the Stored Procedure Runner will use when executing COBOL stored procedures.

This script will do the following:

  • Grant read permissions to ALL APPLICATION PACKAGES on the SQL Server installation directory (required to run an external language)

  • Grant read permissions to ALL APPLICATION PACKAGES and NT Service\MSSQLLaunchpad to all necessary directories (including ProgramsPath and HelpersPath in the configuration file)

  • Configure the database:

    • Create the RAINCODE_STORED_PROCEDURE external language

    • Install the RainCodeLegacyStoredProcedureRunner external library, which serves as the entry point for running a COBOL stored procedure in SQL Server.

  • Copy all required .dll files to the ExternalLibraries directory of SQL Server, under the appropriate external language ID.

  • Fill attribute SQLServerExternalLibrariesPath in the configuration file.

  • Run a test to ensure that everything is installed successfully.

Needless to say, no error should occur. After a successful installation, your terminal should display a message similar to the one shown below, confirming that everything was installed and configured correctly.

VERBOSE: STDOUT message(s) from external script:
The Raincode Stored Procedure runner is working.

Done.
install.ps1 should be run every time a new version of Raincode Crossbow is deployed.

2.3. Configuration file

The configuration file of the Raincode Stored Procedure Runner is located at $env:RCDIR\scripts\StoredProcedureRunner\config.xml. An empty configuration is provided by default. This path can be overridden by passing :StoredProcedureConfigurationPath to the compiler. Note that this value will be resolved at runtime when the stored procedure is run, the configuration file is not used by the compiler itself, so it does not need to exist at compile time.

  • ProgramsPath is the absolute path to the location of the compiled COBOL programs (including subprograms)

  • HelpersPath is the absolute path to the location of the compiled helper programs

  • SqlRuntime is the name of the SQL runtime to use. Possible values are the same as for the SQL command line option of rclrun

  • SqlRuntimeConnectionString is the connection string that will be passed to the specified SQL runtime

  • LogLevel is the log level to pass to running programs. Possible values are the same as for rclrun. Default is WARNING

  • PluginsPaths is the semi-colon separated list of paths where the Stored Procedure Runner will load plugins from

  • Plugins is the semi-colon separated list of plugins to load prior to execution

  • SQLServerExternalLibrariesPath is the path to the ExternalLibraries directory of the RAINCODE_STORED_PROCEDURE external language. This value is automatically filled by the install.ps1 script

Set values appropriate to your environment, and make sure that ALL APPLICATION PACKAGES have read and execute permissions on these directories (this is done by the install.ps1 script).

Whenever the configuration is updated with new or different paths, permissions mut be set appropriately on all directories used by the Stored Procedure Runner. This can be done by running script set_permissions.ps1 as an administrator, passing the configuration file like so:

& $env:RCDIR/Scripts/StoredProcedureRunner/set_permissions.ps1 -ConfigurationFilePath "path to the configuration file"
It is not necessary to call set_permissions.ps1 after running install.ps1.
You can specify environment variables in all values, the stored procedure runner will expand them upon reading the configuration file.

3. Deploying a COBOL stored procedure

Deploying a COBOL stored procedure requires multiple steps:

  1. Compile the COBOL program.

  2. Generate a T-SQL file to create the stored procedure in SQL Server (which is a bridge to calling the Raincode Stored Procedure Runner).

  3. Generate and compile a helper file, necessary to marshal parameters to and from the COBOL program.

    This step is optional if your stored procedure has no parameters.

  4. Run a helper script that deploys everything.

3.1. Compile the program

Start by compiling COBOL program using the Raincode Legacy Compilers. This can be achieved using Raincode Language Service Visual Studio Extension. This produces a .dll.

Either copy this file to the directory specified by the ProgramsPath in the configuration file, or run the deployment script.

Make sure that ALL APPLICATION PACKAGES have the correct permissions to read from that directory.

3.2. Generate stored procedure file and C# helper

Compile your program with both :StoredProcedureName and :StoredProcedureParamsMode. Optionally pass :StoredProcedureConfigurationPath to specify the configuration file to be used by the generated stored procedure. If :StoredProcedureConfigurationPath is not specified, the stored procedure runner will load the configuration at the default location (see the configuration file section).

The code samples featured here have been generated with the following COBOL LINKAGE SECTION and USING clause on the PROCEDURE DIVISION and compiler options:

       LINKAGE SECTION. 
       01 LS-INPUT PIC 9(9) BINARY. 
       01 LS-OUTPUT PIC S9(9) BINARY. 
       PROCEDURE DIVISION USING LS-INPUT LS-OUTPUT.

:StoredProcedureName=COB_STORED_PROC :StoredProcedureParamsMode=IN,OUT.

  • :StoredProcedureName provides the name of the stored procedure.

  • :StoredProcedureParamsMode gives, for each parameter, whether it should be treated as input, output, or input-output. This option is required because it is not possible to know whether a parameter is input or output just by looking at the COBOL program. See Producing SQL Server stored procedures for more details.

This does not compile your program, you need to separately invoke the compiler without specifying these parameters to generate a .dll.

This process generates two files:

  1. StoredProcedureName.sql: a T-SQL file to be fed to your instance of SQL Server. It creates a stored procedure with the name provided by :StoredProcedureName and uses Language Extensions to call the Raincode Stored Procedure Runner. Such a sql script looks like this (assuming the program is named COB_PROGRAM):

    CREATE PROCEDURE [COB_STORED_PROC]
(@P0 INT, @P1 INT OUTPUT)
AS
BEGIN
    EXEC sp_execute_external_script
        @language = N'RAINCODE_COBOL',
        @script = N'RainCodeLegacyStoredProcedureRunner.dll;RainCodeLegacyStoredProcedureRunner.StoredProcedureRunner',
        @input_data_1 = N'SELECT null',
        @params = N'@rc_progname CHAR(50), @rc_param_0 INT, @rc_param_1 INT OUTPUT',
        @rc_progname = 'COB_PROGRAM',
        @rc_param_0 = @P0,
        @rc_param_1 = @P1 OUTPUT
;
END
GO

  2. COB_PROGRAM_Helper.cs: a C# file that must be compiled to a .dll. This program is responsible for building the parameter list based on the parameters of the program. By default, the name of the file bears the name of the COBOL file with the _Helper suffix. This can be changed with compiler options :ModuleName=ANOTHERNAME or :UseFilenameAsModuleName=FALSE. The _Helper suffix cannot be modified.

            public CallParameters BuildParameterList(Dictionary<string, dynamic> rcParams)
        {
            var callParameters = CallParameters.AllocateParameterList(TheExecutionContext, rcParams.Count);
            callParameters.CallingConvention = CallingConvention.Cobol;

            arg0 = ParamHelper0.Allocate(TheExecutionContext);
            arg0.Value = (uint)rcParams["@rc_param_0"];
            callParameters.SetParameterAddress(TheExecutionContext, 0, arg0.Raw.Value);

            arg1 = ParamHelper1.Allocate(TheExecutionContext);
            arg1.Value = (int)rcParams["@rc_param_1"];
            callParameters.SetParameterAddress(TheExecutionContext, 1, arg1.Raw.Value);

            return callParameters;
        }

    It also reads back the output parameters to pass them back to SQL Server Language Extensions.

            public void TransferOutputParameters(Dictionary<string, dynamic> rcParams)
        {
            rcParams["@rc_param_1"] = arg1.Value;
        }

    This program relies on helper classes to achieve this (see section Passing parameters between legacy programs and .NET code)

3.3. Compile the C# helper

The generated C# helper must be compiled to a .dll that can be loaded by the Raincode Stored Procedure Runner. To help achieve this, a sample Visual Studio C# project HelperSample.csproj is provided in $env:RCDIR\Scripts\StoredProcedureRunner\net10.0.

Add the generated C# helper file to the project, then either rename the project to the same name as the C# file (COB_PROGRAM_Helper for example), or set the <AssemblyName>COB_PROGRAM_Helper</AssemblyName> property in the .csproj file to match the name of the C# file. The .dll produced when building the project should have the same name as the cobol program, suffixed with _Helper. Failure to do so will prevent the stored procedure runner from finding the helper .dll.

After building the project, copy the resulting .dll into the directory specified under HelpersPath in the configuration file, or run the deployment script.

3.4. Run the deployment script

Feed the sql file to SQL Server using the script install_storedproc.ps1. It takes the following parameters:

  • ConnectionString (mandatory): the connection string to the SQL Server instance where you want to create the stored procedure.

  • SqlFilePath (mandatory): the path to the sql file generated by the Raincode Legacy compilers (.sql).

  • ProgramDllPath (mandatory): the path to the compiled program (.dll).

  • HelperDllPath (optional): the path to the compiled helper program (.dll).