creating explain tables for a user

itsonlyme44 · #1 (**permalink**) 04-06-09, 14:27

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. Do I need to create a whole new set of explain tables for them? Can I give them access to existing explain and access plan tables?? and if so, which ones?

itsonlyme44 · #2 (**permalink**) 04-06-09, 15:45

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.

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

Marcus_A · #3 (**permalink**) 04-06-09, 16:08

Just add this to the script and run it yourself:

set schema <user-name>;

You will then have to grant insert, update, delete, and select on all the tables to the user.

itsonlyme44 · #4 (**permalink**) 04-07-09, 09:46

Can you tell me how I can run this from the command editor?

I tried this but it does not work:

Connect to DUTDM user DB2Admin using db2admin ;
-tf EXPLAIN.DDL set schema SCJCG10;

or can I just edit the sample DDL file like this:

example only

Code:

CREATE TABLE "SCJXG10 "."EXPLAIN_INSTANCE"  (
		  "EXPLAIN_REQUESTER" VARCHAR(128) NOT NULL , 
		  "EXPLAIN_TIME" TIMESTAMP NOT NULL , 
		  "SOURCE_NAME" VARCHAR(128) NOT NULL , 
		  "SOURCE_SCHEMA" VARCHAR(128) NOT NULL , 
		  "SOURCE_VERSION" VARCHAR(64) NOT NULL , 
		  "EXPLAIN_OPTION" CHAR(1) NOT NULL , 
		  "SNAPSHOT_TAKEN" CHAR(1) NOT NULL , 
		  "DB2_VERSION" CHAR(7) NOT NULL , 
		  "SQL_TYPE" CHAR(1) NOT NULL , 
		  "QUERYOPT" INTEGER NOT NULL , 
		  "BLOCK" CHAR(1) NOT NULL , 
		  "ISOLATION" CHAR(2) NOT NULL , 
		  "BUFFPAGE" INTEGER NOT NULL , 
		  "AVG_APPLS" INTEGER NOT NULL , 
		  "SORTHEAP" INTEGER NOT NULL , 
		  "LOCKLIST" INTEGER NOT NULL , 
		  "MAXLOCKS" SMALLINT NOT NULL , 
		  "LOCKS_AVAIL" INTEGER NOT NULL , 
		  "CPU_SPEED" DOUBLE NOT NULL , 
		  "REMARKS" VARCHAR(254) , 
		  "DBHEAP" INTEGER NOT NULL , 
		  "COMM_SPEED" DOUBLE NOT NULL , 
		  "PARALLELISM" CHAR(2) NOT NULL , 
		  "DATAJOINER" CHAR(1) NOT NULL )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "SCJXG10 "."EXPLAIN_INSTANCE"

ALTER TABLE "SCJXG10 "."EXPLAIN_INSTANCE" 
	ADD PRIMARY KEY
		("EXPLAIN_REQUESTER",
		 "EXPLAIN_TIME",
		 "SOURCE_NAME",
		 "SOURCE_SCHEMA",
		 "SOURCE_VERSION");

Internet Services

Web Development

Digital Marketing

Consumer Tech