SQL CLR Stored Procedures

Issuing COBOL Calls from within SQL CLR Stored Procedures

When using Visual COBOL, SQL CLR stored procedures are comprised of at least two projects:

• One or more Visual Studio COBOL Library Project(s) that contain your COBOL code.
• One SQL CLR .Publish project from which to deploy your COBOL code for execution as a SQL Server stored procedure.

To successfully issue a COBOL call to a routine from within a SQL CLR stored procedure, do either of the following:

• Include and compile the called routines in the same Visual Studio COBOL Library Project as the stored procedure, which automatically includes it as part of the stored procedure's assembly. We highly recommend this approach as all CALLs by literal are resolved at compile time, and because all routines are contained in one assembly, all CALLs by data-name are executed properly.
• Include and compile the called routines in a Visual Studio COBOL Library Project or Projects distinct from the project that contains the stored procedure.

If you decide to use distinct projects for your stored procedure and called COBOL routines, please be aware of the following:

• To ensure successful deployment, you must also update the SQL CLR .Publish project with references to all Visual Studio COBOL Library Projects that contain called routines.
• In addition, we recommend that you create references from the project that contains the stored procedure to the projects that contain called routines. This ensures that all CALLs by literal become static, and can be successfully resolved at run time.

Optimizing Read-only Cursor Performance

To help you to optimize read-only cursor performance for SQL CLR stored procedures, you should understand how OpenESQL processes them. SQL CLR stored procedures cannot use the MARS (Multiple Active Result Sets) directive, requiring OpenESQL to provide a work-around for processing active results sets. The solution implemented depends on the version of Visual COBOL.

Starting with Enterprise Developer 2.1, Hot Fix 8, OpenESQL defaults to using the FAST FORWARD rather than DYNAMIC server cursor option for read-only, non-scrollable COBOL cursors in SQL CLR stored procedures. FAST FORWARD cursors typically generate better query access plans than dynamic cursors, but the query plan might not be quite as efficient as a firehose cursor.

In this scenario, you could avoid using a server cursor completely by switching to an OpenESQL DATASET cursor. This change isolates code change to the DECLARE CURSOR statement itself. A drawback to this approach, however, is that it buffers the entire result set in the memory used by the stored procedure. If the result set is large or the procedure is called concurrently from many clients, this can drain memory from SQL Server while the cursor is open. Also, while DATASET cursors are not restricted to read-only cursors, they cannot be locked, which means that pessimistic concurrency is not available (although positioned updates are still supported). This could cause the application logic to fail.

You can avoid code change completely by using the OPTION=OPTIMIZESPCURSORS directive or, for Enterprise Developer 2.2 Update 1 and later, the OPTIMIZESPCURSORS directive. In this case OpenESQL opens a firehose cursor. Should any other database access occur before the cursor is closed, OpenESQL converts the remainder of the result set to a DATASET cursor.

The following table summarizes your options: