ravindra.vadali New User Joined: 30 Jan 2012 Posts: 34 Location: USA
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
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
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
Akatsukami Global Moderator Joined: 03 Oct 2009 Posts: 1784 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.
Anuj Dhawan Senior Member Joined: 22 Apr 2006 Posts: 6258 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.
magesh23586 Active User Joined: 06 Jul 2009 Posts: 212 Location: Chennai
A sample runstat for your reference.
//DSNNPROC EXEC PGM=DSNUTILB,REGION=5000K,
//STEPLIB DD DSN=YOUDB2LOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=D
//SYSIN DD *
RUNSTATS TABLESPACE ZZZZ.YYYYY
Note system uid utproc and STEPLIB varies as per your shop installation.
sushanth bobby Senior Member Joined: 29 Jul 2008 Posts: 1013 Location: India
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.
All times are GMT + 6 Hours