DB2 9.5 global variables

[Frank Swarbrick]

Interesting! I was going to ask if such a thing existed, but I was pretty
much convinced they did not so I didn't ask.

Looks like with version 9.5 DB2 supports global variables:

"Global variables improve data sharing between SQL statements.
Version 9.5 introduces the concept of global variables, which are named
memory variables that you can access and modify through SQL statements.
Global variables enable you to share data between different SQL statements
running in the same session (or connection) without the need for application
logic to support this data transfer."

The reason I had wanted something like this is for applications where a
generic userid is used for an application to connect to a database, but a
specific userid is used by the user when using the application. This way I
think we can do something like this:

CREATE VARIABLE global.userid VARCHAR(20) DEFAULT NULL^

CREATE TABLE maint_log (
userid VARCHAR(20)
, field_name VARCHAR(40) NOT NULL
, old_value VARCHAR(255)
, new_value VARCHAR(255)
)^

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF name, city, state
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
IF NOT old.name = new.name THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'NAME', old.name, new.name);
END IF;
IF NOT old.city = new.city THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'CITY', old.city, new.city);
END IF;
IF NOT old.state = new.state THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'STATE', old.state, new.state);
END IF;
END^

And then in an application:

CONNECT ...
SET global.userid = 'JAUser';

UPDATE test.table1
SET name = 'Frank Swarbrick'
, state = 'CA'
WHERE name = 'Francis J Swarbrick';

And then
SELECT * FROM maint_log;
gives:
USERID FIELD_NAME OLD_VALUE
NEW_VALUE
-------------------- ----------------------------------------
--------------------- -------------------
JAUser NAME Francis J
Swarbrick Frank Swarbrick
JAUser STATE CO
CA

This would allow the application to connect to the database and immediately
set the 'global.userid' variable. Then later it could do updates to tables
that have triggers attached and the trigger could retrieve the global.userid
variable and insert it in to the maintenance log table.

This seems to work, and seems to be to be a decent idea. Any thoughts?

One thing about the trigger is, could there possibly be a way to make the IF
and INSERT statements more generic so that I don't have to add a new one for
each column that I want to do logging for?

Anyway, cool new feature in DB2!

Frank

[Serge Rielau]

You may want to look up "trusted context". Another cool feature in Db2
9.5 which may be more applicable to your problem.

Cheers
Serge

PS: Glad you like global vars :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Frank Swarbrick]

>>On 2/22/2008 at 6:25 PM, in message
<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

Interesting! I was going to ask if such a thing existed, but I was
pretty
much convinced they did not so I didn't ask.

Looks like with version 9.5 DB2 supports global variables:

"Global variables improve data sharing between SQL statements.
Version 9.5 introduces the concept of global variables, which are named
memory variables that you can access and modify through SQL statements.
Global variables enable you to share data between different SQL
statements
running in the same session (or connection) without the need for
application
logic to support this data transfer."

The reason I had wanted something like this is for applications where a
generic userid is used for an application to connect to a database, but
a
specific userid is used by the user when using the application. This
way I
think we can do something like this:

CREATE VARIABLE global.userid VARCHAR(20) DEFAULT NULL^

CREATE TABLE maint_log (
userid VARCHAR(20)
, field_name VARCHAR(40) NOT NULL
, old_value VARCHAR(255)
, new_value VARCHAR(255)
)^

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF name, city, state
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

That's wierd. It cut off the rest of my message and put it in some
unreadable attachment.

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF
NAME
, CITY
, STATE
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
IF NOT old.name = new.name THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'NAME', old.name, new.name);
END IF;
IF NOT old.city = new.city THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'CITY', old.city, new.city);
END IF;
IF NOT old.state = new.state THEN
INSERT INTO maint_log (userid, field_name, old_value, new_value)
VALUES (global.userid, 'STATE', old.state, new.state);
END IF;
END

SET global.userid = 'JAUser';

insert into test.table1
values ('Francis J Swarbrick', 'Lakewood', 'CO');

update test.table1
set name = 'Frank Swarbrick'
where name = 'Francis J Swarbrick';

select * from maint_log;

I wonder if there's a way to make a more generic IF statement so that I
don't have to add a new one for each column...?

Frank

[Frank Swarbrick]

>>On 2/22/2008 at 8:20 PM, in message
<62*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:

You may want to look up "trusted context". Another cool feature in Db2
9.5 which may be more applicable to your problem.

I have looked at that a bit, but I don't think it will work for me.
Firstly, it appears that it cannot be used from an 'embedded SQL'
application. Is this true?

Secondly, does the 'alternate user ID' have to be defined to DB2 as an
actual user? I don't think that would work out well for us, with a few
thousand possible users.

Thanks,
Frank

[Frank Swarbrick]

n 2/25/2008 at 9:53 AM, in message <47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>>>On 2/22/2008 at 6:25 PM, in message

<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>Interesting! I was going to ask if such a thing existed, but I was
pretty
much convinced they did not so I didn't ask.

Looks like with version 9.5 DB2 supports global variables:

"Global variables improve data sharing between SQL statements.
Version 9.5 introduces the concept of global variables, which are named
memory variables that you can access and modify through SQL statements.
Global variables enable you to share data between different SQL
statements
running in the same session (or connection) without the need for
application
logic to support this data transfer."

The reason I had wanted something like this is for applications where a
generic userid is used for an application to connect to a database, but
a
specific userid is used by the user when using the application. This
way I
think we can do something like this:

CREATE VARIABLE global.userid VARCHAR(20) DEFAULT NULL^

CREATE TABLE maint_log (
userid VARCHAR(20)
, field_name VARCHAR(40) NOT NULL
, old_value VARCHAR(255)
, new_value VARCHAR(255)
)^

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF name, city, state
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

That's wierd. It cut off the rest of my message and put it in some
unreadable attachment.

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF
NAME
, CITY
, STATE
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

It did it again! *!#@&#@ Let's try something else...

..CREATE TRIGGER TEST.NAME_LOG_TR
.. AFTER UPDATE OF
.. NAME
.. , CITY
.. , STATE
.. ON TEST.TABLE1
.. REFERENCING OLD AS old NEW AS new
.. FOR EACH ROW
.. MODE DB2SQL
..BEGIN ATOMIC
.. IF NOT old.name = new.name THEN
.. INSERT INTO maint_log (userid, field_name, old_value, new_value)
.. VALUES (global.userid, 'NAME', old.name, new.name);
.. END IF;
.. IF NOT old.city = new.city THEN
.. INSERT INTO maint_log (userid, field_name, old_value, new_value)
.. VALUES (global.userid, 'CITY', old.city, new.city);
.. END IF;
.. IF NOT old.state = new.state THEN
.. INSERT INTO maint_log (userid, field_name, old_value, new_value)
.. VALUES (global.userid, 'STATE', old.state, new.state);
.. END IF;
..END@

[Frank Swarbrick]

>>On 2/25/2008 at 12:05 PM, in message
<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>
n 2/25/2008 at 9:53 AM, in message <47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>>>>On 2/22/2008 at 6:25 PM, in message

<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>>Interesting! I was going to ask if such a thing existed, but I was
pretty
much convinced they did not so I didn't ask.

Looks like with version 9.5 DB2 supports global variables:

"Global variables improve data sharing between SQL statements.
Version 9.5 introduces the concept of global variables, which are named
memory variables that you can access and modify through SQL statements.
Global variables enable you to share data between different SQL
statements
running in the same session (or connection) without the need for
application
logic to support this data transfer."

The reason I had wanted something like this is for applications where a
generic userid is used for an application to connect to a database, but

>>a
specific userid is used by the user when using the application. This
way I
think we can do something like this:

CREATE VARIABLE global.userid VARCHAR(20) DEFAULT NULL^

CREATE TABLE maint_log (
userid VARCHAR(20)
, field_name VARCHAR(40) NOT NULL
, old_value VARCHAR(255)
, new_value VARCHAR(255)
)^

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF name, city, state
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

That's wierd. It cut off the rest of my message and put it in some
unreadable attachment.

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF
NAME
, CITY
, STATE
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

It did it again! *!#@&#@ Let's try something else...

.CREATE TRIGGER TEST.NAME_LOG_TR
. AFTER UPDATE OF
. NAME
. , CITY
. , STATE
. ON TEST.TABLE1
. REFERENCING OLD AS old NEW AS new
. FOR EACH ROW
. MODE DB2SQL
.BEGIN ATOMIC
. IF NOT old.name = new.name THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'NAME', old.name, new.name);
. END IF;
. IF NOT old.city = new.city THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'CITY', old.city, new.city);
. END IF;
. IF NOT old.state = new.state THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'STATE', old.state, new.state);
. END IF;
.END@

That's better.

Anyway, I am interested in a way to make the IF etc statements more generic
so as to not require a new IF statement for each column. Any ideas?

Frank

[Serge Rielau]

Frank Swarbrick wrote:

>>>On 2/25/2008 at 12:05 PM, in message

<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>n 2/25/2008 at 9:53 AM, in message <47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:

>>>>>On 2/22/2008 at 6:25 PM, in message
<47******************@efirstbank.com>,
Frank Swarbrick<Fr*************@efirstbank.comwrote:
Interesting! I was going to ask if such a thing existed, but I was
pretty
much convinced they did not so I didn't ask.

Looks like with version 9.5 DB2 supports global variables:

"Global variables improve data sharing between SQL statements.
Version 9.5 introduces the concept of global variables, which are named
memory variables that you can access and modify through SQL statements.
Global variables enable you to share data between different SQL
statements
running in the same session (or connection) without the need for
application
logic to support this data transfer."

The reason I had wanted something like this is for applications where a
generic userid is used for an application to connect to a database, but

>>>a
specific userid is used by the user when using the application. This
way I
think we can do something like this:

CREATE VARIABLE global.userid VARCHAR(20) DEFAULT NULL^

CREATE TABLE maint_log (
userid VARCHAR(20)
, field_name VARCHAR(40) NOT NULL
, old_value VARCHAR(255)
, new_value VARCHAR(255)
)^

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF name, city, state
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL
That's wierd. It cut off the rest of my message and put it in some
unreadable attachment.

CREATE TRIGGER TEST.NAME_LOG_TR
AFTER UPDATE OF
NAME
, CITY
, STATE
ON TEST.TABLE1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
MODE DB2SQL

It did it again! *!#@&#@ Let's try something else...

.CREATE TRIGGER TEST.NAME_LOG_TR
. AFTER UPDATE OF
. NAME
. , CITY
. , STATE
. ON TEST.TABLE1
. REFERENCING OLD AS old NEW AS new
. FOR EACH ROW
. MODE DB2SQL
.BEGIN ATOMIC
. IF NOT old.name = new.name THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'NAME', old.name, new.name);
. END IF;
. IF NOT old.city = new.city THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'CITY', old.city, new.city);
. END IF;
. IF NOT old.state = new.state THEN
. INSERT INTO maint_log (userid, field_name, old_value, new_value)
. VALUES (global.userid, 'STATE', old.state, new.state);
. END IF;
.END@

That's better.

Anyway, I am interested in a way to make the IF etc statements more generic
so as to not require a new IF statement for each column. Any ideas?

Frank

INSERT INTO .. SELECT ..FROM (VALUES ('NAME', old.name, new.name),
(...), ...) AS X(col, oldval, newval) WHERE oldval <newval

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