By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Can't browse stored procedure in Navigator

P: n/a

I created a stored procedure called MANLIB.MAN535CL based on a CL/COBOL
program.

I know it works because I can run it successfully.
However, I don't really understand where it is on the iSeries.

For example, if I use Navigator to browse the "Databases" section, there
is one database, s10cd4b. Under that is the "schemas" section which
has QGPL and QTEMP and under those is "Procedures". There are no
entries under either of those?

So, is the stored procedure on the IFS?

Or somewhere else?
--
Texeme Construct
http://www.myspace.com/texeme

Aug 18 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Right-Click on Schemas and click on "Select schemas to display" and add
MANLIB to it. Now drill down to MANLIB Stored Procedures and you
should see it.

Stored procedure definitions can also be seen by doing:

SELECT * FROM SYSPROCS

John Bailo wrote:
I created a stored procedure called MANLIB.MAN535CL based on a CL/COBOL
program.

I know it works because I can run it successfully.
However, I don't really understand where it is on the iSeries.

For example, if I use Navigator to browse the "Databases" section, there
is one database, s10cd4b. Under that is the "schemas" section which
has QGPL and QTEMP and under those is "Procedures". There are no
entries under either of those?

So, is the stored procedure on the IFS?

Or somewhere else?
--
Texeme Construct
http://www.myspace.com/texeme
Aug 18 '06 #2

P: n/a
John Bailo wrote:
>
I created a stored procedure called MANLIB.MAN535CL based on a CL/COBOL
program.

I know it works because I can run it successfully.
However, I don't really understand where it is on the iSeries.

For example, if I use Navigator to browse the "Databases" section, there
is one database, s10cd4b. Under that is the "schemas" section which
has QGPL and QTEMP and under those is "Procedures". There are no
entries under either of those?

So, is the stored procedure on the IFS?

Or somewhere else?
If you didn't register your program to DB2 with the
CREATE PROCEDURE (EXTERNAL) statement, you're not going
to see it as a procedure using Navigator.
Aug 19 '06 #3

P: n/a
John Bailo wrote:
>
I created a stored procedure called MANLIB.MAN535CL based on a CL/COBOL
program.

I know it works because I can run it successfully.
However, I don't really understand where it is on the iSeries.

For example, if I use Navigator to browse the "Databases" section, there
is one database, s10cd4b. Under that is the "schemas" section which
has QGPL and QTEMP and under those is "Procedures". There are no
entries under either of those?
You need to add MANLIB to the list of schemas under
Databases.

1. Expand the Databases folder, then the Schemas
(called Libraries if you're a V5R2 or lower).
2. Right click on Schemas/Libraries, then select
Select New Schemas/Libraries to Display.
3. In the Enter Schemas/Libraries enter MANLIB and
click Add, then click OK.
4. MANLIB should now appear in your list of schemas/
libraries, and you should be able to find your
procedure.

So, is the stored procedure on the IFS?

Or somewhere else?

Aug 19 '06 #4

P: n/a
Jonathan Ball wrote:
You need to add MANLIB to the list of schemas under Databases.
THANK YOU!

That worked instantly.
>
1. Expand the Databases folder, then the Schemas
(called Libraries if you're a V5R2 or lower).
2. Right click on Schemas/Libraries, then select
Select New Schemas/Libraries to Display.
3. In the Enter Schemas/Libraries enter MANLIB and
click Add, then click OK.
4. MANLIB should now appear in your list of schemas/
libraries, and you should be able to find your
procedure.

--
Texeme Construct
http://www.myspace.com/texeme

Aug 20 '06 #5

P: n/a
John Bailo wrote:
Jonathan Ball wrote:
>You need to add MANLIB to the list of schemas under Databases.
This is great.

Not only can I see my sprocs now -- but another thing I have been
puzzling about is ALIASES. I create a lot of alias in order to be able
to use SQL to work with multimember files in the IFS. I can see them
all in the Aliases section of Schemas in Navigator!

>
THANK YOU!

That worked instantly.
>>
1. Expand the Databases folder, then the Schemas
(called Libraries if you're a V5R2 or lower).
2. Right click on Schemas/Libraries, then select
Select New Schemas/Libraries to Display.
3. In the Enter Schemas/Libraries enter MANLIB and
click Add, then click OK.
4. MANLIB should now appear in your list of schemas/
libraries, and you should be able to find your
procedure.



--
Texeme Construct
http://www.myspace.com/texeme

Aug 20 '06 #6

P: n/a
Jonathan Ball wrote:
If you didn't register your program to DB2 with the CREATE PROCEDURE
(EXTERNAL) statement, you're not going to see it as a procedure using
Navigator.
That's exactly how I created it.

As background, what I did was take someones CL/COBOL and use a CREATE
PROCEDURE from it. At that point, I can call it from the iDB .NET
provider.

I then wrote a web service that lets us interface with an OS400 COBOL
program and it can now be used companywide in any of our offices!
--
Texeme Construct
http://www.myspace.com/texeme

Aug 20 '06 #7

P: n/a
Elvis wrote:
Right-Click on Schemas and click on "Select schemas to display" and add
MANLIB to it. Now drill down to MANLIB Stored Procedures and you
should see it.
This process, which you and another poster suggested, worked perfectly.

Thank you.
>
Stored procedure definitions can also be seen by doing:

SELECT * FROM SYSPROCS
Could it be I don't have enough permissions for this?

Connected to relational database S10dcd4b on 192.168.1.43 as Jbailo -
073480/Quser/Qzdasoinit
SELECT * FROM SYSPROCS
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] SYSPROCS in JBAILO type *FILE not found. Cause . . .
.. . : SYSPROCS in JBAILO type *FILE was not found. If this is an ALTER
TABLE statement and the type is *N, a constraint or partition was not
found. If this is not an ALTER TABLE statement and the type is *N, a
function, procedure, or trigger was not found. If a function was not
found, SYSPROCS is the service program that contains the function. The
function will not be found unless the external name and usage name do
not match exactly. Examine the job log for a message that gives more
details on which function name is being searched for and the name that
did not match. Recovery . . . : Change the name and try the request
again. If the object is a node group, ensure that the DB2 Multisystem
product is installed on your system and create a nodegroup with the
CRTNODGRP CL command. If an external function was not found, be sure
that the case of the EXTERNAL NAME on the CREATE FUNCTION statement
exactly matches the case of the name exported by the service program.

Processing ended because the highlighted statement did not complete
successfully

--
Texeme Construct
http://www.myspace.com/texeme

Aug 21 '06 #8

P: n/a
It's in QSYS2 library, as are other catalogue views. For SQL naming
you can qualify it like this:

SELECT * FROM QSYS2.SYSPROCS

John Bailo wrote:
Elvis wrote:
Right-Click on Schemas and click on "Select schemas to display" and add
MANLIB to it. Now drill down to MANLIB Stored Procedures and you
should see it.

This process, which you and another poster suggested, worked perfectly.

Thank you.

Stored procedure definitions can also be seen by doing:

SELECT * FROM SYSPROCS

Could it be I don't have enough permissions for this?

Connected to relational database S10dcd4b on 192.168.1.43 as Jbailo -
073480/Quser/Qzdasoinit
SELECT * FROM SYSPROCS

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] SYSPROCS in JBAILO type *FILE not found. Cause . . .
. . : SYSPROCS in JBAILO type *FILE was not found. If this is an ALTER
TABLE statement and the type is *N, a constraint or partition was not
found. If this is not an ALTER TABLE statement and the type is *N, a
function, procedure, or trigger was not found. If a function was not
found, SYSPROCS is the service program that contains the function. The
function will not be found unless the external name and usage name do
not match exactly. Examine the job log for a message that gives more
details on which function name is being searched for and the name that
did not match. Recovery . . . : Change the name and try the request
again. If the object is a node group, ensure that the DB2 Multisystem
product is installed on your system and create a nodegroup with the
CRTNODGRP CL command. If an external function was not found, be sure
that the case of the EXTERNAL NAME on the CREATE FUNCTION statement
exactly matches the case of the name exported by the service program.

Processing ended because the highlighted statement did not complete
successfully

--
Texeme Construct
http://www.myspace.com/texeme
Aug 21 '06 #9

P: n/a
Elvis wrote:
It's in QSYS2 library, as are other catalogue views. For SQL naming
you can qualify it like this:

SELECT * FROM QSYS2.SYSPROCS
Nice, thank you.

It worked like a charm!
Aug 21 '06 #10

P: n/a
John Bailo wrote:
Elvis wrote:
Right-Click on Schemas and click on "Select schemas to display" and add
MANLIB to it. Now drill down to MANLIB Stored Procedures and you
should see it.

This process, which you and another poster suggested, worked perfectly.

Thank you.

Stored procedure definitions can also be seen by doing:

SELECT * FROM SYSPROCS

Could it be I don't have enough permissions for this?

Connected to relational database S10dcd4b on 192.168.1.43 as Jbailo -
073480/Quser/Qzdasoinit
SELECT * FROM SYSPROCS
Elvis already indicated to you that you needed to qualify the table
with a schema:

select * from qsys2.sysprocs

You could narrow this down by adding a WHERE clause:

where specific_schema = 'MANLIB'
If you are using SQL naming convention, you either need to qualify your
objects with the schema name, as above, or you need to set your default
schema to the main one that contains the tables and views you're mostly
using in your statements:

set schema manlib
Then, any table or view you reference in MANLIB doesn't need to be
qualified. Tables and views in other schemas will need qualification.
In your error statement below, you'll note that the error indicates the
table SYSPROCS (actually, it's a view, but never mind...) wasn't found
in schema JBAILO. When you are using SQL naming convention and you
don't specify an explicit default schema, as above, then DB2 assumes
your default schema is the same as your user profile.

Another important specification in addition to the current schema is
the current path. This applies to user-defined functions (UDFs) and, I
think, user-defined data types (UDTs; seems to me it should be UDDT,
but they didn't ask me.)

set path = my_schema

A difference between this and SET SCHEMA is that you can specify
multiple schemas for the current path, but only one for the current
schema. If you use a user-defined function or user-defined data type
that is not in the current path, you need to qualify it with its
schema.

>
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] SYSPROCS in JBAILO type *FILE not found. Cause . . .
. . : SYSPROCS in JBAILO type *FILE was not found. If this is an ALTER
TABLE statement and the type is *N, a constraint or partition was not
found. If this is not an ALTER TABLE statement and the type is *N, a
function, procedure, or trigger was not found. If a function was not
found, SYSPROCS is the service program that contains the function. The
function will not be found unless the external name and usage name do
not match exactly. Examine the job log for a message that gives more
details on which function name is being searched for and the name that
did not match. Recovery . . . : Change the name and try the request
again. If the object is a node group, ensure that the DB2 Multisystem
product is installed on your system and create a nodegroup with the
CRTNODGRP CL command. If an external function was not found, be sure
that the case of the EXTERNAL NAME on the CREATE FUNCTION statement
exactly matches the case of the name exported by the service program.

Processing ended because the highlighted statement did not complete
successfully

--
Texeme Construct
http://www.myspace.com/texeme
Aug 21 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.