VX/SQLMOD converts Rdb SQLMOD to Oracle pro*C SQLPC - OpenVMS Migration


VX/SQLMOD is a migration tool that will convert an SQLMOD used to access Rdb into an equivalent pro*C SQLPC that can used to access Oracle 12c or similar.

Learn More

INTRODUCTION

Application programs on Open VMS can access Rdb data by having the Rdb directives embedded in the application code or by using SQLMODs, which contain procedures that include Rdb directives. These procedures can be used by multiple programs.
VX/SQLMOD is a migration tool that takes in the source code for SQLMODs and produces something we call an SQL Procedure Container (SQLPC.) The various procedures in the original SQLMODs also exist in the SQLPC with the underlying Rdb directives within these procedures transformed into Oracle Pro*C directives.
Several features are included in the generated SQLPC modules to ensure the best performance from Oracle and to deal with the few Rdb datatypes that do not exist in Oracle or are different between Oracle and Rdb.

All this ensures that the application code calling the current Rdb procedures do not have to be altered to use the equivalent Oracle pro*C procedures and still achieve the best possible performance

 

SQLPC use Oracle Pro*C

The SQLPCs modules generated by VX/SQLMOD are coded in pro*C, the Oracle supported language for Oracle access. VX/SQLMOD reads the data libraries associated with the SQLMOD and generates the required C support files for the SQLPC module and its various procedures. VX/SQLMOD detects where data conversion needs to occur and generates the code for those conversions. The generated SQLPC pro*C code is highly maintainable are resembles closely the original SQLMOD. VX/SQLMOD modifies the various SQL calls so the current syntax is now Oracle-ready and conforms to the business logic of the original SQLMOD procedures. The generated SQLPC module is ready to go through the pro*C pre-compiler and then Gnu C compiler to become a compiled object that can be used with Fortran, COBOL, C and C++ application code.

 

Performance enhancements in Oracle

A big difference between Rdb and Oracle usage is that Oracle servers are usually network attached to application servers whereas VMS servers usually runs the Rdb server and the application server on the same server. This makes Rdb throughput faster that equivalent Oracle throughput, the difference between direct-attached storage and networked servers. VX/SQLMOD compensates for this by using features only available in Oracle to transfer large amounts of data over the network, yet requires no changes to the application code which will continue to interface to the SQLPC procedures exactly as it was interfacing with the original SQLMOD procedures. VX/SQLMOD detects the need for this bulk fetch technique and generates the requisite code in the SQLPC to make use of this feature.

 

Data Type Transformations

The SQLPC procedures are engineered in a way so specific Rdb datatypes are automatically transformed to and from Oracle data types. Examples of those include the transformation of Oracle date to/from VMS date and the use of Rdb tinyints and Oracle single character. By automatically doing these transformations in the SQLPC, the application code does not have to be modified in any way to accommodate these data storage differences and the code is immediately available for use.

Oracle DATE to/from MJD OpenVMS DATE
Tinyints to/from single CHARs

Sqlmod/CDD To Pro*C

The SQLPCs modules generated by VX/SQLMOD are coded in pro*C, the Oracle supported language for Oracle access.  VX/SQLMOD reads the CDD libraries associated with the SQLMOD and generates the required C support files for the SQLPC module and its various procedures.  VX/SQLMOD detects where data conversion needs to occur and generates the code for those conversions.  The generated SQLPC code is highly maintainable and closely resembles the original SQLMOD. VX/SQLMOD modifies the various SQL calls so the current syntax is now Oracle-ready and conforms to the business logic of the original SQLMOD procedures.  The generated SQLPC module is ready to go through pro*C and then the Gnu C compiler to become a compiled object that can be used with Fortran, COBOL, C and C++ application code. You may choose to support the SQLPC code after conversion or keep VX/SQLMOD integrated into your build procedures.

Function Calls & Exadata

Oracle running on Exadata has a data retrieval performance problem when the application is selecting rows using non-indexed columns and the selection argument is the product of a function call.  VX/SQLMOD will automatically generate code to circumvent this issue and the SELECT statement will not have an argument that includes a function.  This is done inside the procedure in the SQLPC and is thus hidden from the application source code and it executes silently to preserve the performance of Oracle on Exadata where indexed columns are rarely used.

SQLMOD

PROCEDURE Get_Cust_Addr_In_Cust_Master
  sqlcode
  :cust_id CUST_ID
  :cust_addr CUST_ADDR;

  SELECT cust_addr INTO :cust_addr from customer_master
  WHERE cust_id = :cust_id
  LIMIT to 1 row;

SQLPC

void Get_Cust_Addr_In_Cust_Master(
      int *sqlcode,
      int *cust_id,
      char *cust_addr )
{
local_cust_id = *cust_id;
memcpy( local_cust_addr, cust_addr, sizeof(local_cust_addr) );

EXEC SQL SELECT address INTO :local_cust_addr from customer_master
WHERE cust_id = :local_cust_id AND
rowid < = 1;
memcpy( cust_addr, local_cust_addr, sizeof(local_cust_addr) );
*sqlcode = sqlca.sqlcode;
}