× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.