IBMi (AS400) fans only ' A simple way to create table with Rpg Free & SQL embedded

#IBMiSample

Compile and Run this SQLRPGLE to create table CLANA00F, index CLANA01L and table OTORD00F, and populate:

      **free
      *****************************
      * create CLANA00F & OTORD00F
      *****************************
       ctl-opt option(*nodebugio) dftactgrp(*no) actgrp(*new);
        // The EXEC SQL is never executed. It is used at compile time.
        exec sql Set Option Commit = *None;
        // Create File
        exec sql
          CREATE or REPLACE TABLE myLib/CLANA00F (
          CLANN0 CHARACTER(01) NOT NULL WITH DEFAULT,
          CLCCL0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          CLCIB0 CHARACTER(03) NOT NULL WITH DEFAULT,
          CLNOM0 CHARACTER(50) NOT NULL WITH DEFAULT
          )
          RCDFMT CLANA
          ;
        // Create index
        exec sql
          CREATE INDEX myLib/CLANA01L ON myLib/CLANA00F (CLCCL0 ASC);
        // Fill values
        exec sql
          INSERT INTO CLANA00F (CLCIB0, CLNOM0)
          VALUES ('EN ','Rod Stewart');
        exec sql
          INSERT INTO CLANA00F (CLCIB0, CLNOM0)
          VALUES ('EN ','Bobby McFerrin');
        exec sql
          INSERT INTO CLANA00F (CLCIB0, CLNOM0)
          VALUES ('IT ','Adriano Celentano');
        exec sql
          INSERT INTO CLANA00F (CLCIB0, CLNOM0)
          VALUES ('IT ','Vasco Rossi');
        // Create file
        exec sql
          CREATE or REPLACE TABLE myLib/OTORD00F (
          OTANN0 CHARACTER(01) NOT NULL WITH DEFAULT,
          OTCOR0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          OTTIP0 CHARACTER(02) NOT NULL WITH DEFAULT,
          OTCCL0 NUMERIC(7, 0) NOT NULL WITH DEFAULT,
          OTDTA0 NUMERIC(6, 0) NOT NULL WITH DEFAULT,
          OTDTP0 NUMERIC(6, 0) NOT NULL WITH DEFAULT,
          OTCCM0 CHARACTER(06) NOT NULL WITH DEFAULT
          )
          RCDFMT OTORD
          ;
        // Fill values
        exec sql
          INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)
          VALUES ( '10', 1 , 190108 , 190114 , '101  ' );
        exec sql
          INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)
          VALUES ( '10', 2 , 190108 , 190112 , '103  ');
        exec sql
          INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)
          VALUES ( '10', 3 , 190112 , 190116 , '102  ');
        exec sql
          INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)
          VALUES ( '10', 4 , 190108 , 190121 , '102  ');
        *inlr = *on;

 I appreciate all the comments made on this blog.