Re: AW: Field names in SQL-generated files -- MIDRANGE-L

Then you need to:

RENAME table CUSTMSTR TO CUSTOMER_MASTER;
RENAME table CUSTOMER_MASTER TO SYSTEM NAME CUSTMSTR;
LABEL ON TABLE CUSTOMER_MASTER IS 'Customer Master';

Using my example of the customer master. The member name will correspond to the system name this way. Otherwise it will be a generated member name.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects

On 8/23/2012 9:03 AM, mgarrison@xxxxxxxxxx wrote:

Probably to keep the member name the same as the short name rather than
the name that the system creates for the member name.

Rob Berendt wrote on 08/23/2012 08:53:24 AM:

> From:rob@xxxxxxxxx
> To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxx>,
> Date: 08/23/2012 08:53 AM
> Subject: Re: AW: Field names in SQL-generated files
> Sent by:midrange-l-bounces@xxxxxxxxxxxx
> > Jim,
> > Why do you create the table with the short name, rename it to the long
> name, and rename the system name to the short name instead of creating

the

> table with the long name and just renaming the short name?
> > > 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:rob@xxxxxxxxx
> To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxx>,
> Date: 08/23/2012 09:35 AM
> Subject: Re: AW: Field names in SQL-generated files
> Sent by:midrange-l-bounces@xxxxxxxxxxxx
> > > > Birgitta,
> > You just killed two of my requirements. Apparently iNav has this
> capability and so does the API
> System name option. The system name option specifies whether a RENAME
> statement should be generated for the system name when it is different
> from the SQL name and the object type is an INDEX, TABLE, or VIEW. The
> valid values are:
> 0 A RENAME statement should not be generated.
> 1 A RENAME statement should be generated.
> > I figured if iNav has this capability the API had to have it. After
all,

> I'd bet that iNav uses that API.
> > Sample output generated:
> > CREATE TABLE ROB.REALLYLONGNAME (
> MYBIGHONKINGCOLUMN FOR COLUMN MYBIG00001 CHAR(5) CCSID 37

DEFAULT

> NULL )
> > RCDFMT RCD001 ;
> > RENAME TABLE ROB.REALLYLONGNAME
> TO SYSTEM NAME REALL00001 ;
> > GRANT ALTER , DELETE , INSERT , REFERENCES , SELECT , UPDATE
> ON ROB.REALLYLONGNAME TO PROGRAMMER WITH GRANT OPTION ;
> > GRANT DELETE , INSERT , SELECT , UPDATE
> ON ROB.REALLYLONGNAME TO PUBLIC ;
> > > > 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: "Birgitta Hauser"<Hauser@xxxxxxxxxxxxxxx>
> To: "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>,
> Date: 08/23/2012 09:13 AM
> Subject: AW: Field names in SQL-generated files
> Sent by:midrange-l-bounces@xxxxxxxxxxxx
> > > > Vern,
> >>But Generate SQL ignores this short name - that's not cool, as Rob

> pointed
> out. Seems a bug to me.
> > Generate SQL ignores the short name only by default!
> If you click on Options you'll see the option "System names for

objects".

> Select this options, generate your SQL script and voilà the short names
> are
> included.
> > 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!"
> > -----Ursprüngliche Nachricht-----
> Von:midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vernon Hamberg
> Gesendet: Thursday, 23.8 2012 15:00
> An: Midrange Systems Technical Discussion
> Betreff: Re: Field names in SQL-generated files
> > Bill
> > I believe it's in the "Run an SQL script" option. When you have selected
a

> system under Databases in the environment list on the left, open it to

see

> the schemas (libraries). Open a library to see all the SQL objects,
> including tables. Right-click Tables and see the New... option - this
> opens
> a CREATE TABLE wizard.
> > And I see that there is an option there to assign a system (short) name,
> instead of the generated one. I was looking at V5R4 of Navigator for

this

> function. Not sure when it came in - maybe forever. But Generate SQL
> ignores
> this short name - that's not cool, as Rob pointed out. Seems a bug to

me.

> > Also interesting - since I've never looked at this before - the record
> format name is the generated name - I created a table named
> THISISALONGNAME,
> short name LONGNAME. Format was named THISI00001, which was the system
> name
> when the table was created, then I have to assume a RENAME TABLE was run

-

> didn't do a monitor, which would tell us everything done.
> > There are similar wizards for all the SQL objects types.
> > For SELECT, UPDATE, INSERT, DELETE, there's an SQL Assist in the Run an
> SQL
> script task. Once you've selected a system under databases, there's a

link

> to this on the Databases tasks on the lower-right. SQL Assist is under

the

> Edit menu option. There's also a drop-down of some templates that get
> copied
> into the statement space.
> > HTH
> Vern
> > On 8/23/2012 7:15 AM, Erhardt, Bill wrote:
> > Where does one find the "Table Create Wizard" in Ops Nav?
> >
> > -----Original Message-----
> > From:midrange-l-bounces@xxxxxxxxxxxx

> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of

alan0307d@xxxxxxxxx

> > Sent: Wednesday, August 22, 2012 9:31 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: Field names in SQL-generated files
> >
> > Do you have Ops Nav installed. It has a table create wizard that will

> generate the sql automatially and you can enter short and long names. I
> always give short and long names. I could also send you a script with

both

> but use Ops Nav if you can.

> >
> > Sent from my Verizon Wireless 4GLTE smartphone
> >
> > ----- Reply message -----
> > From: "James Lampert"<jamesl@xxxxxxxxxxxxxxxxx>
> > To: "Midrange Systems Technical Discussion"<midrange-l@xxxxxxxxxxxx>
> > Subject: Field names in SQL-generated files
> > Date: Wed, Aug 22, 2012 6:33 pm
> >
> >
> > We've got an SQL jock with little or no AS/400 experience developing

an

> entirely SQL-generated database for us (and in fact, that script that

was

> giving us trouble recently was part of it -- I passed along the answers,
> and
> he was able to get the script working; thanks!).

> >
> > At any rate, we're getting (not unexpectedly) SQL field names coming

up

> as
> column headings, looking at the file in QuestView. and generated

> > AS/400 native field names appearing wherever the SQL field names

exceed

> > 10 characters.
> >
> > Is there a way to specify both the AS/400-native and SQL-native field

> names when creating the file? And what about specifying an AS/400-native
> filename when the SQL-native filename exceeds 10 characters?

> >
> > I know about "LABEL ON" for column headings; I've used it in my own

SQL

> scripts

> >
> > --
> > JHHL