Re: SQL Table rename problem

Unfortunately, the desired result is to have both names. We (the RPG
developers) want to have a consistent naming such as STGSTORE,
STGWHSE, and STGITMMST (no underscores) but they (the SQL developers)
want to have STG_IN_STORE, STG_IN_WH, and STG_IN_ITEM_MASTER.

It sounds like I need to convince them to change their table names to
be > 10 characters and this response along with Glenn's gives me some
ammo.

Thanks.

John

On Nov 3, 2014, at 18:08, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 03-Nov-2014 15:36 -0600, John R. Smith, Jr. wrote:
We have an external system with a data connection that is using SQL
to insert data into files/tables on the iSeries.

To not have PF names like STG_Ixxxxx where xxxxx is a one up
sequence number, I was doing the following rename in the create
script

RENAME TABLE IIASTGFIL/STGWHSE
TO STG_IN_WH
FOR SYSTEM NAME STGWHSE

An alternate approach [though per later comments, somewhat the /same/ potential issue applies; how this approach /flows/ in contrast to the other, may have some value] is to start with the desired final naming. If the chosen name effects a different system-name than the given-name, then issue the RENAME ... TO SYSTEM NAME <valid-system-name> to /undo/ the generated name. For example:

CREATE TABLE IIASTGFIL/STG_IN_WHSE ( ...
-- Table STG_I##### in IIASTGFIL created [w/ alt name STG_IN_WHSE]

RENAME TABLE IIASTGFIL/STG_IN_WHSE
TO SYSTEM NAME STGWHSE

This appears to work until I get to a table name of 10 characters or
less as in the above example. When I try to do this rename, it fails
with the error:
SQL7029 30 229 Position 7 New name STG_IN_WH is not valid.

The /Alternative/ file name for the *FILE object must not be a valid system-name. Otherwise the given attempt at RENAME is implicitly a request to assign the file object _two_ system-name values. Only one valid system-name is allowed for the file object, because that valid system-name will be the value stored in the unique-index [that is the *LIB object; aka the LIC Context object] to distinguish that object of that name and type from all others of the same object-type; due to what is effectively a UNIQUE CONSTRAINT on the addresses of the objects in the list, two names can not resolve to the same address.

Following the aforementioned /alternative approach/, the failing request persists, but the effects could be moot, because the file will be known by the name with the convention xxx_IN_yy*

CREATE TABLE IIASTGFIL/STG_IN_WH ( ...
-- Table STG_IN_WH in IIASTGFIL created [w/out any alt name]

RENAME TABLE IIASTGFIL/STG_IN_WH
TO SYSTEM NAME STGWHSE
-- request fails like before, but achieves the xxx_IN_xx naming
-- albeit achieved originally in the CREATE, not in the RENAME

It appears that if I include the "FOR SYSTEM NAME", then the table
name must be greater than 10 characters and if I omit it, the file
name becomes STG_Ixxxxx.

The name length is not the sole issue, but presuming [and in most cases] that is all, then omit the FOR SYSTEM NAME clause and change the TO clause from "TO <valid-system-name>" to "TO SYSTEM NAME <valid-system-name>" when the final name to assign is 10-bytes or less.

The table names have already been defined in the external systems
and documentation and getting them changed is going to be a feat just
short of moving Mt. Everest.

If the name by which the SQL should know the file is the renamed-to name, such that the original system-name by which the file is known when created is immaterial, then the above change that will lose the original system-name and assign the new system-name, would suffice.

I have less than 5% of the files hitting this problem and I don't
want different rules for just these few that will confuse someone
down the road. For example, I don't want an index (STG_IN_WHS) built
over these few which makes them different than all the rest. I also
don't want these few named differently (STG_IN_WH, etc.) while the
rest are named with regular iSeries file names such as STGSTORE
(instead of STG_IN_STO).

Does anyone have any ideas how I can get past this wall without
having a bunch of one-offs?

Nothing obvious beyond the aforementioned /alternative approach/ or [probably dynamically] choosing between two RENAME requests according to the chosen-name, *both* of which will replace the "regular iSeries file name" with the name that is not valid as an alternative name.

There will be no way to establish two short-names, so if that is a requirement, then there is unlikely to be any hope to find any resolution without overrides to the unassigned name(s) at run-time.

--
Regards, Chuck
--
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.