Access plan for DB2 cannot be calculated - lack permission on SYSPROC.SYSINSTALLOBJECTS
db2 add tag
AngocA

I have a question about this fiddle:

<>https://dbfiddle.uk?rdbms=db2_11.1&fiddle=d21840bf2df6c905b2238624c9ddf090

I am trying to create the Explain tables in Db2, but the user that runs the fiddle does not have permission on the stored procedure that create the tables.

I think the platform should give execution permission to all:

By granting this, the execution plans for Db2 can be analyzed.

comments
transcript
3 years
Jack Douglas — Thursday, 16th Dec 2021 18:06

The explain tables are workng tables for db2 explain or db2 advise.

Yes I think I have that right though I’m not a DB2 expert. If it’s like Oracle we would need to have the explain tables in the fiddle schema as then concurrent use of explain in 2 fiddles wouldn’t clash

a day
Brian — Wednesday, 15th Dec 2021 20:22
  1. db2 set current explain mode no. The difficult will be how to run db2exfmt from a fiddle and return its output…
Brian — Wednesday, 15th Dec 2021 20:20

The explain tables are workng tables for db2 explain or db2 advise . So a user would reference the tables indirectly via 1. db2 set current explain mode explain 2. db2 –tvf indextune.sql 3. db2exfmt -1 –d myDB –o db2exfmt.out

3 hours
Jack Douglas replying to Brian — Wednesday, 15th Dec 2021 17:08

we can’t share the tables between concurrent fiddles - they need to be created in the fiddle schema I think

2 days
Brian — Monday, 13th Dec 2021 22:03

Hello, you need to create the Explain tables as the DB2 instance using call SYSPROC.SYSINSTALLOBJECTS( ‘EXPLAIN’, ‘C’ , ‘’) then grant select,update,insert,delete on the explain & db2 advise tables [ that where generated by installobjects] to your user … Lastly grant connect, explain on database to your user id. This will allow your user to run db2 explains. Note Your user, does need to even have read/write access to user data…!

Brian — Monday, 13th Dec 2021 22:01

Hello, you need to create the Explain tables as the DB2 instance using call SYSPROC.SYSINSTALLOBJECTS( ‘EXPLAIN’, ‘C’ , ‘’) then grant select,update,insert,delete on the explain & db2 advise tables [ that where generated by installobjectsto your user … Lastly grant connect, explain on database to your user

a day
Jack Douglas replying to AngocA — Sunday, 12th Dec 2021 16:24

Can you post a link to a fiddle with that error please?

14 hours
AngocA — Sunday, 12th Dec 2021 02:51

Hi Jack, it is still not enough. I need select permission on these tables:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: “FIDDLE_PPAHJTSDDNTVLMM”. Operation: “SELECT”. Object: “FIDDLE_PPAHJTSDDNTVLMMLEIRO.EXPLAIN_STRE”. SQLSTATE=42501 SQLCODE=-551

18 days
Jack Douglas — Tuesday, 23rd Nov 2021 18:40

@AngocA, re: your question, I don’t think it is safe to grant that permission, but I’ve made the fiddles pre-create the plan tables so you can EXPLAIN, is that sufficient for your use case? https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=8b7566b5ba48b6b1868ead3d8053ae43

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.