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[color=blue][color=green][color=darkred]
>>> it boils down to question we always have with new functionality: Who's
>>> gonna pay for it and which feature should be moved back instead?[/color][/color][/color]
[color=blue][color=green]
>> Perhaps IBM should ask Oracle or MS if they can license their engines.[/color][/color]
[color=blue]
> Ehm, what were you saying about 'walking a fine line' again... ;-)[/color]

Yeah, that was in jest. But my point was that database vendors should be
competing for customers. And it's a real problem if you don't do what they
can do.

[color=blue]
> "Who's gonna pay for it and which feature should be moved back instead?"[/color]

i assume that IBM has bottomless pits of money to draw from when updating
DB2. My first exposure to DB2 was not a very pleasent one - version i don't
know what running on an AS/400. It's a gawdawefull mess, that really soured
my opinion on DB2. Seeing the improvements in the version we got shipped
(8.2), i assumed that DB2 just made leaps and bounds since then, and that
all these features that other vendors have would already be in "Big Blues"
version.
[color=blue]
> You really should try to start a thread like this on c.d.o.s., and watch
> what happens...[/color]

If we ever have a customer that insists on Oracle, then i guess i'll be
learning Oracle-SQL. i hope they aren't case-sensitive, i hope they allow
mixed-case identifiers, i hope they allow identifiers to be longer than 18
characters.

8. Triggers for update

My first task with DB2 was to create a table and 3 triggers. i've created
the table - mostly the way i wanted, and i've created two of the three
required triggers:

AFTER INSERT
AFTER DELETE

Now i just have to finish my last one, the hardest one, the one with the
most problems coming together. i'm also try to take the advice of people and
use the FOR EACH ROW rather than the simpler more obvious FOR EACH
STATEMENT.

So, now for my update trigger, and the error that DB2 is giving me. NOTE: i
am intentionally not snipping apparently repetative code, because the error
talks about "too much." Also note, only formatted the first few entries and
the last entry, as OE does not honor tab characters when pasting.

<query>
CREATE TRIGGER SUPERDUDE.LU_DAILYLOGS
AFTER UPDATE
ON SUPERDUDE.DAILY_LOGS
REFERENCING OLD AS d
NEW AS i
FOR EACH ROW
BEGIN ATOMIC
DECLARE SavedUserID INTEGER;
SET SavedUserID = (
SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID());

--Daily_Log_Number varchar(20)
IF (d.Daily_Log_Number IS NULL AND i.Daily_Log_Number IS NOT NULL) OR
(d.Daily_Log_Number IS NOT NULL AND i.Daily_Log_Number IS NULL)
OR
(d.Daily_Log_Number <> i.Daily_Log_Number) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
d.Daily_Log_Number,
i.Daily_Log_Number);
END IF;

--Created_By_User_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Created_By_User_ID IS NULL AND i.Created_By_User_ID IS NOT NULL)
OR
(d.Created_By_User_ID IS NOT NULL AND i.Created_By_User_ID IS
NULL) OR
(d.Created_By_User_ID <> i.Created_By_User_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Created_By_User_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Created_By_User_ID AS char(50)) AS varchar(50))
);
END IF;

--Property_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Property_ID IS NULL AND i.Property_ID IS NOT NULL) OR
(d.Property_ID IS NOT NULL AND i.Property_ID IS NULL) OR
(d.Property_ID <> i.Property_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Property_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Property_ID AS char(50)) AS varchar(50))
);
END IF;

--Shift_ID bigint
--Syntax for CAST(abigint AS) varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Shift_ID IS NULL AND i.Shift_ID IS NOT NULL) OR
(d.Shift_ID IS NOT NULL AND i.Shift_ID IS NULL) OR
(d.Shift_ID <> i.Shift_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Shift_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Shift_ID AS char(50)) AS varchar(50))
);
END IF;

--Bay_Number varchar(25)
IF (d.Bay_Number IS NULL AND i.Bay_Number IS NOT NULL) OR
(d.Bay_Number IS NOT NULL AND i.Bay_Number IS NULL) OR
(d.Bay_Number <> i.Bay_Number) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
d.Bay_Number,
i.Bay_Number
);
END IF;

--Supervisor_User_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Supervisor_User_ID IS NULL AND i.Supervisor_User_ID IS NOT NULL) OR
(d.Supervisor_User_ID IS NOT NULL AND i.Supervisor_User_ID IS NULL) OR
(d.Supervisor_User_ID <> i.Supervisor_User_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Supervisor_User_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Supervisor_User_ID AS char(50)) AS varchar(50))
);
END IF;

--Bay_Number varchar(25)
IF (d.Bay_Number IS NULL AND i.Bay_Number IS NOT NULL) OR
(d.Bay_Number IS NOT NULL AND i.Bay_Number IS NULL) OR
(d.Bay_Number <> i.Bay_Number) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
d.Bay_Number,
i.Bay_Number);
END IF;

--Location_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Location_ID IS NULL AND i.Location_ID IS NOT NULL) OR
(d.Location_ID IS NOT NULL AND i.Location_ID IS NULL) OR
(d.Location_ID <> i.Location_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Location_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Location_ID AS char(50)) AS varchar(50))
);
END IF;

--Occurrence_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Occurrence_ID IS NULL AND i.Occurrence_ID IS NOT NULL) OR
(d.Occurrence_ID IS NOT NULL AND i.Occurrence_ID IS NULL) OR
(d.Occurrence_ID <> i.Occurrence_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Occurrence_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Occurrence_ID AS char(50)) AS varchar(50))
);
END IF;

--Checklist_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Checklist_ID IS NULL AND i.Checklist_ID IS NOT NULL) OR
(d.Checklist_ID IS NOT NULL AND i.Checklist_ID IS NULL) OR
(d.Checklist_ID <> i.Checklist_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Checklist_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Checklist_ID AS char(50)) AS varchar(50))
);
END IF;

--Daily_Log_Type_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50) AS varchar(50))
IF (d.Daily_Log_Type_ID IS NULL AND i.Daily_Log_Type_ID IS NOT NULL) OR
(d.Daily_Log_Type_ID IS NOT NULL AND i.Daily_Log_Type_ID IS NULL) OR
(d.Daily_Log_Type_ID <> i.Daily_Log_Type_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Daily_Log_Type_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Daily_Log_Type_ID AS char(50)) AS varchar(50))
);
END IF;

--Daily_Log_SubType_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigint AS char(50)) AS varchar(50))
IF (d.Daily_Log_SubType_ID IS NULL AND i.Daily_Log_SubType_ID IS NOT NULL)
OR
(d.Daily_Log_SubType_ID IS NOT NULL AND i.Daily_Log_SubType_ID IS NULL) OR
(d.Daily_Log_SubType_ID <> i.Daily_Log_SubType_ID) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(CAST(d.Daily_Log_SubType_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Daily_Log_SubType_ID AS char(50)) AS varchar(50))
);
END IF;

--Start_Date timestamp
IF (d.Start_Date IS NULL AND i.Start_Date IS NOT NULL) OR
(d.Start_Date IS NOT NULL AND i.Start_Date IS NULL) OR
(d.Start_Date <> i.Start_Date) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(d.Start_Date AS varchar(50)),
CAST(i.Start_Date AS varchar(50))
);
END IF;

--End_Date timestamp
IF (d.Start_Date IS NULL AND i.Start_Date IS NOT NULL) OR
(d.Start_Date IS NOT NULL AND i.Start_Date IS NULL) OR
(d.Start_Date <> i.Start_Date) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(d.End_Date AS varchar(50)),
CAST(i.End_Date AS varchar(50))
);
END IF;

--Description varchar(2048)
IF (d.Description IS NULL AND i.Description IS NOT NULL) OR
(d.Description IS NOT NULL AND i.Description IS NULL) OR
(d.Description <> i.Description) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
d.Description,
i.Description);
END IF;
END
</query>


Now the error message i get is:
<error>
Error: SQL1424N Too many references to transition variables and transition
table columns or the row length for these references is too long.
Reason code="2".
LINE NUMBER=524.
SQLSTATE=54040
(State:54040, Native Code: FFFFFA70)
</error>

The web-sites explains this error:
The trigger includes a REFERENCING clause that identifies one or more
transition tables and transition variables. The triggered action of the
trigger contains references to transition table columns or transition
variables with one of the following conditions identified by the reason
code:
1 references total more than the limit of the number of columns in a table
2 sum of the lengths of the references exceeds the maximum length of a row
in a table.
User Response:
Reduce the number of references to transition variables and transition table
columns in the trigger action of the trigger so that the length is reduced
or the total number of such references is less than the maximum number of
columns in a table.



Now, i am taking this to mean that because for every field i do:
IF (d.Daily_Log_Number IS NULL AND i.Daily_Log_Number IS NOT NULL) OR
(d.Daily_Log_Number IS NOT NULL AND i.Daily_Log_Number IS NULL)
OR
(d.Daily_Log_Number <> i.Daily_Log_Number) THEN
...
i.Daily_Log_ID,
...
i.Daily_Log_ID,
i.Daily_Log_Number,
d.Daily_Log_Number,
i.Daily_Log_Number);
END IF;

For every field in the table, my trigger references the "Old row" transition
variable 4 times, and the "New row" transition variable 6 times. Is it
really that fact that i am talking to a variable too many times? What should
i be doing instead? Should i declare a variable for every row's old value
and every row's new value, then copy the values from the old and new
transition variables into my local variables, then do everything from those?

Is it really a limitation on the number of times i can use a transition
variable? Is something like the following going to "hit my limit":

IF (i.Daily_Log_Number <> 1) and
(i.Daily_Log_Number <> 2) and
(i.Daily_Log_Number <> 3) and
(i.Daily_Log_Number <> 4) and
(i.Daily_Log_Number <> 5) and
...
(i.Daily_Log_Number <> 997423) THEN

Is just referencing a variable bad?


Do i have to write my trigger like:

CREATE TRIGGER SUPERDUDE.LU_DAILYLOGS
AFTER UPDATE
ON SUPERDUDE.DAILY_LOGS
REFERENCING OLD AS d
NEW AS i
FOR EACH ROW
BEGIN ATOMIC
--Get the row key value
DECLARE rowid BIGINT;
SET rowid = i.Daily_Log_ID;

--Daily_Log_Number varchar(20)
DECLARE d_Daily_Log_Number varchar(20);
DECLARE i_Daily_Log_Number varchar(20);
SET d_Daily_Log_Number = d.Daily_Log_Number;
SET i_Daily_Log_Number = i.Daily_Log_Number;

IF (d_Daily_Log_Number IS NULL AND i_Daily_Log_Number IS NOT NULL) OR
(d_Daily_Log_Number IS NOT NULL AND i_Daily_Log_Number IS NULL)
OR
(d_Daily_Log_Number <> i_Daily_Log_Number) THEN
INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
VALUES (
rowid,
'UPDATED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
rowid,
i.Daily_Log_Number,
d_Daily_Log_Number,
i_Daily_Log_Number);
END IF;

i can do this, it will make things much more difficult, but i can do it. But
is DB2 telling me this is the trick i have to do to get around it's
n-references to a transisition variable limitation? Or am i missing
something more basic, that will make it just work?

>If we ever have a customer that insists on Oracle, then i guess i'll be learning Oracle-SQL

It's called PL/SQL.
[color=blue]
>i hope they aren't case-sensitive[/color]

For object names, databases are case-sensitive. However, if not
enclosed in double-quotes, it is implicitly uppercased before being
evaluated. Therefore, it appears as case-insensitive. SQL Server breaks
this, however, and is rather annoying.
[color=blue]
>i hope they allow mixed-case identifiers[/color]

Yes.
[color=blue]
>i hope they allow identifiers to be longer than 18 characters.[/color]

IIRC, it's 30.

Been a while though.

B.

>Right now it's WoW.

Which server?

I finally just got enough gold to get my epic mount. I just need a
guildy to buy it for me, so i can save the extra hundred gold.

B.

Ian Boyd wrote:[color=blue]
> 8. Triggers for update
>
> My first task with DB2 was to create a table and 3 triggers. i've created
> the table - mostly the way i wanted, and i've created two of the three
> required triggers:
>
> AFTER INSERT
> AFTER DELETE
>
> Now i just have to finish my last one, the hardest one, the one with the
> most problems coming together. i'm also try to take the advice of people and
> use the FOR EACH ROW rather than the simpler more obvious FOR EACH
> STATEMENT.
>
> So, now for my update trigger, and the error that DB2 is giving me. NOTE: i
> am intentionally not snipping apparently repetative code, because the error
> talks about "too much." Also note, only formatted the first few entries and
> the last entry, as OE does not honor tab characters when pasting.[/color]
The error is related to the semantic temporary table that needs to hold
the row.
I presume you have created the database using a default 4K page size and
not added any other table spaces.
Add an 8K SYSTEM TEMPORARY tablespace (and an 8k Buffer pool to serve )
using the CREATE BUFFERPOOL and CREATE TABLESPACE SQL statements.
This should solve the problem.
If you don't want to manage different tablespace (as I suspect :-)
recreate your database with a bigger default page size (assuming you're
on FP9 or higher) from the get go.
You can go up to 32K if you wish.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Ian,
try c-style comments. They should work across the board.
/*... */
Prereq is FP9

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>[color=blue]
> Now the error message i get is:
> <error>
> Error: SQL1424N Too many references to transition variables and transition
> table columns or the row length for these references is too long.
> Reason code="2".
> LINE NUMBER=524.
> SQLSTATE=54040
> (State:54040, Native Code: FFFFFA70)
> </error>
>
> The web-sites explains this error:
> The trigger includes a REFERENCING clause that identifies one or more
> transition tables and transition variables. The triggered action of the
> trigger contains references to transition table columns or transition
> variables with one of the following conditions identified by the reason
> code:
> 1 references total more than the limit of the number of columns in a table
> 2 sum of the lengths of the references exceeds the maximum length of a row
> in a table.[/color]


Hi Ian,

The reason code would mean that the generated rowlength is to long.
Just a guess but maybe it helps if you define a temporary tablespace
with 36K pages.

Ian Boyd wrote:[color=blue]
> How do you give names to the fields returned from a SELECT of a VALUES
> table?
>
>[/color]
SELECT * FROM (VALUES (1, 2, 3)) AS T(c1, c2, c3)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Ian Boyd wrote:[color=blue]
> 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 :)[/color]
Using VALUES you can PIVOT tables and you can also simplify that
UPDATE trigger of yours into a single INSERT statement.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>> VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but[color=blue][color=green]
>> SQL Server should have something like it - even if i can't think of good
>> uses for it right now :)[/color]
> Using VALUES you can PIVOT tables and you can also simplify that
> UPDATE trigger of yours into a single INSERT statement.[/color]

Whoa, whoa, slow down.

Let me figure out how to do SELECTs first :)

> It's called PL/SQL.
[color=blue][color=green]
>>i hope they aren't case-sensitive[/color]
>
> For object names, databases are case-sensitive.[/color]

*shudder* Let's hope i never have to use Oracle.
[color=blue]
> However, if not
> enclosed in double-quotes, it is implicitly uppercased before being
> evaluated. Therefore, it appears as case-insensitive.
> SQL Server breaks this, however, and is rather annoying.[/color]

Breaks what? SQL Server isn't case sensitive.
[color=blue][color=green]
>>i hope they allow identifiers to be longer than 18 characters.[/color]
>
> IIRC, it's 30.[/color]

i tried to do a quick check: At *least* 2000 identifiers we would have
to shorten if we're limited to 30 characters. Some database names,
some table names, index, keys, foreign-keys, etc

> >Right now it's WoW.[color=blue]
>
> Which server?
>
> I finally just got enough gold to get my epic mount. I just need a
> guildy to buy it for me, so i can save the extra hundred gold.[/color]

Elune (PvE).

We were on Ner'zhul (PvP), but jumped ship when the honor system came out.

Our guild on Elune is <Honor System Refugees>

Not too many people get it, but i think it's clever.

> Ian,[color=blue]
> try c-style comments. They should work across the board.
> /*... */
> Prereq is FP9[/color]

Excellent! That works. i don't know what FP9 is, but what do i care, it
works!

> The error is related to the semantic temporary table that needs to hold[color=blue]
> the row.
> I presume you have created the database using a default 4K page size and
> not added any other table spaces.
> Add an 8K SYSTEM TEMPORARY tablespace (and an 8k Buffer pool to serve )
> using the CREATE BUFFERPOOL and CREATE TABLESPACE SQL statements.
> This should solve the problem.
> If you don't want to manage different tablespace (as I suspect :-)
> recreate your database with a bigger default page size (assuming you're on
> FP9 or higher) from the get go.
> You can go up to 32K if you wish.[/color]

DB2 is running as "out of the box" as possible.

i'm leary of doing these odd things, since we will not be allowed to manage
the customer's database. They can only be sent scripts. And seeing how often
scripts that the Command Center generate to perform operations, and the
number of times we've lost tables because of it, i'd be very hesitant to
send the customer scripts that perform wholesale database modifications.

Can you go into detail the problem is? If "old row" and "new row" are held
each held in temporary tables, each on a 4k table space, what is exceeding
that 4k?

> Hi Ian,[color=blue]
>
> The reason code would mean that the generated rowlength is to long.
> Just a guess but maybe it helps if you define a temporary tablespace
> with 36K pages.[/color]

i just asked Serge, but no harm in re-posting :)

What is the nature of this "generated" row? What, for example, would the
pseudo-create statement of this temporary table? Is one table holding
the old and new values for every column? If so, it must only generate it
based on the columns you ask for in teh trigger, otherwise any table
that is 4k wide could never be used by an update trigger.

But i also tried moving the auditing of the longest column to it's own
trigger


--Description varchar(2048)
IF ... (d.Description <> i.Description) THEN
INSERT INTO Audit_Log(
RowID,
...
OldValue,
NewValue)
VALUES (
i.Daily_Log_ID,
...
CAST(d.Description AS varchar(1000)),
CAST(d.Description AS varchar(1000))
);
END IF;

and it still fails.

Heh.

I'm a wimp sticking to Stormrage. :)

Though i got to level 4 on Earthen Ring when Stormrage was down at one
point.

Hmm.. i wionder what db server *they* use. :)

B.

Ian Boyd wrote:
[color=blue][color=green]
> > Ian,
> > try c-style comments. They should work across the board.
> > /*... */
> > Prereq is FP9[/color]
>
> Excellent! That works. i don't know what FP9 is, but what do i care,
> it works![/color]

FP9 = FixPak 9

Run db2level from a command window and it should show you what fixpak
level you've got installed (obviously you've got FP9 or above in this
case, but in case you need to know for the future). Also works on pure
client installations. For example, from my Windows box (which only has
the client installed):

DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08010"
with
level identifier "01010106".
Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".


HTH,

Dave.
--

In article <dusalu02ddm@enews2.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...[color=blue][color=green]
> > Hi Ian,
> >
> > The reason code would mean that the generated rowlength is to long.
> > Just a guess but maybe it helps if you define a temporary tablespace
> > with 36K pages.[/color]
>
> i just asked Serge, but no harm in re-posting :)
>
> What is the nature of this "generated" row? What, for example, would the
> pseudo-create statement of this temporary table? Is one table holding
> the old and new values for every column? If so, it must only generate it
> based on the columns you ask for in teh trigger, otherwise any table
> that is 4k wide could never be used by an update trigger.
>
> But i also tried moving the auditing of the longest column to it's own
> trigger
>
>
> --Description varchar(2048)
> IF ... (d.Description <> i.Description) THEN
> INSERT INTO Audit_Log(
> RowID,
> ...
> OldValue,
> NewValue)
> VALUES (
> i.Daily_Log_ID,
> ...
> CAST(d.Description AS varchar(1000)),
> CAST(d.Description AS varchar(1000))
> );
> END IF;
>
> and it still fails.
>
>
>[/color]

I guess DB2 is calculating the max total length using the max length
of the referenced columns. This means both Desciption reference
columns don't fit on a 4096 bytes page (every pages has contains some
overhead).
When looking at the error message it seems like DB2 generates a row
with all referenced columns. This means a system temporary tablespace
is needed to fit that row. If I counted it correctly you have 29
references to old or new records so as Serge suggested you most
likely need a pagesize of 8K.
To create a system temporary tablespace you can run the command
"create system temporary tablespace TMPSPACE_8K
pagesize 8K
managed by system
using ('TMPSPACE_8K')"

> Heh.[color=blue]
>
> I'm a wimp sticking to Stormrage. :)
>
> Though i got to level 4 on Earthen Ring when Stormrage was down at one
> point.
>
> Hmm.. i wionder what db server *they* use. :)[/color]

Bliz's website had job opening for people experienced in VLDB's on Oracle.
Maybe they were switching to Oracle :)

They are soooo ready for Oracle.

> you most[color=blue]
> likely need a pagesize of 8K.
> To create a system temporary tablespace you can run the command
> "create system temporary tablespace TMPSPACE_8K
> pagesize 8K
> managed by system
> using ('TMPSPACE_8K')"[/color]

Error: SQL1582N The PAGESIZE of the table space "TMPSPACE_8K" does not match
the PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table
space. SQLSTATE=428CB
(State:428CB, Native Code: FFFFF9D2)

If we dumped the database and started over with 8k, would the system's temp
space also now be 8k? Is tempspace per database?

> Run db2level from a command window

db2level
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"db2level".

db2level;
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"db2level".

begin atomic
db2level;
end
Error: SQL0104N An unexpected token "db2level" was found following "begin
atomic ".

select db2level from sysibm.sysdummy1
Error: SQL0206N "DB2LEVEL" is not valid in the context where it is used.

select db2level from sysibm.sysdummy1;
Error: SQL0206N "DB2LEVEL" is not valid in the context where it is used.


Ummm...i'll run it later :)

In article <dusi8k01tl3@enews1.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...[color=blue][color=green]
> > you most
> > likely need a pagesize of 8K.
> > To create a system temporary tablespace you can run the command
> > "create system temporary tablespace TMPSPACE_8K
> > pagesize 8K
> > managed by system
> > using ('TMPSPACE_8K')"[/color]
>
> Error: SQL1582N The PAGESIZE of the table space "TMPSPACE_8K" does not match
> the PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table
> space. SQLSTATE=428CB
> (State:428CB, Native Code: FFFFF9D2)
>
> If we dumped the database and started over with 8k, would the system's temp
> space also now be 8k? Is tempspace per database?
>
>[/color]

Sorry about that, I forgot you also have to create a 8K bufferpool.
If you start all over again using 8K as default, the bufferpool would
also be 8K automatically.
And yes, tempspace is per db.

Ian Boyd wrote:[color=blue][color=green]
>> Run db2level from a command window[/color]
>
> db2level
> Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
> "db2level".[/color]
db2level is an executable (like db2start, db2stop).
To get the information from SQL do:
SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Gert van der Kooij wrote:[color=blue]
> In article <dusi8k01tl3@enews1.newsguy.com>, Ian Boyd (ian.msnews010
> @avatopia.com) says...[color=green][color=darkred]
>>> you most
>>> likely need a pagesize of 8K.
>>> To create a system temporary tablespace you can run the command
>>> "create system temporary tablespace TMPSPACE_8K
>>> pagesize 8K
>>> managed by system
>>> using ('TMPSPACE_8K')"[/color]
>> Error: SQL1582N The PAGESIZE of the table space "TMPSPACE_8K" does not match
>> the PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table
>> space. SQLSTATE=428CB
>> (State:428CB, Native Code: FFFFF9D2)
>>
>> If we dumped the database and started over with 8k, would the system's temp
>> space also now be 8k? Is tempspace per database?
>>
>>[/color]
>
> Sorry about that, I forgot you also have to create a 8K bufferpool.
> If you start all over again using 8K as default, the bufferpool would
> also be 8K automatically.
> And yes, tempspace is per db.[/color]
Thread up to here is correct.
The old and new columns are flowing in the same row.
You are correct that only the referenced columns are relevant, but it's
all the column referenced by any trigger, RI and check constraint and of
course the UPDATE itself.
AFAIK SQL Server uses actual size per row. As a result you could get
runtime errors. DB2 is consciously more conservative. If the worst case
won't fit DB2 will fail the trigger definition or at latest the
compilation of the UPDATE statement.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

> To get the information from SQL do:[color=blue]
> SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X[/color]

Error: SQL0440N No authorized routine named "ENV_GET_INST_INFO" of type
"FUNCTION" having compatible arguments was found

It's not important.

Ian Boyd wrote:
[color=blue][color=green]
> > Run db2level from a command window[/color]
>[/color]
[snip lots of errors][color=blue]
>
>
> Ummm...i'll run it later :)[/color]

Sorry -- I meant an *ordinary* command window (like, Start|Run|cmd)

Cheers,

Dave.

--

Ian Boyd wrote:[color=blue][color=green]
>> To get the information from SQL do:
>> SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X[/color]
>
> Error: SQL0440N No authorized routine named "ENV_GET_INST_INFO" of type
> "FUNCTION" having compatible arguments was found[/color]
So much for you being on FP9 :-(

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

"Ian Boyd" <ian.msnews010@avatopia.com> schreef in bericht
news:dus9nq02cem@enews2.newsguy.com...[color=blue][color=green]
>> It's called PL/SQL.[/color]
>[color=green][color=darkred]
>>>i hope they aren't case-sensitive[/color]
>>
>> For object names, databases are case-sensitive.[/color]
>
> *shudder* Let's hope i never have to use Oracle.
>[color=green]
>> However, if not
>> enclosed in double-quotes, it is implicitly uppercased before being
>> evaluated. Therefore, it appears as case-insensitive.
>> SQL Server breaks this, however, and is rather annoying.[/color]
>
> Breaks what? SQL Server isn't case sensitive.
>[color=green][color=darkred]
>>>i hope they allow identifiers to be longer than 18 characters.[/color]
>>
>> IIRC, it's 30.[/color]
>
> i tried to do a quick check: At *least* 2000 identifiers we would have
> to shorten if we're limited to 30 characters. Some database names,
> some table names, index, keys, foreign-keys, etc[/color]

It's indeed limited to 30.
According to the Oracle10g docs it's a limitation of the PL/SQL-compiler.
Might have something to do with the fact that PL/SQL is based on ADA.

--
Jeroen

Dave Hughes wrote:
[color=blue]
> Will Honea wrote:
>[color=green]
>> On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave@waveform.plus.com>
>> wrote:
>>[color=darkred]
>> > 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)[/color]
>>
>> 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?[/color]
>
> 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.[/color]

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.


--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze wrote:[color=blue]
> Dave Hughes wrote:
>[color=green]
>> Will Honea wrote:
>>[color=darkred]
>>> On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave@waveform.plus.com>
>>> wrote:
>>>
>>>> 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)
>>> 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?[/color]
>> 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.[/color]
>
> 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.
>
>[/color]
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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
[color=blue]
> 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).[/color]

Oh, all right. The SQL standard does make this distinction and I assumed it
applies to DB2 as well.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Brian Tkatch wrote:[color=blue][color=green]
> >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.[/color][/color]
-snip-[color=blue][color=green]
> >1. Declaring a variable
> >
> >SQL Server:
> > DECLARE @SavedUserID int
> > The command(s) completed successfully.[/color]
>
> 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[/color]

I've got the same problem -- I'm trying to use the reporting services
in visual studio against a DB2 database, and I need to pass in a couple
of parameters. I've gotten this to work, using unnamed parameters (a ?
instead of @whatever), but I need to use the parameter in two different
places, and I don't want to have 4 parameters. It seems that a
variable would be the ideal solution, but how do I declare it?

BEGIN
DECLARE theP INT;
END

SELECT Count(*) FROM theTable

doesn't work -- "[DB2] SQL0199N The use of the reserved word "THEP"
following ''' is not valid."

So, perhaps I ned to have my select inside the BEGIN END....
But I get the exact same error...

So, how do I declare the variable so that DB2 will accept it?

(Sorry for the late reply, but I read through the thread and didn't
find an answer I understood).

planb@newsreaders.com wrote:[color=blue]
> Brian Tkatch wrote:[color=green][color=darkred]
>>> 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.[/color][/color]
> -snip-[color=green][color=darkred]
>>> 1. Declaring a variable
>>>
>>> SQL Server:
>>> DECLARE @SavedUserID int
>>> The command(s) completed successfully.[/color]
>> 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[/color]
>
> I've got the same problem -- I'm trying to use the reporting services
> in visual studio against a DB2 database, and I need to pass in a couple
> of parameters. I've gotten this to work, using unnamed parameters (a ?
> instead of @whatever), but I need to use the parameter in two different
> places, and I don't want to have 4 parameters. It seems that a
> variable would be the ideal solution, but how do I declare it?
>
> BEGIN
> DECLARE theP INT;
> END
>
> SELECT Count(*) FROM theTable
>
> doesn't work -- "[DB2] SQL0199N The use of the reserved word "THEP"
> following ''' is not valid."
>
> So, perhaps I ned to have my select inside the BEGIN END....
> But I get the exact same error...
>
> So, how do I declare the variable so that DB2 will accept it?
>
> (Sorry for the late reply, but I read through the thread and didn't
> find an answer I understood).
>[/color]
DB2 does not support global variables. No ifs, no butts.
Either you use parameter markers, or you have to use a procedure/function.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

planb@newsreaders.com wrote:
[color=blue]
> Brian Tkatch wrote:[color=green][color=darkred]
>> >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.[/color][/color]
> -snip-[color=green][color=darkred]
>> >1. Declaring a variable
>> >
>> >SQL Server:
>> > DECLARE @SavedUserID int
>> > The command(s) completed successfully.[/color]
>>
>> 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[/color]
>
> I've got the same problem -- I'm trying to use the reporting services
> in visual studio against a DB2 database, and I need to pass in a couple
> of parameters. I've gotten this to work, using unnamed parameters (a ?
> instead of @whatever), but I need to use the parameter in two different
> places, and I don't want to have 4 parameters. It seems that a
> variable would be the ideal solution, but how do I declare it?
>
> BEGIN
> DECLARE theP INT;
> END
>
> SELECT Count(*) FROM theTable
>
> doesn't work -- "[DB2] SQL0199N The use of the reserved word "THEP"
> following ''' is not valid."
>
> So, perhaps I ned to have my select inside the BEGIN END....
> But I get the exact same error...
>
> So, how do I declare the variable so that DB2 will accept it?
>
> (Sorry for the late reply, but I read through the thread and didn't
> find an answer I understood).[/color]

If your actual SQL statement is a query, you could use a common table
expression to have just the original two parameter markers:

WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT))
SELECT ...
FROM <table>, parms
WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze <stolze@de.ibm.com> wrote:[color=blue]
> planb@newsreaders.com wrote:
>[/color]
-snip-[color=blue][color=green]
> > So, how do I declare the variable so that DB2 will accept it?
> >
> > (Sorry for the late reply, but I read through the thread and didn't
> > find an answer I understood).[/color]
>
> If your actual SQL statement is a query, you could use a common table
> expression to have just the original two parameter markers:
>
> WITH parms(p1, p2) AS ( VALUES (CAST(? AS INT), CAST(? AS INT))
> SELECT ...
> FROM <table>, parms
> WHERE table.col1 < parms.p1 AND table.col1 > parms.p2 AND ...[/color]

That sounds good -- but I can't get it to work....

Trying to turn the above into a simple query that works, I tried

WITH params(p1, p2) AS ( VALUES (CAST('1' AS INT), CAST('2' AS INT) ) )
SELECT 'a' AS answer FROM params

as well as

WITH params(p1, p2) AS
( SELECT '1', '2' )
SELECT 'a' AS answer FROM params

neither works. I get the same error message for both querys (Server: Msg
156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'. --
from Query Analyzer, and "An unex