I am using v5.5 and running against DB2 for z/os. When I open a connection and set my default schema/user ID to another ID, how come that does not get used when trying to run an explain plan. I then get access plan tables are incomplete. I have seen many threads on this topic indicating that aliaes are needed for be created. After creating the the aliases, the explain works. Is this truly the only solution For other tools, we just tell people to use a common user ID so we don’t have to potentially create hundreds of aliases. Also, what is the purpose of changing your ID upon connection only to have it not be used?
thanks, Mark
Hi Mark,
DB2 uses your auth ID to qualify the plan tables, not current schema or SQLID. That’s just the way DB2 works. If everyone logs on with a common ID you don’t need aliases.
Regards,
Adam
DB2 for z/os uses SQLID when running explains on distributed queries. This would be the same for adhoc explains, data studio or the old visual explain. I have tried to set my default schema/user ID at connection time or even run a SET CURRENT SQLID statement prior to running the query with no success. DB2 should allow it, but if Toad does not then that is a different matter.
thanks,
Mark
Hi Mark,
DB2 uses the authorization ID of your session to qualify the plan tables that the dynamic explain process uses. DB2 does not use SQLID for this purpose.
The reason you see so many threads on this topic is this behavior is very different from running SQL. If we could change Toad to force explain to use the SQLID for plan tables, we would. Then there wouldn’t be so many thread posts to write 
Regards,
Adam
Hi Mark,
After researching this I see you’re correct concerning the SQLID and qualified plan tables. Toad should be using the SQLID when checking for plan tables and running the explain.
I opened issue TDB-659 so we can track the resolution of this problem.
Thanks for bringing this up.
Regards,
Adam
