LF example of dbinfo with "language SQL"

[Bob Sparks]

Can some one post an example, or where to find one, using DBINFO in a
SQL procedure? I can't seem to find one anywhere. I don't see the
linkage between the structure and the SQL language variables in the
documentation.

A SCRATCHPAD example would be useful too.

I need to identify which user is executing a stored procedure, then
pick up the same information in a trigger. The application runs on
several platforms so I don't want to code a c procedure.

Thanks

Bob

[Jonathan Leffler]

Bob Sparks wrote:

Can some one post an example, or where to find one, using DBINFO in a
SQL procedure? I can't seem to find one anywhere. I don't see the
linkage between the structure and the SQL language variables in the
documentation.

A SCRATCHPAD example would be useful too.

I need to identify which user is executing a stored procedure, then
pick up the same information in a trigger. The application runs on
several platforms so I don't want to code a c procedure.

Are you sure this is a DB2 feature? There is a DBINFO in IBM Informix
Dynamic Server, but I was under the illusion that it was peculiar to
IDS and not available anywhere else.

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[Knut Stolze]

Jonathan Leffler wrote:

Bob Sparks wrote:

Can some one post an example, or where to find one, using DBINFO in a
SQL procedure? I can't seem to find one anywhere. I don't see the
linkage between the structure and the SQL language variables in the
documentation.

A SCRATCHPAD example would be useful too.
You can't specify a SCRATCHPAD or the DBINFO clause for procedures that use
LANGUAGE SQL. Have a look at the manual for the CREATE PROCEDURE
statement:
http://www-306.ibm.com/cgi-bin/db2ww...n=r0008329.htm

The two options (and a few others) are solely for external procedures
implemented using C/Java/...
I need to identify which user is executing a stored procedure, then
pick up the same information in a trigger. The application runs on
several platforms so I don't want to code a c procedure.

Have a look at the special registers CURRENT USER, SESSION USER, SYSTEM
USER, and USER. Most probably, the USER register might be what you need.
http://www-306.ibm.com/cgi-bin/db2ww...n=r0005888.htm
Are you sure this is a DB2 feature? There is a DBINFO in IBM Informix
Dynamic Server, but I was under the illusion that it was peculiar to
IDS and not available anywhere else.

Yes, this is a DB2 UDB feature - at least since version 5, maybe even
earlier.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Bob Sparks]

Jonathan Leffler <jl******@earthlink.net> wrote in message news:<wq*****************@newsread1.news.pas.earth link.net>...

Bob Sparks wrote:

Can some one post an example, or where to find one, using DBINFO in a
SQL procedure? I can't seem to find one anywhere. I don't see the
linkage between the structure and the SQL language variables in the
documentation.

A SCRATCHPAD example would be useful too.

I need to identify which user is executing a stored procedure, then
pick up the same information in a trigger. The application runs on
several platforms so I don't want to code a c procedure.

Are you sure this is a DB2 feature? There is a DBINFO in IBM Informix
Dynamic Server, but I was under the illusion that it was peculiar to
IDS and not available anywhere else.

Sure there is are C & Java examples in the samples folder but no SQL.
(..\SQLLIB\samples\java\jdbc\DbInfo.java
...\SQLLIB\samples\c\dbinfo.c) I basically need to access some kind of
common area to synchronize routines and to pass information globally.
So any mechanism will do.

[Knut Stolze]

Bob Sparks wrote:

Jonathan Leffler <jl******@earthlink.net> wrote in message
news:<wq*****************@newsread1.news.pas.earth link.net>...

Bob Sparks wrote:

> Can some one post an example, or where to find one, using DBINFO in a
> SQL procedure? I can't seem to find one anywhere. I don't see the
> linkage between the structure and the SQL language variables in the
> documentation.
>
> A SCRATCHPAD example would be useful too.
>
> I need to identify which user is executing a stored procedure, then
> pick up the same information in a trigger. The application runs on
> several platforms so I don't want to code a c procedure.

Are you sure this is a DB2 feature? There is a DBINFO in IBM Informix
Dynamic Server, but I was under the illusion that it was peculiar to
IDS and not available anywhere else.

Sure there is are C & Java examples in the samples folder but no SQL.
(..\SQLLIB\samples\java\jdbc\DbInfo.java
..\SQLLIB\samples\c\dbinfo.c) I basically need to access some kind of
common area to synchronize routines and to pass information globally.
So any mechanism will do.

Could you possibly describe your scenario/problem in more detail so that we
know and understand the real requirements? Maybe we can come up with a
solution for you.

I believe that even a SCRATCHPAD, if it were available for LANGUAGE SQL
functions, would not be any help for you.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Bob Sparks]

Knut Stolze <st****@de.ibm.com> wrote in message news:<cb**********@fsuj29.rz.uni-jena.de>...

Bob Sparks wrote:

Jonathan Leffler <jl******@earthlink.net> wrote in message
news:<wq*****************@newsread1.news.pas.earth link.net>...

Bob Sparks wrote:

> Can some one post an example, or where to find one, using DBINFO in a
> SQL procedure? I can't seem to find one anywhere. I don't see the
> linkage between the structure and the SQL language variables in the
> documentation.
>
> A SCRATCHPAD example would be useful too.
>
> I need to identify which user is executing a stored procedure, then
> pick up the same information in a trigger. The application runs on
> several platforms so I don't want to code a c procedure.

Are you sure this is a DB2 feature? There is a DBINFO in IBM Informix
Dynamic Server, but I was under the illusion that it was peculiar to
IDS and not available anywhere else.

Sure there is are C & Java examples in the samples folder but no SQL.
(..\SQLLIB\samples\java\jdbc\DbInfo.java
..\SQLLIB\samples\c\dbinfo.c) I basically need to access some kind of
common area to synchronize routines and to pass information globally.
So any mechanism will do.

Could you possibly describe your scenario/problem in more detail so that we
know and understand the real requirements? Maybe we can come up with a
solution for you.

I believe that even a SCRATCHPAD, if it were available for LANGUAGE SQL
functions, would not be any help for you.

Thanks Knut .. I hope you are still reading this thread :-)

We want to log who changed which record. Now as with many
installations we are not using RDBMS authentication. We use our own
security as we have row level security based on the enterprise
directory. So each transaction runs from a connection pool. At the
time the connection is handed from the pool I would like to write the
users enterprise id into a scratchpad. My understanding is that any
code running on that connection would get the same scratchpad. So
therefore.
1. Write enterprise id into a fenced scratchpad and return connection
..
2. Perform any arbitrary procedure.
3. Step 2 fires of a trigger on change of a record.
4. Access the scratchpad and write the enterprise id to audit log from
inside the trigger.

This get around the fact the triggers aren't passed parameters other
than what you can get from dbinfo or the changed record's columns.
My routine, in pseudocode would simply

ENTID (userid varchar(30))
returns varchar
scratchpade
fenced
begin
declare scratchpad cookie varchar(30)
if userid is not null then
set cookie = userid
end if
return cookie
end

[Knut Stolze]

Bob Sparks wrote:

Could you possibly describe your scenario/problem in more detail so that
we
know and understand the real requirements? Maybe we can come up with a
solution for you.

I believe that even a SCRATCHPAD, if it were available for LANGUAGE SQL
functions, would not be any help for you.
Thanks Knut .. I hope you are still reading this thread :-)

We want to log who changed which record. Now as with many
installations we are not using RDBMS authentication. We use our own
security as we have row level security based on the enterprise
directory. So each transaction runs from a connection pool. At the
time the connection is handed from the pool I would like to write the
users enterprise id into a scratchpad.

Ok, I think I get the idea. You like to have some sort of register/storage
where you can put some information, i.e. the user name.
My understanding is that any
code running on that connection would get the same scratchpad. So
therefore.

That's not correct. Each UDF (that can have a scratchpad) gets its own
memory area. Even if you call the same UDF within a single statement more
than once, then each occurrence of the UDF would have its own, independent
scratchpad. Things become worse if the DB2 optimizer changes the plan is
such a way that the number of occurrences of the UDF changes or if you have
a partitioned environment.

For example, the following statement has 3 different, independent scratchpad
areas.

SELECT UDF1(), UDF2()
FROM table
WHERE UDF1() = <something>
You have several options to implement your scenario.
(1) Use a base table to store your data at the beginning of the connection
and remove it at the end. Of course, other connections would access the
same table and you get a bunch of problems (concurrency, how to clean up
stale entries if the application crashes, etc.) So I wouldn't do that.

(2) Use a global temporary table. At the beginning of the connection, you
create the temp table (or clean an existing one) and store the relevant
information. If the connection to DB2 is terminated (without the pooling),
then DB2 will remove the table. And global temp tables are only visible in
the current connection.

So you would do something like this:

1. DECLARE GLOBAL TEMPORARY TABLE ...
2. INSERT INTO <temp-table> ...
3. Upon insert, fire a trigger that runs a SELECT on the temp table and gets
the information you want
(3) Write your own external function to store the data somewhere else like
in the file system or shared memory. For that, you would have to resort to
Java or C/C++ code and that's not something you wanted. Besides, you will
still have to solve some problems if you have a partitioned database.
(4) You could "abuse" one of DB2's special registers like "CURRENT SCHEMA"
or so. But please be aware of the impact it may have on the SQL statement
you execute.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena