Re: Help solve an argument: Reading a SQL index in RPG?

Hi Rob,

Here is an article by Dan Cruikshank at the IBM DB2 Center of Excellence
that explains their recommended plan for converting your DDS to SQL DDL.

http://iprodeveloper.com/database/replacing-dds-physical-file-sql-table

Also, here's a link to more information on the subject:
http://iprodeveloper.com/rpg-programming/database-harmony-traditional-and-sql-coexistence

There's tools like the Adsero Optima series of products, and others, that
help automate the DDS to SQL DDL conversion, if you really want to speed up
the process.

By following that plan / process, you can convert all, or nearly all, of
your DDS objects to SQL DDL without having to recompile your RPG. By
reducing that process to close to zero disruption of the RPG code base, the
project velocity should greatly increase. That plan / process reduces the
effort to one which is mechanical in nature, and it carves out lots of
complicated analysis. By eliminating nearly all of the analysis, you also
avoid burning a lot of time in debates, like the one you're having now with
your coworker!

The advice from our friends at the IBM DB2 Center of Excellence tends to be
excellent imo. People should follow their advice more, and make less
attempts at reinventing an existing wheel.

I'm glad to see you modernizing !

If it were me, I'd bulk convert the entire set of convertible DDS objects
to SQL DDL in one project iteration, and then start planning iterations
centered around refactoring the most heavily used / most important portions
of the database next. Any portions of the database that were particular
pain points might get pushed closer to the earlier refactor iterations as
well.

Read up on IBM's redbook publications on Data Centric design.

Mike

date: Mon, 22 Feb 2016 08:33:12 -0600
from: Rob Michaels <robmichaels766@xxxxxxxxx>
subject: Help solve an argument: Reading a SQL index in RPG?

Hi all:

I'm a long time lurker on this list and have learned a HUGE amount of
stuff ... this is actually my first post.

I need some help resolving a disagreement with one of my co-workers.

We are in the process of modernizing our code base ... part of which
involves incrementally converting our database from DDS to SQL. We
haven't gotten around to changing our database access to SQL yet, so
we're reading the SQL objects using native RPG methods (read, chain,
setll, etc.).

One of my co-workers insists on using the SQL indexes we've built as
if they are tables and reading them directly.

I've been trying to convince him that indexes are intended ONLY to
boost performance on table & view access and that, if he wants to read
a table in a specific key order, he should build a keyed view over the
table.

Unfortunately, he won't budge on this ... I'd like to present him with
some IBM documentation explaining indexes are for performance purposes
only and that they shouldn't be read directly.

I've shown him that you will get a SQL7011 error if you try to read an
index using a select statement, but he thinks it's fine to read the
index using native RPG I/O because the index is implemented as a
logical file.

Can anyone point me to an IBM document (preferably on ibm.com) that
explains that indexes are for performance purposes only?

Thanks!

Rob M