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: 1771
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: 161
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 Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts pre-compiler for program in an HFS Pedro DB2 0 Fri Jun 23, 2017 11:13 pm
No new posts Compiling program in endeavor for tra... RALAKKAL Compuware & Other Tools 4 Mon Jun 05, 2017 8:27 pm
No new posts COBOL DB2 program - zIIP eligible vasanthz COBOL Programming 7 Wed May 31, 2017 5:12 am
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm


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