473,695 Members | 1,815 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Describe non-table objects

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.
--

Feb 19 '07 #1
12 4129
On Feb 19, 7:35 pm, "Serman D." <serma...@hotma il.comwrote:
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.function s where (funcschema, funcname) = (?,?)

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

HTH
/Lennart

Feb 19 '07 #2
Lennart wrote:
On Feb 19, 7:35 pm, "Serman D." <serma...@hotma il.comwrote:
>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.function s where (funcschema, funcname) = (?,?)

select text fropm syscat.procedur es where (procschema, procname) =
(?,?)
Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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
Feb 19 '07 #3
On Feb 19, 9:03 pm, Knut Stolze <sto...@de.ibm. comwrote:
[...]
Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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
Feb 19 '07 #4
Lennart wrote:
On Feb 19, 9:03 pm, Knut Stolze <sto...@de.ibm. comwrote:
[...]
>Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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
Feb 19 '07 #5

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=D B2INST1
ROUTINENAME=BON US_INCREASE
ROUTINETYPE=P
[..]

Serman D.
--

Feb 19 '07 #6
On Feb 19, 9:35 pm, "Serman D." <serma...@hotma il.comwrote:
[...]
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=D B2INST1
ROUTINENAME=BON US_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(hea der)):
print "%s=%s" % (header[n], row[n])
>>>
ROUTINESCHEMA=T OOLBOX
ROUTINENAME=GET _JOIN
ROUTINETYPE=F
ROUTINESCHEMA=T OOLBOX
ROUTINENAME=BUI LD_JOIN
ROUTINETYPE=F
[...]

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

/Lennart

Feb 19 '07 #7
On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:
Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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.{FUNCTIO NS,PROCEDURES,R OUTINES} 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.d k>
http://troels.arvin.dk/
Feb 20 '07 #8
Troels Arvin wrote:
On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:
>Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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.{FUNCTIO NS,PROCEDURES,R OUTINES} 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
Feb 21 '07 #9
Troels Arvin wrote:
On Mon, 19 Feb 2007 20:03:10 +0100, Knut Stolze wrote:
>Actually, SYSCAT.FUNCTION S and SYSCAT.PROCEDUR ES 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.{FUNCTIO NS,PROCEDURES,R OUTINES} 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
Feb 21 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2144
by: f | last post by:
I am writing a java code generation tool. The tool will take a java class description written in xml and translate it to java code using xslt. I am looking for samples of xml files that describe java class. Any help appreciated. ff
1
1584
by: Bruce W.1 | last post by:
I want an XML file to describe hierarchical data where each node is different. Each node will contain different and varied other nodes. Describing this in an XML file would be easy but I'm having a problem with this as related to DTD or XSD. What the XSD really needs to do is describe a node, which can contain other nodes. It could not describe the hierarchy of all the nodes in the XML file. Is it possible to describe a free-form...
7
1855
by: Phlip | last post by:
C++ dudes and dudettes: Ogle this code: static char * p; struct Q { char * p; }; static Q q; int main() { assert(p == NULL);
5
1197
by: Reuven Nisser | last post by:
Hi, How can I describe an XML like this: <X> <Y/> <Z/> <Y/> <Y/> </X>
10
1248
by: mattdaddym | last post by:
Hi all, I'm writing an application in asp .net and vb .net. The question I have just verifies I need to take an official programming course instead of this "learning on the fly" stuff :) Here's the situation. I have a site that people will access from different site codes (site code = building ID). Each user will have a profile, and permissions to the system based on the profile. The user currently can only makes changes to the...
1
1655
by: Daniel | last post by:
Can someone please describe why impersonation requires the impersonator to be local admin?
0
1031
by: tamnguyen | last post by:
Hi everybody, I can't describe the package that has content: CREATE OR REPLACE PACKAGE TEST_NESTED_TYPES IS TYPE EmpRecord1 IS Record ( LEVEL_NO NUMBER(1) default 1, EMPNO NUMBER(4),
3
2432
by: topsyk | last post by:
Is it possible to get just the type length from a describe command in db2?
1
2919
by: kuntal dandapat | last post by:
2. Describe the operators and constants (Symbols) in Visual Basic .Net.
0
8623
marktang
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...
0
8565
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,...
0
8822
tracyyun
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...
0
7656
agi2029
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...
0
5837
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();...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2997
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.