Rdb SQLMOD to embedded SQL for Postgres ECPG and Oracle Pro*C for Migration to x86 Intel Linux OR Client Server on VMS / OpenVMS
Migrate VMS / OpenVMS Rdb to Oracle Pro*C and Postgres ECPG
Postgres offers up to 80% savings for clients seeking to reduce RDBMS costs, while at the same time opening the door to powerful and innovative Open Source database solutions. Existing VMS / OpenVMS applications do not require modification, and can remain on VMS / OpenVMS, can migrate to VMS / OpenVMS on x86 (when available) or can even fully migrate to Linux using Sector7’s migration tools and services.
The new Postgres or Oracle database resides on Linux, now more accessible than ever before to your non-VMS enterprise applications, including cloud and mobile.
Speed up VMS / OpenVMS Rdb by off-loading Rdb to remote Oracle or Postgres server
VX/SQLMOD allows you to free up resources on VMS / OpenVMS by off loading your applications Rdb access to a remote Oracle or Postgres server. In addition the Rdb database once in an open database form can be shared with the rest of your organization.
Automatically generate unit test harnesses for each converted SQLMOD
VX/SQLMOD can generate unit test harnesses each time a SQLMOD is migrated to Oracle Pro*C or Postgres ECPG. These test harnesses allow you to test the functionality in a stand alone environment.
Oracle Rdb to Postgres and Oracle Database Transition and Migration
Converting and Migrating VMS / OpenVMS Rdb to Oracle Pro*C or Postgres SCPG
Application programs on VMS / OpenVMS can access Rdb data by having Rdb directives embedded in the application code or by calling procedures written in Rdb SQL Module Language (SQLMOD).
These SQLMOD's contain the code which accesses the Rdb database.. They can be – and usually are – called by multiple programs and multiple programming languages
.
Sector7 VX/SQLCC is a migration tool that reads SQLMOD source code and produces native natural Embedded SQL in C for either Postgres or Oracle databases.
This code can then be pre-processed using the Postgres or Oracle Embedded SQL pre-processors (ecpg or Pro*C) and then compiling using the standard C/C++ compiler and linked to executable… either on VMS / OpenVMS or Linux.
This results in VMS / OpenVMS applications that can remain on VMS / OpenVMS but utilizing the Postgres or Oracle RDBMS instead of Rdb.
The database engine – Postgres or Oracle RDBMS – runs on Linux. Your VMS / OpenVMS applications that have been forever locked to Rdb are now unlocked to run on Postgres or ‘regular’ Oracle.
Your VMS / OpenVMS applications that have been stranded on VMS / OpenVMS on Titanium (or VAX or Alpha) are now able to run on VMS / OpenVMS for x86 (when available) or Linux (using other Sector7 migration tools).
Converting Rdb SQLMOD to Oracle Pro*C
The Embedded SQL modules generated by Sector7 VX/SQLMOD for Oracle are coded in Pro*C, the language pre-processor for Embedded SQL in C for Oracle.
Sector7 VX/SQLMOD reads the VMS / OpenVMS data libraries (CDD) associated with the SQLMOD and Rdb schema and generates the required C/C++ ‘include files’ for the Pro*C modules. Sector7 VX/SQLCC detects where data type conversion is needed and generates the code for those conversions.
The resultant Pro*C code is fully maintainable and matches the original SQLMOD, maintaining the business logic of the original code while being fully Oracle compatible and conformant. Rdb dynamic SQL and multi-statement procedures (compound statements) are fully supported.
The Pro*C code can remain on VMS / OpenVMS or move to Linux where in either case it is pre-compiled to C/C++ code with Oracle Pro*C, and then compiled with the normal C/C++ compiler to become a compiled object that can be used with Fortran, COBOL, C and C++ application code.
SQLMOD 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 run the Rdb database and the application layer on the same server.
This makes Rdb throughput faster that equivalent Oracle throughput, the difference between direct-attached storage and networked servers.
Sector7 VX/SQLMOD compensates for this by using features only available in Oracle to ‘bulk fetch’ data over the network, while needing no changes to the application code. Applications connect to the Pro*C procedures in exactly the same way as to the original SQLMOD procedures, and Sector7 VX/SQLMOD detects the need for bulk fetching and generates the required Pro*C code to utilize this feature.
VX/SQLMOD Rdb SQLMOD to Postgres ECPG
The Embedded SQL modules generated by Sector7 VX/SQLMOD for Postgres are coded in ecpg; the language pre-processor for Embedded SQL in C for Postgres.
Sector7 VX/SQLMOD reads the VMS / OpenVMS data libraries (CDD) associated with the SQLMOD and Rdb schema, and generates the required C/C++ ‘include files’ for the ecpg modules.
Sector7 VX/SQLCC detects where data type conversion is needed and generates the code for those conversions.
The resultant ecpg code is fully maintainable, and matches the original SQLMOD, maintaining the business logic of the original code while being fully Postgres compatible and conformant.
Rdb dynamic SQL and multi-statement procedures (compound statements) are fully supported.
Language elements unique to Rdb and Oracle are converted to ANSI-compliant SQL syntax
The ecpg code can remain on VMS / OpenVMS or move to Linux where in either case it is pre-compiled to C/C++ code with Postgres ecpg, and then compiled with the normal C/C++ compiler to become a compiled object that can be used with Fortran, COBOL, C and C++ application code.
VX/SQLMOD generates drivers for unit testing
Application testing is a large part of any conversion or migration project, none more so when changing an application’s underlying database technology and the operating system on which the database runs.
A typical VMS / OpenVMS Rdb application includes 100’s of SQLMOD modules and 1000’s of procedures. Some of the code might be executed only after a series of application steps have executed, which means errors might only be discovered through complex interactive scenario testing.
However, Sector7 VX/SQLMOD processes all this SQLMOD code and the associated CDD definitions, and knows the input and output parameters and types of every procedure in every module. Using this information, it automatically creates companion unit-test “drivers” for each and every SQLMOD module.
The driver is a stand-alone program that compiles to executable and encapsulates a converted SQLMOD procedure. Drivers make it very easy to isolate any SQLMOD procedure and execute it with specific parameters against the new Postgres or Oracle database.
Results can be captured and compared against known-good results, making testing recordable, repeatable and reliable. The drivers can even be compiled and linked with the original SQLMOD code and Rdb database on VMS / OpenVMS, ensuring that tests can be run side-by-side in the VMS / OpenVMS/Rdb and Linux/Postgres or Oracle environments for automated regression testing
SQL Code Quality Assurance
Sector7 VX/SQLMOD generates Embedded SQL in C code from the Rdb SQLMOD sources.
In an Oracle environment, the generated Pro*C code is run through the Oracle Pro*C pre-compiler with full semantics checking enabled which validates all SQL statements against the new Oracle database schema.
The Postgres ecpg pre-processor does not have semantics checking, and so Sector7 VX/SQLMOD generates an accompanying file that contains all the SQL statements that it has converted to Postgres / ANSI syntax.
This file is then run though a semantics checker that approximates the level of checking done by Oracle’s Pro*C pre-processor in semantics mode.
For both Oracle and Postgres databases, this level of SQL semantics checking ensures that all SQL statements are valid and will be executed by the database server.
Off-loading cycles from VMS / OpenVMS using remote Linux DB server
VX/SQLMOD generates Pro*C and ECPG code that can be copied to Linux and VMS / OpenVMS for processing with the Oracle Pro*C or Postgres ecpg pre-compilers, which in turn generates native C code that is compiled, linked and executed on Linux or VMS / OpenVMS.
The unit-test driver programs can also be compiled and run on Linux or VMS / OpenVMS.
An application whose SQLMOD code is converted with Sector7 VX/SQLCC can run on Linux or VMS / OpenVMS and access Oracle or Postgres database servers running on another server.
When the Oracle or Postgres client is run on VMS / OpenVMS and the database server run on Linux, Windows or Linux servers,
a significant number of CPU cycles previously allocated to Rdb can be released to VMS / OpenVMS thus extending the headroom of your VMS / OpenVMS system.
CDD To Postgres ecpg or Oracle Pro*C
The SQL PC 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.
Migrating Rdb to Oracle on 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.
Original VMS / OpenVMS Rdb SQLMOD
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;
SQLMOD Converted into oracle/Pro*C
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;
}