Describe non-table objects

[Serman D.]

Hi all,

How do I describe non-table objects, like UDF functions and
procedures, using db2's normal command line interface?

Database server = DB2/LINUX 9.1.0 Express-C running on Linux 2.6
Intel-32.

Regards,
Serman D.
--

[Lennart]

On Feb 19, 7:35 pm, "Serman D." <[e-mail]:

Hi all,

How do I describe non-table objects, like UDF functions and
procedures, using db2's normal command line interface?

Database server = DB2/LINUX 9.1.0 Express-C running on Linux 2.6
Intel-32.

Regards,
Serman D.
--

select body from syscat.functions where (funcschema, funcname) = (?,?)

select text fropm syscat.procedures where (procschema, procname) =
(?,?)

HTH
/Lennart

[Knut Stolze]

Lennart wrote:

On Feb 19, 7:35 pm, "Serman D." <[e-mail]:

>Hi all,

How do I describe non-table objects, like UDF functions and
procedures, using db2's normal command line interface?

Database server = DB2/LINUX 9.1.0 Express-C running on Linux 2.6
Intel-32.

select body from syscat.functions where (funcschema, funcname) = (?,?)

select text fropm syscat.procedures where (procschema, procname) =
(?,?)

Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Lennart]

On Feb 19, 9:03 pm, Knut Stolze <[e-mail]:
[...]

Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Thanx, I wasn't aware of that. Is this new for V9?

/Lennart

[Knut Stolze]

Lennart wrote:

On Feb 19, 9:03 pm, Knut Stolze <[e-mail]:
[...]

>Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Thanx, I wasn't aware of that. Is this new for V9?

V8.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serman D.]

Knut Stolze wrote:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Thanks for your prompt response, Knut and Lennart.

I there a simple way to display the output vertically? (Either by some
built-in command option or by adjusting the field separator character
or something?)

E.g.

ROUTINESCHEMA=DB2INST1
ROUTINENAME=BONUS_INCREASE
ROUTINETYPE=P
[..]

Serman D.
--

[Lennart]

On Feb 19, 9:35 pm, "Serman D." <[e-mail]:
[...]

I there a simple way to display the output vertically? (Either by some
built-in command option or by adjusting the field separator character
or something?)

E.g.

ROUTINESCHEMA=DB2INST1
ROUTINENAME=BONUS_INCREASE
ROUTINETYPE=P
[..]

Serman D.

I dont think so. However it should rather easy to write a procedure or
a function in your host language that does that. Example in python:

import DB2
import sys

conn = DB2.connect(...)
sql = """
select routineschema, routinename, routinetype
from syscat.routines where routineschema = 'TOOLBOX'
"""
c1 = conn.cursor()
c1.execute(sql)
header = map(lambda t:t[0], c1.description)
for row in c1.fetchall():
# transpose resultset
for n in range(0,len(header)):
print "%s=%s" % (header[n], row[n])

>>>

ROUTINESCHEMA=TOOLBOX
ROUTINENAME=GET_JOIN
ROUTINETYPE=F
ROUTINESCHEMA=TOOLBOX
ROUTINENAME=BUILD_JOIN
ROUTINETYPE=F
[...]

Another idea is to import the output to excel, and transpose it there

/Lennart

[Troels Arvin]

On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:

Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Are there other ways to inspect code in user defined functions?

- You see: I would like to close down access to the
SYSCAT.{FUNCTIONS,PROCEDURES,ROUTINES} views because I don't want to allow
users to see the source of other user's UDFs/procedures.

An idea: For SYSCAT.ROUTINES, set up a view allowing access to CURRENT
USER's routines only. Does this sound reasonable?

Are there other ways to allow the users to view the contents of the
UDFs/SPs that they have created? - Or maybe even: A way to let users see
the body of routines owned by other users that share group membership with
the current user (while excluding others)?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Knut Stolze]

Troels Arvin wrote:

On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:

>Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Are there other ways to inspect code in user defined functions?

Not that I know of. The only other tools like db2look etc. would also
require certain privileges on the SYSCAT views, which you apparently don't
want to give away in the first place, right?

- You see: I would like to close down access to the
SYSCAT.{FUNCTIONS,PROCEDURES,ROUTINES} views because I don't want to allow
users to see the source of other user's UDFs/procedures.

An idea: For SYSCAT.ROUTINES, set up a view allowing access to CURRENT
USER's routines only. Does this sound reasonable?

Definitively.

Are there other ways to allow the users to view the contents of the
UDFs/SPs that they have created? - Or maybe even: A way to let users see
the body of routines owned by other users that share group membership with
the current user (while excluding others)?

Group memberships are a bit tricky. You will have to implement your own UDF
that determines if a user is in a certain group. Depending on your setup,
you may have to talk to the security plugin for that.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Troels Arvin wrote:

On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:

>Actually, SYSCAT.FUNCTIONS and SYSCAT.PROCEDURES are both outdated and
should not be used any longer. Instead, refer to SYSCAT.ROUTINES:

SELECT text
FROM syscat.routines
WHERE ( routine_schema, routine_name ) = ( ?, ? )

Are there other ways to inspect code in user defined functions?

- You see: I would like to close down access to the
SYSCAT.{FUNCTIONS,PROCEDURES,ROUTINES} views because I don't want to allow
users to see the source of other user's UDFs/procedures.

An idea: For SYSCAT.ROUTINES, set up a view allowing access to CURRENT
USER's routines only. Does this sound reasonable?

Are there other ways to allow the users to view the contents of the
UDFs/SPs that they have created? - Or maybe even: A way to let users see
the body of routines owned by other users that share group membership with
the current user (while excluding others)?

For my education... would you mind giving some background WHY you need
this capability?
How come your users aren't allowed to see each other's source code?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Troels Arvin]

On Wed, 21 Feb 2007 07:53:02 -0500, Serge Rielau wrote:

For my education... would you mind giving some background WHY you need
this capability?
How come your users aren't allowed to see each other's source code?

When reading a document like
http://publib.boulder.ibm.com/infoce...c/r0001941.htm
- especially the section on the RESTRICTIVE option, it suggests to me
that one of the things to consider is narrowing down access to certain
catalog views.

And I've seen that it may surprise users that the code of their SQL
routines is available to others. If you assume that your code is only
readable by yourself, you may put things into the code which aren't meant
to be read by others.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Serge Rielau]

Troels Arvin wrote:

And I've seen that it may surprise users that the code of their SQL
routines is available to others. If you assume that your code is only
readable by yourself, you may put things into the code which aren't meant
to be read by others.

Like what? Crappy code? Back doors?
Is this about fear a developer runs away with your source code?

Cheers
Serge

PS: You can hide SQL procedure source code using GET/PUT_ROUTINE btw.
that is used by ISVs to protect their IP.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Troels Arvin wrote:

>And I've seen that it may surprise users that the code of their SQL
routines is available to others. If you assume that your code is only
readable by yourself, you may put things into the code which aren't meant
to be read by others.

Like what? Crappy code? Back doors?
Is this about fear a developer runs away with your source code?

I think hiding this kind of information could be very helpful in school,
where you don't want to have one (or more) teams that just copies the stuff
from other teams. But then, you could fix that by using different
databases and managing access on that level. Besides that, I don't see any
real point either.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany