Shadowbase Support Tips

Andrew J. Bauernschmidt

Andrew Bauernschmidt,
Delivery Specialist

The HPE Shadowbase Data Definition Language Utility (SBDDLUTL) provides a powerful interface to convert and map the Enscribe DDL into their SQL equivalents. DDL conversion is very useful when customers need to migrate data from an Enscribe database on an HPE NonStop system to an SQL database on a different platform. SBDDLUTL creates both a “flattened” DDL definition, as well as a complete “create table” statement for a target platform, such as DB2, DDL, HANA, SQL/MP, SQL/MX, MySQL, Oracle, SQL Server, or Sybase. This utility immensely helps in migration environments where users can use an HPE Shadowbase data replication environment to replicate the source Enscribe data into the target SQL table structure.

When databases are created using Enscribe, it is a common practice for Enscribe data files to have nested DDL definitions within a record, i.e., definitions with sub-definitions that eventually deconstruct into individual fields. This nested data will not translate exactly into a SQL table, because SQL does not support nested data schemas. The SBDDLUTL conversion function first flattens (normalizes) the DDL, which causes the sub-definitions’ fields to become individual and uniquely renamed columns. SBDDLUTL gives the user full control over the structure and behavior of the conversion function. Users can specify if they want to rename a field, or convert a field into a specific data-type when it is converted. If the user specifies settings and program options that cause the resulting field or column names to be invalid, SBDDLUTL will warn the user about the invalid field/column names. The flattened DDL or converted table will be output, allowing the user to see and correct the issue and rerun the conversion function. If required, the user can edit the resulting SQL DDL scripts to add additional platform/database specific features.

SBDDLUTL does not modify the source Enscribe file’s DDL; instead, it creates a copy of the record definitions found in the specified DDL dictionary and displays it in the terminal window, which optionally can be copied or directly sent to an output file. Because all transformations and conversions occur on copies of the record definition, the user can freely apply settings to convert the Enscribe DDL without changes propagating back to the original source Enscribe file’s DDL.

The example below shows the three steps of conversion:

  1. The left section shows the original un-flattened DDL definition for an Enscribe record named RECORD-1, which is useful for seeing the way the Enscribe record is currently configured.
  2. The middle section shows the flattened DDL (what the DDL looks like as it was flattened completely to one level, with no sub-definitions).
  3. The right section shows the output of the “CONVERT <DEFINITION> TARGET <DATABASE>” command, which converts the flattened Enscribe file definition into the desired target SQL platform syntax (in this case to Oracle syntax). The output of this command could be copied from the terminal (or out-file) and pasted directly into an Oracle instance, where it would create the table.

This example is extremely simple; users could also rename columns or use any of the many conversion commands available in SBDDLUTL, which is a smart tool that realizes the restrictions of the target databases. If the conversion completed successfully and SBDDLUTL found an issue with the create statement (e.g., a reserved word is used for a column, or a data type is invalid for the target platform), then the related error is displayed below the create table statement. Users could either ignore or fix the issue by using the commands available and re-run the convert command.

This utility is currently available from SCOUT with the T1122 AAM (or later) download. However, you must possess a “Data Management Utilities” bundle (BE446AC or equivalent) license before you can use it on your NonStop. For more information, please read our solution brief.

As always, if you foresee needing to exercise this activity in the near future, and you have a question, or any other question related to Shadowbase Support, then feel free to contact us.

Please reference our Newsletter Disclaimer.