Making the leap from VSAM to SQL Server

Virtual Storage Access Method (VSAM) is an IBM DASD file storage access method, which was first used in the OS/VS1, OS/VS2 release 1 (SVS) and release 2 (MVS) operating systems. It was later used throughout the Multiple Virtual Storage (MVS) architecture and is now used in the z/OS.

The term VSAM applies to both a data set type and the access method used to manage various user data types.  VSAM can be used to organize records into four types of data sets: key-sequenced, entry-sequenced, linear, or relative record.

VSAM can be found on almost every mainframe out there, and yet, in some cases it is hardly used.  In other cases, there are full featured mission-critical applications based on VSAM.

As organizations are under increasing pressure to modernize their legacy environments, there’s a powerful case to be made for moving your data from VSAM to SQL Server.

When Microsoft SQL Server became generally available in June 2016, it was developed as part of a “mobile first, cloud first” technology strategy adopted by Microsoft. Among other things, SQL Server 2016 added new features for performance tuning, real-time operational analytics, and data visualization and reporting on mobile devices, plus hybrid cloud support that lets DBAs run databases on a combination of on-premises systems and public cloud services to reduce IT costs.

In contrast to this modern platform, the challenges associated with retaining VSAM are pretty easily understood.  While businesses want to leverage the data housed within VSAM, it’s not really a database. It’s more of a file management system. Loosely speaking, it is ‘closer to the disk’ than the DBMS is. In fact, the DBMS is typically built on top of some kind of file manager. Thus, the user of a file management systems will be able to create and destroy stored files and perform simple retrieval and update operations on stored records in such files.

In contrast to the DBMS, file managers:

  • Are not aware of the internal structure of the stored records, and hence cannot handle requests that rely on a knowledge of that structure (such as “’find all employees with salary less than $50,000’).
  • Provide little or no support for security and integrity rules
  • Provide little or no support for recovery and concurrency controls
  • Have no true data dictionary concept
  • Allow for far less data independence compared to the DBMS

Use Cases that Drive VSAM to SQL Server Modernization

Many organizations look to modernize VSAM to SQL Server to take advantage of its many benefits. SQL Server has the ability to efficiently manage many concurrent users, very large databases, and high transaction rates on multiple platforms.

Additionally, VSAM provides a number of data set types or data organization schemes, including:

  • Key-sequenced data set (KSDS)
  • Entry-sequenced data set (ESDS)
  • Relative record data set (RRDS)
  • Variable-length relative record data set (VRRDS)
  • Linear data set (LDS)

The top business drivers for organizations looking to evaluate the transition from VSAM to SQL Server typically include the following:

  • Improve data management: The interaction between batch, online and other data exchange activities and queries is much more efficient- so performance is improved and deeper data analysis can be performed.
  • Reduce cost: Proprietary language licenses and maintenance are typically expensive. Further development of such applications can be time consuming, restrictive and expensive.
  • Diminishing resources: It is increasingly difficult to find qualified staff with the ability to administer, maintain or further develop nonrelational/hierarchical data tiers.
  • Lack of availability: The emergence of relational databases has left customers with little to no availability of mainstream hierarchical database systems and acceptable support models.
  • Take advantage of hybrid cloud support that lets DBAs run databases on a combination of on-premises systems and public cloud services to reduce IT costs.

The top technical issues driving VSAM to SQL Server transition include the following:

  • SQL Server provides high levels of scalability. VSAM is tightly coupled with the mainframe and hence has a restricted choice of platform.
  • SQL Server provides a high degree of security in the sense that the unit of data that can be individually protected ranges all the way from an entire table to a specific data value at a specific row-and-column position. In VSAM the security options are fairly limited and can be only at Dataset level.
  • SQL Server has support of a rich suite of tools/products for the whole range of activities like administration, management, data manipulation, data replication, data warehousing, performance monitoring, archival and report generation. Performance of VSAM applications is heavily dependent on the initial design and there is very little scope of tuning later.
  • SQL Server is web-enabled with built in Java support. SQL Server data can be accessed from various systems using standard TCP/IP, ODBC, X/Open CLI, JDBC and SQLJ.
  • SQL Server supports disaster recovery. A disaster recovery copy of data can be easily identified by SQL Server. There is no separate disaster recovery mechanism for VSAM.

VSAM to SQL Server Conversion Solution

Modern Systems’ VSAM conversion process is fully automated, and our strategy gives customers a whole new generation of relational databases to replace the functionality, redefinitions, type-of-record indicators, and other data element structures that are currently part of most VSAM files.

The new target database can reside on or off the mainframe, and can use any of the standard relational database management systems (RDBMS): Microsoft SQL Server, Oracle or IBM DB2.

Customers should look to solution providers who handle the conversion of the applications that use the VSAM files.  For example, Modern Systems’ solutions can convert COBOL, PL/I, Assembler, JCL and Procs for VSAM applications. Additionally, we provide a complete replacement for all VSAM file functionality including multi-view records, occurring fields, indexes and more, such as:

  • VSAM record layouts
  • Group-level elements
  • Redefined data within records
  • Occurring data
  • Multi-View record types
  • Indexes

The resulting database is fully relational. Primary keys and index definitions are automatically created. All constraints are generated into the resulting DDL. Table spaces, indexes, table names and column names are all generated according to your naming standards.

VSAM Data Conversion
The VSAM data extract and relational load process is simple, straightforward and fast. Modern Systems can provide a number of extract variations for sites that have special requirements for a short data conversion window. Special workbenches within Modern Systems’ DB-Shuttle® solution provide additional capabilities for tailoring the VSAM migration so that the new database meets each organizations’ needs and requirements. Deliverables include:

  • DDL Syntax for the new database
  • Load Syntax (optional) for use by relational database load utility
  • RI Check Syntax (optional) for use by relational database utility package
  • RUNSTATS Syntax (optional) for use by relational database utility package
  • VSAM Data Extract programs (generated in COBOL and fully self-documented) to unload all VSAM data to the correct format for the relational database load utility
  • VSAM Data Extract JCL (optional; customized to your environment) to execute the extracts and other key-processing utilities
  • DCLGEN syntax (optional) to define COBOL layouts of the tables for replacement applications

Future Proofing with added Visibility and Knowledge
Modern Systems ensures that customer teams have a full understanding of the existing VSAM file structures, as well as a full understanding of the post-conversion relational databases. DB-Shuttle generates many reports and diagrams to assist with this knowledge-building process.

In the end, customers who move VSAM to SQL Server can benefit from the SQL Server Stretch Database technology moving infrequently accessed data from on-premises storage devices to Microsoft Azure cloud, while keeping their data available and safe for querying.

Regardless of where your company is on the VSAM to SQL Server conversion journey, we can help.  Contact us to learn more about our processes, technologies and experience with VSAM.

 

 

Share This

Share This

The world needs to know more about modernization. Help us spread the word!