Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 8 Mar 2006 15:38
> Working as documented:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000888.htm
>

By working at intended do you mean that only some special registers can be
used as default values on columns and not others? Does that mean that it
can't be done? Can you suggest some workarounds to accomplish the same task?

--Fails (unexpected token near "CLIENT_APPLNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

--Fails (unexpected token near "CLIENT_WRKSTNNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;


This goes to what i was saying before about non-sensical nature of DB2.
"Why can some special registers be used as column defaults and not others?"
"Cause."
"Wouldn't you maybe want to clean that up so it is consistent?"


From: Ian Boyd on 8 Mar 2006 15:41
> Nope, in this case I think it's the thing I mentioned in my other post:
> comments can only appear as the first non-whitespace characters in a
> line.

i can't get that to work either:

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 (
--DailyLogNumber
'DL-20060307-36',
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))
);

SQL0104N An unexpected token "(" was found following "DESCRIPTION) VALUES".
Expected tokens may include: "<table_value_constructor_list>".
SQLSTATE=42601




From: Dave Hughes on 8 Mar 2006 16:25
Ian Boyd wrote:

> > Nope, in this case I think it's the thing I mentioned in my other
> > post: comments can only appear as the first non-whitespace
> > characters in a line.
>
> i can't get that to work either:
>
> 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 (
> --DailyLogNumber
> 'DL-20060307-36',
> 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))
> );
>
> SQL0104N An unexpected token "(" was found following "DESCRIPTION)
> VALUES". Expected tokens may include:
> "<table_value_constructor_list>". SQLSTATE=42601

In that case it definitely sounds like the problem Serge mentioned:
that the client (or something somewhere) is stripping out line breaks.
I'm not sure how one could confirm this. I guess you could stick a
packet sniffer like Ethereal between the client and server, grab the
query going to the server and check it for line breaks, but that seems
like overkill (and even then it wouldn't tell you *what* was stripping
the line breaks, just that it was happening).

In this case, unfortunately, the only solution is to chop out the
comments altogether (shame DB2 doesn't support C-style /*..*/ comments
in which line break chopping doesn't result in ambiguity).


Dave.

--

From: Ian Boyd on 8 Mar 2006 17:10
> In that case it definitely sounds like the problem Serge mentioned:
> that the client (or something somewhere) is stripping out line breaks.
> I'm not sure how one could confirm this. I guess you could stick a
> packet sniffer like Ethereal between the client and server, grab the
> query going to the server and check it for line breaks, but that seems
> like overkill (and even then it wouldn't tell you *what* was stripping
> the line breaks, just that it was happening).

Good idea. You are correct sir.
0x0000 00 53 D0 51 00 01 00 4D-20 0A 00 44 21 13 4F 4C .S?Q...M ..D!.OL
0x0010 47 43 53 55 52 56 20 20-20 20 20 20 20 20 20 20 GCSURV
0x0020 4E 55 4C 4C 49 44 20 20-20 20 20 20 20 20 20 20 NULLID
0x0030 20 20 53 59 53 53 48 32-30 30 20 20 20 20 20 20 SYSSH200
0x0040 20 20 20 20 53 59 53 4C-56 4C 30 31 00 41 00 05 SYSLVL01.A..
0x0050 21 05 F1 02 14 D0 43 00-01 02 0E 24 14 00 00 00 !.?..?C....$....
0x0060 02 04 49 4E 53 45 52 54-20 49 4E 54 4F 20 44 61 ..INSERT INTO Da
0x0070 69 6C 79 5F 4C 6F 67 73-20 28 20 20 20 20 20 20 ily_Logs (
0x0080 44 61 69 6C 79 5F 4C 6F-67 5F 4E 75 6D 62 65 72 Daily_Log_Number

i'm sure either the ODBC driver, OLEDB provider or DB2 itself must have an
option to preserve linebreaks somewhere, but it's not really feasable to go
looking for it.


From: Ian Boyd on 8 Mar 2006 17:13
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.

DO NOT RESPOND

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.

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

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.

3. DB2 cannot implicitly cast a NULL to any data type. The NULL values
specified for columns OldValue and NewValue, must therefore be explicitly
converted to the target column's data type, or to some data type that can be
implicitly to the target column's data type. In my case here, i use a dummy
cast of CAST to varchar(1).


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.
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.

ADDITIONAL NOTES:

6. Many other SQL constructs are not understood by DB2.

SELECT 'Hello, world!';

is invalid because every select in DB2 must be from a table. DB2 provides a
dummy table for this purpose

SELECT 'Hello, world!' FROM sysibm.sysdummy1;

This system table contains only 1 row and only 1 column.

7. Every DB2-SQL Statement must end with a semi-colon (;)

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).

The major cavaet with this limitation is that something like the following
is invalid:

CREATE TABLE Users (
UserID int,
Username varchar(50);
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;

This is because you tried to execute more than one statment at a time. You
need to break it up and run only one statment at a time.

9. Quite a few SQL constructs are not defined by DB2 as "statments", so you
cannot run them. e.g.

DECLARE MyUsername varchar(50);

is invalid because DECLARE is not a statement. So you would also be unable
to accomplish the following:

DECLARE MyUsername varchar(50);
SET MyUsername = 'Hello, world!';
SELECT MyUsername AS MyUsername FROM sysibm.sysdummy1;

because DECLARE and SET are not statements. The workaround for this in DB2
is to use another generic statement wrapper:

BEGIN ATOMIC
DECLARE MyUsername varchar(50);
SET MyUsername = 'Hello, world!';
SELECT MyUsername AS MyUsername FROM sysibm.sysdummy1;
END;

Like CREATE TABLE, CREATE PROCEDURE, etc, 'BEGIN ATOMIC' is a valid
"statement", and can be used to enclose non-statements.

10. Stored procedures cannot issue select statements. For example, the
following is invalid:

CREATE PROCEDURE doStuff
BEGIN
SELECT * FROM Users;
END;

Instead, the stored procedure must declare a cursor, open it, and leave the
cursor declared and open when leaving the stored procedure:

CREATE PROCEDURE doStuff
BEGIN
DECLARE abc CURSOR WITH RETURN FOR
SELECT * FROM Users
OPEN abc
END;



First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE