IBM Power Ideas Portal
Hide about this portal


This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


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:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Add a new idea Subscribe
Status Delivered
Workspace IBM i
Categories IBM i Access Family
Created by Guest
Created on Sep 2, 2020

RELATED IDEAS

iSeries ACS Run SQL Scripts - SQL SELECT - locking file

See this idea on ideas.ibm.com

Running SQL SELECTs in ACS "Run SQL Scripts" on the iSeries leaves locks on physical files which prevent the CL command CLRPFM from running over the file.

Old fashioned RPG programs never lock files when reading from them or writing to them and only lock them momentarily when updating. Leaving a lock on a file was and is a bug. And a very serious bug.

Leaving a lock on a file indefinitely which arises from SQL SELECT statements which only read the file is terrible. It is not just a lock on the last select the user has run but there are locks left on all the files the user has looked at.

Tom Forstie has already kindly replied to my email and told me the locks can be avoided by adding limit n to the SELECT statement and making sure to page down to the end of the resulting rows or to press the "Retrieve all rows" button in the bottem right corner. Both of these work and I will be communicating a "Health Warning" to my colleagues to help them avoid locks. I have passed on Tom's email already.

It is the idea of leaving the locks that is wrong. I find it hard to believe consideration has not been given to the damage this will do.

= = =

As soon as the user has pressed Ctrl-R on an SQL select all locks (from previous selects ) can be removed.

Then you are left with the problem of the locks from last SQL SELECT run. Maybe a "snapshot" can be taken in order to keep track while releasing the locks.

= = =

Tom has told me there may be a timeout in the future. That would be good compared to what is going on but shows a lack of realisation that you just should not be leaving these locks.


Use Case:

iSeries user running SQL SELECT on LIBA.FILEA in ACS "Run SQL Scripts" yesterday.
Scheduled job run on the iSeries this morning and crashes when it tries to run CLRPFM on LIBA/FILEA. It happened where I work last week.


Idea priority Urgent
  • Guest
    Reply
    |     Dec 7, 2020

    This support is now available in version 1.1.8.6 dated December 2020.

  • Guest
    Reply
    |     Nov 11, 2020

    Due to processing by IBM, this request was reassigned to have the following updated attributes:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - IBM i Access Family
    Operating system - IBM i
    Source - Other

    For recording keeping, the previous attributes were:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - Db2 for i
    Operating system - IBM i
    Source - Other

  • Guest
    Reply
    |     Nov 2, 2020

    This will be in our next update available in December 2020.

  • Guest
    Reply
    |     Oct 21, 2020

    Another +1 for Stixi suggestion. ACS should offer an option to disable the closed pseudo-cursors.

    Or, closing the result window should automatically release any previous lock.

  • Guest
    Reply
    |     Oct 8, 2020

    I second the suggestion/comment here by Stixi
    It would be nice if there was an option in ACS to disable pseudo closed cursers .

  • Guest
    Reply
    |     Sep 9, 2020

    Due to processing by IBM, this request was reassigned to have the following updated attributes:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - Db2 for i
    Operating system - IBM i
    Source - Other

    For recording keeping, the previous attributes were:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - Other
    Operating system - IBM i
    Source - Other

  • Guest
    Reply
    |     Sep 9, 2020

    This problem seems to be caused by pseudo closed cursers.
    https://www.ibm.com/support/pages/pseudo-closed-cursor-faq

    It would be nice if there was an option in ACS to disable pseudo closed cursers .

  • Guest
    Reply
    |     Sep 4, 2020

    If I write an RPG program with input only file access, open the file, and read from it the file becomes locked and CLRPFM is not possible. The data might not be locked but the file-level access locks the file. This does not seem like an SQL specific problem.

  • Guest
    Reply
    |     Sep 3, 2020

    I agree. Unless the SELECT specifies FOR UPDATE, it shouldn't lock the table.

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.