VX/SQLMOD- SQLMOD to Embedded SQL

Description:

VX/SQLMOD is a powerful migration tool that converts Rdb SQLMOD procedures into embedded SQL in C using Oracle Pro*C or Postgres ECPG. This transformation allows existing VMS/OpenVMS applications to access Postgres or Oracle databases on Linux while maintaining the original business logic and structure. VX/SQLMOD supports both on-premises and cloud-based database migrations, providing cost savings and performance enhancements while ensuring compatibility with future database technologies.
‍

Why Migrate Rdb SQLMOD to Oracle or Postgres?

Eliminate Dependence on Rdb and Reduce Costs

• Postgres offers up to 80% cost savings for enterprises transitioning away from proprietary database solutions.

• By migrating Rdb to Oracle or Postgres, businesses can modernize their infrastructure while retaining full application functionality.

Seamless Integration with Existing VMS/OpenVMS Applications

• Applications using Rdb SQLMOD procedures can continue to run on VMS/OpenVMS or migrate to Linux without modifications.

• The converted SQLMOD procedures interact natively with Oracle and Postgres databases, enabling organizations to transition smoothly.

VX/SQLMOD Migration Process

Step-by-Step Conversion Workflow

1. Reads SQLMOD source code from Rdb applications.

2. Generates Pro*C or ECPG embedded SQL modules, maintaining functional equivalence.

3. Extracts and converts data type definitions from the VMS CDD libraries into equivalent C header files.

4. Modifies SQL calls to conform to ANSI SQL standards, ensuring compatibility with Oracle and Postgres.

5. Produces compiled C modules that integrate seamlessly with Fortran, COBOL, C, and C++.

6. Links the newly generated code with the original application, allowing for immediate execution.

File Conversion Breakdown

Input File VX/SQLMOD Output Files Description

Filename.sqlmod Filename.pc Pro*C module containing the converted SQL calls.

Filename.h C header file defining procedure prototypes.

Filename.inc Fortran/C header file for procedure definitions.

Filename.c Transformed SQLMOD logic in standard C.

Once processed, the .pc file runs through the Oracle Pro*C or Postgres ECPG pre-processor, compiling into C before linking into the main application.

Migrating Rdb SQLMOD to Oracle Pro*C

VX/SQLMOD generates fully maintainable Pro*C code, preserving the original SQLMOD’s logic and supporting dynamic SQL and multi-statement procedures.

Example: Converting SQLMOD to Pro*C

Original Rdb SQLMOD Code

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;

Converted Pro*C Code

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;
}

• The Pro*C version retains the original business logic while ensuring compatibility with Oracle databases.

• The generated Pro*C code can be compiled using Oracle’s Pro*C pre-compiler and linked into the VMS/OpenVMS or Linux application.

‍

Migrating Rdb SQLMOD to Postgres ECPG

For applications transitioning to Postgres, VX/SQLMOD generates maintainable ECPG embedded SQL in C, ensuring a smooth migration.

How VX/SQLMOD Handles Postgres Conversion

• Converts Rdb-specific SQL to ANSI-compliant Postgres SQL.

• Translates VMS data types to Postgres-compatible data structures.

• Supports dynamic SQL, multi-statement transactions, and cursor management.

Example: SQLMOD to ECPG Conversion

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 DECLARE cust_cursor CURSOR FOR
  SELECT cust_addr FROM customer_master
  WHERE cust_id = :local_cust_id;

  EXEC SQL OPEN cust_cursor;
  EXEC SQL FETCH cust_cursor INTO :local_cust_addr;
  EXEC SQL CLOSE cust_cursor;

  memcpy(cust_addr, local_cust_addr, sizeof(local_cust_addr));
  *sqlcode = sqlca.sqlcode;
}

• Uses ANSI SQL-compatible syntax for Postgres compatibility.

• Replaces Rdb’s LIMIT with a cursor-based approach.

• The resulting ECPG code is pre-processed using the Postgres ECPG compiler and linked into the final application.

Performance Enhancements in Oracle and Postgres

Bulk Fetching for Oracle Performance

Since Oracle databases are typically network-attached, VX/SQLMOD optimizes SQL calls by using bulk fetching, reducing the number of round trips between the application and the database server.

EXEC SQL FETCH 1000 ROWS FROM cust_cursor INTO :cust_data;

This method reduces latency and improves performance in high-volume transactional systems.

Optimized SQL Execution in Postgres

VX/SQLMOD ensures that SQL queries in Postgres use indexed lookups, optimizing database access patterns. The generated queries avoid costly operations such as function-based filtering within WHERE clauses, which can degrade performance.

Automated Testing and Validation

Unit Test Harness for Each SQLMOD Procedure

VX/SQLMOD automatically generates unit test drivers to validate the converted SQLMOD procedures.

• Ensures correct SQL execution after conversion.

• Allows for side-by-side testing of original Rdb SQLMOD vs. converted Oracle/Postgres procedures.

Example: Unit Test Driver

int main() {
  int sqlcode;
  int cust_id = 1001;
  char cust_addr[100];

  Get_Cust_Addr_In_Cust_Master(&sqlcode, &cust_id, cust_addr);

  printf("Customer Address: %s\n", cust_addr);
  return 0;
}

This allows automated regression testing, ensuring that the migrated SQLMOD procedures function identically in Oracle and Postgres.

Offloading Database Workloads from VMS/OpenVMS

Reducing CPU Load on VMS

VX/SQLMOD allows VMS/OpenVMS applications to execute SQL queries on remote Linux-based Postgres or Oracle databases. This reduces CPU cycles consumed by Rdb and extends the lifespan of legacy VMS systems.

• The application remains on VMS/OpenVMS, but database operations run on Linux.

• Frees up resources, allowing VMS systems to scale efficiently.

Conclusion

VX/SQLMOD enables a seamless transition from VMS/OpenVMS Rdb to Oracle or Postgres, ensuring that legacy applications can:

✅ Continue running without modifying business logic.

✅ Use modern, Oracle-supported Pro*C or Postgres ECPG SQL.

✅ Improve database performance with bulk fetching and optimized SQL execution.

✅ Reduce Rdb licensing costs while increasing database flexibility.

✅ Utilize automated unit testing for accurate validation.

For enterprises looking to future-proof their VMS/OpenVMS applications, VX/SQLMOD provides a fully automated migration solution, delivering modern database compatibility without disrupting existing workflows.