Re: AW: Field names in SQL-generated files

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

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

I'd bet that iNav uses that API.

Sample output generated:

CREATE TABLE ROB.REALLYLONGNAME (
MYBIGHONKINGCOLUMN FOR COLUMN MYBIG00001 CHAR(5) CCSID 37

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

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."

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

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

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

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

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

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

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

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

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

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

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

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

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

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

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

scripts

--
JHHL
--
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

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

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.


--
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.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

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.