RE: 'Convert' Library To Schema

Both RUNSQLSTM and iNav's Run SQL Scripts have options you could set to
ignore the error on Drops.

That said, another, perhaps more generic way to handle this scenario, is to
create a generic stored procedure to drop anything. Here's the simplest
example I can think of:

/* One-time step to create a stored procedure that'll execute a dynamic SQL
statement */
create procedure dropAnything(dropStmt VARCHAR(32739))
language sql
begin
declare i int;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' SET i = 0;
execute immediate dropStmt;
end

Then to test it using RUNSQLSTM command (but could be other SQL statement
processor as well):

/* create a source member (i.e. DRPANYTST) with following lines in it */
CALL dropAnything('DROP TABLE QTEMP/T1');
CREATE TABLE QTEMP/T1 (F1 INT NOT NULL WITH DEFAULT);

/* now call it to see if it works */
RUNSQLSTM SRCFILE(ELVIS/H) SRCMBR(DRPANYTST) COMMIT(*NONE)

You'll see a message in the joblog like:
"T1 in QTEMP type *FILE not found."
But when you then run DSPFD QTEMP/T1 you'll see that the object exists.
In other words, the stored procedure didn't drop (delete) the object, but it
ignored the 'not found' condition and the script continued on with its
processing.

If you run the script again, you won't see a message in the joblog, since
the file now exists and DROP succeeds.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: 'Convert' Library To Schema

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?

Thanks,
Scott J