We try to convert the DDS defined physical files to DDL defined SQL tables. Most our application program are written with RPG using native API to access the physical file. Is there any way to convert the physical file to SQL tables without changing their Format Level Identifier? so that we don?t have to recompile the RPG programs.

We understand that we can use a DDS defined logical file using the old physical file format to shield the new SQL tables, however we try to avoid this approach otherwise we will have 6000 logical files to shield the existing 6000 physical files in our database.

We have do some test and found that the Format Level Identifier is an output variable of Record Format Name, Field Definitions and SQL Table Indicator. That means even we can create a DDL that can generate a table with the same Record Format Name and Field Definitions as the old physical file, but the Format Level Identifier still change since the SQL Table Indicator has to be changed.

Hi,

first question on which release are you working?

iSeries Navigator offers a simple way to generate a SQL script for DDS described files. But be careful physical files are converted into SQL tables while logical files are converted into views.

In iSeries Navigator database just position on the database object for what you want to generate the SQL script, right click and generate SQL.

If you are not yet on release V5R4, just convert the script to create a table with the format name. After add a SQL Rename command and rename the table to the original table name. In this way, the format name stays unconverted, i.e. table and format name are different.

Create Table MySchema/MyTableFmt
(MyCol1 Integer, ....);

Rename Table MySchema/MyTableFmt To MyTable;

With release V5R4 a new option RCDFMT, can be inserted at the end of the create table statement and allows you to define a format name that differs from the table name. This option can be specified in either the create table or the create view statement.

If you are using reverse engineering (as described before), the different record format will automatically added.

Create Table MySchema/MyTable
(MyCol1 Integer, ....)
RcdFmt MyTableFmt


Birgitta

Birgitta,

Thanks for your information.

My OS version is V5R3.

What I want is to keep the Format Level Identifier as the old physical file after converting it to the SQL talbe, so that I don't need to recompile the RPG programs. However, even I did as what you mention:

1. generate DDL using iSeries Navigator,
2. change the table name in the DDL to the Record Format Name of the old physical file,
3. Run the DDL,
4. Rename the new created SQL table back to the name of the old physical file.

The Format Level Identifer is still different with the old physical file. I have tried different scenario and found that:

Format Level Identifier = F(Record Format Name, All field definitions, SQL table identifier)

That means even I can keep the Record Format Name and All field definitions the same as the old physical file, the Format Level Identifier still change since the SQL table identifer has to be changed.

I'm not sure whether V5R4 has different algorithm to generate the Format Level Identifier.


Regards,
Michael

Are you including the "with default" clause on the fields? I found the
record format level ID changed between

create table qtemp/rlsnxtrf (rlsno decimal (7) not null)

and

create table qtemp/rlsnxtrf (rlsno decimal (7) not null with default)

and using the last one I got a file that matched the file created by
DDS. This was done on both a V5R4 and V5R3 system.

-Stevers

czhijun (AT) hotmail (DOT) com wrote:

The SQL table attribute/indicator [see DSPFD "SQL file type"; based
on one of several values like Qdbfsqlt] is *not* part of the hash for
the level identifier. That is, an SQL TABLE and a DDS PF created with
the same record format name, field names, and field attributes, should
have the same Record Format Level Identifier. 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.
Switching entirely to SQL of course nullifies a varying level/hash,
because as noted later, an SQL program does not care about the level.
The SQL as a language, unlike coding to the DB directly from another HLL
like RPG, the changed data attributes typically will implicitly map to
allow the program to run without any recompile. That is just one of the
reasons that programmers may choose to use SQL vs non-SQL interfaces to
access the DB -- as one method to somewhat insulate/protect applications
from database changes.

FWiW:
The only recent incompatible change that I am aware of, was to make
the TIMESTAMP type in SQL TABLE to match the DDS. Unfortunately there
was a long undiagnosed difference in the indication for 'separator is
*job' vs 'separator is implied' -- a meaningless value externally, but
since the indicator for the field [search kwd: Qddfdtts] was included in
the hashing used to generate the LvlId, that caused mismatches between
DDS and SQL created files. Since the value has no meaning for the
timestamp data type, as an oversight in QA, failure to effect a
consistent value for a chosen default led to that defect. That mismatch
was corrected, I believe in all of the more recent DB fixpacks and
probably the cumulatives on v5r2 and above. Probably it is best to be
sure to have the latest fixpack before making comparisons between SQL
and DDS created formats, irrespective of the use of TIMESTAMP.
The choice to change SQL to match DDS was done with the assumption
that fewer users & applications would be impacted. That would be
typical, since level checks are not an issue for SQL programs. Also in
consideration, was that it makes little sense for anyone to have
switched existing DDS created files to be SQL DDL created files due to
the inherent cost of doing so, since data copy is required. That change
is unexpected without also having changed to use SQL DML for the
programs that access those files, since having changed to use DML first,
realizes the most benefits of the SQL language; how a file was created
is of limited value, when the file already exists, if the file can be
used as is. And that some features of DDS in an existing application
environment provide development functions that are lost when moving to
SQL is another reason that changes to use SQL are more likely to be all
or nothing vs piecemeal starting only with DDL.

czhijun (AT) hotmail (DOT) com wrote:

I was able to convert the DDS files to DDL table following the direction of the Red book link listed here.

My situation is to have existing COBOL programs using the DDL table.

We use Synon(CA/2e) for the application development. We will be creating Resequence access paths (LFs) for the application development using Synon. I generated the LF using Synon. I changed the PFILE reference to the DDL table. Upon compilation, I got the error saying "UNIQUE" keyword is required. Why do I need to specify the "UNIQUE" keywork in DDS?

Your reply will be helpful.

I was able to convert the DDS files to DDL table following the direction of the Red book link listed here.

My situation is to have existing COBOL programs using the DDL table.

We use Synon(CA/2e) for the application development. We will be creating Resequence access paths (LFs) for the application development using Synon. I generated the LF using Synon. I changed the PFILE reference to the DDL table. Upon compilation, I got the error saying "UNIQUE" keyword is required. Why do I need to specify the "UNIQUE" keywork in DDS?

Your reply will be helpful.

I was able to convert the DDS files to DDL table following the direction of the Red book link listed here.

My situation is to have existing COBOL programs using the DDL table.

We use Synon(CA/2e) for the application development. We will be creating Resequence access paths (LFs) for the application development using Synon. I generated the LF using Synon. I changed the PFILE reference to the DDL table. Upon compilation, I got the error saying "UNIQUE" keyword is required. Why do I need to specify the "UNIQUE" keywork in DDS?

Your reply will be helpful.

I was able to convert the DDS files to DDL table following the direction of the Red book link listed here.

My situation is to have existing COBOL programs using the DDL table.

We use Synon(CA/2e) for the application development. We will be creating Resequence access paths (LFs) for the application development using Synon. I generated the LF using Synon. I changed the PFILE reference to the DDL table. Upon compilation, I got the error saying "UNIQUE" keyword is required. Why do I need to specify the "UNIQUE" keywork in DDS?

Your reply will be helpful.

I was able to convert the DDS files to DDL table following the direction of the Red book link listed here.

My situation is to have existing COBOL programs using the DDL table.

We use Synon(CA/2e) for the application development. We will be creating Resequence access paths (LFs) for the application development using Synon. I generated the LF using Synon. I changed the PFILE reference to the DDL table. Upon compilation, I got the error saying "UNIQUE" keyword is required. Why do I need to specify the "UNIQUE" keywork in DDS?

Your reply will be helpful.