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.  Archival Job taking too long to run

    Posted Fri May 01, 2020 12:20 PM
    I have recently started running jobs in Optim so this Question might sound dumb to some.

    I ran a archival job (consisting of 43 tables in AD) through a scheduler and it took more than 24hours to run. Below are few statistics

    Time Started                                            4/22/2020 10:26:41
    Time Finished                                          4/23/2020 10:51:16
    Elapsed Time                                         24:24:35
    Archive File Data Byte Count                    1039578115 Bytes (0.968 GB)
    Process Status                                        no errors, no warnings

    I do not think it is normal to have the job run for 24hours just for the above size.

    My Question: How and where can I check which tables are taking longer times? and if its a relationship issue, can I check that as well?

    Thank you for your inputs.

    ------------------------------
    Optim Learner
    ------------------------------

    #InfoSphereOptim
    #Optim


  • 2.  RE: Archival Job taking too long to run

    Posted Mon May 04, 2020 09:39 AM
    Optim Learner,

    You should be able to see how Optim processed each table, if you asked for a detailed report, below the list of rows for each table.  You can check to see how long it took Optim to process each table.

    Typically the things you can change if you see a great amount of time on any particular table would be to up the key lookup limit on that table.  We just had a similar situation where a job someone wrote was taking 15 plus hours to run.  Examining this detail report we found that many tables on took 1 second to process while others took 38 min - 2 hours.  When we changed the key lookup limit to 100 for the correct relationships the job now takes 30 minutes totally. In order to change this limit you need to turn on advanced options and examine the key lookup limit on the relationships tab.  

    I would rerun this with a much smaller subset by either using selection criteria or a table limit on the start table so you don't have to wait 24 hours for the next set of results and once you have it running reasonably you can eliminate the extra limits for the full test.  

    Hope this helps.

    ------------------------------
    Cheers,
    Fred Booker
    AB Martin
    609-356-6877
    fbooker@abmartin.com
    ------------------------------

    Original Message


  • 3.  RE: Archival Job taking too long to run

    Posted Mon May 04, 2020 10:58 AM
    Thank you Fred. I will try changing the Key Limit and run the process.

    I looked at the report and found that there are several such tables/relations where the time is in the range 40min-2.5hrs. When I turn on the advanced options and look at the 'Key lookup limit' under relationship tab, I see that it shows two rows - Parent and Child each having 1 by default. Do I need to change this value to 100 for both Parent/Child? (Q1)

    After reading your inputs here, to understand more on what this 'Key lookup limit' is, I went through the below link and found that certain conditions must be true. One of the conditions - "The table does not have any child tables" isn't true in my case i.e. I have tables and they child tables. Do you happen to know what happens if I still go ahead and change the key limit? (Q2)

    https://www.ibm.com/support/knowledgecenter/en/SSMLNW_11.3.0/com.ibm.nex.designer.doc/topics/optdescom-t-defining_key_lookup_limits.html


    I also noticed that the START table also took 2hrs 15mins to process and the SQL criteria I used is as below

    LastActivityDtTm > dateadd (dd, -3*365,getdate ())

    I understand that the processing time would be based on the criteria used but is there any other reason that you can think of which would contribute to the longer processing time in case of Start table? (Q3)

    Thank you.
    SA

    ------------------------------
    Optim Learner
    ------------------------------

    Original Message


  • 4.  RE: Archival Job taking too long to run

    Posted Mon May 04, 2020 11:40 AM
    Before you expand the key limits, you should verify Optim is using sound execution paths against the tables. Optim is executing SQL under the covers, so if you see tables taking a long time to run you need to get back to some basic SQL query principles. 

    • Is your query on the Start table using an index? Doesn't matter if it's a simple Date query, if unindexed it will do a scan. 
    • Are there indexes being used to access the other tables in your AD using the foreign keys in the relationships? 
    • Just because RI exists between tables doesn't mean it is indexed. 
    • Is the slow process time against tables on the SELECT or when you DELETE? This can also give clues as where the slowdown is. SELECT will use Foreign Key criteria, but DELETES use Primary Key, which is a different execution path. 

    This is SQL 101 stuff. You should verify the execution paths of your SQL queries as well as indexes against the tables in your AD. Slow tables often mean no indexing being used or using an index ineffectively. If you are unsure how to verify these things, you should consult with your DBA for guidance. 

    If indexing exists and you are using the Higher Level nodes of the index, then you can look at expanding the Key limits and DB connections within Optim. 





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

    Original Message


  • 5.  RE: Archival Job taking too long to run

    Posted Mon May 04, 2020 11:43 AM
    Optim Learner:

    Re Q1:  you don't have to change them all.  Just change the ones that are causing the issue, where you see the tables taking an extrodianry amount to time to access the table.    If you did change them all it might not hurt anything but you might run into issues if the keys are extremely large as the resulting SQL statement may be too large to handle.  If you change the key lookup limit for a table where those key values are not being used, like the PARENT relationship if that relationship is not used, then it would have no effect as no SQL statement is created by Optim.  

    Re Q2:  that link is specific to the Key LOOKUP limit on the DELETE step or the Arvhive process.  I was referring to the Key Lookup Limit for the archive step (Table Access Strategy) where you are just extracting the rows to be put into the archive file (or extract file).  The limit you specify and the link are specific to the Key Lookup Limits for just the Primary Key of the table which Optim will use to delete rows.  

    Re Q3:   I am not a SQL expert so perhaps check with someone who is more experience about writing efficient SQL queries.  I would perhaps check to see it your column "LastActivityDtTm" is part of an index.  If not, is it possible to make a DBMS index for that column?  Also on the right side of our > sign, is "dateadd" , is that a column in the table?  Is it part of an index?  I am not really sure what you are trying to get with this SQL logic.  It seems to me that you are trying to compare 2 columns of data on the same table and doing a complex calculation on one of the columns first.  Is there a simpler way to compare those 2 columns?  One thought here might be to generate a PNS list of values outside of Optim and use that as the driver to this Start table instead of this complex SQL.   Not sure if that helps you or not.  

    One other suggestion is to do the index analysis of your relationships.  That will show you where an index is needed and whether you have one or not. 




    ------------------------------
    Fred Booker
    AB Martin
    fbooker@abmartin.com
    609-356-6877
    ------------------------------

    Original Message


Related Content