× 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.



Well, since we use change management here, any alterations to an existing table flow through a normal change cycle...

Move the old object to an archive library.
Create a new table from the DDL source. (The DDL source contains the Create Table, rename table, and the "label on" commands)
CPYF *MAP *DROP to move data to new table

We don't normally use CHGPF or ALTER TABLE commands when using Change Management.... It causes problems with rolling back a promotion.

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Mark S. Waterbury
Sent: Thursday, February 07, 2008 11:42 AM
To: Midrange Systems Technical Discussion
Subject: question for any SQL Gurus out there


The CHGPF command has this feature since at least V3R2 and V3R7:
CHGPF FILE(library/filename) SRCFILE(library/sourcefile)

where CHGPF reads the new DDS source member from the named source file
and ("under the covers") issues an equivalent SQL ALTER TABLE against
the named *FILE. This usually works okay for files created from DDS.
(There are a few cases it does not handle.) But what about SQL tables
created using DDL?

I know we can do this using SQL DDL:
CREATE TABLE table1 LIKE table2

But, is there a way to do something like this using SQL:
ALTER TABLE table1 LIKE table2

Let's say we have an altered table in a development library, (altered
via SQL using STRSQL or RUNSQLSTM or iSeries Navigator, etc.), but we
don't have a copy of the "script" containing the ALTER TABLE statment(s)
used. (e.g. the developer no longer works here.) All we have now is the
new altered table.

The previous version of the same table also exists in another library
(on the same or another system or LPAR), presumably the "production"
copy. We want to "apply" these changes to the production version of the
table (presumably after some amount of testing has occurred on the
"development" copy).

(We could have a developer analyze the current "production" copy of the
table versus the new "development" copy, to "reverse-engineer" or
recreate the ALTER TABLE statements, but we desire an automatic process,
not one that requires human intervention, wherever possible.)

We could "CREATE TABLE table2 like table1" and then copy all the data,
or even use OS/400 commands such as CRTDUPOBJ on the underlying *FILE
for the table, followed by CPYF with FMTOPT(*MAP *DROP), and so on.

But I am wondering if there is a more direct way to do this (apply the
changes directly to the existing table) using "native" DB2 SQL? If we
must resort to using CRTDUPOBJ on the underlying *FILE objects, followed
by CPYF *MAP *DROP, will that work correctly with all SQL created tables
and column data types, just like it should for DDS created PFs?

So, what are considered to be the "best practices" for this scenario?

Thanks in advance for any suggestions, insights, etc.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.