IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 View Only

  • 1.  COMMIT and CLRPFM

    Posted Fri August 30, 2024 10:18 AM
    If I have programs running under commit scoped to *JOB, will a CLRPFM
    be rolled back?
    It appears it does not?

    Thanks!


  • 2.  RE: COMMIT and CLRPFM

    Posted Fri August 30, 2024 10:24 AM

    Hi Steve,

    no - CLRPFM isn't "rollbackable" (is this even a word?).

    If you want to delete all rows in a table in an operation that can be rolled back - then use the SQL TRUNCATE statement - but without the IMMEDIATE clause/keyword (because with IMMEDIATE the TRUNCATE can't be rolled back).

    -> https://www.ibm.com/docs/en/i/7.5?topic=statements-truncate

    HTH

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 3.  RE: COMMIT and CLRPFM

    Posted Fri August 30, 2024 10:51 AM
    Thank you....
    That is what I was seeing but could not find any info on why it was happening. 



    Original Message


  • 4.  RE: COMMIT and CLRPFM

    Posted Tue September 03, 2024 03:51 PM

    Hi Steve,

    You want to use CLRPFM because it takes much time when using SQL DELETE without WHERE clause.

    But with SQL DELETE (without WHERE clause) you can activate a "SQL fast delete" that  supports Commit/Rollback.

    How it works? Instead deleting row by row Db2 for i renames your table (OLDTABLE) and gives you a new empty table with your original name.

    If commit: the renamed table (OLDTABLE) is deleted.

    If rollback: Db2 for i deletes the new table and renames OLDTABLE table with its original name.

    The overhead versus CLRPFM is very small.

    How to activate SQL fast delete? Use SQL_FAST_DELETE_ROW_COUNT in QAQQINI file.

    Hope this help.



    ------------------------------
    Christian GRIERE
    DB2 for i expert
    Christian GRIERE Services
    BUSSY ST GEORGES near PARIS
    ------------------------------

    Original Message


  • 5.  RE: COMMIT and CLRPFM

    Posted Tue September 03, 2024 08:42 PM
      |   view attached

    I would like to add to Christian that SQL fast delete does not always work.  Scott Forstie from DB2i Rochester team suggests that we review the job log to ensure there is no CPF9898 message that indicates failure to implement fast delete.  I attach Scott's article herewith for reference.  



    ------------------------------
    Satid S
    ------------------------------

    Attachment(s)

    pdf
    SQL-Fast Delete.pdf   221 KB 1 version
    Original Message