• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Let’s Start Over With a New Beginning

    January 6, 2010 Hey, Ted

    I’m writing in response to your article Let’s Start Over from the Beginning. Neither specifying CLOSQLCSR(*ENDMOD) nor running your program in a activation group *NEW (which is not even possible if your procedure is located in a service program and repeatedly called) is a good solution.

    In both situations the open data path (ODP) will be deleted after execution and cannot be reused. That means each time you execute your query, the system performs a full open, which is a time-consuming process.

    A full open is always necessary the first and second time a query is executed within the same activation group. When executing a full open, an access plan must be either created or validated. (If an access plan already exists, for example in the SQL plan cache or stored within the program object.) An access plan describes which indexes must be used, or whether a table scan or table probe must be executed, and also contains all information about any temporary objects, such as hash tables or relative record lists, that are necessary for executing the SQL statement.

    After the access plan is generated or validated, the temporary objects will be created and filled with data (i.e., the ODP will be opened). Opening the ODP is the most time-consuming part of the optimization. After the first execution the ODP will be deleted. The next time the same query is executed, the already actualized access plan will be checked again and the ODP reopened. After the second execution the ODP stays open, which means for all subsequent executions, only the data within the temporary objects must be actualized.

    When specifying CLOSQLCSR(*ENDMOD), the ODP will be deleted at the end of the module. That means each time you execute the same query–even within the same activation group–the access plan must be validated and the ODP must be recreated and cannot be reused.

    If I tell my users that they will have to wait for half a minute the first time they call a program, but that subsequent calls will be faster, they will accept it. But my users will complain if they have to wait for 30 seconds each time they call the program.

    The easiest way to solve Lynne’s problem is to close the cursor before opening it. If the cursor is open, it will be closed. If it was not open, the system generated an error–SQLCODE -501 or SQLSTATE 24501–which can be checked and ignored.

    In this way the ODP becomes reusable, and beginning with the third execution, only a soft (or pseudo) open is necessary, which means only the data must be actualized when opening the cursor.

    –Birgitta Hauser

    The following comes from the IBM i 6.1 Information Center:

    The first time (or times) a open occurs for a specific statement in a job is a full open. A full open creates an Open Data Path (ODP) that will be then be used to fetch, update, delete, or insert rows. Since there will typically be many fetch, update, delete, or insert operations for an ODP, as much processing of the SQL statement as possible is done during the ODP creation so that same processing does not need to be done on each subsequent I/O operation. An ODP may be cached at close time so that if the SQL statement is run again during the job, the ODP will be reused. Such an open is called a pseudo open and is much less expensive than a full open. You can control the number of ODPs that are cached in the job and then number of times the same ODP for a statement should be created before caching it.

    Thanks to Birgitta Hauser for starting off the new year with this valuable information. My answer to Lynne was just for her situation, and therefore too simplistic.

    –Ted

    RELATED STORY

    Let’s Start Over from the Beginning



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    10ZiG Technology:  Ask us about our new Ethernet Terminal for only $195!
    Bytware StandGuard Security:  Are you monitoring and auditing your System i security? FREE trial.
    Manta Technologies:  Year-End SALE! 40% off the complete library and all combo packs. Ends Jan 15

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Wolf Construction Taps CGC for Outsourced ERP Power Systems i: The Word From On High

    Leave a Reply Cancel reply

Volume 10, Number 1 -- January 6, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
System i Developer

Table of Contents

  • Let’s Start Over With a New Beginning
  • A Helpful Tool for Dealing with Unexpected Problems
  • Admin Alert: Upgrading a 550 to a 520 with V5R4?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle