Re: Using Schemata - to test a library exists

On 9/28/10 2:56 AM, Kevin Passey wrote:

I have two issues I am working on.

1. Is there anyway of suppressing the copy when testing the schemata
file for a library existence test.

The query is unlikely to ever operate without the temporary copy. Little matter anyhow, as the primary work to obtain the data is going to have a similar effect of delaying access to the data; i.e. having completed before starting the query, effectively, the request to:

DSPOBJD QSYS/*ALL *LIB *FULL OUTPUT(*OUTFILE) OUTFILE(QTEMP/QQQSCHEMAS)

Notice the definition of the VIEW, which is created as the result of a TABLE FUNCTION [UDTF] in the FROM clause, and omitting rows not authorized to the user using a FUNCTION [UDF] in the WHERE clause:

CREATE VIEW SCHEMATA AS
SELECT ...
FROM
TABLE (SYSIBM.SCHEMAS()) AS A
WHERE
SYSIBM.SQLGETAUTH( 'QSYS', ODOBNM, 'LIB '
, ' ', X'FF3C' ) <> '2'

Here is the SQL I'm testing..

SELECT * FROM qsys2/SCHEMATA fetch first row only for read only

2. Which schemata should I use - sysibm or qsys2

I would suggest using neither; using instead, your own UDF, VIEW, or TABLE according to the needs\desire.

For a simple existence test [optionally authorization], I would wrap the CL request CHKOBJ QSYS/&LibName *LIB AUT(&MinAut) in a UDF, then use a SET or VALUES INTO. If the data\effect must more closely mimic the SCHEMATA catalog VIEW [i.e. rows], then...

For [continued] lack of the System Database Cross Reference [QADB* files in QSYS] properly tracking creation of all library objects, only libraries with a database or DDM device file will be available in the QADBXREF file. If only *LIB objects with either a DBF or DDMF is acceptable, then a VIEW [or adopting program] referencing QADBXREF would suffice. If all library objects must be available, depending on how stale or limits on the capability to enable active tracking by your own code\coding, then either maintain the data tracking all [created; by CRTLIB or RSTLIB] library objects in a TABLE or use a TABLE FUNCTION using an API like QUSLOBJ [effecting similar to SCHEMAS in SYSIBM, with your own rows optimization and optional selection according to the authority\capability by the invoker].

Regards, Chuck