MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2008

Re: 'Convert' Library To Schema



fixed

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.





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact