Data Validation in DDS and DDL tables

Data Validation in DDS and DDL tables

Data Validation

Data validation is one of the most important parts of any application created as the complete application runs on data evaluation only.

In this article, we will learn how we can get valid data most optimally.

Data validation can be added at any level like in files, programs, or separate validity programs. We can choose any of the options as per our requirement, but in this article, we will talk about the data validation at the file creation level.

We all know so far that we can create the file as a DDS file or DDL Table.

And, now we will see how to add data validation in both DDS files and DDL table also which one will be better.

DDS file

In DDS file COMP is used to add any type of data validation at the field level. By using COMP we can add any type of validation on any field of the file.

A                                      UNIQUE       
A          R RDDS3                                  
A            FLD1          10A         COMP(NE ' ') 
A            FLD2           2P 0       COMP(GE 2)   
A          K FLD1                                  

In the above example file fields FLD1 and FLD2 have data validation conditions with them FLD1 field values can't be blanks, FLD2 field can't have a value less than 2, and FLD1 is also the unique key.

DSPFD (DDS File)

Unique key values required  . . . . . . . . : UNIQUE
Access path journaled . . . . . . . . . . . :            No        
Access path . . . . . . . . . . . . . . . . :            Keyed     
Constraint Type . . . . . . . . . . . . . . :            NONE      
Number of key fields  . . . . . . . . . . . :              1       
Record format . . . . . . . . . . . . . . . :            RDDS3     
  Key field . . . . . . . . . . . . . . . . :            FLD1      
    Sequence  . . . . . . . . . . . . . . . :            Ascending 
    Sign specified  . . . . . . . . . . . . :            UNSIGNED  
    Zone/digit specified  . . . . . . . . . :            *NONE     
    Alternative collating sequence  . . . . :            No        

DSPFFD (DDS File)

Field Level Information                                                 
             Data        Field  Buffer    Buffer        Field    Column 
  Field      Type       Length  Length  Position        Usage    Heading
  FLD1       CHAR           10      10         1        Both     FLD1   
    Validity check keyword  . . . . . . . . . :  COMP NE        ' '     
    Coded Character Set Identifier  . . . . . :    273                  
  FLD2       PACKED       2  0       2        11        Both     FLD2   
    Validity check keyword  . . . . . . . . . :  COMP GE        +2      

UPDDTA (Update Data with Temp Program) 

Now we will use the UPDDTA command to insert the records in the above shown DDS file

UPDDTA  command is used to modify the records in the file

In the above screenshot, we can clearly see that both the data validation conditions put on FLD1 and FLD2 are working just fine and we can filter out the data through this. 

BUT...

When we try to add the records in the file through SQL then the case is completely different.

STRSQL (SQL Statements)

Here, we can see that data validation is not working and the FLD1 field can have BLANK and FLD2 can have '0' in them.

Now, let work on DDL tables which will be created with the same data validation conditions.

DDL table

In DDL table CHECK is used to add the data validation on the filed level of the file.

this DDL table has the same structure and data validation that are present in the DDS table.

And, now we will try to add the records in this DDL table using the SQL query.

Here we are getting the CHECK constraint error message while inserting the invalid values in the table.

So, from the above-shown cases, we can conclude the SQL query processes the DDS files and DDL tables differently.

Conclusion

The reason for this is the processing of the validation during the execution of the SQL Insert query. 

 DDS files data validation happens while reading the file and not during insert that why invalid records can be added in the DDS file through SQL Insert query. But when the program reads the file using SQL cursor then due to invalid data it will fail.

 DDL table data validation happens while inserting the data itself so, it will stop the query for any invalid data.

DDL tables are more efficient than DDS files in this way as there will be no invalid data present in the table. So, programs will not fail due to them.
Also, DDL tables are optimized as data validation proccing has already been done at the time of insertion so, it will save the data validation time while processing multiple files are used in the application.

Related Post

Read also :

• CRTPF and Create Table in IBM i
• Primary Key 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...)