How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Brian Tkatch on 8 Mar 2006 21:39 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.
From: Brian Tkatch on 8 Mar 2006 21:40 Point taken. :) Obviously the software should do some things, just don;t take my power to change them away. What you said. B.
From: Pierre Saint-Jacques on 8 Mar 2006 23:18 I quite agree on how careful and respectful both the OP and MOST of the responders have been. For myself, this has been quite instructive(?) (It's your language anyway). I've learned a lot and I've rarely met an OP that has been as careful as he has been with knowing as much as he has! Thnaks, Pierre. -- Pierre Saint-Jacques SES Consultants Inc. 514-737-4515 "Dave Hughes" <dave(a)waveform.plus.com> a ?crit dans le message de news: 440f75d0$0$6964$ed2619ec(a)ptn-nntp-reader02.plus.net... .....snip> > [snip] >> > PS: I find this thread quite interesting actually. >> In a morbid train-wreck sorta way? .....snip >> i'm walking a fine line here: of trying to extract information from >> the people in the know, without touching a nerve. > > You're doing a good job so far I'd say. > > -- >
From: Knut Stolze on 9 Mar 2006 03:50 Ian Boyd wrote: > 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 > -------- -------- -------- > 3.14159 6 15 > > 1 record(s) selected. > > 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). -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Knut Stolze on 9 Mar 2006 04:12
Ian Boyd wrote: > The progress so far. Note, this is mainly for me, and my coworkers who > want help understanding the limitations of DB2. This post will be google > archived, and available as a future reference. 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?) > Here is the syntax i've divined for creating a table in IBM DB2-SQL: > > CREATE TABLE "SUPERDUDE"."AUDIT_LOG" ( > "AUDITLOGID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( > START WITH +0 > INCREMENT BY +1 > MINVALUE +0 > MAXVALUE +2147483647 > NO CYCLE > NO CACHE > NO ORDER ) , > "CHANGEDATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , > "ROWID" INTEGER NOT NULL , > "CHANGETYPE" VARCHAR(10) NOT NULL , > "TABLENAME" VARCHAR(128) NOT NULL , > "FIELDNAME" VARCHAR(128) NOT NULL , > "OLDVALUE" LONG VARCHAR , > "NEWVALUE" LONG VARCHAR , > "USERNAME" VARCHAR(128) , > "HOSTNAME" VARCHAR(50) , > "APPNAME" VARCHAR(255) , > "USERID" INTEGER , > "TAGID" INTEGER , > "TAG" VARCHAR(1000) ) > IN "USERSPACE1" ; > > Notes: > 1. Username, Hostname an Appname field would like to have defaults of > SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME > respectivly, but those special registers are not supposed as column > default values. Whereas CURRENT TIMESTAMP is an example of a special > register that is supported as a column default value. 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. > And my trigger code is: > > CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS > AFTER INSERT > ON SUPERDUDE.DAILY_LOGS > REFERENCING NEW_TABLE AS INSERTED > FOR EACH STATEMENT > BEGIN ATOMIC > -- Load the saved UserID > DECLARE SavedUserID INTEGER; > > SET SavedUserID = (SELECT User_ID > FROM Connection_Users > WHERE Application_ID = Application_ID()); > > INSERT INTO Audit_Log( > RowID, > ChangeType, > UserID, > TableName, > FieldName, > Username, > Hostname, > Appname, > TagID, > Tag, > OldValue, > NewValue) > SELECT > i.Daily_Log_ID, > 'INSERTED', > SavedUserID, > 'Daily_Logs', > '', > SESSION_USER, > CURRENT CLIENT_WRKSTNNAME, > CURRENT CLIENT_APPLNAME, > i.Daily_Log_ID, > i.Daily_Log_Number, > CAST(NULL AS varchar(1)), > CAST(NULL AS varchar(1)) > FROM Inserted i; > END (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. > NOTES: > 2. i had to specify SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT > CLIENT_APPLNAME here because DB2 does not support these specific system > registers as column default values. > > 2. DB2 does not support comments inside in insert statement (e.g. to > document what each field is). Comments are not supported either on the end > of a line, or on it's own line. Not true. DB2 does support comments: $ cat trig.sql create trigger a_ins after insert on a referencing new as n for each row -- comment 1 insert into b -- comment 2 values (n.a); $ db2 -t -f trig.sql DB20000I The SQL command completed successfully. $ db2 "select text from syscat.triggers where trigname = 'A_INS'" ---------------------------------------------------------- create trigger a_ins after insert on a referencing new as n for each row -- comment 1 insert into b -- comment 2 values (n.a) > 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: VALUES CASE WHEN 1 = 0 THEN 123 ELSE NULL END The "123" tells DB2 the data type for the CASE expression and DB2 will implicitly use this type for the (untyped) NULL. Only if the type cannot be derived, you have to explicitly cast the NULL. <Celko-mode>NULL is not a value.</celko mode> > Here is my sample insert into a table getting logged: > > INSERT INTO Daily_Logs ( > Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, > Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID, > Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, > End_Date, > Description) > VALUES ( > 'DL-20060307-412', > 0, > 1, > 1, > 'A74', > 1, > 2, > CAST(NULL AS bigint), > CAST(NULL AS bigint), > 2, > 5, > '2006-03-01 11:11:07.11111', > '2006-03-01 11:21:18.22222', > CAST(NULL AS varchar(1)) > ); > > NOTES: > 4. You cannot have comments inside the select; either on the end of each > line, or on its own line. Not true. See above. > 5. DB2 cannot cast NULL to any data type. You must manually cast any NULLs > to a data type that DB2 can implicitly cast to the data type of the target > column. In this case, i have to manually cast a NULL to an integer field, > and a varchar field. Not true. See above. > ADDITIONAL NOTES: > > 6. Many other SQL constructs are not understood by DB2. > > SELECT 'Hello, world!'; This is not a SQL construct. ;-) > 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. > 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. > 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. > INSERT INTO Users (UserID, Username) VALUES (1, 'Ian'); > INSERT INTO Users (UserID, Username) VALUES (2, 'Brian'); > INSERT INTO Users (UserID, Username) VALUES (3, 'Knut'); > INSERT INTO Users (UserID, Username) VALUES (4, 'Serge'); > SELECT * FROM Users > WHERE Username = 'Knut'; > DROP TABLE Users; $db2 -t -vf a CREATE TABLE Users ( UserID int, Username varchar(50) ) DB20000I The SQL command completed successfully. INSERT INTO Users (UserID, Username) VALUES (1, 'Ian') DB20000I The SQL command completed successfully. INSERT INTO Users (UserID, Username) VALUES (2, 'Brian') DB20000I The SQL command completed successfully. INSERT INTO Users (UserID, Username) VALUES (3, 'Knut') DB20000I The SQL command completed successfully. INSERT INTO Users (UserID, Username) VALUES (4, 'Serge') DB20000I The SQL command completed successfully. SELECT * FROM Users WHERE Username = 'Knut' USERID USERNAME ----------- -------------------------------------------------- 3 Knut 1 record(s) selected. DROP TABLE Users DB20000I The SQL command completed successfully. -- Knut Stolze DB2 Information Integration Development IBM Germany |