DROP INDEX for a non-existing INDEX should result in an error which
can be ignored by the /end severity/ or /error level/ for the script
processor. For RUNSQLSTM that is the ERRLVL() parameter.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyexecutestmt.htm
"Most SQL errors have a severity of 30. If you want to continue
processing after an SQL statement fails, set the ERRLVL parameter of the
RUNSQLSTM command to 30 or higher. DROP statements issue a severity
level 20 error if the object is not found to be dropped."
Basically you are wanting to code to the dependency of those VIEW
objects existing in the SCHEMA. That conflicts with my claim that they
are /typically of little value/ only because in that case the VIEWs
would then be inherently required. That could suggest your requirement
is atypical.
I suggest that the SCHEMA VIEW objects are of little value because
the VIEW objects in QSYS2 can typically be referred to instead. All
that is required is using the SCHEMA name in the WHERE clause to limit
the data; that is of course, the only difference between the VIEW in a
SCHEMA and its equivalent VIEW in QSYS2. In either case, the processing
will need to know which library name is of interest, either to choose
the proper catalog VIEW to query [from the specific SCHEMA versus
QSYS2], or to add that SCHEMA name as selection against the VIEW in
QSYS2. The library list concept does not work well for that since QSYS2
would normally be placed before the specific SCHEMA, but CURRENT SCHEMA
is available to provide a library name for a SELECT to be used on either
FROM or WHERE clauses. Thus creating the extra objects is not typically
much of a requirement.
Regards, Chuck
Scott Johnson wrote:
I read your other post and this post. I am looking to create the
"SQL Catalog VIEW objects" in the libraries. Sorry, don't have the
terminology down yet. I am confused why you say in so many words
these Catalog View objects are not needed.
Here is what I am trying to achieve. We have two libraries: TestLib
and ProdLib. I have SQL scripts to create SQL indexes over tables.
The same script is ran for both libraries. When I make changes to
these indexes/scripts and re-run them, I get the lovely index already
exist error message. A simple Drop at top of the script takes care of
this, but wait that causes problems on a new index (trying to create
a standard template). Solution, look up in the Catalog if the object
already exists or not. Well if I use the one in QSys2, the indexes
are listed twice, once for each library. Having the Catalog View
objects in the libraries, I get what is just in that library.
Am I missing something?
CRPence wrote:
What is the point in wanting /the objects/ that make up a SCHEMA?
http://archive.midrange.com/midrange-l/200803/msg01345.html
Regardless, the following script will effect the requested, for an
existing library named MYLIB:
crtjrnrcv mylib/qsqjrn0001 threshold(as_required_and_reasonable)
crtjrn mylib/qsqjrn mylib/qsqjrn0001 mngrcv(*system) dltrcv(*yes)
call qsys2/qsqxrlf (CRT MYLIB)
If the goal is actually automatic journaling, research QDFTJRN data
area. Note that the undocumented QSQXRLF API has almost surely still
not been updated to recognize that data area; i.e. the token QSQJRN as
name for a *JRN, is still required to exist to enable the API for an
invocation with token CRT as the first parameter, in order to effect
the creation of the SQL catalog VIEW objects in a user library.
