RTVDDLSRC – Retrieve DDL SQL Source from existing Physical and Logical Files

High level view of Database Modernization

Learn just how easy it is to convert DDS files to SQL tables. Yes, it really is as simple as running one command – RTVDDLSRC!

You do NOT need to recompile your programs.

Use this simple database modernization tool to start your database modernization exercise.

Using RTVDDLSRC you can easily rebuild your files in SQL format — let’s take an example file created with this DDS:

 R RTSTFIL2
 * Character fields
   A1CHAR1        1    COLHDG('Character' '1')
   A1CHAR5        5    COLHDG('Character' '5')
   A1CHAR10      10    COLHDG('Character' '10')
   A1CHAR100    100    COLHDG('Character' '100')
   A1CHAR1K    1000    COLHDG('Character' '1K')
   A1VARLEN   16000    VARLEN(10)
                       COLHDG('Character' '16K VARY')
 * simple signed numeric
   A1S0          13S 0 COLHDG('Signed' '0 dp')
 * packed numeric
   A10            7  0 COLHDG('Packed' '0 dp')
   A12           35  9 COLHDG('Packed' '9 dp')
 * date, time and stamp fields
   A1DATE          L   COLHDG('Date')
   A1TIME          T   COLHDG('Time')
   A1STAMP         Z   COLHDG('Timestamp')
 * some tricky data types to give RTVDDLSRC a workout
   A1FLOAT        7F   COLHDG('Float')
   A1FLOAT2      15F   COLHDG('Float' 'Double' 'Precision')
                       FLTPCN(*DOUBLE)
   A1BINARY2      3B 0 COLHDG('Binary' '2 Bytes')

RTVDDLSRC – Retrieve DDL Source

When the following RTVDDLSRC command is run over it:

it will generate this source code in SQL format:

-- Generate SQL
-- Version: V7R2M0 
-- Relational Database:
-- Standards Option: DB2 for i

CREATE TABLE PROJEX4I/TSTFIL3DDS (
-- SQL150B 10 REUSEDLT(*NO) in table TSTFIL3DDS in PROJEX4I ignored.
 A1CHAR1 CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
 A1CHAR5 CHAR(5) CCSID 37 NOT NULL DEFAULT '' ,
 A1CHAR10 CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
 A1CHAR100 CHAR(100) CCSID 37 NOT NULL DEFAULT '' ,
 A1CHAR1K CHAR(1000) CCSID 37 NOT NULL DEFAULT '' ,
 A1VARLEN VARCHAR(16000) ALLOCATE(10) CCSID 37 NOT NULL DEFAULT '' ,
 A1S0 NUMERIC(13, 0) NOT NULL DEFAULT 0 ,
 A10 DECIMAL(7, 0) NOT NULL DEFAULT 0 ,
 A12 DECIMAL(35, 9) NOT NULL DEFAULT 0 ,
 A1DATE DATE NOT NULL DEFAULT CURRENT_DATE ,
 A1TIME TIME NOT NULL DEFAULT CURRENT_TIME ,
 A1STAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 A1FLOAT FLOAT(23) NOT NULL DEFAULT 0 ,
 A1FLOAT2 FLOAT(49) NOT NULL DEFAULT 0 ,
 A1BINARY2 SMALLINT NOT NULL DEFAULT 0 )

RCDFMT RTSTFIL2 ;

LABEL ON TABLE PROJEX4I/TSTFIL3DDS
 IS 'TSTFIL: test file PF lots of small fields' ;

LABEL ON COLUMN PROJEX4I/TSTFIL3DDS
( A1CHAR1 IS 'Character 1' ,
 A1CHAR5 IS 'Character 5' ,
 A1CHAR10 IS 'Character 10' ,
 A1CHAR100 IS 'Character 100' ,
 A1CHAR1K IS 'Character 1K' ,
 A1VARLEN IS 'Character 16K VARY' ,
 A1S0 IS 'Signed 0 dp' ,
 A10 IS 'Packed 0 dp' ,
 A12 IS 'Packed 9 dp' ,
 A1DATE IS 'Date' ,
 A1TIME IS 'Time' ,
 A1STAMP IS 'Timestamp' ,
 A1FLOAT IS 'Float' ,
 A1FLOAT2 IS 'Float Double Precision' ,
 A1BINARY2 IS 'Binary 2 Bytes' ) ;

LABEL ON COLUMN PROJEX4I/TSTFIL3DDS
( A1CHAR1 TEXT IS 'Character 1' ,
 A1CHAR5 TEXT IS 'Character 5' ,
 A1CHAR10 TEXT IS 'Character 10' ,
 A1CHAR100 TEXT IS 'Character 100' ,
 A1CHAR1K TEXT IS 'Character 1K' ,
 A1VARLEN TEXT IS 'Character 16K VARY' ,
 A1S0 TEXT IS 'Signed 0 dp' ,
 A10 TEXT IS 'Packed 0 dp' ,
 A12 TEXT IS 'Packed 9 dp' ,
 A1DATE TEXT IS 'Date' ,
 A1TIME TEXT IS 'Time' ,
 A1STAMP TEXT IS 'Timestamp' ,
 A1FLOAT TEXT IS 'Float' ,
 A1FLOAT2 TEXT IS 'Float Double Precision' ,
 A1BINARY2 TEXT IS 'Binary 2 Bytes' ) ;