DSN Bind Utility

Important: Starting with Enterprise Server 2.2 Update 1 and continuing with each subsequent release, run the DSN Bind utility against all of your existing SQL Server databases. This ensures that all mainframe artifacts stored in the database are updated appropriately with each software release.

Using the DSN Bind utility, you can bind your database to your SQL Server application from the command line

The DSN Bind utility requires that you have an active connection to your SQL Server database, and that you provide a bind script file (.hcodsn file) containing the DSN commands you want to execute. You can create the bind script file using the Manage Packages and Plans tool, or create it manually in a text editor.

Note: For a listing of commands that are valid in HCOSS bind script files, see the topic Bind Script Commands.

The DSN Bind utility executes in two sequential phases each time you run it - the Schema Update execution phase followed by the Bind Script execution phase.

Schema Update Execution

In this phase of execution, DSN checks the SQL Server database to which you are connected to ensure that the tables and stored procedures it requires are present in the database, and that the table and function definitions are consistent with the version of HCOSS in use. If the checks return successfully, DSN moves on to the Bind Script Execution phase. However, if the checks fail, DSN performs a schema update before moving on.

DSN database checks fail, triggering a schema update, only when either of the following is true:

You run DSN against a new SQL Server database
You run DSN for the first time after installing an HCOSS upgrade
DSN determines that necessary tables and/or stored procedures are missing from the database

To perform a schema update, DSN executes the following SQL scripts against your SQL Server database:

InstallSYSIBM.sql - creates metadata tables that DSN uses to manage bound DBRMs.
InstallBindProcs.sql - creates stored procedures that DSN uses to manage these tables.
InstallDigitsFunction.sql - creates the following functions to emulate DB2 functions not provided by SQL Server:
- DATEINTERVAL
- DIGITS
- JULIANDAY
- LASTDAY
- LPAD
- MIDNIGHT_SECONDS
- NEXTDAY
- RPAD
- STRIP
- TIMEINTERVAL
- TRANSLATE
- TRUNCATE

These SQL scripts reside in your %ALLUSERSPROFILE%\Micro Focus\Enterprise Developer\hcoss directory.

To ensure successful completion of the schema update process, connect to your SQL Server database using login credentials that include the following permissions:

CREATE SCHEMA
CREATE TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
CREATE PROCEDURE in the SYSIBM schema
DROP and CREATE FUNCTION in the dbo schema

Important: This set of required permissions provides broader access to the SQL Server database than is necessary for the checking and binding done by DSN on a regular basis, that is, when database checks do not trigger the schema update process. If you routinely bind your applications by executing DSN from the command line, and you prefer to restrict permissions for day-to-day use, you can avoid possible errors and prevent DSN from triggering an automatic schema update by completing the process manually immediately after creating a new SQL Server database and after each HCOSS upgrade. To perform a manual schema update, connect to your SQL Server database using login credentials that grant the required permissions, and then run the SQL scripts manually.

Permissions required to run database checking are the same as those required for bind script execution.

Bind Script Execution

In the Bind Script Execution phase, DSN:

Generates stored procedures that bind packages and plans, maintaining them as metadata in the following SQL Server database tables:
```
SYSIBM.SYSDBRM
SYSIBM.SYSPACKAGE
SYSIBM.SYSPACKLIST
SYSIBM.SYSPLAN
```
For each DBRM package specified in a BIND PACKAGE command, generates a separate stored procedure for each static SQL statement, saving them to your SQL Server database using the following naming convention:
PKG:collectionName.memberName consistencyToken$statementNumber

Where consistencyToken is a DSN-generated string that provides a unique name for the stored procedure, and statementNumber is a DSN-generated number starting at 0. For example:

PKG:COLLECTION1.MEMBERAEEEcKIKy$0
For each plan specified in a BIND PLAN command, generates stored procedures for specified packages/members, saving them to your SQL Server database using a similar naming convention:
PLN:planNameconsistencyToken$statementNumber

For example:

PLN:MYPLAN1.MEMBERAFFFdLJLz$0

To ensure successful completion of the Bind Script Execution phase, connect to your SQL Server database using login credentials that include the following permissions:

CREATE SCHEMA
CREATE PROCEDURE
EXECUTE PROCEDURE in the SYSIBM schema