IBM IMS® Explorer for VS Code

Editing and Executing IMS DDL

IMS Explorer for VS Code supports editing and executing IMS DDL statements from SQL files within an IMS project. You can create or modify DBD and PSB definitions by writing IMS DDL syntax directly in the editor and executing the file against an IMS database connection.

Prerequisite: An IMS database connection must be configured in the IMS Connections view before you can execute DDL. If you have not done so already, see Creating IMS Connections.

Overview

IMS DDL allows you to define database structures (DBDs) and program views (PSBs) using SQL-like syntax. When you execute IMS DDL from IMS Explorer for VS Code, the definitions are written to the IMS catalog staging area. A second step — issuing IMPORT DEFN SOURCE(CATALOG) — is required to activate the changes in the running IMS system.

The workflow consists of two stages:

  1. Create and execute the DBD DDL — define the database, tablespace, and tables, then commit.
  2. Create and execute the PSB DDL — define the program view, then commit. This must be done in a separate, subsequent run after the DBD has been committed and imported.

Requirements and constraints

Before editing and executing IMS DDL, note the following requirements:

  • The user PSB must be set to DFSCP001.
  • All DDL for a DBD — CREATE DATABASE, CREATE TABLESPACE, and all CREATE TABLE statements — must be submitted as one unit of work in a single execution, ending with COMMIT DDL.
  • All DDL for a PSB — CREATE PROGRAMVIEW and CREATE SCHEMA statements — must similarly be submitted as one unit of work, ending with COMMIT DDL.
  • The PSB DDL must be executed in a separate, subsequent run after the DBD DDL has been successfully committed and imported.
  • COMMIT DDL writes the definition to the IMS catalog staging area and generates ACBs, but the definition does not become active in the online IMS system until IMPORT DEFN SOURCE(CATALOG) is issued.
  • The database must be stopped before issuing IMPORT DEFN SOURCE(CATALOG) for structural changes. Start the database again after the import completes.
  • A DBD created through DDL has only a logical definition. To allocate physical data sets, use JCL on the mainframe or Zowe.

Creating and executing DBD DDL

Step 1: Create a SQL file

  1. In the IMS Projects view, right-click your project and select New SQL File.
  2. Enter a name for the file (for example, create-dbd).

The file opens in the editor with IMS DDL syntax support.

Step 2: Write the DBD DDL

Write the DDL statements for the database. The following example creates a database with a tablespace and tables. All statements must be included in the same file and submitted in a single execution:

DROP DATABASE <database_name>;

CREATE DATABASE <database_name>
  ACCESS HDAM OSAM
  RMNAME(DFSHDC40 RMANCH 3 RMRBN 3 RMBYTES 25)
  CCSID 'Cp1047'
  VERSION '1.0';

CREATE TABLESPACE <tablespace_name> IN <database_name>
  SIZE PRIMARY 8192;

CREATE TABLE <root_segment_table> (
  <column_1> CHAR(6)  INTERNALNAME <internal_1> PRIMARY KEY,
  <column_2> CHAR(25) INTERNALNAME <internal_2>,
  <column_3> CHAR(25) INTERNALNAME <internal_3>
) IN <database_name>.<tablespace_name> INTERNALNAME <segment_name> MAXBYTES <n>;

CREATE TABLE <dependent_segment_table> (
  <column_1> CHAR(25) INTERNALNAME <internal_1>,
  <column_2> CHAR(25) INTERNALNAME <internal_2>,
  FOREIGN KEY REFERENCES <root_segment_table>
) IN <database_name>.<tablespace_name> INTERNALNAME <segment_name> MAXBYTES <n>;

COMMIT DDL;

Replace all values enclosed in angle brackets (< >) with the names and sizes appropriate for your database design.

Step 3: Stop the database (for structural changes)

If you are modifying an existing database, stop it before executing the DDL to prevent incomplete activation:

/STOP DB <database_name>

Issue this command using the IMS Operations Manager API, TSO SPOC, or the COMMAND folder in your IMS project. See Executing IMS Commands.

Step 4: Execute the DDL

  1. At the top of the SQL file, click the IMS Connection CodeLens to select the IMS database connection to execute against.
  2. Click the Execute button in the editor title bar, or right-click in the editor and select Run All SQL in File.
  3. Select the IMS database connection from the list when prompted.

Results are displayed in the results view. Review the output to confirm that COMMIT DDL completed successfully before proceeding.

Step 5: Import the definition

COMMIT DDL alone does not activate the changes in IMS. Issue IMPORT DEFN SOURCE(CATALOG) to apply the staged definition to the running IMS system:

IMPORT DEFN SOURCE(CATALOG)

Issue this command using the IMS Operations Manager API, TSO SPOC, or the COMMAND folder in your IMS project.

Step 6: Start the database

If you stopped the database in Step 3, restart it after the import completes:

/START DB <database_name>

Step 7: Refresh the IMS connection

In the IMS Connections view, right-click the connection and select Refresh to see the applied changes reflected in the connection tree view.

Creating and executing PSB DDL

After the DBD has been committed and the definition imported, you can define the PSB in a separate SQL file.

Step 1: Create a SQL file for the PSB

  1. In the IMS Projects view, right-click your project and select New SQL File.
  2. Enter a name for the file (for example, create-psb).

Step 2: Write the PSB DDL

Write the DDL statements for the program view. All statements must be included in the same file and submitted in a single execution:

CREATE PROGRAMVIEW <programview_name> (
  CREATE SCHEMA <pcb_name> USING <database_name> (
    CREATE SENSEGVIEW <root_segment_table>,
    CREATE SENSEGVIEW <dependent_segment_table_1>,
    CREATE SENSEGVIEW <dependent_segment_table_2>
  ) PROCOPT 'A' POSMULT
) LANG<language>;

COMMIT DDL;

Replace all values enclosed in angle brackets (< >) with values appropriate for your program view design. Valid values for LANG include COBOL, PLI, JAVA, and ASSEM.

Step 3: Execute the PSB DDL

Follow the same execution steps described in Step 4 of the DBD DDL procedure, then issue IMPORT DEFN SOURCE(CATALOG) as described in Step 5.

See also