0

I'm trying to convert DDS defined tables to DDL without impacting the existing legacy RPG code. I can do it if I recreate the table in SQL, and then manually turn off the record level checking.

Wondering if the record level check can be turned off somehow as part of the create statement, or another SQL command.

gfroese
  • 58
  • 8
  • Short answer is no. But do you have some *really*, *really*, **really** good reason why you **must** have record-level checking off? (Only acceptable reason I can personally even imagine off the top of my head is that you don't have the RPG source, and thus cannot recompile.) – John Y Sep 16 '15 at 13:46
  • The reasoning is to avoid having to recompile the RPG programs due to the change from DDS -> DDL. After a brief review, it appears the Cruikshanking method suggested below by @Danny117 will accomplish this as well with the use of logical files between the RPG programs and the SQL created tables. – gfroese Sep 16 '15 at 13:52

2 Answers2

2

No.

Besides you shouldn't need to. If you're not getting the same record format ID with the SQL created table that you did with the DDS defined one then you probably doing something wrong.

I say probably because there were a couple bugs years ago where the record format ID wasn't created properly.

Now if you want to add columns along with redefining in DDL, then what you need to do is:

  1. Extract the DDL from the DDS table (MYPF)
  2. Add whatever is needed to the DDL, while changing the name of the table (MYPF_T)
  3. Copy data from MYPF to MYPF_T
  4. Repoint existing logicals/views/indexes from MYPF to MYPF_T; without including any new columns.
  5. Recreate MYPF as a logical with the same name; leaving out any new columns.

Done correctly, none of the record format IDs for existing objects will change; thus no existing RPG programs will need to be recompiled.

Step 4 and 5 are the key. Many existing logicals probably share the existing PF record format like so:

A          R MYPFR                     PFILE(MYPF)  
A          K ALTKEY

You want to change this to explicitly include the original columns in the PF:

A          R MYPFR                     PFILE(MYPF_T)  
A            MYKEY     R
A            ALTKEY    R
A            FLD3      R
A            FLD4      R
A            <...>
A          K ALTKEY

Note that the File level identifier will be different.

Data Base File Attributes                                               
  Externally described file . . . . . . . . . :            Yes          
  SQL file type . . . . . . . . . . . . . . . :            TABLE        
  File level identifier . . . . . . . . . . . :            1121222233155

That's fine, level checks are caused by differences in Record Format's Format Level Identifier 

Record Format List                            
                       Record  Format Level   
 Format       Fields   Length  Identifier     
 IORITEM1        114      541  2FED88D05AB32
Charles
  • 21,637
  • 1
  • 20
  • 44
  • Does this handle the case where the current DDS file is *already* "mismatched" with the RPG programs that use it? In other words, can't there be a case where an existing RPG program which has been running error-free suddenly starts to produce error messages if level checks are suddenly turned on? Are you saying that building a new LF masquerading as the old PF solves this? (To me, this is much more work than simply recompiling the RPG program(s), but hey, I guess some folks don't have the source to recompile with.) – John Y Sep 16 '15 at 13:39
  • No, if it's already broke, it remains broke. But you could turn off level checking on the MYPF logical and it would work as it currently does. Recompiling isn't the problem, especially if you have a CMS as I do. The problem is that even if all I've done is recompile the program I really should be testing it. That's were the time savings is. – Charles Sep 16 '15 at 13:49
1

I call it Cruikshanking when you replace dds files with sql tables because Dan Cruikshank wrote a paper on it.

Summary: create a ddl table. List out the columns in your logical files and point them to your new ddl table. Make a logical file that lists out all of the physical file columns and point it at your new ddl table.

Watch out for code that does crtdupobj and anything with a member like clrpfm or addpfm.

Detailed Cruikshank Method paper

danny117
  • 5,581
  • 1
  • 26
  • 35
  • 2
    More recent doc, [Modernizing IBM i Applications from the Database up to the User Interface and Everything in Between](http://www.redbooks.ibm.com/abstracts/sg248185.html), chapter 9 is basically Dan's paper. – Charles Sep 16 '15 at 13:56
  • Thanks Charles. I'll take a look. – danny117 Sep 16 '15 at 14:56