Hello All,

Good day.

I have just found out that our test and production environments are very different. Some of the procedures developed in test do not function in production.


Here is the grant authority in test for ADMIN_CMD:

GRANTOR.................GRANTEE..............GRANT EETYPE.....SCHEMA
--------------------------------------------------------------------
SYSIBM...................I2TESTENV............U... ..................SYSPROC
I2TESTENV...............PUBLIC.................G.. ...................SYSPROC
--------------------------------------------------------------------

SPECIFICNAME......ROUTINETYPE......EXECUTEAUTH
--------------------------------------------------------------------
ADMIN_CMD.................P.....................G
ADMIN_CMD.................P.....................Y
--------------------------------------------------------------------


This is the grant authority in production for ADMIN_CMD:

GRANTOR.................GRANTEE..............GRANT EETYPE.....SCHEMA
-------------------------------------------------------------------
SYSIBM...................PUBLIC.................U. .................SYSPROC
-------------------------------------------------------------------


SPECIFICNAME......ROUTINETYPE......EXECUTEAUTH
-------------------------------------------------------------------
ADMIN_CMD.................P.....................G
-------------------------------------------------------------------

Our instance user in production is I2PRODENV. All this were queried using
select * from syscat.routineauth where specificname like '%ADMIN%';


My problem is how do I grant the Execute Authority to I2PRODENV (or any user under production) the SYSPROC.ADMIN_CMD.

Thank you in advance.

Either I missed something vital in your question or the answer is really a simple GRANT EXECUTE statement:

http://publib.boulder.ibm.com/infoce...c/r0007699.htm

Anyway, the instance owner I2PRODENV has SYSADM authority, which includes DBADM. Therefore, that user implicitly has EXECUTE privileges on the routine already.

Thanks stolze. Pardon me for not understanding. How was it that you determined I2PRODENV has SYSADM authority?

I have been toying around with that GRANT EXECUTE but does it follow that once the PUBLIC has EXECUTE privilege all other users will have that too? :-)

Thanks again.

You said that yourself: "Our instance user in production is I2PRODENV."

If you grant the EXECUTE privilege to PUBLIC, then the answer is yes. But you could also grant that privilege to specific users or groups. So you can apply a much finer control if necessary.

Thanks Stolze. Now its turning up to be not an authority problem. It still is returning an "Unexpected System Error".

Thanks for all the help.

Hello,

I still keep getting this error.

call MDMPROD.PROC_SCP_BKP;
SQL1042C An unexpected system error occurred. SQLSTATE=58004

Thanks

Regarding the same problem above.

Executing the ADMIN_CMD in production won't work. We have this one rs6000 partitioned to hold the Database servers(SERVER1) and the Applications server (SERVER2). The database holds the test and production database and the applications server holds all applications...

Using db2 connect on the command line. I can access the test database in SERVER1 and perform a stored proc using the ADMIN_CMD. But I can't access the prod database in SERVER1. And a stroed proc using ADMIN_CMD produces the: call MDMPROD.PROC_SCP_BKP;
SQL1042C An unexpected system error occurred. SQLSTATE=58004.

BUT I can access the prod database using db2 connect from SERVER2.

Our DBA can't help us says its our problem not his. I was pulled from a different project and dumped into this one.

Any help would be greatly appreciated.

If you get an SQL1042, you may want to have a look at the output from the db2diag command to figure out if something was logged during the operation. That can help you (or your DBA) to diagnose and fix the issue.

p.s: You should try to get a new DBA because SQLxxxxC are critical errors, which should be investigated. And those are the DBA's problem. If you get stuck, you can call IBM support and open a PMR to get this resolved.