Db2 for z/OS & Db2ZAI

Db2 for z/OS and its ecosystem

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  RTS Real-time Statistics - copying from prod to non-prod

    Posted Wed February 04, 2026 02:38 PM

    Does anyone have a process to copy real-time stats from prod to non-prod?  

    There are 2.3b rows in the largest table in our prod system. 
    The SQL is TS scanning due to a 'NOT' and cardinality on the index key order. 
    I've recommended a new index, but need prod RTS to run the EXPLAIN on non-prod.     

    There's insufficient data on non-prod for an EXPLAIN with the new index.   

    Wasn't there talk of a pseudo-what-if index tool, or was it just wishful thinking?  



    ------------------------------
    Linda Hagedorn
    ------------------------------


  • 2.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Wed February 04, 2026 02:53 PM

    I think you mean DB2 catalog statistics instead ok RTS. We use DB2admin to transfer them to non-prod environments.



    ------------------------------
    Sergi Padró i Blasi
    ------------------------------

    Original Message


  • 3.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Wed February 04, 2026 05:06 PM

    Hi Linda

     

    Have you considered creating a virtual index in your EXPLAIN database and feeding in the appropriate statistics to validate your hypothesis?

     

    Reference: https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=tables-dsn-virtual-indexes

     

    Cheers

    Bruce

    --

     




    Original Message


  • 4.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Fri February 06, 2026 02:40 PM

    I found one of our tools will create the virtual index.  I'm trying it.  



    ------------------------------
    Linda Hagedorn
    ------------------------------

    Original Message


  • 5.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Thu February 05, 2026 04:48 AM

    Hi Linda

     

    To paraphrase Obi-Wan, "These are not the stats you are looking for." Explain uses catalog statistics rather than RTS.

     

    BMC's SQL Performance Solution has a brilliant method of copying catalog statistics from prod to anywhere else. It does object name (schema, database, etc.) translation in-flight too. I used it a lot when I was a customer in for your same predicament.

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer
    Intelligent Z Optimization and Transformation
    BMC Software Ltd
    Email marcus_davage@bmc.com

    MarcusDavage@bmcsoftware.onmicrosoft.com/bookings/">Book time to meet with me

    My Digital Pin IBM Champion 2025 badge image. Experience. Advanced level. Issued by IBM

     




    Original Message


  • 6.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Thu February 05, 2026 09:45 PM

    If, as Sergi suggested, you want the catalog statistics (not RTS), the following is the replacement for DSN8PLI - used to generate DDL and update statements for statistics

    https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=db2-admin-info-sql
    I have found this messy because the output datasets are generated under the WLC STC's userid.

    https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=utilities-dsnadmsb
    which is what the SP actually uses

    https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=zos-dsntej6i

    If you raise a service request about access performance IBM will probably ask you to use this to generate something they can use to recreate your DDL/statistics.



    ------------------------------
    James Campbell
    ------------------------------

    Original Message


  • 7.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Fri February 06, 2026 06:08 AM

    Just for a bit of fun ask this question of your AI bot.

    I asked this question "write me the sql to migrate key catalog stats from my production system to development. This is with Db2 v13 on system z" . This generated quite a comprehensive set  of SQL and details on how to run it.  It included cleaning up old stats and verification SQL. Like anything in this new era there many tasks like this that the good AI bots can be of great assistance (note the word assistance :) ) 



    ------------------------------
    CRAIG MCKELLAR
    ------------------------------

    Original Message


  • 8.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Fri February 06, 2026 03:20 PM

    What a fun idea!  



    ------------------------------
    Linda Hagedorn
    ------------------------------

    Original Message


  • 9.  RE: RTS Real-time Statistics - copying from prod to non-prod

    Posted Sun February 08, 2026 03:15 AM
    Edited by Jørn Thyssen Sun February 08, 2026 03:15 AM

    Hi Linda,

    All of the vendors have features that copy runstats from one environment to another. 

    If you have IBM Db2 Administration Tool or IBM Db2 SQL Performance Analyzer feel free to reach out to me, and I can show you how. 

    Nerd note: as others mentioned you want to copy the runstats, however, RTS can be used by the optimizer in some rare circumstances, so IBM Db2 Administration Tool can copy both sets. 



    ------------------------------
    Jørn Thyssen
    Principal Solutions Advisor
    Rocket Software
    Randers SV
    ------------------------------

    Original Message


Related Content