This article demonstrates how to use IRI Workbench to facilitate the creation of new relational database tables for database schema management, database data migration tools like IRI NextForm, or other IRI management product operations such as:

• IRI FieldShield to read flat-file or table data, mask it, and write it into new tables
• IRI CoSort to wrangle data and populate staging tables for ETL or wrangled views for analytics
• IRI RowGen to subset data, or synthesize data from scratch, into empty tables based on DDL info
• IRI Voracity to extract, transform, and load data from existing sources to new targets

Thanks to the enhanced Data Tools Platform (DTP) interface, IRI Workbench users can easily create and use Structured Query Language (SQL) to build tables and run SQL jobs. SQL is a standard language for interfacing with a database. SQL was initially developed by IBM®. 

Data Definition Language (DDL) is a subset of SQL, and is used to create and alter the structure and metadata of objects in the database. These database objects include schemas, tables, views, indexes, and more. Commonly used DDL commands are Create, Alter, and Drop.

IRI Workbench

IRI Workbench™ is the graphical user interface (GUI) for all IRI products, and more. Among many other things, IRI Workbench can connect to most databases.

There are wizards and editors that can generate, edit, and run SQL scripts. Among the DDL statements which can be generated are those to create, alter, and drop tables, keys, indexes and constraints.

For these wizards to work with your tables, you will need to connect your databases to IRI Workbench using both JDBC and ODBC connections. Once connected through JDBC drivers, you can view schemas and tables and work with them with IRI products. Find how-to-connect articles for various databases here.  

To create one or more tables, you will need to execute the applicable CREATE TABLE (as well as available ALTER TABLE) statements in IRI Workbench. These DDL statements can come from three places:

1. SQL database creation syntax; i.e., via a .SQL file you have containing the statements
2. Tables in schema connected in IRI Workbench, as seen in the Data Source Explorer
3. Automatic conversion (in Workbench) of SortCL /FIELD statements

The next two sections of this article cover methods 2 and 3 above.

Create New Tables from Workbench-Connected, Existing Table DDL 

Thanks to the Data Tools Platform (DTP) plugin for Eclipse and IRI enhancements thereto, IRI Workbench users can do a variety of data discovery, administration, and IRI processing of multiple databases connected through JDBC.

Workbench users can start with data discovery; they can profile, search, and diagram any connected schema. The screenshot below shows the E-R Diagram of the tables in Schema name Person in SQL Server. 

To create this view, select the tables in the DTP Data Source Explorer (DSE) view, right-click on IRI, and click New ERD to open the E-R diagram displaying the tables’ structure and relationships:

You can also use the connection to export and re-use the DDL information of the tables to facilitate the creation of new tables with the same or modified CREATE and ALTER table attributes. 

To do that, double click on a JDBC-connected database instance, and then the schema and tables you want to use. Specifically: 

1. Right-click on the DB name and schema (e.g., SQL Server -> Person schema)
2. Select the tables to recreate
3. Right-click and select Generate DDL … (Figure 1)
4. The Generate DDL dialog will open (Figure 2)
5. Select Next and click to check the desired attributes to include in the new DDL script
6. Click Next (so you can Save the Script), and Next
7. A summary page shows the CREATE TABLE and constraint statements (Figure 3).

Figure 1: Generate CREATE TABLE DDL using the wizard built-into the IRI Workbench Data Source Explorer. Select the existing tables you want to recreate, right-click, and choose Generate DDL …

 

Figure 2: Select model objects to include in the DDL script

 

Figure 3: After reviewing the script, select Next to Save (and eventually run) “Script.SQL”.

 

Figure 4: Once saved, specifications open in the Workbench “SQL Scrapbook” (editor) above.

 

To use these commands to build new table/s from the existing DDL, I also need a new schema ready to receive those tables. To do that,

1. In the SQL Scrapbook editing window (where the new Script.sql is open), modify the Schema name, and save the .SQL file (as shown below).
2. Run in the new .SQL file to create the tables for the “Sales” Schema in SQL Server 

As shown above, I used the Find/Replace wizard to modify the scripts. Then after running the .SQL statements, all my tables from the “Person” schema are now also in the “Sales” schema:

Generate CREATE TABLE DDL from SortCL /FIELD Statements

It is also possible to automatically generate CREATE TABLE statements in IRI Workbench from the /FIELD (column) layouts of source or target files or tables defined in the SortCL job scripts or DDF files.

To generate DDL from IRI metadata:

1. Open an existing .SCL or .DDF file open for view/edit in the Workbench editor
2. Right-click inside the view. Select IRI -> Export -> Export Fields to DDL… (Figure 1)
3. In Section Selection, the screen chooses the relevant phase in the script (INFILE, INREC, or OUTFILE) to pull the /FIELD attribute from (Figure 2)
4. Select OK to open the SQL configuration screen (Figure 3)
5. Change the table name and file name and select Next
6. Follow the remaining steps captioned in Figures 4-7 below to create new tables.

Figure 1: From the Context Menu Select IRI -> Export -> Export Fields to DDL.

 

Figure 2: Choose the section of the script with the /FIELDs you want to convert into DDL.

 

Figure 3: Name (or rename) the .SQL file, table name, and schema name. Click Next.

 

Figure 4: Specify or modify specific field widths for the target columns. Click Next.

 

Figure 5: Select the Primary Key, Unique Constraints, and Indexes. Click to check which (different!) field name to assign in each case. Then click Next.

 

Figure 6: Review the summary of table data types and constraints. Click Finish.

Once you are done with this wizard, the SQL Scrapbook editor opens. For the SQL file you are creating, you should specify the applicable database connection profile and confirm its: 1) driver type, 2) connection name, and 3) database instance.

Once you have the appropriate connections, you can highlight the desired section of the DDL code, right-click, and select Execute All …, as shown below:

Figure 7: Running the /FIELD-derived DDL to create one or more new tables in Oracle.

Summary

IRI Workbench provides the ability to easily interact with and modify any connected relational database through the generation, manipulation, and execution of DDL statements. This article demonstrated two ways to create DDL and tables in IRI Workbench for a variety of purposes. If you have any questions or need assistance, please contact support@iri.com.