Data Definition Language

• Data definition language (DDL) is described in the form of SQL which creates, alters, and deletes database objects.
• DDL is created by using the option F6. To compile the DDL source RUNSQLSTM command is used
• RUNSQLSTM SRCFILE(*lib/File) SRCMBR(*member) COMMIT(*NONE)

Create a DDL table: By statement ‘Create or replace table *tablename*’ can create a table.

RUNSQLSTM: This is the CL command used to run the SQL statements. To create a table from the DDL source RUNSQLSTM is used. Commit is the commitment control that determines the changes in file outcomes. If *CHG, *ALL is for commit then there will be a record lock that has happened for ALTER, INSERT, DELETE, DROP, etc., on that record of the file. If *None is mentioned then there will be no lock on that file.

View: The table can be viewed in 2 ways.

• STRSQL – by using the command “SELECT * FROM Lib/file”
• By using RUNQRY “RUNQRY *N Lib/file”
• Using WRKQRY

To view the table in WRKQRY, choose option 5 concerning query (file name) and Lib. Before that, the table should be created using option 1, and also select the fields for it. Then copying query also be done using option 3.

Insert: Inserting a row in a table can be done in two ways.

• By using STRSQL – “insert into lib/file values(fld1_val, fld2_val,….)”
• Using UPDDTA library/file.

Update: Update a row can be done in two ways.

• By using STRSQL – “update lib/file set fld1= ‘value’, fld2=’value’… Where fld = ‘value’”
• By using UPDDTA library/file.

Indexes: An index is similar to LF and simply a set of pointers used to point the rows in a table. These are used to speed up the data access. Mainly for partitioning the data as we need. This table can be viewed by the RUNQRY command.

Converting DDS to DDL source:

• DDL is in text format that can be generated as SQL after ‘RUNSQLSTM’.
• SQL statement with the equivalent of DDS source is to be created by ‘Create table’ or ‘Replace table’.
• If DDS has existing data needs to do ‘CHGPF’ to duplicate those data.

Usage:

• DDL is more stabilized compared to the DDS table.
• DDL is capable of doing all types of enhancement as did in SQL.
• The length of the field name is DDL has no limit compared with DDS which has a maximum of length 10. DDS field names or in a short form are not able to be understood by all.
• DDL can store both the Field name and the System name. For example, the field name is ‘EMP_name’ and the system name is ‘Name’.

Restrictions:

• The altering of the structure of the table may lead to errors. If it depends on some other file(constraints/integrity).
• Multi-format files are difficult to handle.
• Select/Omit is not in the DDL source for table. Yet, can be used as where condition for VIEWS/INDEXES.
• There is no exact match for DATFMT/DATSEP.

Examples:

• Here creating a table for ticket booking timestamp data types is used. Timestamp gives output as both date and time.

  Primary Key is used to identify the particular unique key record in the file and it should not allow a null value.

  Record format(RCDFMT) is mandatory for all files and when it is not used system generates a record format(RCDFMT) as a system value which is used for the input/output of the file.
  The Rename keyword for the name of the system file name. If the rename keyword is not used then the same name is considered for the system name.
  The Label keyword is used to describe the file for understanding.

  

• Employee details with the comparison of DDL and DDS.
  

Label on Column:

The label on the command is used to display the field name in the output and for the references. To separate the field name should start after the 20th position. For example ’Employee name’ is represented below to separate ‘Employee’ and ‘Name’.

If we use the TEXT IS command for the own reference for the field.

Index: In DDL, index is used as part of LF from DDS source. The index is used to pinpoint the particular fields from the Table or PF. These tables are not able to be seen