HPE Shadowbase Data Definition Language Transformation Utilities

Systematically Transform Enscribe Records into their SQL Equivalent

Many Enscribe users need to replicate their Enscribe data into external systems and environments, for example, to feed a data warehouse. This process is usually accomplished by converting the Enscribe information into standard SQL data structures and schemas.

Shutterstock/Darrin Loeliger

Dramatically Save Time and Eliminate User Errors

Instantly Flatten Enscribe DDL Definitions and Create, Convert, and Map SQL/MP Schemas into Target SQL Equivalents

The HPE Shadowbase Data Definition Language Utility (SBDDLUTL) is a command line interface (CLI) database schema conversion tool which converts an Enscribe record definition (which is naturally hierarchical) into a SQL schema definition (which is naturally a ‘flattened’ structure). The SBDDLUTL feature is available as part of the HPE Shadowbase Essentials product bundle.

The HPE Shadowbase SQL/MP Schema Conversion Utility (SBCREATP) converts and maps SQL/MP table schema data structure definitions (columns, data types) into target SQL equivalents (columns, data types), and produces an editable CREATE TABLE statement for the target SQL environment.

SBCREATP supports a variety of target SQL databases, including NonStop SQL/MX, Oracle, Microsoft SQL Server, IBM Db2®, Oracle MySQL, PostgreSQL, SAP HANA, and SAP Sybase. It is a key utility for customers performing Shadowbase data integration replication from NonStop source SQL/MP tables out to heterogeneous SQL target tables. Without SBCREATP, the conversion work will have to be done by hand, which is a time-consuming and error-prone process.

Note: To use these solutions, HPE Shadowbase BC Basic or HPE Shadowbase DIAI Basic must be licensed for the system.

Related Content
Briefs: HPE Shadowbase Data Definition Language (SBDDLUTL) Technical Brief

Key SBDDLUTL Features

Creates a flattened Enscribe DDL definition from the original hierarchical Enscribe DDL definition
Creates a SQL CREATE TABLE DDL statement from the flattened Enscribe DDL definition
Maps Enscribe DDL fields/data types into equivalent SQL columns/data types
Converts Enscribe DDL REDEFINES structures and ARRAYS/OCCURS clauses into uniquely-named and normalized SQL target columns
Alerts if the target SQL environment does not support any of the source Enscribe DDL definitions, allowing for user input to either correct or disregard the offending data structures
Supports a powerful command language to manage the metadata conversion environment (e.g., for target column naming conventions or for ARRAY processing rules)
Can be run on any NonStop system, even if no NonStop SQL/MP or SQL/MX license exists

Format Enscribe DDL

SBDDLUTL allows the user to view Enscribe DDL schemas, and includes a variety of useful formatting commands to read, store, and manipulate Enscribe DDL file definitions.

 

Read in Existing DDL Dictionaries

SBDDLUTL automatically renames fields when flattening to ensure that there are no target column name collisions and the Enscribe record is properly converted to an SQL table.

Set Options

If necessary, the utility can change the default file write access for an input/output operation by adding a modifying character at the end of the specified file name.

  • To specify unique data type conversions, the user can use the command: “SET TYPE <type> for <fully qualified field name>,” which allows the user to set the data type for the specific field.
  • Additionally, “REPLACE <source type> with <target type>” allows broad, global datatype changes with some intelligent wildcarding.
Perform Conversions on the Stored Records

The user has full control over the flattened Enscribe DDL definition field names and target table column-naming conventions.

Output Results to Various Destinations
  • The terminal
  • An edit file as ASCII
  • A binary file as a serialized Google Protocol Buffer
Extensive Output

SBDDLUTL can redirect a command’s output to an edit file with the default file write access.

Please refer to the SBDDLUTL manual for additional details.

Key SBCREATP Features

Convert SQL/MP DDL schemas into target DDL schemas.

Maps SQL/MP fields/data types into equivalent target columns/data types
Alerts if the target SQL environment does not support any of the source SQL/MP definitions