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

Projex4i - The IBM i Software Developers Toolkit

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 — lets 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')

When the following RTVDDLSRC command is run over it:

rtvddlsrc convert dds to ddl sql utility command

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' ) ;
>