By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

How to do...well...anything...in DB2 SQL

P: n/a
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)
Mar 7 '06
Share this Question
Share on Google+
138 Replies


P: n/a
>>> So
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?
Perhaps IBM should ask Oracle or MS if they can license their engines.
Ehm, what were you saying about 'walking a fine line' again... ;-)
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.

"Who's gonna pay for it and which feature should be moved back instead?"
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.
You really should try to start a thread like this on c.d.o.s., and watch
what happens...


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.

Mar 10 '06 #101

P: n/a
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?
Mar 10 '06 #102

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

It's called PL/SQL.
i hope they aren't case-sensitive
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.
i hope they allow mixed-case identifiers
Yes.
i hope they allow identifiers to be longer than 18 characters.


IIRC, it's 30.

Been a while though.

B.

Mar 10 '06 #103

P: n/a
>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.

Mar 10 '06 #104

P: n/a
Ian Boyd wrote:
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.

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
Mar 10 '06 #105

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

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 10 '06 #106

P: n/a
>
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.

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.
Mar 10 '06 #107

P: n/a
Ian Boyd wrote:
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 10 '06 #108

P: n/a
Ian Boyd wrote:
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 10 '06 #109

P: n/a
>> 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 :)
Mar 10 '06 #110

P: n/a
> It's called PL/SQL.
i hope they aren't case-sensitive
For object names, databases are case-sensitive.


*shudder* Let's hope i never have to use Oracle.
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.


Breaks what? SQL Server isn't case sensitive.
i hope they allow identifiers to be longer than 18 characters.


IIRC, it's 30.


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
Mar 10 '06 #111

P: n/a
> >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.


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.
Mar 10 '06 #112

P: n/a
> Ian,
try c-style comments. They should work across the board.
/*... */
Prereq is FP9


Excellent! That works. i don't know what FP9 is, but what do i care, it
works!
Mar 10 '06 #113

P: n/a
> 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.


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?
Mar 10 '06 #114

P: n/a
> 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.


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.
Mar 10 '06 #115

P: n/a
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.

Mar 10 '06 #116

P: n/a
Ian Boyd wrote:
Ian,
try c-style comments. They should work across the board.
/*... */
Prereq is FP9


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


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

Mar 10 '06 #117

P: n/a
In article <du*********@enews2.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...
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.


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.


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')"

Mar 10 '06 #118

P: n/a
> 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. :)


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.
Mar 10 '06 #119

P: n/a
> 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')"


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?
Mar 10 '06 #120

P: n/a
> 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 :)
Mar 10 '06 #121

P: n/a
In article <du*********@enews1.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...
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')"


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?


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.
Mar 10 '06 #122

P: n/a
Ian Boyd wrote:
Run db2level from a command window


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

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
Mar 10 '06 #123

P: n/a
Gert van der Kooij wrote:
In article <du*********@enews1.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...
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')"

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?


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.

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
Mar 10 '06 #124

P: n/a
> To get the information from SQL do:
SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X


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

It's not important.
Mar 10 '06 #125

P: n/a
Ian Boyd wrote:
Run db2level from a command window

[snip lots of errors]

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


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

Cheers,

Dave.

--

Mar 10 '06 #126

P: n/a
Ian Boyd wrote:
To get the information from SQL do:
SELECT * FROM TABLE(ENV_GET_INST_INFO()) AS X


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

So much for you being on FP9 :-(

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 10 '06 #127

P: n/a

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du*********@enews2.newsguy.com...
It's called PL/SQL.

i hope they aren't case-sensitive


For object names, databases are case-sensitive.


*shudder* Let's hope i never have to use Oracle.
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.


Breaks what? SQL Server isn't case sensitive.
i hope they allow identifiers to be longer than 18 characters.


IIRC, it's 30.


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


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
Mar 10 '06 #128

P: n/a
Dave Hughes wrote:
Will Honea wrote:
On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <da**@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?


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.


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
Mar 13 '06 #129

P: n/a
Knut Stolze wrote:
Dave Hughes wrote:
Will Honea wrote:
On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <da**@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?

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.


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.

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
Mar 13 '06 #130

P: n/a
Serge Rielau wrote:
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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 13 '06 #131

P: n/a
Brian Tkatch wrote:
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. -snip-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


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

Apr 5 '06 #132

P: n/a
pl***@newsreaders.com wrote:
Brian Tkatch wrote:
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. -snip- 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


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

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
Apr 5 '06 #133

P: n/a
pl***@newsreaders.com wrote:
Brian Tkatch wrote:
>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. -snip- >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


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


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
Apr 6 '06 #134

P: n/a
Knut Stolze <st****@de.ibm.com> wrote:
pl***@newsreaders.com wrote:

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


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


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 unexpected token "(" was found following '''.
Expected tokens include: "IS <hexstring><charstring<graphstring>".
SQLSTATE=46201 -- from report services when I run it against the DB2
database).

--
J. Moreno
Apr 6 '06 #135

P: n/a
<posted & mailed>

J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote:
pl***@newsreaders.com wrote:
-snip-
> 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).


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


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


This works quite nicely:

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

ANSWER
------
a

1 record(s) selected.
as well as

WITH params(p1, p2) AS
( SELECT '1', '2' )
SELECT 'a' AS answer FROM params
This can obviously not work because you have a syntax error in the common
table expression. There is no valid SELECT statement specified there.
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 unexpected token "(" was found following
'''. Expected tokens include: "IS <hexstring><charstring<graphstring>".
SQLSTATE=46201 -- from report services when I run it against the DB2
database).


What's the exact error message?

I'd suspect that something else besides DB2 is screwing things up. Maybe
you're not using the IBM ODBC/CLI driver?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 7 '06 #136

P: n/a
Knut Stolze <st****@de.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip-
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 ...
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


This works quite nicely:

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

ANSWER
------
a

1 record(s) selected.

-snip- What's the exact error message?
A cut/paste of your code gives this error message:
An error occurred while executing the query.
DB2[SQL0104N] An unexpected token "(" was found following '''. Expected
tokens may include: "IS <HEXSTRING><CHARSTRING><GRAPHSTRING>".
SQLSTATE=42601

If I switch to the Microsoft ODBC driver, I get this error message:
An error occurred while executing the query.
ERROR[42401][IBM][CLI Driver][DB2]SQL0104N An unexpected token "(" was
found following ''''. Expected tokens may include: "IS
<HEXSTRING><CHARSTRING><GRAPHSTRING>". SQLSTATE=42601
I'd suspect that something else besides DB2 is screwing things up. Maybe
you're not using the IBM ODBC/CLI driver?


I've tried the "IBM OLE DB Provider for DB2 Servers" and the
"Microsoft OLE DB Provider for ODBC Drivers".

Too bad I can't get this to work (it'd be ideal for the query that I'm
working on, which involves two subquery's that are almost identical).

--
J. Moreno
Apr 7 '06 #137

P: n/a
J. B. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote:
J. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote: -snip- 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 ...
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

This works quite nicely:

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

ANSWER
------
a

1 record(s) selected.

-snip-
What's the exact error message?


A cut/paste of your code gives this error message:
An error occurred while executing the query.
DB2[SQL0104N] An unexpected token "(" was found following '''. Expected
tokens may include: "IS <HEXSTRING><CHARSTRING><GRAPHSTRING>".
SQLSTATE=42601

If I switch to the Microsoft ODBC driver, I get this error message:
An error occurred while executing the query.
ERROR[42401][IBM][CLI Driver][DB2]SQL0104N An unexpected token "(" was
found following ''''. Expected tokens may include: "IS
<HEXSTRING><CHARSTRING><GRAPHSTRING>". SQLSTATE=42601
I'd suspect that something else besides DB2 is screwing things up. Maybe
you're not using the IBM ODBC/CLI driver?


I've tried the "IBM OLE DB Provider for DB2 Servers" and the
"Microsoft OLE DB Provider for ODBC Drivers".

Too bad I can't get this to work (it'd be ideal for the query that I'm
working on, which involves two subquery's that are almost identical).

J.B.,

What platform are you on. Knut is referring to DB2 for LUW.
DB2 for iSeries or zOS do not support WITH clause yet.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 7 '06 #138

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote:
J. B. Moreno wrote:
Knut Stolze <st****@de.ibm.com> wrote:
J. Moreno wrote:

Knut Stolze <st****@de.ibm.com> wrote:

-snip-
> 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 ...
That sounds good -- but I can't get it to work....
-snip- Too bad I can't get this to work (it'd be ideal for the query that I'm
working on, which involves two subquery's that are almost identical).


J.B.,

What platform are you on. Knut is referring to DB2 for LUW.
DB2 for iSeries or zOS do not support WITH clause yet.


That's it -- I'm on zOS. Guess I'm stuck with the repeated subqueries...

--
J. Moreno
Apr 7 '06 #139

138 Replies

This discussion thread is closed

Replies have been disabled for this discussion.