RE: convert dds to ddl -- MIDRANGE-L

Vern,

You'll not raise my blood pressure :) I am quite aware of the history,
having been on the platform since those days. My point is that from
Navigator you can only ever get VIEW DDL generated [you get no choice] [and
says someone decided that DDS LF's were VIEW's which is what my point was],
which, given the enhancements at 6.1 (oh, that's 2.5 years since release,
and now not the current OS level), is pretty shortsighted.

I have spent a lot of time automating the replacement of PF/LF [DDS defined]
and I am at 6.1, so I am quite aware of the current SQL capabilities...

Thanks,

Crispin [who is now at home and about to crack a beer!].

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Friday, August 06, 2010 3:33 PM
To: Midrange Systems Technical Discussion
Subject: Re: convert dds to ddl

Crispin

I'll try not to raise your blood pressure.

Views and indexes were not an AS/400 idea - they came out of the SQL
standards and may have come up in parallel with the development of the
System/38, et al. All the lovely things we have in LFs (S/O, subset of
the field list, key fields, etc.) are IBM's idea, not SQL.

Now when SQL had to be implemented on the 400, there was not the option,
I suppose, to use the full LF functionality, since SQL standards knew
nothing of this. But an LF did have the idea of a subset of fields
(VIEW) and of key fields (INDEX).

Now it does appear that at 6.1 we can create something like our favorite
LFs - you can have a field list in the format now, as well as a WHERE
clause - even more flexible than S/O criteria.

CREATE INDEX MYLIB/MYIDX ON MYLIB/MYTBL (FLD1, FLD2) WHERE FLD3 = 'Fred'
RCDFMT MYIDXR ADD FLD5

I hope I just read the 6.1 manual correctly to build that up - that's
pretty wild.

Vern

On 8/6/2010 2:10 PM, Crispin Bates wrote:

Tommy,

Have you actually tried this on an LF with Select/Omit Criteria. I can
guarantee you that you will not get a WHERE clause (I have a DCREQ open

for

it). So, it is the API that is at fault, because it will not give you DDL
with a WHERE clause when you specify INDEX and therefore doesn't live up

it's claims (of being able to generate DDL for DDS objects).

Who decided that DDS LF's were only ever VIEW's has...I'll stop there

before

I raise my blood pressure.

When I first loaded 6.1, I was not getting RCDFMT either, and had to get a
PTF for that. So, I think my frustrations are founded :)

----- Original Message -----
From:<Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion"<midrange-l@xxxxxxxxxxxx>
Sent: Friday, August 06, 2010 2:58 PM
Subject: Re: convert dds to ddl

the API will allow you to retrieve the DDS LF as an INDEX...but you have
to tell the API that you want the output to be an index rather than a VIEW
(which for some unknown reason is the default. i never use Navigator for
this type of thing so it may not be an option there. with the GENDDL
command that interfaces with that API you can specify that is should
retrieve an INDEX statement rather than a VIEW. of course you have the
same caveats with join LFs, etc. but that will get you the majority of
what you are after. in a nutshell, it's not the API that has the issue
it's how it's being called from within Ops Nav

Thanks,
Tommy Holden

From: "Crispin Bates"<cbates@xxxxxxxxxxx>
To: "Midrange Systems Technical Discussion"<midrange-l@xxxxxxxxxxxx>
Date: 08/06/2010 01:42 PM
Subject: Re: convert dds to ddl
Sent by: midrange-l-bounces@xxxxxxxxxxxx

My frustrations with QSQGNDDL showing through :)

----- Original Message -----
From: "Birgitta Hauser"<Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"<midrange-l@xxxxxxxxxxxx>
Sent: Friday, August 06, 2010 2:18 PM
Subject: AW: convert dds to ddl

I knew it, every time if I think I do not have to mention something
someone
points me to it ;)

I'd not use generate SQL to create SQL scripts for indexes and/or views.
I used generate SQL it to get SQL scripts for our DDS described physical
files. But we did not only translate 1:1, instead we added an identity
column to each of the new "physical files" and generated primary key
constraints over the identity column. That will allow us to implement
referential integrities (sometime in future).

Long ago we searched through all our logical files, found the different
key
combinations, generated SQL indexes and replaced the DDS described logical
files in our F-Specs.
Unused logical files were removed after. With each new release we could
replace more DDS described logical files. Currently there are only left a
few keyed join files that are used in some F-Specs.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"