In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Hi Mike,

AFAIK you shouldn't specify the RCDFMT keyword on the CREATE ... LIKE ... statement.

If you specify RCDFMT you always get a new record format level id - if you don't specify it, the record format will be the same, und the record format level id will match the original table.

HTH

Daniel

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Daniel, 

  Thank you, good thought.  But I just tested this and it ended up with a record format name that is the same as the table name instead of that of the parent, and the level Id is still not the same as the parent file.  It has the same record layout, but still has no column headings or field text for each field either, where DDS duplicates have all this information.

Hi Mike,

AFAIK you shouldn't specify the RCDFMT keyword on the CREATE ... LIKE ... statement.

If you specify RCDFMT you always get a new record format level id - if you don't specify it, the record format will be the same, und the record format level id will match the original table.

HTH

Daniel

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

------------------------------
Mike Overlander
------------------------------

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Yes - instead of (non unique) DDS keys, you would use CREATE INDEX - and you can use these indexes for native RPG I/O like any other DDS LF. 

But you should really define at least one "primary key" on the table - preferably with a unique row id. 

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

>>But you should really define at least one "primary key" on the table - preferably with a unique row id. 

You can only define a single Primary Key (Constraint) but additionally multiple Unique Key (Constraints or Indexes).

Yes - instead of (non unique) DDS keys, you would use CREATE INDEX - and you can use these indexes for native RPG I/O like any other DDS LF. 

But you should really define at least one "primary key" on the table - preferably with a unique row id. 

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

I am almost sure many people has written answers to this question.

DDL does support keys, but they are called indexes.  You can create indexes as part of the CREATE TABLE command or using commands CREATE INDEX or CREATE UNIQUE INDEX.  You can also explore different type of indexes.  As far as I know, DDS only support btree indexes, while DDL support btree (traditional) indexes, bitmap (encoded vector indexes or EVI) indexes.  See CREATE INDEX - IBM Documentation

What may cause some confusion is that keyed logical file cannot be created in just one DDL instruction, but two: CREATE VIEW and CREATE INDEX.  Create view to create the data structure and underlined query implementing the "logical file" and a create [unique] index to implement the "key" for the logical file.

Fortunately, DB2 for IBM i documentation y very good at Database - IBM Documentation, just be careful of choose your version given that there are some added functionality from version to version.

I hope this links helps.  Have a nice day 

DDL does not support Keys! 

And since the key in your physical file is not unique it can not even generate a primary Key constraint.

The result might be a table with the same data, but without key! so, it is not usable for native I/O with keyed access.

If you want to modernize your tables you should:

1. Check whether there are keys in your physical file. If so generate an additional logical file (or better an index) replace the keyed access in your programs with a keyed access on the new logical or index (BTW an SQL index can be used with native I/O as a keyed logical file)
2. Generate the SQL Script for the physical file with ACS (Generate SQL) or the SQL Stored Procedure GENERATE_SQL (make sure you selected CREATE OR REPLACE or the SQL Script includes CREATE OR REPLACE TABLE). BTW everything that cannot be converted is commented, so you may double check what is missing
3. After everything is okay just run the SQL Script

If nothing more is changed the Format Level will be the same

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Hello, I was trying not to describe all the complications of our system when I asked about the LIKE feature of creating a table.  All of your points are well taken and many have already been considered, but since I posed a simple version of my problem, the lack of information may have influenced them.  I will expand the information, but try for simplicity.

This is a 2-stage problem.  The file in the DDS Format(name) function can easily be converted to source for a table.  RunSqlScripts gives us the record format keyword and name, so that is why it's used in the source code.  This 'parent' file data is replaced more than 3,000 times a day and is keyed as the example for it's duplicate.  99% of our DDS files are not keyed unique, but programming code usually has maintained the data as unique per the key on the physical file.  The data almost always copies to a DDL Table which has the unique key set like the example, without a problem.  And one of the biggest points of modernization is to move this enforcement code out of programs.

In stage 1, the application that builds this 'parent' data also needs to compare it to yesterdays version of the data, so stage 2  is the duplicate file created with the DDS Format(name) function, and the data copied to it after every successful load of the parent.  In order to successfully convert both of these files to DDL tables, we want them to be exactly the same with the least amount of complexity, work, or opportunity for error.  That is why I'm searching for the answers to the LIKE function.  And there are 14 files involved in this one job stream that need duplicates containing the history of yesterday.

Daniel, I'm no whiz with the command diagrams in the SQL manual, but I thought LIKE showed that it had to be in parenthesis, so I will look at it again and try it without.

Robert, yes I ran the extract tool over the cloned file, and it doesn't interpret it as a LIKE scenario, it extracts all the fields and labels just like it does for the parent.  I'd prefer not to use this to be sure they never get out of sync, which is why the DDS versions use the Format(name) keyword.

And to the points about an identity column, with thousands of DDS files, we are not in a position to make use of this concept and recompile the system.

This isn't the only challenge, we are just trying to incrementally upgrade the system we have.  And may programs use the physical files directly, and in some cases we already have hundreds of LF objects over files and we need the record formats to be the same if we hope to avoid level-check issues.  We cannot change all access to SQL to avoid it, and I don't believe complicating the system with logical files that are not the complete record layout to achieve a certain level id is a good idea either.

  I appreciate all the feedback.  It's quite involved to update a 35-40 year old system, and this may just be one part that remains DDS defined.

  Thank you all.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Hi Mike,

there are 2 variants of the LIKE clause:

Hello, I was trying not to describe all the complications of our system when I asked about the LIKE feature of creating a table.  All of your points are well taken and many have already been considered, but since I posed a simple version of my problem, the lack of information may have influenced them.  I will expand the information, but try for simplicity.

This is a 2-stage problem.  The file in the DDS Format(name) function can easily be converted to source for a table.  RunSqlScripts gives us the record format keyword and name, so that is why it's used in the source code.  This 'parent' file data is replaced more than 3,000 times a day and is keyed as the example for it's duplicate.  99% of our DDS files are not keyed unique, but programming code usually has maintained the data as unique per the key on the physical file.  The data almost always copies to a DDL Table which has the unique key set like the example, without a problem.  And one of the biggest points of modernization is to move this enforcement code out of programs.

In stage 1, the application that builds this 'parent' data also needs to compare it to yesterdays version of the data, so stage 2  is the duplicate file created with the DDS Format(name) function, and the data copied to it after every successful load of the parent.  In order to successfully convert both of these files to DDL tables, we want them to be exactly the same with the least amount of complexity, work, or opportunity for error.  That is why I'm searching for the answers to the LIKE function.  And there are 14 files involved in this one job stream that need duplicates containing the history of yesterday.

Daniel, I'm no whiz with the command diagrams in the SQL manual, but I thought LIKE showed that it had to be in parenthesis, so I will look at it again and try it without.

Robert, yes I ran the extract tool over the cloned file, and it doesn't interpret it as a LIKE scenario, it extracts all the fields and labels just like it does for the parent.  I'd prefer not to use this to be sure they never get out of sync, which is why the DDS versions use the Format(name) keyword.

And to the points about an identity column, with thousands of DDS files, we are not in a position to make use of this concept and recompile the system.

This isn't the only challenge, we are just trying to incrementally upgrade the system we have.  And may programs use the physical files directly, and in some cases we already have hundreds of LF objects over files and we need the record formats to be the same if we hope to avoid level-check issues.  We cannot change all access to SQL to avoid it, and I don't believe complicating the system with logical files that are not the complete record layout to achieve a certain level id is a good idea either.

  I appreciate all the feedback.  It's quite involved to update a 35-40 year old system, and this may just be one part that remains DDS defined.

  Thank you all.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

There are two notable differences to keep in mind.

PFs created from DDS have the following default values :

·       REUSEDLT(*NO)

·       SIZE(10000 1000 3)

Table created with SQL have the following default values :

·       REUSEDLT(*YES)

·       SIZE(*NOMAX)

that may impact the operation of certain applications.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Dear Dominique

To avoid the undesired impact, those 2 attributes can be changed by CHGPF command right after the file is created. 

There are two notable differences to keep in mind.

PFs created from DDS have the following default values :

·       REUSEDLT(*NO)

·       SIZE(10000 1000 3)

Table created with SQL have the following default values :

·       REUSEDLT(*YES)

·       SIZE(*NOMAX)

that may impact the operation of certain applications.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

But I would ask yourself:  Do you really want to change them?

Sure there are some cases where reusing deleted records might make be a negative but how many people are using direct access to files (by record number vs key, etc) anymore?  I've not seen that in over 35 years.  There are many positive reasons to reuse deleted records.  Security, reduces possibility of using undelete tools.  Saves space.  Reduces need to run RGZPFM to remove deleted rows.

Had a few jobs this week blow because they hit the maximum row count.  Changing to *nomax stops that, especially since the developers always reply to the message with *NOMAX.  Now, if you 'never' exceed a certain row count and you want to safety measure to put the brakes on a runaway job filling your disk then you could change it.  There are alternative methods to spot disk spikes.

Dear Dominique

To avoid the undesired impact, those 2 attributes can be changed by CHGPF command right after the file is created. 

There are two notable differences to keep in mind.

PFs created from DDS have the following default values :

·       REUSEDLT(*NO)

·       SIZE(10000 1000 3)

Table created with SQL have the following default values :

·       REUSEDLT(*YES)

·       SIZE(*NOMAX)

that may impact the operation of certain applications.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Morning Robert

On Reuse deleted rows, On some instances I do need to not reuse deleted records and I do not want the rgzpf to reorganize by keys because in some of my designs, when there are duplicate keys, I assume the 1st row (record) entered will be first in the file

I.E. in keeping modified records in a new file, when the same key is modified multiple times, the 1st modification will be the 1st occurrence of the key in the file

But I would ask yourself:  Do you really want to change them?

Sure there are some cases where reusing deleted records might make be a negative but how many people are using direct access to files (by record number vs key, etc) anymore?  I've not seen that in over 35 years.  There are many positive reasons to reuse deleted records.  Security, reduces possibility of using undelete tools.  Saves space.  Reduces need to run RGZPFM to remove deleted rows.

Had a few jobs this week blow because they hit the maximum row count.  Changing to *nomax stops that, especially since the developers always reply to the message with *NOMAX.  Now, if you 'never' exceed a certain row count and you want to safety measure to put the brakes on a runaway job filling your disk then you could change it.  There are alternative methods to spot disk spikes.

Dear Dominique

To avoid the undesired impact, those 2 attributes can be changed by CHGPF command right after the file is created. 

There are two notable differences to keep in mind.

PFs created from DDS have the following default values :

·       REUSEDLT(*NO)

·       SIZE(10000 1000 3)

Table created with SQL have the following default values :

·       REUSEDLT(*YES)

·       SIZE(*NOMAX)

that may impact the operation of certain applications.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.

Carlos,

I appreciate the explanation.  Thank you.

I might use a pseudo key, like an automatically generated identity column, or even a timestamp out to the most precision.

However, now that you mention it, your case is not the first I've heard of this.

Hmm, "same key modified multiple times"... This sounds like a homegrown temporal table history table.  Have you looked at implementing temporal tables?

Morning Robert

On Reuse deleted rows, On some instances I do need to not reuse deleted records and I do not want the rgzpf to reorganize by keys because in some of my designs, when there are duplicate keys, I assume the 1st row (record) entered will be first in the file

I.E. in keeping modified records in a new file, when the same key is modified multiple times, the 1st modification will be the 1st occurrence of the key in the file

But I would ask yourself:  Do you really want to change them?

Sure there are some cases where reusing deleted records might make be a negative but how many people are using direct access to files (by record number vs key, etc) anymore?  I've not seen that in over 35 years.  There are many positive reasons to reuse deleted records.  Security, reduces possibility of using undelete tools.  Saves space.  Reduces need to run RGZPFM to remove deleted rows.

Had a few jobs this week blow because they hit the maximum row count.  Changing to *nomax stops that, especially since the developers always reply to the message with *NOMAX.  Now, if you 'never' exceed a certain row count and you want to safety measure to put the brakes on a runaway job filling your disk then you could change it.  There are alternative methods to spot disk spikes.

Dear Dominique

To avoid the undesired impact, those 2 attributes can be changed by CHGPF command right after the file is created. 

There are two notable differences to keep in mind.

PFs created from DDS have the following default values :

·       REUSEDLT(*NO)

·       SIZE(10000 1000 3)

Table created with SQL have the following default values :

·       REUSEDLT(*YES)

·       SIZE(*NOMAX)

that may impact the operation of certain applications.

In traditional DDS coding for a database, you can use the following to create an exact duplicate of a file by another name:

     A          R EDR832BH1                 FORMAT(EDP832BH1)
      *
     A          K H1CMPY
     A          K H1CST1
     A          K H1ID#                                              

And using DDL to define the same file duplication, you can do the following:

CREATE OR REPLACE TABLE EDP832BPH1(
LIKE EDP832BH1
)
RCDFMT EDR832BH1;

LABEL ON TABLE EDP832BPH1 IS 'EDI B2B 832 HEADER FILE, PURGED DATA'    ;

ALTER TABLE EDP832BPH1
PRIMARY KEY (H1CMPY, H1CST1, H1ID#);                   

  Can anyone explain why the SQL table version does not end up with the same level Id as the parent, and also has no column headings or field text for each field?  It is difficult to understand why these two coding techniques do not produce the same result, and if trying to convert to using the tables instead of the file with native I/O you have to recompile all of the programs that depend on the level Id. 

Thank you.