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