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 11:39 i've been thrown into a pit with DB2 and have to start writing things such as tables, indexes, stored procedures, triggers, etc. The online reference is only so helpful. The two pdf manuals are only so helpful. Googling is only so helpful. So let's start with some simple SQL constructs, that i know so very well in SQL Server, that seem to be like pulling teeth in DB2. 1. Selecting a value SQL Server: SELECT 'Hello, world!' (1 row(s) affected) DB2: SELECT 'Hello, world!' Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "LECT 'Hello, world!'". Expected tokens may include: "<table_expr>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) SELECT 'Hello, world!' FROM SYSIBM.SysDummy1 1 Row(s) affected Is there a SysDummy2? 3? Why? 1. Declaring a variable SQL Server: DECLARE @SavedUserID int The command(s) completed successfully. 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) 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); 3. Returning a value SQL Server: SELECT @@spid AS ProcessID or DECLARE @ProcessID int SET @ProcessID = @spid SELECT @ProcessID DB2 SELECT Application_ID() Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ect application_ID()". Expected tokens may include: "<table_expr>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) or SELECT Application_ID() FROM IBM.SysDummy1 --SysDummy2 or DECLARE ApplicationID varchar(128) --can't declare variables SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1); SELECT ApplicationID 3. Returning rows from a stored procedure SQL Server CREATE PROCEDURE foo AS SELECT @@spid AS ProcessID DB2 CREATE PROCEDURE foo DYNAMIC RESULT SETS 1 LANGUAGE SQL P1: BEGIN --declare the cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT Application_ID() FROM SYSIBM.SYSDUMMY1; -- Cursor left open for client application OPEN cursor1; END P1 Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN <joined_table>". LINE NUMBER=7. SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty. SQLSTATE=42617 (State:42617, Native Code: FFFFFF3A) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) And finally, the full trigger i'm trying to create in DB2 that i can't can't make work. CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS --"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2 AFTER INSERT ON SUPERDUDE.DAILY_LOGS REFERENCING NEW_TABLE AS INSERTED FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC -- Load the saved UserID DECLARE SavedUserID integer; SELECT SavedUserID = User_ID FROM Connection_Users WHERE Application_ID = Application_ID(); INSERT INTO Audit_Log( ChangeDate, RowID, ChangeType, -- Username, HostName, AppName, UserID, TableName, FieldName, TagID, Tag, OldValue, NewValue) SELECT getdate(), i.Daily_Log_ID, 'INSERTED', -- USER_NAME(), HOST_NAME(), APP_NAME(), SavedUserID, 'Daily_Logs', --TableName '', --FieldName NULL, --TagID i.Name, --Tag '', --OldValue '' --NewValue FROM Inserted i; END; 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) Error: SQL0104N An unexpected token "=" was found following "SELECT SavedUserID ". Expected tokens may include: "<space>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0204N "SUPERDUDE.INSERTED" is an undefined name. SQLSTATE=42704 (State:42704, Native Code: FFFFFF34) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98)
From: Brian Tkatch on 7 Mar 2006 12:12 >So let's start with some simple SQL constructs, that i know so very well in >SQL Server, that seem to be like pulling teeth in DB2. Though i agree that DB2 can be tedious and confusing, note that SQL Server is super easy, and it pretty much breaks the standard to do it. So, give db2 a shot. :) As long as you come with the attitude that it can be done, you just don't know how, the users of this group are very helpful. >1. Selecting a value SQL Server: SELECT 'Hello, world!' This is wholly incorrect. It is not SQL whatsoever. It is a convenience added by Sybase/SQL Server. In the DB2 world, you use: VALUES 'Hello World' > SELECT 'Hello, world!' FROM SYSIBM.SysDummy1 > 1 Row(s) affected > > >Is there a SysDummy2? 3? Why? Compatability with other systems, that do not use a special statement, and require *all* statments to include a TABLE reference. Oracle supplies a one-record TABLE called Dual. Other systems use other names. >1. Declaring a variable > >SQL Server: > DECLARE @SavedUserID int > The command(s) completed successfully. Drop the useless @ symbol, and end all statement with a semi-colon. Also, there is no implicit block of code, so you must start your own. BEGIN DECLARE SaverUserID INT; END >3. Returning a value >SQL Server: > SELECT @@spid AS ProcessID > or > DECLARE @ProcessID int > SET @ProcessID = @spid > SELECT @ProcessID Again, use VALUES. >3. Returning rows from a stored procedure >SQL Server > CREATE PROCEDURE foo AS > SELECT @@spid AS ProcessID >DB2 > CREATE PROCEDURE foo > DYNAMIC RESULT SETS 1 > LANGUAGE SQL > P1: BEGIN > --declare the cursor > DECLARE cursor1 CURSOR WITH RETURN FOR > SELECT Application_ID() FROM SYSIBM.SYSDUMMY1; > -- Cursor left open for client application > OPEN cursor1; > END P1 db2 => create function application_id() returns int return 1 DB20000I The SQL command completed successfully. db2 => CREATE PROCEDURE foo \ db2 (cont.) => DYNAMIC RESULT SETS 1 \ db2 (cont.) => LANGUAGE SQL \ db2 (cont.) => P1: BEGIN \ db2 (cont.) => --declare the cursor \ db2 (cont.) => DECLARE cursor1 CURSOR WITH RETURN FOR \ db2 (cont.) => SELECT Application_ID() FROM SYSIBM.SYSDUMMY1; \ db2 (cont.) => -- Cursor left open for client application \ db2 (cont.) => OPEN cursor1; \ db2 (cont.) => END P1 DB20000I The SQL command completed successfully. Works for me. :) >And finally, the full trigger i'm trying to create in DB2 that i can't can't >make work. I have little experience with TRIGGERs in DB2. I'll leave that to someone else. :) B.
From: ML on 7 Mar 2006 13:08 What's the fish smell??? Oh, nevermind, just a troll. -- ML
From: Ian Boyd on 7 Mar 2006 14:02 1. Selecting a value SQL Server: > SELECT 'Hello, world!' > In the DB2 world, you use: > VALUES 'Hello World' How about aliasing field names? > SELECT 'Hello, world!' AS MyLovelyWelcomeMessage i try: > VALUES 'Hello, world!' AS MyLovelyWelcomeMes Error: SQL0104N An unexpected token "AS" was found following "LUES 'Hello, world!'". Expected tokens may include: "INTO". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) i assume that i should be using a SELECT, and not VALUES > SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1 2. Declaring Variables > BEGIN > DECLARE SaverUserID INT; > END Doesn't work for me: Error: SQL0104N An unexpected token "DECLARE" was found following "BEGIN ". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) i throught that the field type in DB2-SQL was INTEGER. No? Really scary when i can't get 3 lines to work. So i try: > DECLARE SaverUserID INT; And now i can't even get one line to work. (Yes, frustrating) Error: SQL0104N An unexpected token "INT" was found following "DECLARE SaverUserID". Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) 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: > VALUES Application_ID() AS ProcessID Error: SQL0104N An unexpected token "AS" was found following "UES Application_ID()". Expected tokens may include: "->". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) And that i really should be using SELECT > SELECT Application_ID() AS ApplicationID FROM SYSIBM.SysDummy1 which does work, but i'd really like to know how to declare variables, set variables and return variables. So i try: >BEGIN > DECLARE ApplicationID varchar(128); >END >BEGIN > SET ApplicationID = VALUES Application_ID(); >END >BEGIN > SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1; >END Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "END" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<update>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "END BEGIN" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) So i'm pretty much stuck. Three lines, and i can't any one work. Note the heavy use of BEGIN/END because "there is no implicit block of code, so you must start your own." Surely that can't be the syntax i have to use. i would have thought that the following would have been enough, but i can't really tell since i get the errors: >BEGIN > DECLARE ApplicationID varchar(128); > SET ApplicationID = VALUES Application_ID(); > SELECT ApplicationID AS ApplicationID > FROM SYSIBM.SysDummy1; >END Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "SET ApplicationID =" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0206N "APPLICATIONID" is not valid in the context where it is used. SQLSTATE=42703 (State:S0022, Native Code: FFFFFF32) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) 3. Returning rows from a stored procedure >DB2 > CREATE PROCEDURE foo > DYNAMIC RESULT SETS 1 > LANGUAGE SQL > P1: BEGIN > --declare the cursor > DECLARE cursor1 CURSOR WITH RETURN FOR > SELECT Application_ID() FROM SYSIBM.SYSDUMMY1; > -- Cursor left open for client application > OPEN cursor1; > END P1 > Works for me. :) Any idea why it doesn't work for me? Any idea what the error message is trying to say: Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN <joined_table>". LINE NUMBER=7. SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty. SQLSTATE=42617 (State:42617, Native Code: FFFFFF3A) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) 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. >CREATE PROCEDURE foo > DYNAMIC RESULT SETS 1 > LANGUAGE SQL >P1: BEGIN > DECLARE cursor1 CURSOR WITH RETURN FOR > SELECT Application_ID() FROM SYSIBM.SYSDUMMY1 > FULL OUTER JOIN SYSIBM.SYSDUMMY1 ON (1=1); > OPEN cursor1; >END P1 No, that doesn't work: Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "IBM.SYSDUMMY1 ON (1=1)". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=7. SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "OPEN cursor1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601 (State:42601, Native Code: FFFFFF98) >>And finally, the full trigger i'm trying to create in DB2 that i can't >>make work. >I have little experience with TRIGGERs in DB2. I'll leave that to someone >else. :) That's fine, pretend it's not a trigger. Pretend it's just a regular query: >-- Load the saved UserID >DECLARE SavedUserID integer; i can't get any variant of any variable declaration to work. Any ideas? > SELECT SavedUserID = User_ID > FROM Connection_Users > WHERE Application_ID = Application_ID(); Is this valid DB2-SQL syntax to put a value into a variable? i can't really test it, since i cannot declare variables. Or is it invalid syntax, and i should be using: > SET SavedUserID = SELECT User_ID > FROM Connection_Users > WHERE Application_ID = Application_ID(); or should i be using VALUES along the lines of: > SET SavedUserID = VALUES Application_ID; i guess, which is preferred? Again, i can't test anything, because i cannot declare variables. > INSERT INTO Audit_Log( > ChangeDate, > RowID, > ChangeType, > -- Username, HostName, AppName, > UserID, > TableName, > FieldName, > TagID, > Tag, > OldValue, > NewValue) > SELECT > getdate(), > i.Daily_Log_ID, > 'INSERTED', > -- USER_NAME(), HOST_NAME(), APP_NAME(), > SavedUserID, > 'Daily_Logs', --TableName > '', --FieldName > NULL, --TagID > i.Name, --Tag > '', --OldValue > '' --NewValue > FROM Inserted i; (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) 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 ... 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 or is it some other syntax, maybe more like Java, which IBM seems to live: > FROM (i)Inserted 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 But originally, and most importantly, what's wrong with: DECLARE SomeNumber INT; i think if i can get that working, i'll knock back a bottle of scotch and call today very productive. If i can get a declare working, i'll have written one line of DB2-SQL in 2 weeks of work.
From: Ian Boyd on 7 Mar 2006 14:04
> What's the fish smell??? Oh, nevermind, just a troll. Hey, i honestly tried to be very very nice. i revised my draft post to removed little comments. i don't care if T-SQL prefixes variables with @ and DB2-SQL finishes each line with ; i don't care if T-SQL sticks me with all CAPS 18-character table names. There are things that DO bug me, but i won't bother anyone with my grumblings - my grumblings are my own (and many other peoples). |