|
View previous topic :: View next topic
|
| Author |
Message |
ravindra.vadali
New User
Joined: 30 Jan 2012
Posts: 34
Location: USA
|
|
|
|
Hi All,
Probably, my question was already asked. If so then please ignore it and direct me to that topc (I searched a few topics but found not relevant to my problem).
I am trying to improve the performance of a temp table and our DBAs suggested to include index on the temp table and then do a RUNSTATS on that.
I tried to accomplish that in my program, I coded the following lines
EXEC SQL
RUNSTATS ON TABLE SESSION.TEMPTABLE FOR INDEXES ALL;
But then when I do the compile I am getting the following error:
DSNH104I E DSNHPARS LINE 264 COL 10 ILLEGAL SYMBOL "RUNSTATS".
I even tried the following option
EXEC SQL
RUNSTATS INDEX(SESSION.TEMPTABLE_INDX)
REPORT YES UPDATE ALL;
but got the same error
We are using DB2 Version 10. So could you please let me know if DB V10 supports embedding RUNSTATS in application program
Please let me know if you need any further details for this problem |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| RUNSTATS is not a SQL statement; it is a DB2 utility. You cannot do what you are trying, nor is it likely to ever be possible. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| There are two formats for the RUNSTATS utility. RUNSTATS TABLESPACE and RUNSTATS INDEX. RUNSTATS TABLESPACE gathers statistics on a table space and, optionally, on tables, indexes or columns; RUNSTATS INDEX gathers statistics only on indexes. RUNSTATS does not collect statistics for clone tables or index spaces. Having said that your DBA did not mean to modify your program - instead, possibly, was suggesting to use an INDEX in your intermediate table and then run RUNSTATS to see if the Index was qualified. |
|
| Back to top |
|
|
magesh23586
Active User
Joined: 06 Jul 2009
Posts: 213
Location: Chennai
|
|
|
|
A sample runstat for your reference.
| Code: |
//DSNNPROC EXEC PGM=DSNUTILB,REGION=5000K,
// PARM='&SYSTEM,&UID,&UTPROC'
//STEPLIB DD DSN=YOUDB2LOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=D
//SYSIN DD *
RUNSTATS TABLESPACE ZZZZ.YYYYY
INDEX (ALL)
SHRLEVEL CHANGE
REPORT YES
UPDATE ACCESSPATH
/*
|
Note system uid utproc and STEPLIB varies as per your shop installation. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Ravindra,
Did you clearly say to your DBA that its a temporary table in mainframe not in LUW environment, because in DB2 LUW you can do this.
If you want this to be done in mainframe via application program you must call DSNUTILU stored procedure, here is the link to set it up.
Even before going through the above link, you go back to the DBA and CONFIRM because its NOT RECOMMENDED to execute a utility from an non-dba APPLICATION program.
Most of the time, i have seen people create a actual table with indexes for temporary purpose and clean it up before or after batch ends, instead of creating a temporary table.
Thanks,
sushanth |
|
| Back to top |
|
|
|
|
