Hi,

I've two queries regarding SQL stored procedures :

(The IBM DB2 Server Version I work on is DB2/LINUX 7.2.0)

Query 1 : How to define my own package for SQL stored procedure ?
I don't know how to control the package in which a SQL Stored Procedure is getting created. Is there a DB2 command equivalent to Oracle's "CREATE PACKAGE" statement?

Query 2 : Is there an "ALIAS" equivalent command for calling SQL Stored Procedure?
I've created 2 db users - 1st user with all privileges - TESTADMIN & 2nd user with only read only privilege - TESTREAD. I have executed a SQL stored procedure called TEST_PROC in TESTADMIN login mode. I want to execute the same from TESTREAD user login. I can execute the command "GRANT EXECUTE ON PACKAGE xxxxx TO USER TESTREAD", & then from the TESTREAD user login, I have to execute "CALL TESTADMIN.TEST_PROC" command. (If it were a table/view, I can use CREATE ALIAS for SCHEMA.TABLENAME & refer the table simply by omitting the schema name.) What I want to know is - Is there a way that I can omit the schema name & just give "CALL TEST_PROC"? (I don't want to use SET SCHEMA statement)

Any help would be greatly appreciated.

Regards
Aneesh

Aneesh,

In general I would say you can't do what you want in the simple manner you want when you exclude the DB2 command that does it but..

1. Have you tried setting the package name as a BIND variable in development centre when you build the stored procedure?

2. Have tried setting the FUNCPATH as a bind variable as I gather the SET PATH would also not be appropriate for you. You can also set schema and funcpath in the ODBC/CLI config file for the database connection.

Did have time to test these but they may work, I could work exactly what you want question 1 to achieve.

Tony Winch
www.datasync.com.au