CRTPF and Create Table in IBM i

CRTPF vs CREATE TABLE in AS400
CREATE TABLE vs CRTPF in IBMi AS400,Database modernization in IBMi,Database modernization in AS400,DDS to DDL in IBMi,DDS to SQL,DDS to DDL,DDS to DDL in AS400,DDS to SQL in AS400,DDS to SQL in IBMi,dds to ddl conversion,dds to ddl,dds to sql,dds to table source,dds to sql conversion,dds to table conversion,generate table/sql source from dds,AS400 and sql tricks,as400 tutorials,DDS PF to DDL Table conversion in IBM i,DDS PF to DDL Table conversion in AS400
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain