Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: creating explain tables for a user

    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?

  2. #2
    Join Date
    Dec 2007
    Posts
    288
    Can I just generate DDL for existing explain tables and create new ones using the USERS schema name? DB2 Help says to do this:

    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
    Last edited by itsonlyme44; 04-06-09 at 16:49.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Posts
    288
    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");
    Last edited by itsonlyme44; 04-07-09 at 11:13. Reason: add code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •