Skip to Main Content
IBM Data and AI Ideas Portal for Customers


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The product management team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

The correct URL for entering your ideas is: https://hybridcloudunit-internal.ideas.aha.io


Status Future consideration
Workspace Db2 AI for z/OS
Created by Guest
Created on Jan 17, 2019

Dynamic Plan Stability - Capture statements from explain

If we want to capture a certain dynamic statement for dynamic plan stability, the statement has to reside in the dynamic statement cache. Than you have to search for the statement id and with the statement íd you're able to capture a certain statement.

It can be quite hard to find the statement in the dynamic statement cache (think of a batch job running only once a day) because the residency of the statement in the dynamic statement cache is very short.

There should be a possibility to do an explain of the statement which captures the statement (stabilizes it ) in dynamic plan stability tables.

 

Here is my current workaround:

//XCA10A1S  JOB 'SY832',PASCHKE,                                       
//         CLASS=A,MSGCLASS=H,MSGLEVEL=(1,1),NOTIFY=XCA10A1,SCHENV=DB27
//  SCHEDULE HOLDUNTL=('02:10','12/20/2018')                           
//JOBLIB  DD  DISP=SHR,DSN=SCD.DB27888.A.DSNLOAD                       
//**********************                                               
//** STEP0010 Löschen **                                               
//**********************                                               
//STEP0010 EXEC DB27TEP2                                               
//SYSIN    DD *                                                        
TRUNCATE TABLE XCA10A1.DSN_STATEMENT_CACHE_TABLE;                      
COMMIT;                                                                
//**********************                                               
//** STEP0020 Explain **                                               
//**********************                                               
//STEP0020 EXEC DB27TEP2                                               
//SYSIN    DD *                                                        
EXPLAIN STMTCACHE ALL;                                                 
COMMIT;                                                                
//*                                                                    
//**********************                                               
//** STEP0030 Select  **                                               
//**********************                                               
//STEP0030 EXEC DB27TEP2                                               
//SYSREC00 DD DISP=SHR,DSN=XCA10A1.DB27.SYSREC00.DATA                  
//SYSPUNCH DD SYSOUT=*                                                 
//SYSPRINT DD SYSOUT=*                                                 
//SYSTSPRT DD SYSOUT=*                                                 
//SYSTSIN   DD *                                                       
  DSN SYSTEM(DB27)                                                     
  RUN PROGRAM(DSNTIAUL) PARMS('SQL')                                   
//SYSIN    DD *                                                        
 SELECT                                                                
 CHAR('-STA DYNQUERYCAPTURE STBLGRP(SEPA) STMTID('),                   
 CHAR(STMT_ID),                                                        
 CHAR(')'),                                                            
 CHAR('                                    ')                          
   FROM XCA10A1.DSN_STATEMENT_CACHE_TABLE                              
   WHERE  STMT_TEXT LIKE ' SELECT DISTINCT swap.KEY_BOOKING_DATE,%'    
   AND SCHEMA = 'XCTC233'                                              
   AND LITERAL_REPL = '';                                              
//*                                                                    
//**********************                                               
//** STEP0040 -STA DYNQUERY                                            
//**********************                                               
//STEP0040 EXEC PGM=IKJEFT01,DYNAMNBR=20                               
//SYSTSPRT DD  SYSOUT=*                                                
//SYSPRINT DD  SYSOUT=*                                                
//SYSUDUMP DD  SYSOUT=*                                                
//SYSTSIN  DD  *                                                       
DSN SYSTEM(DB27)                                                       
//         DD DISP=SHR,DSN=XCA10A1.DB27.SYSREC00.DATA                  
//**********************                                               
//** STEP0050 -STO DYNQUERY                                            
//**********************                                               
//STEP0050 EXEC PGM=IKJEFT01,DYNAMNBR=20                              

 

The workaround has several restrictions:

1) You can't be sure that the statement is in DSC when the job is running

2) The access path of the statement found in DSC may not be the desired one.

 

The benefits:

We'ld have an excellent possibility to stabilize statements for the purpose of adhoc handling in case of  turning accesspathes.

Eventually we could avoid prio 1 PMRs as we have a workaround with stabilizing the statements.