How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 9 Mar 2006 10:32 > PS: Name any software product (which I know of course) and I can rattle > down a set of odd limitations. But an important thing that perhaps IBM itself should be aware of, is that this was stuff that worked in Microsoft SQL Server. Yes, there are workaround to it, but i would have that locked into the table itself, and implementers of my audit log triggers not have to deal with CURRENT USER/HOSTNAME/APPLNAME. But take note that a competitors product does this fine. Because a competitors product has other limitations is not an excuse not to bother implementing them in yours.
From: Ian Boyd on 9 Mar 2006 10:39 > This isn't right, it depends. If you put multiple commands in one > file you need to seperate them if you keep them on one line Nobody here will write queries on all one line. It's jut a practical thing. > 'create function' and 'create trigger' statements needs to be > seperated by another delimiter. > connect to <yourdb> @ i am already connected using ADO. The "connect to <yourdb> @" is invalid DB2-SQL. If i need to separate statements, i'm going to have to do it manually.
From: Knut Stolze on 9 Mar 2006 11:01 Ian Boyd wrote: >> As someone >> else mentioned, in SQL "everything is a table" (a single row is just a >> special case of a table, and a single value is another such special >> case). > > Not everything is a table. USER is a special register. There are plenty of > "special registers". When you use USER as an expression in a SQL statement, its value is used. Where the value originates from is not of interest and the "everything is a table" also applies to other scalar values, for example constants or values recieved from an application via host-variables. A single value (also called scalar value) is the same as a table with one row and one column. If you try to think that way, statements like SET (a, b, c) = (1, 2, 3) are much easier to understand, I'd say. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Ian Boyd on 9 Mar 2006 11:14 > I also respond because there are some more things not correct. So I'd > rather correct it before someone else gets the wrong ideas in the future. > (I'm wondering, don't you have a database (not necessarily relational) for > such things?) Google groups! My database search will be "ian boyd db2 create table", and brought right to valid DB2-SQL example to create a table, etc. > This is because the special registers are considered to be not > deterministic. A different user connecting to the system implies > different > values for those defaults. So it _is not_ deterministic. So use a > trigger > instead. > You could argue that the same holds for CURRENT TIMESTAMP and I would > agree. > However, the user cannot influence the current timestamp, so DB2 can > safely > determine it when a row is inserted. Well...my post isn't wrong. DB2 can't do it. Maybe it has a reason for it, maybe not. Either way, it can't be done that way. > (1) I would throw away the procedural logic for the "SavedUserID" and do > this purely in SQL as we discussed before. > (2) You should switch to a FOR EACH ROW trigger as Serge explained. It's also a reference of how to do things. i'll will follow your suggestions in the final implementation. > Not true. DB2 does support comments: Turns out that the Windows ODBC or OLEDB provider strip CRLF's. So this destroys inline comments. You can argue that the driver is not DB2, but in the end i cannot use inline comments. If IBM will update the drivers so it works, we can then use them - but now we cannot. >> 3. DB2 cannot implicitly cast a NULL to any data type. > It does if it can derive the data type, for example from the column name > or > by other means like here: In this case DB2 *should* be able to derive the data type, but won't: INSERT INTO Users (Username, Address) SELECT uid, NULL FROM Employees In this case it does INSERT INTO Users (Username, Address) VALUES ('Ian', NULL) So if you ever deal with nulls, it's safer to just cast it. Save time by not going round and round trying to guess what db2 will and won't do. That's my point there. > <Celko-mode>NULL is not a value.</celko mode> NULL is the absense of database. If i personally don't think DB2 should enforce a type on nothing. But here we get into deep philosophical ideas (What does nothing sound like? It can sound like whatever you want. Ooooo) > Not true. See above. See above :) > Not true. See above. See above :) > This is not a SQL construct. ;-) Change your definition :) >> 7. Every DB2-SQL Statement must end with a semi-colon (;) > Not true. You can choose your statement terminator freely, and it can > even > be the end of line. Well *i* can't. >> 8. You cannot give DB2 some arbitrary SQL to run. You are only allowed to >> give DB2 one "statement" at a time. If you try to give it more than one >> statement, it will choke. Examples of statements include CREATE TABLE, >> CREATE TRIGGER, CREATE PROCEDURE, DECLARE CURSOR, CALL (which executes a >> stored procedure). > > Not true. You have to separate the statements with the statement > terminator. See above :) >> The major cavaet with this limitation is that something like the >> following >> is invalid: >> >> CREATE TABLE Users ( >> UserID int, >> Username varchar(50); > > Closing ')' is missing. Pwned.
From: Brian Tkatch on 9 Mar 2006 11:31
> 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. |