Basics are pretty straightforward, but question is do you have constraints
and more importantly triggers on the new file(s)?
CRTDUPOBJ will help you there as it has options to duplicate these
(external) objects as well.
For table source, non-automated option could involve Generate DDL iNav
option or code to the back-end api (QSQGNDDL).
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/qsqgnddl.ht
m
We usually maintain source members that include CREATE TABLE, LABEL ON, ...
and potentially ALTER TABLE statements. We then execute RUNSQLSTM command
against this source member. I say potentially, as we avoid adding ALTER
TABLE to the source and change the CREATE TABLE code instead. Next rebuild
of database (if there is one) picks up the changed source. If you don't
want to wait for next rebuild to have the change put in effect, then resort
to interactive ALTER TABLE run (and don't forget to change that create table
statement in the original source).
As for other best practices... I know some of our customers that used field
reference files in the past (talking DDS here). Similar function is
available with CREATE TABLE AS command in DDL. These customers are using
this as substitute for field reference file. IBM has added several non-ANSI
options to the command to simulate field reference file functionality
faithfully.
Hope that helps.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
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.
midrange.com
