Re: AW: Field names in SQL-generated files

Generally I just create the table with the long name, change SYSTEM NAME
and then in the same script use CALL QCMDEXC to rename the member...but
both ways work :)





From: Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 08/23/2012 10:35 AM
Subject: Re: AW: Field names in SQL-generated files
Sent by: midrange-l-bounces@xxxxxxxxxxxx



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