How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 7 Mar 2006 17:46 >> It is important to note that whenever you write a statement that contains >> ';' due to "substatements" you need to make sure the statement delimiter >> (what you know as "go" I think) is set to a value other than ';' (imagine >> placing 'go' after each line in a T-SQL procedure.. same confusion). > > i've never had to use a "go" in T-SQL. All the text i submit is one batch. > There is no ; needed at the end of every statement. i think i see the confusion. SQL Server has no "substatements". And statements don't have to be separated by semi-colons. If i were to give the OLE DB Provider for Microsoft SQL Server the following string, as one long string (and this example is taken from the SQL Server documentation): string myQuery = "DECLARE @MyMsg VARCHAR(50) SELECT @MyMsg = 'Hello, World.' -- Yields an error because @MyMsg not declared in this batch. PRINT @MyMsg SELECT @@VERSION; -- Yields an error: Must be EXEC sp_who if not first statement in -- batch. EXECUTE sp_who" Connection.Execute(myQuery); This will just run. The entire set of all the statments are sent over to SQL Server as one "batch", and SQL Server runs them. Additionally, there is a standard feature built into the query tools, and that is the use of the keyword "go". It is not a T-SQL keyword, it is a word only recognized by Microsoft's query tools. If you entered the following into Microsoft's Query Analyzer; <quote> DECLARE @MyMsg VARCHAR(50) SELECT @MyMsg = 'Hello, World.' GO -- @MyMsg is not valid after this GO ends the batch. -- Yields an error because @MyMsg not declared in this batch. PRINT @MyMsg GO SELECT @@VERSION; -- Yields an error: Must be EXEC sp_who if not first statement in -- batch. sp_who </quote> The query tool will now send 3 individual batches to SQL Server. Each batch is separate from the others. You are free to send over all the text in one batch, or you can have the tool send it over the multiple batches. But as it indicated in the example query, variables declared in one batch will no longer exist in the next batch. So, when i'm trying to do something in DB2-SQL, e.g.: DECLARE UserID integer SET UserID = ( SELECT application_ID() FROM sysibm.sysdummy1 ) update MyTable SET UserID = UserID WHERE UserID IS NULL delete from MyTable WHERE UserID = 3 select * from MyTable i expect all that text to be sent to to DB2, i expect DB2 to run the query, and return me what it is supposed to return me. But DB2 doesn't just read the SQL it's given. DB2 seems to require semicolons to separate each statment. e.g.: DECLARE UserID integer; SET UserID = ( SELECT application_ID() FROM sysibm.sysdummy1 ); update MyTable SET UserID = UserID WHERE UserID IS NULL; delete from MyTable WHERE UserID = 3; select * from MyTable; Which is fine. It would be nicer if it didn't need semicolons, but okay, i'll live with it. But now, in addition, this apparently isn't enough. Just because i've given DB2 some statements to run, doesn't mean that it will run them. For some reason, i have to tell it that the SQL it just received really is all together in one "batch" (to steal a MSSQL term) BEGIN ATOMIC DECLARE UserID integer; SET UserID = ( SELECT application_ID() FROM sysibm.sysdummy1 ); update MyTable SET UserID = UserID WHERE UserID IS NULL; delete from MyTable WHERE UserID = 3; select * from MyTable; END But not only that, i have to actually begin the batch with the keywords BEGIN ATOMIC and end the batch with END. So as i understand it, just sending a bunch of SQL to DB2 is not enough for it to decide to run the batch, i have to explicitly tell it that it is a batch. Fine, okay, messy, but i think i understand. But wait, i don't understand. Because i can send the SQL to create a stored procedure CREATE PROCEDURE doStuff(...) BEGIN ... END and i don't have to wrap the batch in BEGIN ATOMIC..END e.g. BEGIN ATOMIC CREATE PROCEDURE doStuff(...) BEGIN ... END END So perhaps because it is a CREATE PROCEDURE, or CREATE TRIGGER, or CREATE TABLE, etc that i can omit the BEGIN ATMIC...END around the statement in those batches. Perhaps it is because a CREATE PROCEDURE, CREATE TRIGGER, CREATE TABLE itself is a single statement that it doesn't need to be wrapped. Maybe batches that only consist of a single statement don't need to be wrapped. No, that's not true either: DECLARE myValue int; fails also. Maybe Create XXXXX statements are just special like that. But now, to throw another level of confusion into it, inside a CREATE TRIGGER, you DO have to have BEGIN ATOMIC...END, but not inside a CREATE PROCEDURE. So, if you read this, please try not to respond to things in detail. Hopefully you can see my confusion, and this must be because i have a different mental picture of how SQL Server is given and runs T-SQL and how DB2 is given and runs T-SQL. If you can see the error in my understanding, and point out exactly where my thinking is wrong - that would be great. It would be nice to have an understanding, rather than hoping understanding will come after being exposed to dozens of disprate examples.
From: Dave Hughes on 7 Mar 2006 18:22 Hi Ian, I must admit I'm not at all familiar with T-SQL, but from your posts I get the impression that it allows procedural type stuff (like declaring variables) *outside* a procedure or trigger. Up until fairly recently, this was impossible in DB2 (if you wanted procedural logic, you either used a stored procedure inside the database, or an external application to manipulate the data). However, more recent versions (I think it first appeared in version 7 or thereabouts) have introduced a limited version of this capability with the BEGIN ATOMIC statement. Here's an example from a command line session under Linux: $ db2 -td! (c) Copyright IBM Corporation 1993,2002 Command Line Processor for DB2 SDK 8.2.0 ... [boring help snipped] ... db2 => BEGIN ATOMIC db2 (cont.) => DECLARE SAVEDUSERID INTEGER; db2 (cont.) => END! DB20000I The SQL command completed successfully. This construct is like declaring a stored procedure in that each statement within the block must be terminated with semi-colon, while the block as a whole counts as a single SQL statement and must be terminated with some alternate character (hence why I used the -td! switch in the example above to set the statement terminator to bang). Where it differs from a stored procedure is that the ATOMIC keyword after BEGIN is mandatory. ATOMIC indicates that the entire block of instructions will be executed in a single transaction (hence "atomic"). Therefore, you can't use COMMIT / ROLLBACK within the block (only outside it). However, I suspect mere syntactic differences are not the major problem here. You're thinking of SQL in a procedural manner (which I guess is perfectly fine for SQL Server but will complicate things for you horribly in DB2). You need to think of SQL as a "functional" language, not an "imperative" (procedural) language. Therefore, instead of writing something like this: BEGIN ATOMIC DECLARE var1 INTEGER; DECLARE var2 INTEGER; SET var1 = (SELECT afield FROM table1); SET var2 = (SELECT anotherfield FROM table2 WHERE yetanotherfield = var1); INSERT INTO table3 VALUES (var2); END! It'd be considered a lot more "normal" (at least, under DB2) to write something like this: INSERT INTO table3 SELECT anotherfield FROM table2 WHERE yetanotherfield = (SELECT afield FROM table1); If you're familiar with functional programming (Lisp, Haskell, ML, etc.), note the similarities: * No variable declarations * Expressions wrapped within each other (SELECT in a SELECT in an INSERT) instead of separate statements executed in an explicit order * Execution order determined "naturally" (i.e. evaluation of the outer most expression implicitly evaluates inner expressions) If you want to become comfortable with DB2's implementation of SQL, you need to start thinking in this "functional" manner. That's not to say it's all like this; as you've already discovered, there are stored procedures, triggers and such like which are fairly procedural in their nature. You might be able to get away with the BEGIN ATOMIC statement mentioned above for a lot of things, but I'd encourage you to avoid it wherever possible. As Knut mentioned in his post the DB2 optimizer will work a lot better without procedural logic (again, this ties into the functional programming analogy). Don't give up on the VALUES expression either. The VALUES expression allows you to generate a constant set (scalar or vector) within SQL. For example: db2 => VALUES 1; 1 ----------- 1 1 record(s) selected. db2 => VALUES 1, 2; 1 ----------- 1 2 2 record(s) selected. db2 => VALUES ('A', 1), ('B', 2), ('C', 3); 1 2 - ----------- A 1 B 2 C 3 3 record(s) selected. To answer your question about changing the names of the fields generated by the VALUES expression: SELECT * FROM ( VALUES ('A', 1), ('B', 2), ('C', 2) ) AS TEMP(LETTER, NUMBER); LETTER NUMBER ------ ----------- A 1 B 2 C 2 3 record(s) selected. VALUES itself has no way of controlling the names of the fields of the set it creates, but the fields can be aliased by the enclosing expression (in this case a SELECT expression). The above example could also be written using "common table expressions" (something introduced in ANSI SQL-99, and implemented in DB2 v6 (?) if I recall correctly): WITH TEMP(LETTER, NUMBER) AS ( VALUES ('A', 1), ('B', 2), ('C', 2) ) SELECT * FROM TEMP; LETTER NUMBER ------ ----------- A 1 B 2 C 2 3 record(s) selected. Common table expressions can make a query involving a lot of sub-SELECTs a hell of a lot more readable by defining all the sub-SELECTs before the main body of the query. Ahh, I've just read that common table expressions have been added to the latest version of SQL Server (2005?), so maybe you're familiar with them already? Incidentally, the VALUES expression as detailed above, and common table expressions are not available on DB2 for z/OS, just the Linux / Unix / Windows version. Weird. Anyway, hopefully the above will be enough to get one or two (perhaps even three!) lines of SQL working in DB2 :-) HTH, Dave.
From: Stefan Momma on 7 Mar 2006 19:56 Ian Boyd wrote: > i'm trying to get a super-primer on DB2-SQL, so i can get something up and > running. My favourite reference for DB2 SQL is Graeme Birchall's DB2 SQL Cookbook, which is available from this website: http://mysite.verizon.net/Graeme_Birchall/id1.html hope this helps, -- stefan
From: Serge Rielau on 7 Mar 2006 20:26 OK.. 9 days of labour.. some children appear to cause more trouble than others ;-) Procedural statements are not supported as independent statements by DB2. That is you can do: CREATE..., DROP.., GRANT, REVOKE, ALTER DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from CLP) UPDATE,DELETE, INSERT, MERGE CALL BEGIN ATOMIC .. END That's it! DECLARE variable, SET statement, etc are not 'real' SQL statement. They must be NESTED in a procedure, trigger, function or said BEGIN ATOMIC So if you want to run a script with logic from the client you have to use BEGIN ATOMIC .. END. Now talking of semicolons. The DB2 engine knows semicolon only inside of procedures. However semicolon is often also used by query tools as 'go'. So what happens is that the query tools is chopping up the procedure (or trigger ...) and sends pieces of the statement, which of course cause -104 syntax error (unexpected end of statement) Now I'm somewhat unclear on what query tool you are using. E.g. the IBM provided tools such as the JDBC Type 4 driver (AFAIK) detect the BEGIN ATOMIC, an CREATE PROCEDURE keywords and suppress the batching. Could it be you are using some MS driver which is ignorant to DB2? Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Serge Rielau on 7 Mar 2006 20:29
Stefan Momma wrote: > Ian Boyd wrote: > >> i'm trying to get a super-primer on DB2-SQL, so i can get something up >> and running. > > My favourite reference for DB2 SQL is Graeme Birchall's > DB2 SQL Cookbook, which is available from this website: > > http://mysite.verizon.net/Graeme_Birchall/id1.html > > hope this helps, > > -- stefan BTW, when looking for DB2 looks (or IBM published books in general) this is the place to go: http://www.redbooks.ibm.com/ The books are free for download as PDF. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |