IBM i Global

 View Only
  • 1.  DB2 - Data dictionary

    Posted Mon June 06, 2022 08:20 AM
    Hi, 

    studying for a new project we have think about the use of a dictionary, and the DDS field referenced to this. 
    But always think about this project, I suggest: why don't use DDL instead of DDS? 

    Does exist a concept of dictionary in DDL? 
    Have still sense a dictionary in SQL?

    Many thanks

    ------------------------------
    Paolo Salvatore
    ------------------------------


  • 2.  RE: DB2 - Data dictionary

    Posted Mon June 06, 2022 12:42 PM

    The use of a data dictionary prevents your fields from being defined differently in other files and programs. I am a big fan of using it. The use in SQL is something like this:

     

      CREATE OR REPLACE TABLE OMLAB AS ( 

        SELECT                           

        LabelId AS ABLabelId,                  

        Label AS ABLabel                   

            FROM OMSREF)                 

        DEFINITION ONLY                  

        RCDFMT OMLABR;      

     

    Cheers, Wim Jongman             






  • 3.  RE: DB2 - Data dictionary

    IBM Champion
    Posted Mon June 06, 2022 09:16 PM
    Edited by Satid Singkorapoom Mon June 06, 2022 09:57 PM

    This detailed blog post can be useful for you on this matter :   https://www.rpgpgm.com/2018/08/using-reference-fields.html

    Or you can consider using User Defined Data Type (UDT) in place of data dictionary as proposed in this article :  https://www.itjungle.com/2009/09/02/fhg090209-story02/



    There are 2 good reasons (how widely known in IBM i world?) why you should create a table with SQL CREATE TABLE rather than using DDS to create a Physical File (PF). 

    1) A DDS-created PF performs data type validation at READ time.  This means you CAN write invalid data into a PF and you will be surprise to know this only when you encounter an error message when you read that piece of data.  I encountered this frustrating incidence many times in my early years with AS/400. Only several years later did I learn about this fact and it means it is important to validate the data before it is written to a PF. 

    An SQL-created table validates data at WRITE time so that you do not get frustrated how an invalid data gets in there because you get an error message when writing an invalid data to a table.

    2) Read-time data validation for PF also means that reading A LOT OF records at the same time from a PF has performance overhead.   The more records being read, the more the data-validation overhead.

    Reading a lot of data at the same time from a table does not have the same read-time data validation overhead as a PF.     Writing a lot of data at the same time to a table is rare while reading a lot of data at the same time from a table is much more frequent.  So, a table is performance friendly for the latter.

    At IBM i 7.1 and later releases, I encourage you to use SQL DDL to create all you DB objects. They can still be used by native IO from CL, RPG, COBOL.

    ------------------------------
    Satid Singkorapoom
    ------------------------------