PASSING A COLUMNS NAME TO TABLE FUNCTION

[dharmadam]

Is it possible to pass a column name or the order of the column name
in the DB2 table table function. For example, I want to update the
address of a person by passing one of the address column name like ZIP
CODE or ADDRESS LINE. I will call the function with three
parameter--UpdateAddress(5,zip_code,person_id) where 5 indicates
ZIP_CODE is the fifth column in the table. If 4 is passed, it
indicates the address line is to be updated.

Within the function UpdateAddress, it should identify that the caller
wants to update the zip code or the address line.

[Rhino]

"dharmadam" <dh***********@hotmail.com> wrote in message
news:df**************************@posting.google.c om...

Is it possible to pass a column name or the order of the column name
in the DB2 table table function. For example, I want to update the
address of a person by passing one of the address column name like ZIP
CODE or ADDRESS LINE. I will call the function with three
parameter--UpdateAddress(5,zip_code,person_id) where 5 indicates
ZIP_CODE is the fifth column in the table. If 4 is passed, it
indicates the address line is to be updated.

Within the function UpdateAddress, it should identify that the caller
wants to update the zip code or the address line.

I'm not sure. You haven't said which language you are using for your Table
function. You also haven't specified which DB2 version and platform you are
using.

You can find all of the manuals, except for DB2 for AS/400, at this URL:
http://www-306.ibm.com/software/data/db2/library/

I don't recall where the AS/400 manuals are.

Try looking in the Application Development Guide for your platform to get
the general principles of table functions for that platform. Use the SQL
Reference to get the exact syntax of the "CREATE FUNCTION (External Table)"
or "CREATE FUNCTION (SQL Scalar, Table or Row)" statement for that platform.

I don't think you should have a problem passing an integer as one of the
input parameters of your function. However, your table function *may* have
some work to do to convert that column number to an actual column name so
that the function can do whatever it is supposed to do. You'll probably need
to do some catalog lookups to determine which column of the table is the 5th
column. I don't recall if you can do catalog queries in a table function so
you'll need to figure this out from the manuals. It usually depends on which
language you are using for your function and which DB2 version and platform
you are using.

Rhino

[dharmadam]

Sorry that I failed to mention the machine and DB2 version. The
machine is IBM P650 running Unix, the DB is DB2 V8 FP6. I might use
JAVA. Is the table function available in IBM DB2 Stored Procedures?

[Rhino]

"dharmadam" <dh***********@hotmail.com> wrote in message
news:df**************************@posting.google.c om...

Sorry that I failed to mention the machine and DB2 version.
Don't worry, a *lot* of people forget to give that information. Most DB2
questions are very hard to answer without that information because something
that is true for one OS or version is sometimes not true for a different OS
or version.
The machine is IBM P650 running Unix, the DB is DB2 V8 FP6. I might use
JAVA. Is the table function available in IBM DB2 Stored Procedures?

I just read your original question again but I'm still confused about what
you're trying to do, particularly the role of the table function.

I have only written a very few table functions so I may be missing
something. However, it is my understanding that the primary role of a table
function is to get data that isn't in DB2 from an outside source like a flat
file so that DB2 can work with it. I don't think table functions were ever
intended to be used to *update* that external data. If you're planning to
update addresses outside of DB2 via a table function, I don't think it will
work. You can *get* the addresses from outside DB2 with the table function
but you can't *update* them that way.

On the other hand, if you are getting the new addresses from outside DB2 and
intend to update old addresses *that are in a DB2 table* with the external
data, it appears that this is possible. As far as I can tell from the SQL
Reference, you should have no problem in passing the parameters you
described in the original post to your table function. Again, you will
probably need to have some kind of SQL SELECT statement in the table
function logic to determine the name of the column whose number you pass to
the function. You should also have no problem updating the address or zip
code or whatever within your table function. You probably don't need a
stored procedure at all for what you are trying to do. [Please note that
this paragraph is just my best guess about what is possible, based on what I
found in the SQL Reference for table functions. I may be misunderstanding
something in the manual and I could have missed a footnote that would
contradict something I've said. Unfortunately, I don't have V8 myself so I
can't tell you with 100% certainty that what you want to do is possible. If
I had V8, I'd probably try a simple prototype to be absolutely sure that it
would work.]

In answer to your question in *this*post, I'm really not sure if a table
function can appear within a stored procedure. I've never tried that myself.
Honestly, I'd be a little surprised if you could put a table function in a
stored procedure since they have different purposes but that is just a
guess; I've been wrong before ;-)

If I've misunderstood what you are trying to do, please explain in more
detail and perhaps I, or someone else who reads this newsgroup, can help
more.

Rhino

[Knut Stolze]

dharmadam wrote:

Is it possible to pass a column name or the order of the column name
in the DB2 table table function. For example, I want to update the
address of a person by passing one of the address column name like ZIP
CODE or ADDRESS LINE. I will call the function with three
parameter--UpdateAddress(5,zip_code,person_id) where 5 indicates
ZIP_CODE is the fifth column in the table. If 4 is passed, it
indicates the address line is to be updated.

Within the function UpdateAddress, it should identify that the caller
wants to update the zip code or the address line.

Could you please describe in detail using an example and some sample data
what you want to achieve? I don't see a particular problem right now to
implement whatever you want to do but I am not sure that I understand what
you want to do.

Some more things you might want to know about (table) functions:
- usually, a function is used to compute some scalar value (or a whole table
in the case of a table function); the computation can, of course, also
access external data sources like flat files
- table function can also update other tables in the same database; this is
available since V8.1 FP4, I believe
- you can evaluate the parameters passed to a function in any way you like
and take the appropriate actions, depending on the parameters' values; so
you can decide on the value of the first parameter what should happen
- a table function returns a whole table for each distinct set of
parameters. All those tables (one for each set) are unioned together by
DB2.
- you might want to consider stored procedures for your logic. SPs allow
more SQL statements to be executed inside the procedure.

And to address your other question: yes, you can call a table function from
inside a stored procedure. After all, a table function returns a table and
you can run a SELECT against this returned table. The SELECT statement can
be run in a procedure, of course.

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

[Knut Stolze]

Rhino wrote:

I don't think table
functions were ever intended to be used to *update* that external data.
If you're planning to update addresses outside of DB2 via a table
function, I don't think it will work. You can *get* the addresses from
outside DB2 with the table function but you can't *update* them that way.
You can do that, of course, but a scalar function might be a better
approach. However, it depends on the actual scenario and what the table
function is supposed to do. Here is an example how you can modify external
things:

http://www-106.ibm.com/developerwork...303stolze.html
On the other hand, if you are getting the new addresses from outside DB2
and intend to update old addresses *that are in a DB2 table* with the
external data, it appears that this is possible. As far as I can tell from
the SQL Reference, you should have no problem in passing the parameters
you described in the original post to your table function. Again, you will
probably need to have some kind of SQL SELECT statement in the table
function logic to determine the name of the column whose number you pass
to the function. You should also have no problem updating the address or
zip code or whatever within your table function.

If you want to do this is a dynamic way, then a problem might be to (a)
identify the table the update should be run against, and (b) execute a
dynamic SQL statement. If everything is static, i.e. the function always
runs against the same table, then the update wouldn't be a problem.

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

[dharmadam]

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

dharmadam wrote:

Is it possible to pass a column name or the order of the column name
in the DB2 table table function. For example, I want to update the
address of a person by passing one of the address column name like ZIP
CODE or ADDRESS LINE. I will call the function with three
parameter--UpdateAddress(5,zip_code,person_id) where 5 indicates
ZIP_CODE is the fifth column in the table. If 4 is passed, it
indicates the address line is to be updated.

Within the function UpdateAddress, it should identify that the caller
wants to update the zip code or the address line.
Could you please describe in detail using an example and some sample data
what you want to achieve? I don't see a particular problem right now to
implement whatever you want to do but I am not sure that I understand what
you want to do.

Thanks for all your help. I will try myself some of the points
mentioned in the replies. I might be coming back with more questions
if I run in to problems. Some more things you might want to know about (table) functions:
- usually, a function is used to compute some scalar value (or a whole table
in the case of a table function); the computation can, of course, also
access external data sources like flat files
- table function can also update other tables in the same database; this is
available since V8.1 FP4, I believe
- you can evaluate the parameters passed to a function in any way you like
and take the appropriate actions, depending on the parameters' values; so
you can decide on the value of the first parameter what should happen
- a table function returns a whole table for each distinct set of
parameters. All those tables (one for each set) are unioned together by
DB2.
- you might want to consider stored procedures for your logic. SPs allow
more SQL statements to be executed inside the procedure.

And to address your other question: yes, you can call a table function from
inside a stored procedure. After all, a table function returns a table and
you can run a SELECT against this returned table. The SELECT statement can
be run in a procedure, of course.