Re: Looking for file structure details -- MIDRANGE-L

And if you're not on a release that supports that...

You could always create a DDS logical over the SQL table.

You've always been able to do that...along with creating SQL views and
index over DDS PFs

Hopefully your SQL table has explicit system names assigned to its
columns. But if not, you could rename the implicit names to something more
usable in the LF. Or go back and assign usable names to the columns.

Charles

On Fri, Jun 26, 2015 at 9:01 AM, <rob@xxxxxxxxx> wrote:

You CAN create an index with a WHERE clause. IBM specifically made this
to eliminate 99% of the arguments people had against SQL.
Sample:
Vendor creates table (let's call it IIM for an item master table). Vendor
puts no key on the table. Table has an active record column (IID). Vendor
creates a logical file in DDS like this
R IPI100IM PFILE(IIM )
K IPROD
S IID COMP(EQ 'IM')
Of course the vendor is an a$$ who thinks their program logic will ensure
there are no duplicate keys and whatnot.

Several releases ago IBM came up with this in sql

create index rob.iiml01
on erplxf.iim (iprod)
where iid='IM'
rcdfmt IPI100IM add all columns

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "John R. Smith, Jr." <smith5646midrange@xxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 06/26/2015 08:41 AM
Subject: RE: Looking for file structure details
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I want to start this by saying that I am 100% on board with SQL but not
all
of my clients are. Some are old school and have always used DDS and can't
see a need to change. When IBM finally eliminates DDS, they will probably
never upgrade again or will buy a DDS to SQL conversion tool so they can
still "use DDS" to create their files. While I appreciate all of the
recommendations to use SQL instead of DDS for this project, as a
consultant,
sometimes I have a rope to pull clients into the 21st century and
sometimes
I have a string. This time I only have a thread.

Disclaimer out of the way...

Since someone asked, the project is that I have tables with 500M+ rows
that
I have to be processed multiple times a day (come every 20 minutes), each
time adding up to 7M rows. Because of external requirements (non-iSeries
program inserting the data requires > 10 char file names, etc.), these
were
created via SQL. The records need to be processed in a particular
sequence
so don't process records out of order and there is a flag (and a date) so
we
know if the record has been processed. Purging is done daily based on a
key
structure that is different from the one used to process. Currently every
time the job runs, it reads through all 500M+ records in the specified
sequence looking for the records that have not been processed. I
suggested
that we create the SQL version of a logical where I would specify the key
we
need and a select clause to only pick up unprocessed records. That is
when
I found that you can't create a view with an order by and you can't create
an index with a where clause. I gave in from an earlier debate and we are
now going to archive the data into a separate file so the original file
only
contains unprocessed records and the archive file contains the key
structure
we need for purging. Since the external requirements for SQL do not exist
for this file, I have to revert back to DDS because that is the client's
requirement.

As a last thought, I have been told (and can't find anything on the
internet) that I can't use SQL to create an object with a key structure
and
a select/omit clause. When I suggested that I might be able to create a
view and then an index over the view, the old school reply was that it can
be done in one object via DDS (LF) and it takes two for SQL so SQL can't
do
everything that DDS can do (before you comment on how much more it can do,
please reread the opening line of this email). I would love to be able to
provide the LF equivalent in one object. If anyone knows of a way, please
let me know.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.