On 30-Mar-2015 17:02 -0500, Vicki Wilson wrote:
I'm migrating some SQL Server tables to DB2. I'd like to keep the
long table name so I'm using RENAME to get a palatable system name
rather than the funky generated name.
My comments here ignore existence of SQL /partitioning/ support. What
may be different when using SQL TABLE partitions, I do not know, and I
do not address any scenario with a PARTITION BY clause. I seem to
recall a recent mention of a long-name for partitions being exposed [via
the catalogs?] rather than just the member-name, so perhaps there is an
expectation of eventual RENAME PARTITION support [as a clause in ALTER
TABLE vs as a statement], but I know of none presently. There is no FOR
SYSTEM NAME capability on the ADD PARTITION clause.
Using the SQL RENAME with a TO SYSTEM NAME specification is an option
to modify the name after the CREATE. However, with newer releases\TRs
there is syntax to assign both names in some CREATE statements, e.g. the
FOR SYSTEM NAME system-object-identifier clause on CREATE TABLE; see the
help for the specific CREATE, such as the following CREATE TABLE doc ref:
<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzhctabl.htm>
I presume and expect that the specified SYSTEM NAME on the CREATE
will be used for the member name.
I noticed that I still get a funky member name xxxxx00001.
How do I rename this?
The CL command Rename Member (RNMM) is still, AFaIK, the only
supported method to effect rename of the Member [objects]; e.g.:
RNMM FILE(SHORTNAME) MBR(ORIGINAL) NEWMBR(RENAMED)
Before the FOR SYSTEM NAME clause for a CREATE, the same clause has
existed on the SQL RENAME. If the original CREATE specified a
short-name, after which the SQL RENAME assigns a long-name [and short
name], then the member name would match the original short-name for the
file. In the following create+rename, the original file name of
SHORTNAME from the CREATE will have the same member name as the file,
and that name will remain after the RENAME:
CREATE TABLE SHORTNAME (C CHAR) ;
RENAME TABLE SHORTNAME TO SHORTNOLONGER
FOR SYSTEM NAME SHORTNAME ;
Can I do it in the same script I use to generate the table?
As scripted in the above\prior example, or:
The RNMM CL command can be invoked using an SQL CALL to a CL
interpreter. IIRC the SQL may have includeded a PROCEDURE definition
for the QCMDEXC in QSYS; I usually created my own called EXECCMD that
accepts just one parameter to avoid coding the LENGTH() of the
command-string on the invocation. In the Run SQL script database
feature (iNav), I recall a simpler interface is available by simply
prefixing the source record with the undelimited "CL:" to request that
the SQL pass the remainder of the semicolon-terminated /statement text/
as a request to the CL interpreter.
Note: A typical problem with mixing CL with SQL, is that the library
name inferred for the different requests, if not names are not always
explicitly qualified with a library name, may not match between the SQL
and the CL [per chosen naming rules (*SYS or *SQL) and current schema].
Does it matter???
If "Does it matter?" means to ask "Does the name of the member
matter?", then consider that SQL has no concept of members, so the name
assigned by the system should be of no consequence to the SQL [nor to
the system]. That the *implementation* of the SQL TABLE is a database
Physical File with a Member however, suggests that non-SQL interfaces
will expose that Member name, thus showing the "funky member name". As
a valid member name and that name being moot both to the system and to
the SQL, what the member name is then, also should not be much of a
concern to a user.
Even so, the RNMM allows ameliorating any concerns by users about the
/funkiness/ of an already generated-name per the default naming from a
long-name; within boundary of what the available 10-byte naming
limitation allows, of course. And again, another option being to
establish the member name as the same as the originally short-named file
on the CREATE. Finally, the FOR SYSTEM NAME clause on a CREATE should
use that system-name as the name of the member [I can not test]. Any of
those three methods should function for choosing a desirable name, thus
avoiding or rectifying the /funky/ name.
midrange.com
