On 18 Oct 2012 16:18, Nathan Andelin wrote:
The only con I consider of significance is the lack of support for
QTEMP.
That comes across as humorous, because objects in QTEMP go away,
anyway ;-)
Well the issue for constraints in QTEMP is effectively, that the
foreign key for the QADBFCST data tracking constraints is the
(DBXLIB,DBXFIL) of the QADBXREF\database data tracking physical files.
Since there is no tracking of QTEMP [at least in part] because there is
no uniqueness requirement for names across QTEMP of different jobs, that
integrity definition is not possible; i.e. such constraints can not be
tracked because a physical file in QTEMP is not tracked. Albeit
tracking to the internal QTEMP library name or its address could be
possible, to enable that support. Anyhow, until they are tracked, a
constraint can not be added. Trying to create, restore, or duplicate
the object with constraints will fail to add the constraint even if the
object and data can be produced by the request.
It is possible that with the longer schema name support, something
has changed to enable constraints in QTEMP.?
What about occasional needs to delete or clear tables that have DB
defined dependencies, including constraints?
Was never a problem for me, but some care is required for order of
operations, or the [value specified on the] RMVCST parameter on DLTF,
though I normally stick with the SQL DROP TABLE or sometimes ALTER TABLE
DROP CONSTRAINT. Some [effective; i.e. perhaps embedded] scripts run to
CREATE and ADD [and GRANT, GRTOBJAUT, RVKOBJAUT] to replace them after
DROP. Some specific recovery actions might CHGPFCST to disable, run
some actions that if only temporarily might not meet RI restrictions,
then repopulate\correct data, then re-enable RI.
What about managing DB defined constraints? We are operating under a
SAAS model where we set up new environments for new customers, and we
look forward to migrating a number of organizations off MS SQL Server to
IBM i in coming years. We could have potentially thousands of DB defined
RI constraints, in addition to primary key constraints. Each customer
has their own DB library. But they share a common code base. What might
we use to copy constraints from one DB library to another?
My rule was *always* "CREATE" objects, never "copy" objects, only
copy data. Sometimes restore, for effectively identical "copies" [with
data], but generally only to another system for a mirror-copy and thus
also applying journaled changes; probably OK for organizational copies
using QDFTJRN support to establish a new\separate journal. For existing
TABLE objects, ALTER TABLE ADD CONSTRAINT. Typically only
install\upgrade and\or run-time to effect changes to existing objects,
or creating new and copying old data and authorities, taking care to
ensure that code assumptions for any run-time object changes are
verified to exist.
midrange.com
