Problematic, only if implemented in a way that problems were inherent
or just unexpected fallout, and therefore reviled :-) But as a feature
well-designed and documented, the capabilities could bring forth revelry
instead. All relations could be mostly [or even entirely] restricted to
within the QTEMP of the job, similar to how the logical\physical file
relationships are restricted in a well-defined manner. But the catalog
issue for tracking to "which QTEMP", can be resolved easily enough as I
suggested, in a number of ways; all that is required, is uniqueness
across /name/ values that are not available for an actual library name.
This name could even be tracked to the new SYSSCHEMAS view [which
could be built over a ¿new? *DBXREF file for every library, although I
wonder if the current implementation may be an effective SELECT DISTINCT
DBXLIB FROM QADBXREF, to include all QTEMP libraries with their /alias/
name that is distinct across the system; the *SYSBAS or iASP]. The
capability and thus tracking requirements could even be limited, enabled
perhaps, only after a job has issued a CREATE SCHEMA QTEMP for which the
object could even be implicitly journaled to satisfy another requirement
that needs to be met for some RI.
So the problem with the catalogs is not in ensuring the uniqueness
across the system to enable the DB to track the QTEMP libraries
separately\distinctly, but with providing an ability to query that data
in the catalogs, in a way that can make sense. An API provided with its
results exposed via a UDF, could evaluate the name of the current job's
QTEMP as the unique value that was\would-be used for the catalog
/schema/ name. In that manner a query such as the following could be
performed, whereby the literal\constant value 'QTEMP' would need to be
replaced by the use of the function invocation [which could be
overloaded to enable specifying an effective job name in other form(s),
thus allowing one to inquire of information for another job, e.g. for
debugging purposes]:
SELECT * FROM SYSCST WHERE TABLE_SCHEMA = QTEMP_ALIAS()
The existing catalog VIEWs could even implicitly resolve another part
of the issue with the selected data, similarly using the UDF:
create a_catalog_view as (select case schema_col when QTEMP_ALIAS()
then 'QTEMP' /* or 'SESSION' */ else schema_col end ...)
To avoid having to explicitly code the UDF for selection, the user
inquiry could be redirected from the system catalog VIEW objects to
those VIEWs created into the QTEMP for the job per use of [something
like] the QSQXRLF feature; i.e. just like the WHERE clause is
automatically generated with the constant selection for that particular
schema name [with CREATE COLLECTION or QSQXRLF], the recognition that
the library name is QTEMP, the processing could replace the literal
library name in the predicate with the QTEMP_ALIAS() function invocation.
Some questions come to mind.
Local catalog in QTEMP?
That could be an extension if relations are mostly restricted within
a QTEMP [alluded above], or more fully the implementation if the
relations are fully restricted within a QTEMP. Either way, dealing with
capabilities and\or restrictions for a MOVOBJ from QTEMP into a
non-QTEMP library.
Are other SQL objects listed in the catalog?
Presently the SQL separately tracks /long file names/ to the library
QTEMP, because the OS *DBXREF does not currently [not by v6 anyhow]
provide any mechanism for the QTEMP libraries. The interface between
the SQL and the Database Cross Reference could be more directly just the
one code path [fully depending on the *DBXREF] rather than the SQL
having to maintain its distinct means of tracking [¿are its limits even
documented?], if they would share in the use of such a feature [as
implied by the use of a QTEMP_ALIAS() UDF].
I had thought that the non-database tracked catalog entries, i.e.
those tracked only by the SQL, were tracked outside of their catalog
TABLE objects, similarly to how the long file names are tracked in some
internal storage to the /environment/ associated with the job. I had
always assumed for example, that after issuing CREATE PROCEDURE
QTEMP/BOGUS () LANGUAGE CL EXTERNAL NAME 'QTEMP/BOGUS', there was no
visible entry in the SQL catalogs. However I was surprised to learn [on
v5r3 anyhow; not sure of the effects in newer releases] that the SQL
exposes to every job, every routine that gets created in a QTEMP of any
job. That is arguably messier and more problematic than a legitimate
tracking the routine definition to the specific QTEMP of a job; even if
the effects might be preferable in some cases to avoid each job having
to issue its own CREATE or DECLARE. I created that above-noted BOGUS
procedure in one job, and then could not create the procedure in another
job because the name was a duplicate, but then after a DROP ROUTINE
BOGUS [I also deleted others I had not created], I created a new version
of the procedure which was decidedly incompatible with the routine
created by the other job... which is unlikely to exhibit preferred effects.
Regards, Chuck
On 18 May 2013 15:27, Vernon Hamberg wrote:
I've not thought much about constraints on tables in QTEMP, but it
seems like something that would be problematic. IF this information
were to be stored in the catalog, which QTEMP would this apply to? I
too can see some possible ways to handle this, but it seems messy, no
matter what.
<<SNIP>>
On 5/18/2013 1:17 PM, CRPence wrote:
On 17 May 2013 14:14, Coy Krill wrote:
DB2 on i won't even let you put a constraint on a table in
QTEMP, which drives me a bit bonkers at times.
There are surely a variety of possible implementations that could
enable the QTEMP library to be tracked to the catalogs, thus
enabling constraints to be created into QTEMP. The big issue that
is not so easily, the enabling the capability to query the catalogs
[without an API call to know what to search instead of] using
either of the names QTEMP or SESSION.
midrange.com
