Creating new Explain tables DB2 V9 LUW (windows)

[jpotucek]

DB2 V9 LUW (on Windows)

I need to give a user access to the explain tables so that they can run db2expln and do their own query optimization.

Can I just generate DDL for existing explain tables and create new ones using the USERS schema name?

DB2 Help says to do this:

Quote:
If DB2 has not already been started, issue the db2start command.
From the DB2 CLP prompt, connect to the database that you want to use. To connect to the SAMPLE database, issue the connect to sample command.
Create the explain tables, using the sample command file that is provided in the EXPLAIN.DDL file. This file is located in the sqllib\misc directory. To run the command file, go to this directory and issue the db2 -tf EXPLAIN.DDL command. This command file creates explain tables that are prefixed with the connected user ID. This user ID must have CREATETAB privilege on the database, or SYSADM or DBADM authority.


I really do not want to give the user CREATETAB privilege on the database!!???

 

[stevexff]

Well, you could give him CREATETAB, have him run the script, and then remove the privilege. Alternatively you may need to copy the script and edit it so that you can run it on his behalf.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fredericofonseca]

As SYSADM you can create the tables for ANY user - just set the current SQLID to the new user you want to create it for, and then issue the create commands using the full qualifier e.g. user_name.table_name

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[jpotucek]

Thanks!!! I just took the sample explain tables script that DB2 gives you and changed the schema name and permissions