Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to embed RUNSTATS in an application program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ravindra.vadali

New User


Joined: 30 Jan 2012
Posts: 34
Location: USA

PostPosted: Tue Oct 15, 2013 8:33 pm    Post subject: How to embed RUNSTATS in an application program
Reply with quote

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
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1753
Location: Bloomington, IL

PostPosted: Tue Oct 15, 2013 8:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Oct 31, 2013 12:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 159
Location: Chennai

PostPosted: Thu Oct 31, 2013 12:44 pm    Post subject: Reply to: How to embed RUNSTATS in an application program
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Oct 31, 2013 3:19 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm
No new posts TWS EQQYCAIN - not sure how to get ap... mbenaud IBM Tools 0 Wed Dec 14, 2016 8:45 pm
No new posts I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us