CRTPF and Create Table in IBM i

CRTPF  and Create Table in IBM i

There are two ways in IBM i in which files can be created that are DDS (CRTPF) and DDL (Create Table).

In this article, we will discuss how one can convert an existing DDS into DDL.

CRTPF command (Create Physical File) 

CRTPF command is used when we are planning to create a physical file in IBM i also known as DDS file.

Below is the basic structure of a DDS file.

A          R RDDS1
A            FLD1          10A 
A            FLD2          20A

                         Create Physical File (CRTPF)                     
                                                                          
Type choices, press Enter.                                    
                                                              
File . . . . . . . . . . . . . . FILE         > EXAMPLE1      
  Library  . . . . . . . . . . .              >   LIBRARY     
Source file  . . . . . . . . . . SRCFILE      > DDSTODDL      
  Library  . . . . . . . . . . .              >   EASYCLASS1  
Source member  . . . . . . . . . SRCMBR       > DDS1          
Record length, if no DDS . . . . RCDLEN                       
Generation severity level  . . . GENLVL         20            
Flagging severity level  . . . . FLAG           0             
File type  . . . . . . . . . . . FILETYPE       *DATA         
Member, if desired . . . . . . . MBR            *FILE         
Text 'description' . . . . . . . TEXT           *SRCMBRTXT  

? CRTPF ??FILE(LIBRARY/EXAMPLE1)      
        ?*SRCFILE(EASYCLASS1/DDSTODDL)
        ?*SRCMBR(DDS1)                
        ?&IGCDTA(*N)           

Create Table in SQL

Create Table is used while creating the DDL file or we can say when we try to create the file using SQL. DDL file can be created using DDL script and executed through RUNSQLSTM (Run SQL Statements) or directly on SRTSQL.

Below is the basic DDL script of a DDS file.

CREATE TABLE DDL1 (                          
  FLD1 CHAR(10) NOT NULL WITH DEFAULT ,
  FLD2 VARCHAR(20) NOT NULL WITH DEFAULT)
  RCDFMT RDDS1; 

And to create the object the above-mentioned RUNSQLSTM command is used.

                        Run SQL Statements (RUNSQLSTM)      
                                                            
Type choices, press Enter.                                  
                                                            
Source file  . . . . . . . . . . SRCFILE      > QSQLSRC     
  Library  . . . . . . . . . . .              >   LIBRARY   
Source member  . . . . . . . . . SRCMBR       > DDL         
Source stream file . . . . . . . SRCSTMF                    
                                                            
Commitment control . . . . . . . COMMIT         *CHG        
Naming . . . . . . . . . . . . . NAMING         *SYS        

RUNSQLSTM SRCFILE(LIBRARY/QSQLSRC) SRCMBR(DDL) 

Now we will look at how we can convert DDS files into DDL file manually

DDS physical file

A                                      UNIQUE        
A          R RDDS1                                   
A            FLD1          10A                       
A            FLD2          20A         VARLEN        
A            FLD3          15G         CCSID(1200)   
A            FLD4          30A         ALWNULL       
A            FLD5           2P 0                     
A            FLD6           2S 0                     
A          K FLD1                                    

DDL table

  CREATE TABLE DDL1
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,   
  FLD2 VARCHAR(20) NOT NULL WITH DEFAULT,       
  FLD3 GRAPHIC NOT NULL WITH DEFAULT CCSID 1200,
  FLD4 CHAR(30),                                
  FLD5 DECIMAL(2) NOT NULL WITH DEFAULT,        
  FLD6 NUMERIC(2) NOT NULL WITH DEFAULT)         
  RCDFMT RDDS1;                              

DDS to DDL analysis 

When we analyze the above mentioned DDS and DDL code

• FLD1 is a character field with a length of 10 and it's also a unique field

A            FLD1          10A

FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE

• FLD2  is a character field with a length of 20 and it's also having varying length

A            FLD2          20A         VARLEN    

FLD2 VARCHAR(20) NOT NULL WITH DEFAULT

• FLD3 is a graphic field with length 15 and CCSID as 1200

A            FLD3          15G         CCSID(1200)

FLD3 GRAPHIC NOT NULL WITH DEFAULT CCSID 1200

• FLD4 is a character field with a length of 30 and its allow NULL field

A            FLD4          30A         ALWNULL

FLD4 CHAR(30)

• FLD5 is a decimal field with a length of 2

A            FLD5           2P 0

FLD5 DECIMAL(2) NOT NULL WITH DEFAULT

• FLD6 is a numeric field with a length of 2

A            FLD6           2S 0 

FLD6 NUMERIC(2) NOT NULL WITH DEFAULT

Related Post

Read also :

• Primary Key in DDS and DDL tables
• Data Validation in DDS and DDL tables
• Column Heading in DDS Files and DDL tables
• Column Text in DDS and DDL tables
• Attributes of DDL table
• Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table
• ADD Constraint in DDL tables
• Create Index and LF keyed in IBM i
• Create View and LF Non-Keyed in IBM i
• DDS to DDL Modernization : Level Check Error
• Identity column in DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)