InfoSphere Optim

InfoSphere Optim

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.  Using optim to archive DB2 ZOS tables without unique index

    Posted Tue August 28, 2018 11:29 AM
    How can I use Optim to archive mainframe DB2 tables that do not have a unique index on them.  These tables have duplicate rows on them so there is not a way to create an internal Optim unique index even if we add every column in the table to the index.

    ------------------------------
    Phil Neff
    ------------------------------

    #InfoSphereOptim
    #Optim


  • 2.  RE: Using optim to archive DB2 ZOS tables without unique index

    Posted Tue August 28, 2018 12:10 PM
    ​Hi Phil,

    I think I need more information about your scenario. Optim certainly will be able to archive data from such tables, based on some criteria. There is no need for an Optim index for that. Now, the lack of a unique index will affect processing of such archive performance-wise but not functionally.

    The two scenarios, which you may be worried about, are (selective) RESTORE and DELETE. And only if there are duplicate instances of the same row (contents) in the table.
    RESTORE, with INSERT or UPDATE, would not be able to properly determine the existence of all, possibly duplicate, instances of a row. And the results would not be what you wanted. If you were to restore the rows previously deleted, a LOAD would be a better choice because it would not stumble upon potentially duplicate rows already inserted in to the table. If there were no duplicates, there would be no problem.
    A DELETE, irrespective of duplicates, would work OK. With duplicates, it would possibly give you false "missing row" status because all matching rows would be deleted with the first row of the duplicates set.

    ------------------------------
    Greg Czaja
    Optim for z/OS Development
    Unicom Systems Inc.
    ------------------------------

    Original Message


  • 3.  RE: Using optim to archive DB2 ZOS tables without unique index

    Posted Tue August 28, 2018 03:19 PM
    Greg,

    Here is what we are trying to do.  We have the need to archive and delete the data off of our transactional tables.  We will then keep the archive files for a period of time.  If there is a need to restore the data we will be creating another instance of the tables and place the data there.  There will not be a need to restore the data back to the original tables. Does that help?

    Our Optim architects are telling me that there must be a unique index on the tables for it to work.

    Thanks,

    Phil

    ------------------------------
    Phil Neff
    ------------------------------

    Original Message


  • 4.  RE: Using optim to archive DB2 ZOS tables without unique index

    Posted Tue August 28, 2018 04:00 PM
    ​Hi,

    If you are going to RESTORE the data into an empty table then there is nothing which would prevent Optim from properly doing so, even without an index. Btw, in such scenario an (Optim) LOAD process would be much faster.


    ------------------------------
    Greg Czaja
    Optim for z/OS Development
    Unicom Systems Inc.
    ------------------------------

    Original Message


  • 5.  RE: Using optim to archive DB2 ZOS tables without unique index

    Posted Wed August 29, 2018 11:57 AM
    ​Hi Phil,

    In regard to this comment: "Our Optim architects are telling me that there must be a unique index on the tables for it to work."

    This is not necessarily true, although Optim does require a "key" to do Deletes. Normally when you think of a table key, it is unique, but in this case you just need to define an 'Optim Key' using whichever column(s) you deem most appropriate. When Optim runs the Delete after your archive file is created, it will issue SQL Deletes using the 'Optim key' values from each row archived. Since these keys aren't unique, one delete statement will affect more than one row. This means on a subsequent delete statement, it could get a not found condition because the prior delete statement already deleted the rows for the same 'key'.

    You just need to account for this in the SYSIN parameters for the job so it doesn't abend from a not-found condition during the deletes since it will hit many not-founds as it issues deletes for the same 'Optim Key' values. I think the parameter is: NOT_FOUND_RC4

    Also, as previously mentioned, if the table lacks an index associated with your SELECT criteria and/or your 'Optim Key' used for the deletes, the performance of this archive/purge may not be all that stellar if the DB2 engine is forced to do table scans.

    Hope this helps...

    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------

    Original Message


Related Content