Re: Field names in SQL-generated files

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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