ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL vs DDS - Different Format Level Identifier

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL vs DDS - Different Format Level Identifier

    Hello all,

    i need your help on explaining the following case.

    i have a DB2 file created with the following SQL statement

    CREATE TABLE myLib/T2 (
    A1 NUMERIC(11, 0) NOT NULL with DEFAULT ,
    A2 CHAR(10) NOT NULL with DEFAULT ,
    A3 VARCHAR(20) NOT NULL with DEFAULT ,
    A4 VARCHAR(30) NOT NULL with DEFAULT )


    and also i have the following DDS that i believe it would match the aforementioned SQL
    A R T2
    A A1 11S 0
    A A2 10A
    A A3 20A VARLEN
    A A4 30A VARLEN



    however the T2 file created with DDS, has a different Record Format Identifier.
    Both files are in the same system and are created in the same job (fields ccsid is 37).


    SQL
    Record Format Information
    Record format . . . . . . . . . . . . . . . : T2
    Format level identifier . . . . . . . . . . : 28F9CEF073F67
    Number of fields . . . . . . . . . . . . . : 4
    Record length . . . . . . . . . . . . . . . : 75

    DDS
    Record Format Information
    Record format . . . . . . . . . . . . . . . : T2
    Format level identifier . . . . . . . . . . : 27F9CEF072F67
    Number of fields . . . . . . . . . . . . . : 4
    Record length . . . . . . . . . . . . . . . : 75



  • #2
    Why does it matter if they have different format level identifiers? You aren't actually going to create the file both different ways at different times, are you?

    Comment


    • #3
      Hello Scott,

      thank you for the response.

      currently, i have a db migration tool that recreates a database by executing CRTPF commands based on some configured DDS so it would be convenient for me to use the DDS.

      Anyway i can find some alternatives for my task, but i am still curious to find the reason for different formats, since i thought there is a 1-1 relationship between DDL and DDS.



      Comment


      • #4
        Perhaps you could do a display file field description (DSPFFD) on each file to see if there are any differences.

        Comment


        • #5
          Hi Brian,

          thank you for the response

          i tried the DSPFFD but i did not found significant differences (other than column header, default value etc - which are not taking place in Record format calculation).
          Is it possible the 2 sequential VARLEN fields to be the problem?

          Comment


          • #6
            I found this from IBM which may point at the answer. By using not null and with default you _should_ get the same identifier but ...
            However there may be field attribute settings that are available only in one interface, SQL or DDS, that are part of the hash for the level. If so, it may not be possible to ensure equivalence in those field attributes across both interfaces. I seem to recall an issue with the date data type; that there were cases where each interface has a feature not available in the other, but even if, I think only one was in the hash.

            By design, the algorithm must remain unchanged across releases in order to have consistency to provide legitimate functionality/purpose to identify differences in the record format as compared to the format referenced by the program. Thus the algorithm should change only for added support for new field attributes.
            It is part of this thread which addresses your topic ...


            Comment


            • #7
              i tried all the DFT & VARLEN combos , but no luck. my OS version is v7.2 btw

              Comment


              • #8
                I used your DDS to create a table and then told ACS to generate the SQL. There is a difference from your SQL but whether it accounts for the difference I have no idea. Here' waht it says.

                A1 NUMERIC(11, 0) NOT NULL DEFAULT 0 ,
                B1 CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
                C1 VARCHAR(20) CCSID 37 NOT NULL DEFAULT '' ,
                D1 VARCHAR(20) CCSID 37 NOT NULL DEFAULT '' )

                Does being specific make a difference to the level Id - don't know but there is a difference.

                Comment


                • JonBoy
                  JonBoy commented
                  Editing a comment
                  P.S. If I recreate the table using the values shown from the DDS SQL generation, the level Id changes. For reasons that would take too long to explain I did not get the same as the DDS file _but_ it certainly changed with the new values.

              • #9
                I still don't understand why the level ID matters. I guess this is just a point of curiosity?

                Comment


                • JonBoy
                  JonBoy commented
                  Editing a comment
                  I agree - but I have come across the requirement before - and it IS curious!
              Working...
              X