 |
 | 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 / April 2006How to do...well...anything...in DB2 SQL
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) >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: [quoted text clipped - 3 lines] > 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 [quoted text clipped - 7 lines] > 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. 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 [quoted text clipped - 3 lines] > > 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); [quoted text clipped - 5 lines] > 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 [quoted text clipped - 8 lines] > 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 [quoted text clipped - 5 lines] > 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, [quoted text clipped - 21 lines] > '' --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. > 1. Selecting a value > [quoted text clipped - 4 lines] > > 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: [quoted text clipped - 7 lines] > > 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 [quoted text clipped - 10 lines] >> 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 > [quoted text clipped - 3 lines] >> 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.  SignatureKnut Stolze DB2 Information Integration Development IBM Germany > There are no "fields" in SQL - just rows, columns and values. You channeling Celko? :) > 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. If there are multiple fields (a.k.a. columns) in the returned records (a.k.a. rows), and the client accesses fields (a.k.a columns) by name, then each field (a.k.a column) will need a name. Even if there is only one field (a.k.a column) in the returned records (a.k.a rows), and the client can only access fields (a.k.a columns) by name, that field (a.k.a column) will stil need a name. > Yes, to rename a column you have to have a SELECT statement. > 2. Declaring Variables [quoted text clipped - 6 lines] > DECLARE i INT; > END@ Ah-hah! "BEGIN ATOMIC" >> i throught that the field type in DB2-SQL was INTEGER. No? i see it now in the online help. INT is an alias for Integer field type. 3. Returning a value >> SQL Server: >>>> SELECT @@spid AS ProcessID [quoted text clipped - 6 lines] >> Again, i assume that VALUES is a bad thing: > How so? Because rather than using VALUES: > SET ApplicationID = VALUES Application_ID(); i don't use VALUES: > SET ApplicationID = Application_ID(); Keep in mind that you're responding to a post i made talking to the other guy, before it was explained that VALUES constructs a virtual table. > Have a look at the DB2 special registers in the manual: USER, CURRENT > DATE, > CURRENT TIME, CURRENT TIMESTAMP. Excellent. Thank you. So now i'm getting closer to a compiling trigger. Next question 5. Setting a field (a.k.a column) to NULL SQL Server > UPDATE MyTable SET SomeField = NULL > WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3) or > INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) > VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184') or > INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) > SELECT fname, lname, addr1, NULL, NULL FROM legacy_system In my variant of the 3rd case in DB2, it complains that "NULL is not valid in the context where it is used." >> There are no "fields" in SQL - just rows, columns and values. > You channeling Celko? :) *chuckle* I had the same thought. Seriously though it doesn't hurt to use the correct language. Just like it doesn't hurt to speak proper English outside the pub ;-) >> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system > > In my variant of the 3rd case in DB2, it complains that "NULL is not valid > in the context where it is used." DB2 uses strong typing. An untyped NULL (or ?) is only allowed in specific places where DB2 cann immediatly deduce the datatype. That would be UPDATE SET, SET statement and INSERT VALUES. In all other cases CAST(NULL AS <type>) will do the job. That's the way the standard is defined. No technical reason really. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >>> There are no "fields" in SQL - just rows, columns and values. >> You channeling Celko? :) > *chuckle* I had the same thought. > Seriously though it doesn't hurt to use the correct language. > Just like it doesn't hurt to speak proper English outside the pub ;-) Warning. Celko bait ahead: <CelkoBait> Yes. But those are semantics of the most anal kind. i have seen many, many, many posts of Celko explaining how if you confuse a row/record and column/field, you are doing yourself a disservice. We can all agree that someplace on my hard drive is a the value for a particular column of a particular row. And we can all agree to call that a field. Simarly, if i select a specific row from a table, that is a record. Yes, the terms were invented when one table was stored in one file, and the notion of "the next 6 rows" was perfectly valid. Yes, modern databases store things in pages all over the place, but there is still some physical order. The btree has an order. You can't guarantee that order, or ever even see it. But the rows that you return are records. Let us all agree that the terms record/field while originally didn't apply to RDMS's, now do. </CelkoBait> >>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system [quoted text clipped - 7 lines] > In all other cases CAST(NULL AS <type>) will do the job. > That's the way the standard is defined. No technical reason really. Excellent. Thank you. i, of course, would prefer it if DB2 would just do it. But i'm okay with it forcing me to tell it that what it thinks i want to do is really what i want to do. >>>> There are no "fields" in SQL - just rows, columns and values. >>> You channeling Celko? :) [quoted text clipped - 5 lines] > Warning. Celko bait ahead: > <CelkoBait> <snip> > </CelkoBait> Different styles. In this group using the f-word causes a raised eyebrow. Joe i s abit more opinionated. Let me put in another way: There is a _statistical_ correlation between using the professional vocabulary and the skill level. Fields and Records are EXCEL and ACCESS speak. It _suggests_ a technologcal "redneck". In usenet, perception is everything ;-) >>>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >>>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system [quoted text clipped - 10 lines] > But i'm okay with it forcing me to tell it that what it thinks i want to do > is really what i want to do. When the SQL standard was created strong typing was desired. In reality most products have long abandoned strong typing. I agree that DB2 is doing a futile "last stand" here.. one of these days that will be relaxed. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> There are no "fields" in SQL - just rows, columns and values. > You channeling Celko? :) No, not really. I just want to make sure that you know the terminology used by DB2 (and the SQL standard) so that you will have an easier time when looking at the manual. > 3. Returning a value >>> SQL Server: [quoted text clipped - 12 lines] > i don't use VALUES: >> SET ApplicationID = Application_ID(); That doesn't make VALUES a bad thing. It is just not necessary here. > 5. Setting a field (a.k.a column) to NULL > > SQL Server >> UPDATE MyTable SET SomeField = NULL >> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3) Works. > or > >> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) >> VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184') This works too. > or > [quoted text clipped - 3 lines] > In my variant of the 3rd case in DB2, it complains that "NULL is not valid > in the context where it is used." This does not work because NULL is untyped and DB2 does not know if the data types produced by the subselect match with the data types in the table. So try this: INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) SELECT fname, lname, addr1, CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10)) FROM legacy_system Btw, you got the error SQL0206N in this case. You should first look up the error description to correct your problem: ----------------------------------------------------------- $ db2 "? sql0206" SQL0206N "<name>" is not valid in the context where it is used. Explanation: This error can occur in the following cases: o For an INSERT or UPDATE statement, the specified column is not a column of the table, or view that was specified as the object of the insert or update. o For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement. o For an ORDER BY clause, the specified column is a correlated column reference in a subselect, which is not allowed. o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION statement: - The reference "<name>" does not resolve to the name of a column, local variable or transition variable. - The condition name "<name>" specified in the SIGNAL statement has not been declared. o For a CREATE TRIGGER statement: - A reference is made to a column of the subject table without using an OLD or NEW correlation name. - The left hand side of an assignment in the SET transition-variable statement in the triggered action specifies an old transition variable where only a new transition variable is supported. o For a CREATE FUNCTION statement with a PREDICATES clause: - The RETURN statement of the SQL function references a variable that is not a parameter or other variable that is in the scope of the RETURN statement. - The FILTER USING clause references a variable that is not a parameter name or an expression name in the WHEN clause. - The search target in an index exploitation rule does not match some parameter name of the function that is being created. - A search argument in an index exploitation rule does not match either an expression name in the EXPRESSION AS clause or a parameter name of the function being created. o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH clause or the FILTER USING clause references a variable that is not a parameter name that can be used in the clause. The statement cannot be processed. User Response: Verify that the names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause. For a subselect in an ORDER BY clause, ensure that there are no correlated column references. If a correlation name is used for a table, verify that subsequent references use the correlation name and not the table name. For a CREATE TRIGGER statement, ensure that only new transition variables are specified on the left hand side of assignments in the SET transition-variable statement and that any reference to columns of the subject table have a correlation name specified. sqlcode : -206 sqlstate : 42703 ----------------------------------------------------------- Unfortunately, this particular situation is not explained explicitly. So you can only derive that NULL is interpreted as column name. (Note that DB2 allows a column to be named NULL.) SignatureKnut Stolze DB2 Information Integration Development IBM Germany 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=9 030&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. SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > 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. i don't think SQL Server has global variables either - at least i've never seen them. > I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1 > row. That's an interesting insight! > The VALUES clause is actually very powerful when embedded in the from > clause. [quoted text clipped - 6 lines] > you can do: > SELECT * FROM (VALUES (5), (6)) AS X. i've never used any idea similar to (SELECT 5 UNION SELECT 6) AS X i've never had a problem where i needed to join to such virtual table. Like i said before, i'll have to figure out where VALUES would be useful to me. > This T-SQL: SELECT @@spid AS ProcessID > is NOT column aliasing. If i run this query, i get a record set with one row and one column. And that column has no name. If i want to give that column a name (or a different name) i have to use: > SELECT @@spid AS ProcessID So the column gets renamed from "" to "ProcessID." Whereas the following SET operation > SET ProcessID = spid; returns no rows. > 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. So now having to have not only a statement delimiter, and a batch delimiter is painful. > Again I'm surprised you try this since AFAIK SQL Server supports no such > thing. all T-SQL must be in a procedure. No. i can send T-SQL to SQL Server and it runs it. i don't seen it to be inside a transaction or any such "BEGIN ATOMIC". It runs the SQL it is given. For example >DECLARE @a int >SET @a = 5 >WHILE @A < 5 BEGIN > SET @a = @a + 5 > EXECUTE SomeStoredProcedure >END is perfectly valid. i can send the T-SQL to SQL Sever using it's own query tool, or using 3rd party tools, or using ADO. i give it those characters, no semicolons, no go, no begins, end, atomics; and it just runs. > So let's move into a procedure example: Here's an interesting question. The following rus fine, exactly as is: 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 So now: > CALL dostuff(5, 7, ?) Wrong number of parameters > DECLARE c integer; call dostuff(5, 7, ?); An unexpected token "integer" was found following "DECLARE c ". >BEGIN > DECLARE c integer; call dostuff(5, 7, c); >END An unexpected token "DECLARE" was found following "begin " >BEGIN ATOMIC > DECLARE c integer; call dostuff(5, 7, c); >END The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in procedure "DOSTUFF" Why is it that the create procedure doesn't need atomic begins. It doesn't need the keyword atmoc after the begin, and it doesn't need to wrapped wholly in a begin end such as: BEGIN ATOMIC CREATE PROCEDURE dostuff(...) BEGIN END END But my ad-hoc sql does need atomic begins? By the way, this is far as i can get. i don't now how to fix the call to the stored procedure. > 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. A welcome addition. > OK I think that covers it. Let us know how it goes. Time to go home for the day. Day 9, nothing working yet. >> It is important to note that whenever you write a statement that contains >> ';' due to "substatements" you need to make sure the statement delimiter [quoted text clipped - 3 lines] > 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. 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 SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Procedural statements are not supported as independent statements by DB2. > [quoted text clipped - 5 lines] > CALL > BEGIN ATOMIC .. END On some level i wondered if that was how DB2 did it. But i convinced myself that such a scheme is just too complicated to be true, and i assumed that i had some fundamental misunderstanding about what is going on. Is there a complete list somwhere of what i can and cannot run? Specifically, i'm concerned about the "implied" SELECT and VALUES. My confusion comes from the fact that sometimes i can just run SELECT * FROM ..., and other times i have to declare a cursor for a select, and then leave the cursor open. i don't see OPEN in your list. In fact, a quick check of the IBM DB2 Universal Database SQL Reference Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you point me to some references on this? >But i convinced myself that such a scheme is just too complicated to be true, and i assumed that i >had some fundamental misunderstanding about what is going on. Welcome to the non-MS/Windows world. Where you are expected to understand what your are doing. :) >My confusion comes from the fact that sometimes i can just run SELECT * FROM >..., and other times i have to declare a cursor for a select A SELECT statement can be run outside a block of code, a DECLARE within. The difference is, SELECT is a "statement" and DECLARE is a "control statement". Both clearly delineated in SQL Reference Volume 2. Generally, the beginning of the documentate for a particular statement says when it can (and sometimes when it cannot) be executed. B. > Welcome to the non-MS/Windows world. Where you are expected to > understand what your are doing. :) They have databases on computers these days. i know what i'm doing, and computers are powerful enough these days to know as well; or at least proceed with the only possible course of action. >>My confusion comes from the fact that sometimes i can just run SELECT * >>FROM [quoted text clipped - 8 lines] > Generally, the beginning of the documentate for a particular statement > says when it can (and sometimes when it cannot) be executed. That explains why the reference doesn't include some statements, their not the right "kind" of statements. i see the majority of the reference is in a section called "Statements". i don't see a corresponding section of "control statements", nor is the keyword DECLARE in the index. Is there a Reference Volume 3 that documents the "control statements?" Are there are more kinds of statements? [Comicbook Guy] Umm, excuse me. Clearly select is a statement, and declare is a control statement. Thank you. >They have databases on computers these days. i know what i'm doing, and >computers are powerful enough these days to know as well; or at least >proceed with the only possible course of action. The day DBs do things for me, is the day i stop doing databases. I actually despise Windows mostly because of these assumptions. I love DBs, because they are so simple, and make no assumptions. >That explains why the reference doesn't include some statements, their not >the right "kind" of statements. Close. It's because, they are not statements. >i see the majority of the reference is in a section called "Statements". i >don't see a corresponding section of "control statements" In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL control statements". >, nor is the keyword DECLARE in the index. It is absolutely in the index. Though, it is not a bookmark. It is in Chapter 2.=>Compound Statement (Procedure) under "SQL-variable-declaration". A search of the index (which is a bookmark) found it for me pretty easily. B. [snip] > The day DBs do things for me, is the day i stop doing databases. You hand-crank the execution plan for all your queries? Wow ... hardcore man! (joking :-) > I actually despise Windows mostly because of these assumptions. I love > DBs, because they are so simple, and make no assumptions. I'd take issue with this in one particular area. I've always liked that one can tweak just about *any* performance parameter in DB2. That said, I've come to enjoy the ability added in more recent versions to have the tools figure out an "optimum" configuration, or in the most recent versions to just set the parameter to AUTOMATIC and have the database look after itself. I'd be extremely disappointed if such configuration parameters were ever removed completely from manual control ... but I do appreciate a bit of "intelligence" being added to the system, provided it's optional :-) Cheers, Dave. Point taken. :) Obviously the software should do some things, just don;t take my power to change them away. What you said. B. > The day DBs do things for me, is the day i stop doing databases. I > actually despise Windows mostly because of these assumptions. I love > DBs, because they are so simple, and make no assumptions. The running joke at the office is the DB2 "WARNING: Everything is okay!" dialog box. i hit "OK" to save my changes to, for example, a table. Everything saves okay, but DB2 feels compelled to pop up a big dialog with a lot of text in it. Obviously something bad happened, because if everything saved okay after i told you to save, the edit table screen would go way, and we'd be done. So each and every time we see one of these boxes pop up, we have to read it: DB2 Message Commands attempted <some sql that i didn't ever enter> The command completed successfully. Explanation: No errors were encountered during the execution of this command. User Response: No action required. Warning! Everything is okay. It is just as stupid as installing the game Ghost Recon a few years ago, a dialog box pops up, "You have enough free space on this drive to install the game. Proceed?" As if i needed to know that. <StartOfRanging @skip="yes"> Or another one, i want to shrink a varchar field length: DBAINTRN The table and its related objects will be dropped and re-created. Changing the definition of a column attribute involves a LOAD operation. If the database is recoverable, a load copy will be taken for the table being altered. The load copy will be saved as SUPERDUDE.AUDIT_LOG_table.dat. You can delete the load copy file once a backup has been taken for all the related tablespaces. Would you like to proceed? Would i like to proceed? Well, i don't know. i didn't ask you to drop and re-create the table and all it's dependant objects. i told you to shink a column from 128 to 100 characters. You do whatever it is you have to do to do it. Are you trying to ask me if i'm okay with the various steps you have to do to perform that operation? Why wouldn't i be? My only choices are to either save my changes or not. i said save, so do it. Some people will argue, "But the table will have to be dropped and renamed and all keys will recreated. It could potentially be a long operation and maybe the user didn't realize it would take so long to do - so we need to get their permission before doing it. Or worse yet, what if there's a power failure, and something is left in a broken state. At least the user knows that DB2 was performing this relativly big operation. We have to inform the user before we just go ahead and do this." No you don't. i said save, you save. You do whatever it is you do when you have to save. If it takes a long time, i'm okay with that, because i was changing table structure - i'll expect it to take a long time. And with the nulls. Why can't it implicitly cast a NULL to the type of the column? What alternative is there? You are perfectly okay casting other types around, why not NULL? Null is the lack of data, it is nothing, it is not being. People will argue, "But DB2 is a strongly typed system, and null doesn't have the same type as integer. What if the user didn't mean to put a NULL into that column, and we went ahead and did it anyway, that would be bad. If the user really meant to put null in this field they should indicate that by casting it to the proper target data type." No i don't. i said put null in this column. Your choices are to put it in the column, or not. So why would you not do it? Implicitly cast it and get it done. Just do it. i should have to tell twice, when everything knows that's what i want done. Even the village idiot comes to that conclusion. </StartOfRanging> >>That explains why the reference doesn't include some statements, their not >>the right "kind" of statements. > > Close. It's because, they are not statements. i guess this is where some help with examples would be extraordinarily useful. >>i see the majority of the reference is in a section called "Statements". i >>don't see a corresponding section of "control statements" > > In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL > control statements". SQL Reference Volume 1 Contents About this book Chapter 1. Concepts Chapter 2. Language elements Chapter 3. Functions Chapter 4. Queries Appendix A. SQL limits ... Appendix Q. Notices Index Contacting IBM SQL Reference Volume 2 Contents About this book Statements Appendix A. DB2 Universal Databse technical information Appendix B. Notices Index Contacting IBM These are two PDF files that are referred to by the online documentation. i reach the online documentation from Control Center by hitting: Help->About On the web-site, i see Reference SQL How to read the syntax diagrams Common syntax elements Language elements Functions Procedures Queries Statements Reserved schema names and reserved words SQL statements allowed in routines Communications areas, descriptor areas, and exception tables Explain tables Explain register values Japanese and traditional-Chinese extended UNIX code (EUC) considerations Backus-Naur form (BNF) specifications for DATALINKs >>, nor is the keyword DECLARE in the index. > > It is absolutely in the index. Though, it is not a bookmark. > > It is in Chapter 2.=>Compound Statement (Procedure) under > "SQL-variable-declaration". > A search of the index (which is a bookmark) found it for me pretty > easily. i gotta find this book, web-site, pdf, help file, or eBook you got. [..] >>>That explains why the reference doesn't include some statements, their >>>not [quoted text clipped - 68 lines] > > i gotta find this book, web-site, pdf, help file, or eBook you got. Maybe Brian was referring to (an older version of) the SQL Reference for DB2 on zOS ("Mainframe"). The current (v8) version for that book contains the following TOC: SQL Reference Contents About this book Summary of changes to this book Chapter 1. DB2 concepts Chapter 2. Language elements Chapter 3. Functions Chapter 4. Queries Chapter 5. Statements Chapter 6. SQL control statements Appendix A. Limits in DB2 UDB for z/OS Appendix B. Reserved schema names and reserved words Appendix C. Characteristics of SQL statements in DB2 UDB for z/OS Actions allowed on SQL statements SQL statements allowed in external functions and stored procedures SQL statements allowed in SQL procedures Appendix D .... Appendix H. Sample user-defined functions Notices Glossary Bibliography Index Readers' Comments -- We'd Like to Hear from You This book, as all other ones for DB2 v8 for z/OS can be found here: http://www-306.ibm.com/software/data/db2/zos/v8books.html If you want similar documentation for DB2 on Linux, Unix and Windows you have to go here: http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html where you will find the SQL Reference (Vol 1 and 2) you already have. Next to those however, you may want to take a look at the Command Reference. The Master Index may also be usefull for determining which document you need for a specific purpose. HTH. SignatureJeroen [..] > If you want similar documentation for DB2 on Linux, Unix and Windows you > have to go here: [quoted text clipped - 4 lines] > The Master Index may also be usefull for determining which document you > need for a specific purpose. More specific w.r.t. the Command Reference: Chapter 4. Using command line SQL statements SignatureJeroen >was referring to (an older version of) the SQL Reference for DB2 on zOS ("Mainframe"). Version 8 for LUW. Or at least that's what they have on the corparate intranet. B. > If you want similar documentation for Windows you have to go here: > http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html > where you will find the SQL Reference (Vol 1 and 2) you already have. Yes. > Next to those however, you may want to take a look at the Command > Reference. No. > The Master Index may also be usefull for determining which document you > need for a specific purpose. i see my problem now. This "Master Index" contains an index into all other documents. If you click on an index entry, i will load that PDF and jump to the page. In my case, it contains index entries that the target PDF itself doesn't include. My mistake was trying to use the index in "SQL Reference Volume 2 Version 8.2". That was my mistake, that was folly. i was looking for "DECLARE", when instead i should be looking for "Compound SQL (Procedure)". Then you scan that chapters' SQL Diagrams for one that contains the word DECLARE, which in my case is "SQL-Variable-declaration." Then scroll down a few pages, looking for "SQL-Variable-declaration". Then, ping-pong between the explanation and the SQL diagram in order to try to infer what the syntax is. Then scroll randomly a few pages forward and a few pages back hoping for an example - and in this case there is one. Intuitive. i am SO ready for IBM. > Then, ping-pong between the explanation and the SQL diagram in order to try to infer what the syntax is. Not infer. the diagram is clear, according to the guidelines presented in "About this book" subsection "How to read the syntax diagrams". You may not like the IBM diagrams, but they are indeed very clear. B. > You may not like the IBM diagrams, but they are indeed very clear. The same diagrams are in the ANSI spec. They are very clear once you already know the syntax. I guess you never saw MS Word's message "you must click OK to exit" with one button marked "OK". DB2 is an IBM product, and has a message and an error code for *everything*. It's part and parcel of IBM to document everything. (Worked beautifully in OS/2.) As for NULLs, i have the same gripe. As for "make it works anyway, cus i cliked "save". I hope you are never my DBA. :P B. >> Procedural statements are not supported as independent statements by DB2. >> [quoted text clipped - 15 lines] > ..., and other times i have to declare a cursor for a select, and then leave > the cursor open. i don't see OPEN in your list. I forgot OPEN :-) The interactive tools (like command center and CLP) have short hands for queries. That is when you type VALUES or SELECT interactively they will declare a cursor for you, open it, fetch all the rows and close. Then they pretty print the output. > In fact, a quick check of the IBM DB2 Universal Database SQL Reference > Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN > DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you > point me to some references on this? This is the root for SQL Procedure logic: http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad min/r0004239.htm This is the root for the simpler command line scripting: http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad min/r0004240.htm Here are the sentences for the GOTO statement (random example): "GOTO statement The GOTO statement is used to branch to a user-defined label within an SQL procedure. Invocation This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared." You implicitly raise an interesting point though. The SQL Reference is "dictionary" it is as little the right tool to learn the basics of SQL as any dictionary. You are used to MS SQL Server "Books Online" which is more of a guide. It describes what matters example driven). The DB2 SQL Ref is the _exact_ specification of DB2's SQL. There are plans to deliver a SQL Guide in a future release which will be more appropriate and have information such as which statement can be used where, and include scenario based examples. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > This is the root for SQL Procedure logic: > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad min/r0004239.htm > This is the root for the simpler command line scripting: > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad min/r0004240.htm i get it. Some statements are only valid inside other statements. If of course be convient if they would just work. > This statement can only be embedded in an SQL procedure. It is not an > executable statement and cannot be dynamically prepared." Why? It can't be a technical limitation, and there is little value in making things more difficult for people to use. Is it the standard? And if so how many companies in the consortium are trying to change it? It's fair to say that in the end this stuff is meant to be used by developers, not just computer scientists. > You implicitly raise an interesting point though. > The SQL Reference is "dictionary" it is as little the right tool to learn > the basics of SQL as any dictionary. Every other computer language has keywords or tokens, and someplace you can open a reference manual and get an explanation for that token, or a reference to where it is used. > You are used to MS SQL Server "Books Online" which is more of a guide. > It describes what matters example driven). > The DB2 SQL Ref is the _exact_ specification of DB2's SQL. Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful. > There are plans to deliver a SQL Guide in a future release which will be > more appropriate and have information such as which statement can be used > where, and include scenario based examples. An index would be nice. Seaching a web-site, pdf, google groups, or the internet for "ibm db2 set" doesn't help so much. But if i could type SET and be presented with the index entries that someone has already taken the time to pre-select, would be so so SO SO SO much more useful. In the Books Online, i rarely use the "contents" and i rarely use "search". i use the index almost exclusivly. If i want help on, for example, SET, i type the word SET and am presented with the documentation on the SET keyword. Doing a word search for "SET" would be folly. > i've never used any idea similar to > (SELECT 5 UNION SELECT 6) AS X > > i've never had a problem where i needed to join to such virtual table. I would not call this a "virtual" table. It is a table (like views are tables). The major issue here is where the data of the table comes from. In this case, the table is constructed on the fly. > Like i said before, i'll have to figure out where VALUES would be useful > to me. [quoted text clipped - 9 lines] > > So the column gets renamed from "" to "ProcessID." Note that DB2 names expressions that are returned from a query itself (it numbers them) if they don't have an explicit name. That's why you see the "1" or "2" in the following output: $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" 1 2 ----------- ----------- 6 15 1 record(s) selected. Same thing here: $ db2 "values ( 1+2+3, 4+5+6 )" 1 2 ----------- ----------- 6 15 1 record(s) selected. And those "1" or "2" can be used in a Java application when you fetch the data from the result set by column name. SignatureKnut Stolze DB2 Information Integration Development IBM Germany > $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" > [quoted text clipped - 16 lines] > And those "1" or "2" can be used in a Java application when you fetch the > data from the result set by column name. Unless someone changed the query to SELECT 3.14159, 1+2+3, 4+5+6 1 2 3 -------- -------- -------- 3.14159 6 15 1 record(s) selected. And now all the application logic has to be rewritten. >> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1" >> [quoted text clipped - 27 lines] > > And now all the application logic has to be rewritten. You two got yor wires crossed.. Knut is talking about correlation names (above the ----- line). You are now talking about result types. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >select 1+2+3, 4+5+6 from sysibm.sysdummy1 1 2 ----------- ----------- 6 15 > SELECT 3.14159, 1+2+3, 4+5+6 1 2 3 -------- -------- -------- 3.14159 6 15 > You two got yor wires crossed.. Knut is talking about correlation names > (above the ----- line). > You are now talking about result types. ? i thought we were talking about correlation names and how they are still necessary even if there is only one column in the results set. > Unless someone changed the query to > SELECT 3.14159, 1+2+3, 4+5+6 I guess you mean VALUES 3.14159, 1+2+3, 4+5+6 > 1 2 3 > -------- -------- -------- [quoted text clipped - 3 lines] > > And now all the application logic has to be rewritten. True. But my answers would be (a) Why is the additional column not appended? Then you don't break anything. (b) I don't think this is a big deal in reality. For several decades now the the fetching of values from a result set is based on the order of the columns. So far there hasn't been a great outrage on this. Granted, the fetching using column names is a nice feature. (c) You could always wrap the VALUES into a SELECT to give the column names SELECT * FROM TABLE ( VALUES ( 3.14159, 1+2+3, 4+5+6 ) ) AS t(a, b, c) or use sysibm.sysdummy1 (or DUAL in Oracle). SignatureKnut Stolze DB2 Information Integration Development IBM Germany >> Unless someone changed the query to >> SELECT 3.14159, 1+2+3, 4+5+6 > > I guess you mean VALUES 3.14159, 1+2+3, 4+5+6 Yes, i slipped, i was focused on the concept. i should have written: SELECT 3.14159, 1+2+3, 4+5+6 FROM sysibm.sysdummy1 > True. But my answers would be > (a) Why is the additional column not appended? Then you don't break > anything. Cause i didn't. > (b) I don't think this is a big deal in reality. Granted, the > fetching using column names is a nice feature. From almost day one, i got burned fetching column values by ordinal. It's just a bad idea. Yes i could do it, and i could be forced to keep the ordinal location of every field in every table, result set, or query. But it makes it easier for separate systems to talk to each other if each side talks to the other through named columns. It makes systems much more resiliant to changes. It's just good practice. > For several decades now > the the fetching of values from a result set is based on the order of the > columns. They have databases on computers now. It's not a huge performance penalty to lookup columns by name. It just isn't. No new systems (i.e. those not written in the last few decades) lookup fields by ordinal. No web-sites do it, no desktop applications do it. >So far there hasn't been a great outrage on this. The outrage comes when someone tries to maintain legacy systems, for example removing legacy columns that are no longer used for the business. You delete the column, and every application that depends on column's by ordinal location break. So now we're stuck with junk because someone never bothered to make their system flexible and smart. >From almost day one, i got burned fetching column values by ordinal. It's >just a bad idea. Yes i could do it, and i could be forced to keep the >ordinal location of every field in every table, result set, or query. Not in the TABLE, in the query. And the query changes per application, so no problem. If the query is in a stored PROCEDURE, and outputs a result set, it is best to use the VALUES inside a SELECT statement as Knut pointed out. B. > And the query changes per application, > so no problem. Not when a query is the query was written in one application and used by another. > If the query is in a stored PROCEDURE, and outputs a > result set, it is best to use the VALUES inside a SELECT statement as > Knut pointed out. How do you give names to the fields returned from a SELECT of a VALUES table? > > And the query changes per application, > > so no problem. [quoted text clipped - 8 lines] > How do you give names to the fields returned from a SELECT of a > VALUES table? I'm pretty sure I've provided the syntax for this in a couple of other comments, but maybe I didn't indicate it explicitly: SELECT COLA, COLB, COLC FROM ( VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) ) AS TEMP(COLA, COLB, COLC) The "AS" bit is mandatory and provides a name for the table produced by VALUES. The field aliases in brackets after AS TEMP are optional, but allow you to assign names to the columns in the VALUES table, which can then be referenced in the enclosing SELECT statement as above. Though I could just as easily have done: SELECT * FROM ( VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) ) AS TEMP(COLA, COLB, COLC) HTH, Dave. > I'm pretty sure I've provided the syntax for this in a couple of other > comments, but maybe I didn't indicate it explicitly: [quoted text clipped - 9 lines] > then be referenced in the enclosing SELECT statement as above. Though I > could just as easily have done: Thank you for that. i do remember seeing the post, but i thought that the COLA, COLB, COLC were placeholders for something...i dunno. i didn't appreciate that it is the way to give names to the columns created by VALUES. VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but SQL Server should have something like it - even if i can't think of good uses for it right now :) > VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but > SQL Server should have something like it - even if i can't think of good > uses for it right now :) Using VALUES you can PIVOT tables and you can also simplify that UPDATE trigger of yours into a single INSERT statement. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but >> SQL Server should have something like it - even if i can't think of good >> uses for it right now :) > Using VALUES you can PIVOT tables and you can also simplify that > UPDATE trigger of yours into a single INSERT statement. Whoa, whoa, slow down. Let me figure out how to do SELECTs first :) > I'm pretty sure I've provided the syntax for this in a couple of other > comments, but maybe I didn't indicate it explicitly: [quoted text clipped - 3 lines] > VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) > ) AS TEMP(COLA, COLB, COLC) What is the semantic difference between your syntax and the form WITH TEMP(COLA,COLB,COLC) AS ( SELECT .... ) They appear equivilant - am I missing something? SignatureWill Honea > > I'm pretty sure I've provided the syntax for this in a couple of > > other comments, but maybe I didn't indicate it explicitly: [quoted text clipped - 10 lines] > > They appear equivilant - am I missing something? The WITH ... construct is a "common table expression" which was syntax introduced in the SQL-99 standard. Functionally, both your version and my version above are identical. That said, common table expressions are considerably more powerful than sub-selects and (in my personal opinion) a lot clearer especially if a query includes many sub-selects. One of the things you can do with common table expressions (that you can't do with ordinary sub-selects) is "recursive" queries. I haven't seen them used much in practice, and you've got to be a bit careful with (it's possible to make an infinitely recursive query), but there's some interesting examples in the DB2 Info Center under Reference / SQL / Queries / Select-statement (see the "Recursion example: bill of materials" section). One last thing, using a common table expression instead of a sub-select in my example, one wouldn't even need a SELECT expression around the VALUES expression: WITH TEMP(COLA, COLB, COLC) AS ( VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) ) SELECT ... Anyway, thanks for bringing that up - I should have mentioned common table expressions in my reply, but forgot :) Dave. >> > I'm pretty sure I've provided the syntax for this in a couple of >> > other comments, but maybe I didn't indicate it explicitly: [quoted text clipped - 14 lines] > introduced in the SQL-99 standard. Functionally, both your version and > my version above are identical. Not quite the same - there are some very small differences that you usually don't have to worry about. The common table expression is guaranteed to be evaluated before the SELECT statement. Also, the CTE is evaluated exactly once. The DB2 optimizer is not given a choice to copy the CTE in several branches of the statement if it thinks that would be better. This is usually only relevant for non-deterministic/external action stuff. Besides that, CTEs are identical to the sub-select. SignatureKnut Stolze DB2 Information Integration Development IBM Germany >>>> I'm pretty sure I've provided the syntax for this in a couple of >>>> other comments, but maybe I didn't indicate it explicitly: [quoted text clipped - 21 lines] > > Besides that, CTEs are identical to the sub-select. What you describe there are not the CTEs I know. The rules fro non determinism are teh same for derived tables, views and CTE. Order of execution is only honored for CTE if there is a reason to honor them (like SELECT FORM INSERT/UPDATE/DELETE). Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > What you describe there are not the CTEs I know. The rules fro non > determinism are teh same for derived tables, views and CTE. > Order of execution is only honored for CTE if there is a reason to honor > them (like SELECT FORM INSERT/UPDATE/DELETE). Oh, all right. The SQL standard does make this distinction and I assumed it applies to DB2 as well. SignatureKnut Stolze DB2 Information Integration Development IBM Germany > How do you give names to the fields returned from a SELECT of a VALUES > table? SELECT * FROM (VALUES (1, 2, 3)) AS T(c1, c2, c3) Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab |
Enable EMail Alerts
Start New Thread
Reply to this Message