• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Reader Feedback on Using the SQL SET OPTION Statement

    March 21, 2007 Hey, Michael

    I just read your article about the SET OPTION statement. Here are some minor comments about the OPTION CLOSQLCSR. There is a difference between closing a SQL cursor with the SQL statements CLOSE or COMMIT and closing a cursor at either *ENDMOD or *ENDACTGRP (or the other options for non-ILE programs).

    When closing a cursor with the SQL commands, only a soft (or pseudo) close will be performed, that is the ODP (if reusable) stays cached and will only be reactivated the next time the SQL statement will be executed.

    Closing a cursor at the end of a module (*ENDMOD) or at the end of the activation group (*ENDACTGRP) will hard close the ODPs, that is, all temporary objects are deleted. When rerunning the same SQL statements, a complete full open must be performed. A full open includes among others: Index estimates, creating and validating access plans, creating the ODP based on the validated access plans. The creation of the ODP (i.e. temporary objects such as bitmaps or hash tables or temporary tables or indexes) is the most expensive process in the optimization. And each SQL statement gets its own ODP, which means that if the same statement is coded several times for each of these statements, then the complete optimization process must be performed.

    The main goal to gain performance is, to minimize the full opens and hard closes. This can be achieved by a modular programming, where each SQL statement embedded in its own exported procedure and by avoiding the hard closing of cursors.

    Instead of specifying *ENDMOD to be sure that the cursor really gets closed, it would be much better to execute the SQL statement CLOSE just before executing the OPEN statement or the PREPARE statement with dynamic SQL. If the cursor is already opened, it will be closed and with the next open the ODP can be reused. If the cursor is already closed, so much the better.

    Form performance aspects, specifying *ENDMOD or using activation group *NEW should be avoided, the optimization process is too expensive! I can explain to my users that they have to wait for half a minute the first time they execute a program. They’ll accept it, if all the subsequent executions will perform in less than 2 seconds. But I’ll get into trouble if the users have to wait half a minute each time they call the program.

    By the way, dynamic SQL also should be reduced as much as possible. With dynamic SQL the access plans always must be created from scratch when executing the statement. With static SQL on the other hand the access plans are stored and updated in the (service) program objects and only must be validated at run time. But it becomes better when the SQL statements can be executed by the SQL Query Engine, because these access plans get stored in the plan cache and can be used by all jobs.

    Best regards,

    –Birgitta Hauser, SSS-Software GmbH

    Thanks, Birgitta. These comments are really helpful. We’ll add a link to it at the bottom of the original article so people see it.

    –Michael

    RELATED STORY

    Using the SQL SET OPTION Statement



                         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
    Raz-Lee Security

    With COVID-19 wreaking havoc, cybercriminals are taking advantage of the global impact that it has had on our families, our businesses and our societies. It is more important now than ever to ensure that IT systems are protected, so that when all of this is behind us, we can get back to business as usual as quickly as possible.

    iSecurity Anti-Ransomware protects organizations against ransomware attacks and other kinds of malware that may access and change business-critical data on your IBM i. It even protects against zero-day attacks. Anti-Viruses can only report on the damage an attack has caused, but not stop it.

    iSecurity Anti-Ransomware has been recently enhanced with a Self-Test feature that allows you to simulate a ransomware attack on your IBM i. The simulated attack is limited to the test folder and cannot harm any other folders or files. This new feature lets organizations see how they are protected against known or unknown ransomware.

    Key Features:

    • Real-time scanning for known and unknown ransomware threats.
    • Blocks and disconnects the intruder.
    • Instantaneously sends alerts to SIEM as well as the offending computer.
    • Self-Test for attack simulation
    • Classification of the attack based on log.
    • Automatic updates with the most current ransomware definitions.

    Contact us at https://www.razlee.com/anti-ransomware

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the 2007 conference, April 29 – May 3, in Anaheim, California
    Databorough:  X-analysis is the world leader in AS/400 application retro-documentation
    Patrick Townsend & Associates:  Alliance AES/400 - database field encryption

    IT Jungle Store Top Book Picks

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' 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
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    TomorrowNow Takes Steps to Ensure Rapid Response Rexair Taps Quadrant to Improve Document Processing

    Leave a Reply Cancel reply

Volume 7, Number 11 -- March 21, 2007
THIS ISSUE SPONSORED BY:

Profound Logic Software
Help/Systems
WorksRight Software

Table of Contents

  • iSeries Navigator Job Monitor
  • Reader Feedback on Using the SQL SET OPTION Statement
  • Changing ODBC Sign-On Pop Up Values

Content archive

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

Recent Posts

  • JD Edwards Customers Face Support Decisions
  • Security, Automation, and Cloud Top Midrange IT Priorities, Study Says
  • Cleo and SrinSoft in Integration-Modernization Link Up
  • Four Hundred Monitor, August 3
  • IBM i PTF Guide, Volume 24, Number 31
  • Power10 Entry Machines: The Power S1022 And Power L1022
  • Highly Secure API Enablement for IBM i
  • Guru: IBM i Privileged Account Management, And What’s So Special About Special Authorities
  • IBM Finally Shows Some Growth In Sales And Profits
  • Altair Delivers More Options for Running SAS Code

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.