Convert
SQL ó
Jazz
Step
2. Edit and Process the Definition
Step
1. Prepare a Jazz definition of a SQL
table: -
Step
2. Prepare a CREATE TABLE statement
Jazz can create data definitions of SQL tables simply by importing descriptive information from the database. It can also develop CREATE TABLE statements to create new database tables from Jazz definitions.
Importing
SQL Definitions
Step
1. Import from SQL
From the Jazz workbench click [New], and select Data/Import from SQL: -
The database name and other fields are shared with the SQL tab of Configure. If we change them here their new values will be retained and used again here and in the SQL tab.
Click [Connect] and the tables within the schema are listed. We can select one: it is highlighted, its name appears below, and the [Get Table] button appears.
Tables are listed in TABNAME order. TYPE is one of
H Hierachy table
S Materialised query table
T Table
U Typed Table
V View
W Types View
Alias(A) and Nickname(N) objects are not included in the list.
Click [Get Table] and a Jazz Workbench appears with a Jazz-format record description: -
Step
2. Edit and Process the Definition
As with definitions imported from COBOL we may want to add information. The field name and its type must be left unchanged, as otherwise there will be a mismatch with the database and errors will follow, but we can add any additional Jazz properties such as validity criteria and display options. For example we add CODES to SEX: -
SEX
CHAR(1) CODES(M:Male,F:Female),
This provides automatic interpretation when our Jazz programs display SEX in a screen or report, and validation when a value is entered. We may want to change the PREFIX if EMP is not unique.
With normal Jazz definitions you’d simply save the definition, but with SQL when you’ve finished you should click [Process] to generate a COBOL copy book.
Exporting
to SQL
The reverse process, creating a SQL table definition from a Jazz record layout, is almost as simple.
Step
1. Prepare a Jazz definition of a SQL
table: -
It you are converting another file type to SQL then you should first edit the record’s type, changing it from (e.g.) VSAM to SQL, and then clicking [Check]. You may need to correct “errors”, as SQL does not support GROUP data, dimensions (OCCURS), and the PIC data type.
Step
2. Prepare a CREATE TABLE statement
When you have a valid SQL definition in the workbench the button [Create SQL] appears. Click this and Jazz converts the Jazz definition to SQL: -
Click [Yes] to see the SQL definition with whatever software your computer has associated with .SQL. In my case Microsoft SQL Server Management Studio will open: -
-- Last Updated by JazzUser at
7/10/2018 2:19:23 p.m.
--DEFINE EMPLOYEE SQL PREFIX EMP DATA(
--EMPNO CHAR(6) REQUIRED KEY,
--FIRSTNME VARCHAR(12) REQUIRED,
--MIDINIT CHAR(1),
--LASTNAME VARCHAR(15) REQUIRED,
--WORKDEPT CHAR(3) DKEY,
--PHONENO CHAR(4),
--HIREDATE DATE DPIC 'dd mmm
yyyy',
--JOB CHAR(8),
--EDLEVEL SMALLINT REQUIRED,
--SEX CHAR(1) CODES
(M:Male,F:Female),
--BIRTHDATE DATE DPIC 'dd mmm
yyyy',
--SALARY MONEY(9,2),
--BONUS MONEY(9,2),
--COMM MONEY(9,2));
CREATE TABLE EMPLOYEE
(EMPNO
CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1),
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
HIREDATE DATE,
JOB CHAR(8),
EDLEVEL SMALLINT
NOT NULL,
SEX CHAR(1),
BIRTHDATE DATE,
SALARY DECIMAL(9,2) /*MONEY*/,
BONUS DECIMAL(9,2) /*MONEY*/,
COMM DECIMAL(9,2) /*MONEY*/,
PRIMARY KEY(EMPNO))
You can edit this if you want to add more information. Jazz does not currently: -
1. Create indexes for DKEY and UKEY properties
2. Build validity criteria into the database. Although SQL validity-checking facilities are not as thorough as Jazz, it can provide some checking.
3. If the table already exists then you may wish insert a DROP TABLE statement before the CREATE TABLE, or convert the statement to ALTER to add just the new fields.
Step
3. Execute the SQL
When you have checked [and edited?] the CREATE TABLE statement you can submit it to your database.
Future
Development
We plan to make this facility more powerful later, once we have completed more important features of SQL implementation and gained some user feedback.