How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Knut Stolze on 7 Mar 2006 15:14 Ian Boyd wrote: > 1. Selecting a value > > SQL Server: >> SELECT 'Hello, world!' >> In the DB2 world, you use: >> VALUES 'Hello World' > > How about aliasing field names? There are no "fields" in SQL - just rows, columns and values. >> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage The question is what the column name will be good for. If you get just one row even one value, you usually don't need to name it. And if you have more, you usually have an application dealing with the data. So renamed columns are in my opinion only worthwhile for sub-queries. > i assume that i should be using a SELECT, and not VALUES >> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1 Yes, to rename a column you have to have a SELECT statement. > 2. Declaring Variables > >> BEGIN >> DECLARE SaverUserID INT; >> END BEGIN ATOMIC DECLARE i INT; END@ > i throught that the field type in DB2-SQL was INTEGER. No? Data type. > 3. Returning a value > SQL Server: >>> SELECT @@spid AS ProcessID >>> or >>> DECLARE @ProcessID int >>> SET @ProcessID = @spid >>> SELECT @ProcessID >> >> Again, use VALUES. > > Again, i assume that VALUES is a bad thing: How so? >>BEGIN >> SET ApplicationID = VALUES Application_ID(); SET ApplicationID = Application_ID(); >>END >>BEGIN >> SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1; >>END Just nest this into a single statement: VALUES application_id() > Complaining about me ending my statement after the SELECT, that that it is > expecting a JOIN. Should be joining to something. Do i need to also join > to a dummy table? e.g. No, the join is just a suggestion telling you that how the statement _could_ continue. The problem is actually that you have a syntactically incorrect statement because the END keyword is missing. > (NOTE: Anyone who knows T-SQL will recognize getdate(), USER_NAME(), > HOST_NAME(), APP_NAME(). i assume that DB2-SQL has some built-in function > to get the current date/time. i also assume DB2-SQL has no built-in > function to get the current Username, MachineName or AppName) Have a look at the DB2 special registers in the manual: USER, CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP. > Aside from the SQL syntax stuck in there (because i can't get enough far > enough to debug it), is that a valid syntax for doing an insert into a > table in DB2 when not using VALUES? > > By values i mean: > INSERT INTO foo (Field1, Field2, ..., FieldN) > VALUES (Value1, Value2, ..., ValueN); > > Put it another way, is this a valid syntax in DB2-SQL: > >> INSERT INTO foo (Field1, Field2, ..., FieldN) >> SELECT Value1, Value2, ..., ValueN >> FROM MyTable >> WHERE ... Works both. > Finally, is that the valid way to alias tables in DB2-SQL? >> FROM Inserted i > will that work, or do i have to do something like: >> FROM Inserted AS i Works both. Have a look at the syntax for the sub-select statement. > Finally, does DB2 support derived tables > >> SELECT myo.*, MyDerivedTable.* >> FROM MyTableOne mto >> INNER JOIN (SELECT * FROM MyTableTwo mtt >> WHERE mtt.Field4 = 'Testing') MyDerivedTable >> mto.SomeJoinField = MyDerivedTable.AnotherJoinField This are sub-queries and DB2 supports them. Have a look at the syntax diagram for queries. > But originally, and most importantly, what's wrong with: > > DECLARE SomeNumber INT; Nothing. You just have to use it in the correct and valid context. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Ian Boyd on 7 Mar 2006 14:57 > or use a table constructor: VALUES 'abc' Ahhh. "Table constructor." i get it now. It doesn't declare constants, it creates an intermediate table. That will require some new thinking on what problems i can solve with it. > DB2: > DECLARE SavedUserID integer; > Error: SQL0104N An unexpected token "integer" was found following " > DECLARE > SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE > NUMBER=1. SQLSTATE=42601 > (State:42601, Native Code: FFFFFF98) >Your problem here is probably that you did not explicitly specify a >statement terminator. So the end-of-line terminates your SQL statement, >and that leaves 'integer;' as a ...something... where DB2 rightfully >complains about. Try the -t option of the "db2" command line instead (or >search through the menues if you are using the Command Editor). i'm using neither. i'm using an ODBC connection, and issuing commands that way. But now that you mention it, i'll try Command Editor. i notice that Command Editor shows at the bottom a "Statement termination character" defaulted to semi-colon. And it still doesn't work. The help also says that DB2 understands CR as a statement terminator, and that i should not use a statement termination character inside a CREATE PROCEDURE or CREATE TRIGGER. So i'll stop using it. > DECLARE SavedUserID int SQL0104N An unexpected token "integer" was found following "DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT i try changing the "Statement termination character" to @ and i change my SQL Statement to: > DECLARE SavedUserID integer@ SQL0104N An unexpected token "integer" was found following "DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT > 2. Setting a variable > SQL Server: > DECLARE @ProcessID int > SET @ProcessID = @spid > or > SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid = > @@spid) > or (depricated) > SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid > > DB2: > DECLARE ApplicationID varchar(128) --can't declare variables > SET ApplicationID = Application_ID() > or > DECLARE ApplicationID varchar(128) --can't declare variables > SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1); > First question in a set-oriented language like SQL would be: what do you > want to do with the value that you really need procedural logic here. Honestly, so that while i'm writing and testing the stored procedure, i can check that i have managed to fetch the propert value. i would fetch the value into a variable, and then select it, seeing that i got it. (Although, doing a select into a procedure is a whole thing, not just a simple SELECT - you have to declare cursors, modify the procedure header to say that i'll be returning rows, etc) Also, i want a variable because my audit logging for UPDATE will contain an equal number of insert statements as there are fields in the table (well, almost equal). So rather than DB2 having to join for the same value every time, i will grab it once and then kept it stored. i am not after what i should be doing, i'm looking for the DB2-SQL syntax to perform common operations. i won't detail every form of query i have ever written and why those queries got the solution they did. i'm trying to get a super-primer on DB2-SQL, so i can get something up and running. 3. Returning a value SQL Server: > SELECT @@spid AS ProcessID > or > DECLARE @ProcessID int > SET @ProcessID = @spid > SELECT @ProcessID How do i declare, set and fetch local variables? What would be syntax to do that? > And finally, the full trigger i'm trying to create in DB2 that i can't > can't make work. > >> >> Error: SQL0104N An unexpected token "integer" was found following " >> DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT". >> LINE NUMBER=10. SQLSTATE=42601 >> (State:42601, Native Code: FFFFFF98) > >Now that is really a problem with the statement terminator. DB2 takes the >first ';' as end of the statement so that you will have a syntax error >right away. That's why you see quite ofter the '@' being used as statement >terminator here. Do you mean in general other people who write triggers? Or is the symbol '@' (commerical at sign) not coming through the news server correctly - i don't see any '@' as my statement terminator. > I would write your trigger like this: > CREATE TRIGGER ... > No variables needed in the first place and you give the DB2 optimizer a > much > better chance to do a good job without the procedural logic. How would you translate this trimmed down version of a trigger from SQL Server? (You don't really have to, i'm just showing what i will be writing after i can figure out how to declare a variable, and finish tackling the trivial job of writing an INSERT audit logging trigger) CREATE TRIGGER LogUpdate_Quotes ON Quotes FOR UPDATE AS /* Load the saved context info UserGUID */ DECLARE @SavedUserGUID uniqueidentifier SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier) FROM master.dbo.sysprocesses WHERE spid = @@SPID INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), i.QuoteGUID, 'UPDATED', USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, 'Quotes', 'Tax2', i.ProjectGUID, i.QuoteNumber, CAST(d.Tax2 AS varchar(8000)), CAST(i.Tax2 AS varchar(8000)) FROM Inserted i INNER JOIN Deleted d ON i.QuoteGUID = d.QuoteGUID WHERE (d.Tax2 IS NULL AND i.Tax2 IS NOT NULL) OR (d.Tax2 IS NOT NULL AND i.Tax2 IS NULL) OR (d.Tax2 <> i.Tax2) /* GrandTotal money */ INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), i.QuoteGUID, 'UPDATED', USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, 'Quotes', 'GrandTotal', i.ProjectGUID, i.QuoteNumber, CAST(d.GrandTotal AS varchar(8000)), CAST(i.GrandTotal AS varchar(8000)) FROM Inserted i INNER JOIN Deleted d ON i.QuoteGUID = d.QuoteGUID WHERE (d.GrandTotal IS NULL AND i.GrandTotal IS NOT NULL) OR (d.GrandTotal IS NOT NULL AND i.GrandTotal IS NULL) OR (d.GrandTotal <> i.GrandTotal) ...74 fields ommitted... /* TaxScheduleGUID uniqueidentifier */ INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), i.QuoteGUID, 'UPDATED', USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, 'Quotes', 'TaxScheduleGUID', i.ProjectGUID, i.QuoteNumber, (SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = d.TaxScheduleGUID), (SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = i.TaxScheduleGUID) FROM Inserted i INNER JOIN Deleted d ON i.QuoteGUID = d.QuoteGUID WHERE (d.TaxScheduleGUID IS NULL AND i.TaxScheduleGUID IS NOT NULL) OR (d.TaxScheduleGUID IS NOT NULL AND i.TaxScheduleGUID IS NULL) OR (d.TaxScheduleGUID <> i.TaxScheduleGUID) i specifially chose one of the widest tables i had, to demonstrate the volume of repeative inserts. For some reason everyone in DB2 world prefers for "For Each Row" rather than the "For the Statement" style of triggers. Seems pretty inefficient to run the same trigger statement for each row affected, when you can run it once for all of them. i'm assuming that DB2, like all RDMS's are set-based, and any row-by-row/cursor operations are a waste. But it also makes trying to learn DB2-SQL when everyone prefers the simpler row-by-row triggers. My thinking with creating a variable was trying to save DB2 from having to construct and join to a virtual table over and over. So, i query for the value once, rather than forcing DB2 to do it over and over. But even more than that, i want to learn DB2-SQL. And one of the constructs i am trying to learn is declaring a variable and using it.
From: Knut Stolze on 7 Mar 2006 15:26 Ian Boyd wrote: >> or use a table constructor: > VALUES 'abc' > > Ahhh. "Table constructor." i get it now. It doesn't declare constants, it > creates an intermediate table. That will require some new thinking on what > problems i can solve with it. That's the relational model: everything is a table. Period. ;-) >> DECLARE SavedUserID int > SQL0104N An unexpected token "integer" was found following "DECLARE > SavedUserID". > Expected tokens may include: "END-OF-STATEMENT > > i try changing the "Statement termination character" to @ and i change my > SQL Statement to: >> DECLARE SavedUserID integer@ > > SQL0104N An unexpected token "integer" was found following "DECLARE > SavedUserID". Expected tokens may include: "END-OF-STATEMENT On the command line, you have to embed the DECLARE into a atomic compound statement. BEGIN ATOMIC ... END > Also, i want a variable because my audit logging for UPDATE will contain > an equal number of insert statements as there are fields in the table > (well, almost equal). So rather than DB2 having to join for the same value > every time, > i will grab it once and then kept it stored. i am not after what i should > be doing, i'm looking for the DB2-SQL syntax to perform common operations. > i won't detail every form of query i have ever written and why those > queries got the solution they did. > 3. Returning a value > SQL Server: >> SELECT @@spid AS ProcessID >> or >> DECLARE @ProcessID int >> SET @ProcessID = @spid >> SELECT @ProcessID > > How do i declare, set and fetch local variables? What would be syntax to > do that? It's relational: Build a table, open a cursor and fetch from the table. Then there is some syntactic sugar to simplify this a bit like: SET ( var1, var2, var3 ) = ( val1, val2, val3 ) > Do you mean in general other people who write triggers? I was referring to the posts in this newsgroup. >> I would write your trigger like this: >> CREATE TRIGGER ... >> No variables needed in the first place and you give the DB2 optimizer a >> much >> better chance to do a good job without the procedural logic. > > How would you translate this trimmed down version of a trigger from SQL > Server? What's trimmed down? It does the same thing unless there is more in the SQL Server trigger going. > i specifially chose one of the widest tables i had, to demonstrate the > volume > of repeative inserts. For some reason everyone in DB2 world prefers for > "For Each Row" > rather than the "For the Statement" style of triggers. Seems pretty > inefficient to run the > same trigger statement for each row affected, when you can run it once for > all of them. The thing is than DB2 compiles the trigger into the INSERT statement itself. So doing things "for each row" is not slower than your way - I would guess that it is even faster because no temp tables will be needed. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Serge Rielau on 7 Mar 2006 15:37 Ian, (I'm consciously not quoting anything.. fresh start) DB2 supports variables in the context of stored procedures, functions, methods, triggers and "dynamic compound statements". DB2 does not support "global variables" (and I thought SQL Server doesn't either, so I'm surprised you have an issue. DB2 supports the SQL/PSM standard for procedural logic. To learn about this language I strongly recommend: "DB2 SQL PL" by Paul Yip et al: http://btobsearch.barnesandnoble.com/booksearch/isbninquiry.asp?btob=Y&cds2Pid=9030&isbn=0131477005 I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1 row. The VALUES clause is actually very powerful when embedded in the from clause. E.g. instead of doing: T-SQL: SELECT * FROM (SELECT 5 UNION SELECT 6) AS X you can do: SELECT * FROM (VALUES (5), (6)) AS X. This T-SQL: SELECT @@spid AS ProcessID is NOT column aliasing. This is a SET statement. You can either use: SET ProcessID = spid; or VALUES spid INTO processID; Note the INTO clause. The same principle works for this T-SQL consruct: SELECT SavedUserID = User_ID FROM Connection_Users WHERE Application_ID = Application_ID(); In the SQL Standard (and thus DB2): SELECT User_ID INTO SavedUserID FROM Connection_Users WHERE Application_ID = Application_ID(); There in one difference though: while SQL Server will quietly reyurn any value if your where clause matches more than one row, DB2 will riot if more than one row is return (rightly so). 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). With the CLP you can set the delimiter with db2 -td<character>. E.g. db2 -td@ You can also change the delimiter on the fly in CLP using --#SET TERMINATOR <character> GUIs (such as control center) typically have a preference that can be set. DB2 supports limited(!) scripting capabilities using dynamic compound (in Oracle this would be called an "anonymous block"). Again I'm surprised you try this since AFAIK SQL Server supports no such thing. all T-SQL must be in a procedure. BEGIN ATOMIC -- Note the ATOMIC keyword! DECLARE a INTEGER DEFAULT 5; WHILE A < 5 DO SET a = a + 5; CALL proc(a); END WHILE; END So let's move into a procedure example: db2 -t --#SET TERMINATOR @ DROP PROCEDURE dostuff @ CREATE PROCEDURE dostuff(IN a INTEGER, INOUT b INTEGER, OUT c INTEGER) BEGIN DECLARE d INTEGER; DECLARE cur CURSOR WITH RETURN FOR SELECT TABNAME FROM SYSCAT.COLUMNS FETCH FIRST 2 ROWS ONLY; SET d = a + b; SET c = d * a; SET b = 7; OPEN cur; END @ CALL dostuff(5, 7, ?)@ --- db2 => CALL dostuff(5, 7, ?)@ Value of output parameters -------------------------- Parameter Name : B Parameter Value : 7 Parameter Name : C Parameter Value : 60 Result set 1 -------------- TABNAME ---------------------------- COLDIST COLDIST 2 record(s) selected. Return Status = 0 OK I think that covers it. Let us know how it goes. Cheers Serge PS: One more thing..... the SQL/PSM standard (which is the foundation for DB2's SQL procedures) uses exception handlers for error handling. Do NOT overload them to emulate old style T-SQL. Use them just like in SQL Server 2005 Microsoft encourages you to use exception handlers. -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Serge Rielau on 7 Mar 2006 15:51
Ian Boyd wrote: > i specifially chose one of the widest tables i had, to demonstrate the > volume > of repeative inserts. For some reason everyone in DB2 world prefers for "For > Each Row" > rather than the "For the Statement" style of triggers. Seems pretty > inefficient to run the > same trigger statement for each row affected, when you can run it once for > all of them. Au contraire! This code from your T-SQL trigger in inefficient. FROM Inserted i INNER JOIN Deleted d ON i.QuoteGUID = d.QuoteGUID In a FOR EACH ROW trigger the new and old transition variables are already matched. Also a FOR EACH ROW trigger does not necessarily have to produce any inserted and deleted temporary tables. Instead it can pipeline. A straight forward audit trigger has a cost which is virtually identical to the cost of the individual inserts. It is ironic that in most OLTP systems the number of rows changes with one statement is 1 anyway, so a statement trigger would execute only for one row. talking of triggers, you may also want to familiarize yourself with BEFORE triggers. Very powerful and much more lightweight than patching up the rows after the update/insert. (they come at the cost of the evaluation of the expression) CREATE TRIGGER trg1 BEFORE UPDATE ON T FOR EACH ROW REFERENCING NEW AS n OLD AS o WHEN (n.c1 <= o.c1) SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!' -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |