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_T R
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 6 2566
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
>>On 2/22/2008 at 6:25 PM, in message
<47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.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_T R
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_T R
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
>>On 2/22/2008 at 8:20 PM, in message
<62************ *@mid.individua l.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
n 2/25/2008 at 9:53 AM, in message <47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.comwrote :
>>>On 2/22/2008 at 6:25 PM, in message
<47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.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_T R 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_T R
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_T R
.. 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@
>>On 2/25/2008 at 12:05 PM, in message
<47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.comwrote :
>
n 2/25/2008 at 9:53 AM, in message <47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.comwrote :
>>>>On 2/22/2008 at 6:25 PM, in message
<47*********** *******@efirstb ank.com>, Frank Swarbrick<Fr*** **********@efir stbank.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_T R 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_T R 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_T R
. 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
Frank Swarbrick wrote:
>>>On 2/25/2008 at 12:05 PM, in message
<47************ ******@efirstba nk.com>,
Frank Swarbrick<Fr*** **********@efir stbank.comwrote :
>n 2/25/2008 at 9:53 AM, in message <47************ ******@efirstba nk.com>, Frank Swarbrick<Fr*** **********@efir stbank.comwrote :
>>>>>On 2/22/2008 at 6:25 PM, in message <47********** ********@efirst bank.com>, Frank Swarbrick<Fr*** **********@efir stbank.comwrote : Interestin g! 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 applicatio n 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_T R 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_T R 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_T R . 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Matt |
last post by:
Greetings,
What are people's thoughts on global variables in C++?
Why are we taught not to use them in programming?
Is it true that if you are running two copies of the C program one
copy can overwrite another copies global variable?
I know that you could overwrite a value in a global variable from a
function, but you could also do that if you pass the variable in and
then out again... so how is that any different?
|
by: Andrew V. Romero |
last post by:
I have been working on a function which makes it easier for me to pull
variables from the URL. So far I have:
<script language="JavaScript">
var variablesInUrl;
var vArray = new Array();
function loadUrlVariables()
{
varString = location.search;
|
by: David WOO |
last post by:
Hi,
I am a newbie on C++, I need to define some global variables which should be
accessible to most classes. In the mean time, I don't won't the global
variables be modified freely at most of these classes. I know there is a
pattern called singleton can more or less do such a trick. I am wondering is
this the best way to do it (regarding the convenience and safety), as this
is such a fundamental thing, I believe most of you have a say...
|
by: Bryan Parkoff |
last post by:
….I would like to know which is the best optimization to use global
variable or global struct. I always tell C/C++ Compiler to turn on
optimization.
….I use underscore between first name and second name for better
readable. After optimization, global variables might be misaligned
because each global variables must be converted to 32 bits, but I do
see that C/C++ Compiler do padding between variables. Struct does the
same to do padding....
|
by: MLH |
last post by:
A97 Topic: If there is a way to preserve the values assigned to
global variables when an untrapped runtime error occurs? I don't
think there is, but I thought I'd ask.
During development, I'm constantly running tests on imperfect code.
On of the cumbersome jobs encountered is reassigning global vars
their values after a close encounter with an untrapped runtime error.
Rather than writing a procedure to simply reassign them all with a...
| |
by: MLH |
last post by:
I've read some posts indicating that having tons of GV's in
an Access app is a bad idea. Personally, I love GVs and I
use them (possibly abuse them) all the time for everything
imaginable - have been for years. If the machine has memory
to spare and windows can use it - I'm thinking "Why not?"
I was wondering what some of you have to say about that,
particularly any severe "gotchas" you've had the unfortunate
experience to contend with.
|
by: CDMAPoster |
last post by:
About a year ago there was a thread about the use of global variables
in A97:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/fedc837a5aeb6157
Best Practices by Kang Su Gatlin, casual mention was made about using
static variables as an alternative to using global variables. This
caused me to think of the following:
'-----Begin module code
|
by: Sandman |
last post by:
I dont think I understand them. I've read the section on scope in the
manual inside out.
I'm running PHP 5.2.0 Here is the code I'm working on:
//include_me.php
<?php
$MYVAR = array();
global $MYVAR, $a;
?>
|
by: weaknessforcats |
last post by:
C++: The Case Against Global Variables
Summary
This article explores the negative ramifications of using global variables. The use of global variables is such a problem that C++ architects have called it polluting the global namespace. This article explores what happens when the global namespace becomes polluted and how to avoid this condition.
The opinions expressed in this article are those of the author alone although many have...
|
by: istillshine |
last post by:
When I control if I print messages, I usually use a global variable
"int silent". When I set "-silent" flag in my command line
parameters, I set silent = 1 in my main.c.
I have many functions that may print some messages.
foo(...)
{
if (!silent)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |