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



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

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.