 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / December 2006DB2 newbie SQL question
I have a SQL file containing a few CREATE PROCEDURE/FUNCTION statements. This file is meant to be run against a database when installing a software product, and again when updating the product. The problem is that the script should not generate any errors or warnings (that's a requirement), but since DB2 complains if trying to create an already exisiting procedure, and also complains if trying to drop a non-existing procedure, there seems to be no way to write this SQL file without generating some sort of error or warning. For instance if I were to write: CREATE PROCEDURE blah... CREATE PROCEDURE foo... This would work when first installing the product, but would be an error when updating the product, since the procedures would already exist. Likewise, if I write: DROP PROCEDURE blah... CREATE PROCEDURE blah... DROP PROCEDURE foo... CREATE PROCEDURE foo... This would generate errors when first installing the product. So, I rooted around some scripts written by others. In the other scripts, I found that the author creates a QUIET_DROP procedure which executes a conditional DROP. This works well, but what about QUIET_DROP itself? One runs into a chicken/egg problem. How does QUIET_DROP quietly drop itself? It can't. Well, to further the solution, one can create QUIET_DROP at the beginning of their SQL script, and then drop it at the end. This is *almost* perfect. What if the SQL script errors in some way before reaching the end? Then QUIET_DROP is not removed and we'll get an error on the next run. So, I'm trying to do something like we do in Oracle: BEGIN DECLARE COUNT NUMBER := 0; BEGIN SELECT 1 INTO COUNT FROM USER_FUNCTIONS WHERE UPPER(FUNCTION_NAME) = UPPER('MYFUNC'); EXCEPTION WHEN NO_DATA_FOUND THEN EXECUTE IMMEDIATE 'CREATE FUNCTION MYFUNCT...'; END; END; But when I try to do something similar to this in DB2 (at the DB2 command prompt) it balks at me: BEGIN ATOMIC IF (select statement looking for MYFUNC in the system catalog succeeds) THEN DROP FUNCTION MYFUNC; END IF; END CREATE FUNCTION MYFUNC BEGIN ... END The parser says that DROP isn't expected or allowed. So, I'm out of ideas. Hoping someone can help. Thanks. Wouldn't it be a lot simpler to just execute the drop statement and redirect all output to /dev/null (on unix) or any file, and delete the file afterwards (since you're not interested in the result). > Wouldn't it be a lot simpler to just execute the drop statement and > redirect all output to /dev/null (on unix) or any file, and delete the > file afterwards (since you're not interested in the result). .. one can actually control the output on the fly via UPDATE COMMAND OPTIONS USING .... So simply flank the offending DROP statements. Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html > I have a SQL file containing a few CREATE PROCEDURE/FUNCTION > statements. > This file is meant to be run against a database when installing a > software product, and again when updating the product. > The problem is that the script should not generate any errors or > warnings (that's a requirement), How exactly are you executing the SQL statements? I would wrap some C/C++ or Java code (or whichever programming language you prefer) around it. Then you can handle the error in whichever way you want, for example simply discarding it. Also, just ignoring such errors doesn't sound like a great idea at all. Let's assume you install your stuff in an existing database, and the user may already have some routines in there. Now, when a collision occurs, you would silently drop the old routine without even giving a hint to the user via installation logs? Scary thought... > In the other scripts, I found that the author creates a QUIET_DROP > procedure which executes a conditional DROP. > This works well, but what about QUIET_DROP itself? > One runs into a chicken/egg problem. How does QUIET_DROP quietly drop > itself? It can't. You could write the procedure in C/C++ code using PARAMETER STYLE DRDA. This style is deprecated since V8, but it allows you to call a procedure that is not registered through DB2 using CALL library!function > Well, to further the solution, one can create QUIET_DROP at the > beginning of their SQL script, and then drop it at the end. > This is *almost* perfect. > What if the SQL script errors in some way before reaching the end? > Then QUIET_DROP is not removed and we'll get an error on the next run. What's the problem? CREATE/DROP statements are, of course, under transactional control. So you could simply run a ROLLBACK if some statement fails and the whole initialization is rolled back. You would need some logic that intercepts the error and issues the ROLLBACK. > So, I'm trying to do something like we do in Oracle: > BEGIN [quoted text clipped - 13 lines] > succeeds) THEN > DROP FUNCTION MYFUNC; Use EXECUTE IMMEDIATE here. > END IF; > END > CREATE FUNCTION MYFUNC Use EXECUTE IMMEDIATE here. > BEGIN > ... > END > The parser says that DROP isn't expected or allowed. p.s: In your situation, I would really, really start to question the requirement about ignoring error messages. If something goes wrong, you could easily end up in a lot of trouble. SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany > How exactly are you executing the SQL statements? As I stated, directly within DB2 command processor. Like this... db2 -td@ -f mysqlfile.sql Where the commands I want to execute are in mysqlfile.sql. > Also, just ignoring such errors doesn't sound like a great idea at all. > Let's assume you install your stuff in an existing database, and the user > may already have some routines in there. Now, when a collision occurs, you > would silently drop the old routine without even giving a hint to the user > via installation logs? Scary thought... No, not scary. We don't install into just any database. We control the database. It's not just some random new schema someone sets up and then we dump our stuff into. We know we won't collide with anything other than our own routines that MAY already be there. > Use EXECUTE IMMEDIATE here I cannot use that where you suggest. Like DROP and CREATE, DB2 seems to disallow using EXECUTE IMMEDIATE in the context of an "anonymous" BEGIN ATOMIC block. Try it... BEGIN ATOMIC IF (select statement looking for MYFUNC in the system catalog succeeds) THEN EXECUTE IMEDIATE 'DROP FUNCTION MYFUNC'; END IF; END This doesn't work. Parser error. > p.s: In your situation, I would really, really start to question the > requirement about ignoring error messages. If something goes wrong, you > could easily end up in a lot of trouble. Er, no, that's the point. I don't want to ignore any errors. I just want to DROP a FUNCTION/PROCEDURE without the command processor complaining if it's not already there. That's not a REAL error - at least not to my software. >> Also, just ignoring such errors doesn't sound like a great idea at all. >> Let's assume you install your stuff in an existing database, and the user [quoted text clipped - 8 lines] > We know we won't collide with anything other than our own routines that > MAY already be there. I won't hold my breath that this assumption will still be true one year from now... >> Use EXECUTE IMMEDIATE here > [quoted text clipped - 9 lines] > END > This doesn't work. Parser error. Because you are using an atomic compound statement. Employ a non-atomic BEGIN ... END block and you will be fine. You don't need the check/drop or check/create to be atomic in the first place. A transactional bracket around the complete setup/install will ensure atomicity anyways. > Er, no, that's the point. I don't want to ignore any errors. I just > want to DROP a FUNCTION/PROCEDURE without the command processor > complaining if it's not already there. That's not a REAL error - at > least not to my software. So you are worrying only about specific errors, e.g. SQL0454. I understood your posting in a much more general way. SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany dynamic compound SQL does not allow DDL or exception handlers. If you want to place such logic into SQL PL you MUST use a stored procedure. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html > Because you are using an atomic compound statement. Employ a non-atomic > BEGIN ... END block and you will be fine. You don't need the check/drop or > check/create to be atomic in the first place. A transactional bracket > around the complete setup/install will ensure atomicity anyways. Employing a non atomic block doesn't work..see... (i left out the IF statement to illustratrate that even without it, this fails) ------------------------------ Commands Entered ------------------------------ BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION FOO'; END @ ------------------------------------------------------------------------------ BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION FOO'; END DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "EXECUTE" was found following "BEGIN ". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 SQL0104N An unexpected token "EXECUTE" was found following "BEGIN ". Expected tokens may include: "JOIN <joined_table> ". This doesn't work either: BEGIN DROP FUNCTION FOO; END @ >From what I've read of the other responses I have two choices: 1. Have two scripts: initstuff.sql and dostuff.sql where I put DROPs in initstuff and then flatly ignore the results, then execute dostuff and die on any errors. 2. Use "update command options using s off" to suppress error output during DROPs. I have my doubts about #2. Even though I've suppressed error output wont the script halt when DROP fails? I know #1 will work. I just don't like this bass-ackwards way of going about it. I don't have to execute a special prep script for Oracle or SQLServer because their SQL command language is rich enough to handle things. I'd like to do the same in DB2. Since my knowledge of their SQL command language is new and limited at the moment, I thought I just didn't know something, but it truely looks like there is no way to create a proc or function from ONE SQL script without generating an error. That perturbs me to say the least. > I have my doubts about #2. > Even though I've suppressed error output wont the script halt when DROP > fails? No it won't, unless you tell it so. DB2 is perfectly capable of running your DDL script just as soon as you quit insisting on using an anonymous block or a batch. There are many ways to skin a cat.... (and some day I'll figure out where that expression comes from...) Let's assume you run your script with: db2 -stf myfile.sql > myfile.log -s stop on error -t use ';' for terminator -f filename Then you could use: UPDATE COMMAND OPTIONS USING S ON O OFF DROP TABLE youredead; UPDATE COMMAND OPTIONS USING S OFF O ON If you don't want to use stdout you can redirect to dev0 using the z option. Example: test.sql CONNECT TO REGEXP; UPDATE COMMAND OPTIONS USING S OFF O OFF; DROP TABLE T; UPDATE COMMAND OPTIONS USING S ON O ON; CREATE TABLE T(c1 INT); TERMINATE; D:\temp>db2 -tvsf test.sql CONNECT TO REGEXP Database Connection Information Database server = DB2/NT 9.1.0 SQL authorization ID = SRIELAU Local database alias = REGEXP UPDATE COMMAND OPTIONS USING S OFF O OFF DB20000I The UPDATE COMMAND OPTIONS command completed successfully. CREATE TABLE T(c1 INT) DB20000I The SQL command completed successfully. TERMINATE DB20000I The TERMINATE command completed successfully. D:\temp>db2 connect to regexp Database Connection Information Database server = DB2/NT 9.1.0 SQL authorization ID = SRIELAU Local database alias = REGEXP D:\temp>db2 drop table t DB20000I The SQL command completed successfully. D:\temp>db2 -tvsf test.sql CONNECT TO REGEXP Database Connection Information Database server = DB2/NT 9.1.0 SQL authorization ID = SRIELAU Local database alias = REGEXP UPDATE COMMAND OPTIONS USING S OFF O OFF DB20000I The UPDATE COMMAND OPTIONS command completed successfully. CREATE TABLE T(c1 INT) DB20000I The SQL command completed successfully. TERMINATE DB20000I The TERMINATE command completed successfully. D:\temp> SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html Do you execute the statement from a terminal or console or is the statement in a script? What operating system are you using? Phil Sherman >> How exactly are you executing the SQL statements? > [quoted text clipped - 3 lines] > > Where the commands I want to execute are in mysqlfile.sql. > I have a SQL file containing a few CREATE PROCEDURE/FUNCTION > statements. [quoted text clipped - 55 lines] > Hoping someone can help. > Thanks. You're question here seem to want to make a perfect script that handles all situations, taking as little as possible for granted. While that is admirable, even if it is possible, it may be impractical. In most cases, certain assumptions are made, including the computer the database is in proper working order, the database is there and running, the TABLESPACEs are not full, the user has the appropriate rights, and that there are no LOCK conditions that will make the statement time out. Mostly, there are things that cannot be tested for, especially since things may change while the script itself is running. The appropriate thing to do is to ask the database to make a best shot at it, and the trap errors and warnings. If the error or warning is known, the script can handle it. If the error is not known, it is reported to the user for futher investigation. As the coder learns of more and more cases, and how to handle them, a robust script is created over time that will handle most--but not all--situations. For low impact on the system would simply be to check SysCat.Routines for the PROCEDURE name with the same amount of parameters, and, if provided, the SPECIFIC name of the PROCEDURE. If it isn't there, the script can most likely CREATE it (unless it gets CREATEd in between the two statements, which can happen it a user runs the script twice in quick succession), if it is there, generate a second name and check for it. Other than that, if you could list the actual requirements and which error conditions must be handled, and perhaps we can offer more solid advice. B. > For low impact on the system would simply be to check SysCat.Routines > for the PROCEDURE name with the same amount of parameters, and, if [quoted text clipped - 3 lines] > quick succession), if it is there, generate a second name and check for > it. You're suggesting this...? (Again, directly from the DB2 prompt) BEGIN ATOMIC IF (select statement looking for MYFUNC in the system catalog fails) THEN CREATE FUNCTION MYFUNC BEGIN ... END; END IF; END This is the reverse of what I tried with DROP and it also doesn't work. The parser complains that CREATE can not be done in this context. Can you supply code to show what you mean? > > For low impact on the system would simply be to check SysCat.Routines > > for the PROCEDURE name with the same amount of parameters, and, if [quoted text clipped - 20 lines] > > Can you supply code to show what you mean? If you are doing it from the db2 command line, issue a DROP with no output and check the return code ($?) to see if it was successful or not. Otherwise, the statement to check would be (IIRC, i don't have the documentation here) SELECT COUNT(*) INTO block-variable FROM SysCat.Routines WHERE RoutineSchema = '' AND SpecificName = '' IF block-variable > 0 THEN..... END IF I know a BLOCK cannot have the DROP statement. Just mimic whatever the QUIET_DROP you mentioned earlier does, i'm assuming it's a PREPAREd statement or the like. B. > Otherwise, the statement to check would be (IIRC, i don't have the > documentation here) SELECT COUNT(*) INTO block-variable FROM [quoted text clipped - 5 lines] > QUIET_DROP you mentioned earlier does, i'm assuming it's a PREPAREd > statement or the like. What goes in the "..." in your IF statement? The point I've been trying to drive home here is that there seems to be a very limited set of things you can do in there. DROP, CREATE, EXECUTE IMMEDIATE are all rejected by the parser within a block statement. If you think you have the answer then please post the complete SQL file. > I have a SQL file containing a few CREATE PROCEDURE/FUNCTION > statements. [quoted text clipped - 5 lines] > drop a non-existing procedure, there seems to be no way to write this > SQL file without generating some sort of error or warning. Hi, I've posted an example of the way we do that a couple of years ago, check the google archive at http://tinyurl.com/yapvhg for the full message. Hope this helps, Gert So the final verdicts are as a stated in my last post. 1. Create another SQL file containing the DROP statements; run it, ignore results, then run the SQL file containing my CREATE statements. 2. Simply surround the DROPs in my original SQL file with "UPDATE COMMAND OPTIONS..." statements that will suppress any failure. I still think both methods get around glaring deficiencies in the DB2's SQL language. Choice number 2 sounds nice, but it's particular to the DB2 command processor. What if someone wanted to paste this SQL script into an Aqua Data Studio command parser window? I bet it wouldn't understand the UPDATE command. Anyways, we're not officially supporting that, so it's moot, but all our previous SQL scripts for other dbs are carefully crafted not to have commands in them that only run in any particular parser (for instance, embedding commands in an Oracle sql file that had commands only understood by sqlplus). Thanks for all the input. For now, I'm going with #1. Scott, Thanks for the feedback. You issue with portability across tools is compelling and I'll gladly use it as ammunition to get SQL PL extended to scripting in a future release. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.