Re: Field names in SQL-generated files

Rob

I've wondered why we had to do extra work to get a short name that we want. Especially since IBM gave us a clause for renaming columns in the CREATE TABLE statement. Alas!!

Here's a developerworks article by Kent Milligan on long and short names for DB objects on IBM i - from 2001.

http://tinyurl.com/9srv4u8

Basically you use the RENAME TABLE statement - it has a couple different flavors, one for renaming the generated system name for a long sql name, another for assigning a long or delimited sql name for a short system name.

The article also covers stored procedures, et al.

HTH
Vern*
*
On 8/23/2012 7:21 AM, rob@xxxxxxxxx wrote:

James,

Others have suggested to you how to specify both the short system name and
the long sql name for columns. Table names seem to be a tad bit trickier.
Near as I can find it involves using a data area, as shown at:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhctabl.htm#rbafzhctabl__rbafznamrul

I tried using the Generate SQL function of iNav on a table created from
CREATE TABLE ROB/REALLYLONGNAME
(MYBIGHONKINGCOLUMN CHAR (5 ))
rcdfmt rcd001

It generated this:
CREATE TABLE ROB.REALLYLONGNAME (
MYBIGHONKINGCOLUMN FOR COLUMN MYBIG00001 CHAR(5) CCSID 37 DEFAULT
NULL )
RCDFMT RCD001 ;
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 ;

I don't see anything in there for the long table name.

DSPFD on the above file shows:
DSPFD Command Input
File . . . . . . . . . . . . . . . . . . . : FILE REALL00001
Library . . . . . . . . . . . . . . . . . : ROB
Data Base File Attributes
Externally described file . . . . . . . . . : Yes
SQL file type . . . . . . . . . . . . . . . : TABLE
Alternative file name . . . . . . . . . . . . :
REALLYLONGNAME
Record Format List
Record Format Level
Format Fields Length Identifier
RCD001 1 5 272752C6C0EE4
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR REALL00001

Generate Data Definition Language (QSQGNDDL) generates the SQL data
definition language statements required to recreate a database object.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/apis/qsqgnddl.htm
I don't suspect it will work any better than iNav. I suspect iNav uses
this API.

I see Jim Oberholtzer gave you a fine example of how to do this.


Rob Berendt