Posted: Thu Apr 15, 2004 2:20 pm Post subject: DB2 Stored Procedure and DB2 Catalog
When calling Stored Procedures running in a SPAS environment from a remote location, what is the necessity and the use of a DB2 Node, DB2 Catalog and Schema Name?
How can these be determined?
Is DB2 Node the same as the Location? That which is displayed on the QMF Screen as "Connected to ...."
Edited to add:
Also, to call the Stored Procedure, do we have to insert a row into SYSIBM.SYSPROCEDURES? I have used the CREATE PROCEDURE and GRANT EXECUTE and thus was thinking that inserting into SYSPROCEDURES would be wrong. Any advice? Also does the userid have to have SELECT access to the SYSPROCEDURES table??? Thanks! _________________ Thanks & Regards,
Manoj.
The advantage of using the second form is that you do not need to execute a CONNECT statement. The disadvantage is that this form of the CALL statement is not portable to other platforms.
Quote:
Is DB2 Node the same as the Location? That which is displayed on the QMF Screen as "Connected to ...."
No DB2 Node is different from location name. Location is the unique name of a database server. An application uses the location name to access a DB2 database server.
LU (logical unit name) is what vtam refers to a NODE in a network.
Thanks for the quick reply. However, I am unable to get how to determine the DB2 Node and the DB2 Catalog. Is there a way to do this? I guess the catalog tables is different from the DB2 Catalog, as I am being asked for the DB2 Catalog Name by the team trying to connect to the Stored Procedure. The catalog tables would be the same through all installations of DB2.
What is the use of the Node when calling a stored procedure. As you mentioned above, they would use the location.
Also, as I added in the Edited version, do you have an idea on this. To call the Stored Procedure, do we have to insert a row into SYSIBM.SYSPROCEDURES? I have used the CREATE PROCEDURE and GRANT EXECUTE and thus was thinking that inserting into SYSPROCEDURES would be wrong. Any advice? Also does the userid have to have SELECT access to the SYSPROCEDURES table???
Thanks once again for your help. _________________ Thanks & Regards,
Manoj.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Fri Apr 16, 2004 5:12 am Post subject:
Manoj,
Quote:
I guess the catalog tables is different from the DB2 Catalog, as I am being asked for the DB2 Catalog Name by the team trying to connect to the Stored Procedure. The catalog tables would be the same through all installations of DB2.
I am not really sure as to what you meant by DB2 catalog. But I don't think you need any of catalog tables when trying to connect to stored procedure. The DB2 system catalog tables(prefixed sysibm) are the same through all installations of DB2.
Quote:
Also, as I added in the Edited version, do you have an idea on this. To call the Stored Procedure, do we have to insert a row into SYSIBM.SYSPROCEDURES? I have used the CREATE PROCEDURE and GRANT EXECUTE and thus was thinking that inserting into SYSPROCEDURES would be wrong. Any advice? Also does the userid have to have SELECT access to the SYSPROCEDURES table???
You must define your stored procedure to DB2. DB2 Versions 4,5,6 use the SYSIBM.SYSPROCEDURES table to keep information about stored procedures.If you are using DB2 Version 7 then it is sysibm.sysroutines. DBA at your shop usually does the addition of the row into these tables. Every stored procedure must have at least one row in the SYSIBM.SYSPROCEDURES table. When DB2 receives an SQL CALL statement, it searches for information about the stored procedure in the SYSIBM.SYSPROCEDURES catalog table.
DB2 uses the information in the SYSIBM.SYSPROCEDURES table to:
Obtain the load module name associated with the stored procedure name, authorization ID, and LU name
Verify the parameters required by the stored procedure
Validate the parameters supplied by the calling application
Perform data conversion for the parameters when required DB2 for MVS/ESA
Specify run-time options for LE/370
Here are the basic steps in creating a stored procedure.
Write a stored procedure program in language of choice (C, Cobol, PL/I, Assembler)
Write the calling application program in language of choice.
Bind a package for the stored procedure.
Bind a package or plan for the calling application.
Define the stored procedure in the catalog table SYSIBM.SYSPROCEDURES.
Regarding the authorization to access the sysprocedure table, it varies from shop to shop. At my shop we use DB2V7 , I can browse sysprocedures tables but not the sysroutines table. So give it a try and see if you get a -551 which means that you are not authorized.
I would recommend you to go thru this excellent redbook which explains in detail about Getting Started with DB2 Stored Procedures
Thanks for your extensive explanation. A few of the issues have now been resolved.
When the people trying to connect to our database asked for the DB2 Catalog, we were confused like above, and we could not understand what they wanted. It turned out that we did not need to give them any information and it was the catalog they would create to connect to our database.
Quote:
db2 "catalog tcpip node XXXX remote XXX.YYY.COM server 5000 remote_instance db2 ostype mvs with 'ABCD T'";
db2 "catalog dcs db YYYY as Location parms ',,INTERRUPT ENABLED' with 'ABCD T'";
db2 "catalog db YYYY as ZZZZ at node XXXX authentication dcs with 'ABCD T'";
db2 "connect to ZZZZ user username using ????;
I am yet to get feedback if they could call the SP or not.
What I could not gather is this... When i do a "CREATE PROCEDURE" it creates a row in sysroutines, but does it also create a row in sysprocedures??? I do not have access to both these tables. We are using DB2 V7 and thus what I was thinking is, does the user id executing the stored procedure need access to the sysprocedures table. The reason im asking this is because you had mentioned above that
Quote:
When DB2 receives an SQL CALL statement, it searches for information about the stored procedure in the SYSIBM.SYSPROCEDURES catalog table.
and as the executing user id does not have access to this table, would it succeed.
I am towards the inclination that if we use the CREATE PROCEDURE statement, then we would NOT have to manually/separately insert rows into any table. However I am un-sure whether the executing user id should have read access to the sysprocedures table or not.
Comments are welcome. Thanks! _________________ Thanks & Regards,
Manoj.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Thu Apr 22, 2004 10:49 am Post subject:
Manoj,
Quote:
What I could not gather is this... When i do a "CREATE PROCEDURE" it creates a row in sysroutines, but does it also create a row in sysprocedures??? I do not have access to both these tables. We are using DB2 V7 and thus what I was thinking is, does the user id executing the stored procedure need access to the sysprocedures table. The reason im asking this is because you had mentioned above that Quote:
When DB2 receives an SQL CALL statement, it searches for information about the stored procedure in the SYSIBM.SYSPROCEDURES catalog table.
and as the executing user id does not have access to this table, would it succeed
Prior to version 7 DB2 used the information in sysibm.sysprocedures to validate the stored procedure. In versions 4 & 5 of DB2 you have to manually insert a row into the sysibm.sysprocedures whenever you create a stored procedure. But with DB2 version 6 IBM automated the task of inserting a row into the catalog table when the CREATE PROCEDURE statement is execueted.
A row will only be inserted in sysibm.sysroutines when you execuete the "CREATE PROCEDURE " in DB2 V7. You don't need a row in the sysibm.sysprocedures table.
It is true that when DB2 receives an SQL CALL statement, it searches for information about the stored procedure in the SYSIBM.SYSPROCEDURES or SYSIBM.SYSROUTINES catalog table. It makes sure that the stored procedure is defined to DB2. It has got nothing to do with the userid.
Thanks for the explanation Kolusu. Sure has cleared up my thoughts on how it goes about doing it. Good to know that the calling userid does not need access. Thanks once again. _________________ Thanks & Regards,
Manoj.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
