Converting Logical File to DDL

[bbunney]

I know how to create tables from physical files and I know how to create views and indexes from logical files using the QSQGNDDL API or SQL table qsys2/generate_sql. But what do you replace logicals with? If I use an index, then I guess it would work with RPG programs that use the logical with native I/O but you supposedly can't use a imbedded SQL SELECT on an index. So how would I replace a LF without changing the programs that use them?

Thanks,
Brian

[Scott Klement]

SQL (embedded or not) is not supposed to work that way. You aren't supposed to tell it which index to use, but instead, it's supposed to analysis and figure out the most efficient way to do the file access. This is a big philosophical difference between native I/O and SQL.

In native I/O, you tell it explicitly how to do everything. Use this particular index, move to this particular starting point, read this many records from this index from this starting point, etc.

In SQL you say "I want these columns from these rows, based on this selection criteria". And it figures out the way to do that for you.

So, you see, it doesn't make sense to reference a logical file from embedded SQL. You always reference the table, and it figures out which index/logical to use to read it. You can still create logicals for native I/O, and then once you've replaced all your native I/O with embedded SQL, you won't need the LFs anymore.

[bbunney]

Understood. So is it fair to say that in order to change an old database from DDS to DDL without any program changes, we would use indexes to replace the logicals in programs with native I/O and wouldn't need to do anything to programs using embedded SQL as long as they use physical files and not logical files correct? Thanks.

[Scott Klement]

...we would use indexes to replace the logicals in programs with native I/O...

It depends on what the DDS logicals do. In my experience, most DDS logicals are just indexes, they only serve the purpose of providing keyed access over a different field (or set of fields.) In this case, yes... you can replace them with indexes.

If the logical, instead, provides a different list of fields (columns) for the calling program vs. the standard ones, then it would be replaced by a view rather than an index.

Where you run into trouble is when you have a logical that BOTH provides a list of fields AND different keys. There is no equivalent to this in SQL, so you have to keep the DDS-based logical file in this case until you're able to change the coding.

...and wouldn't need to do anything to programs using embedded SQL as long as they use physical files and not logical files...

Yes, this is correct. Indeed, my approach is usually to do the opposite of what you're doing. First, I change all the coding to use SQL, then when all of tht is complete, I re-do the tables with DDL. In my experience, the benefits of using DDL over DDS are rather small... the big benefits come from replacing the native I/O with SQL. But, of course, your situation may be different.