Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How can I throttle an application ?

    My specific need is to load a huge table from another table (temp).

    How can I control this process to continue loading throttled as the available resources.

    For an instance if I load 25 milion rows online slowly few rows at a time , this may take over a day, I want to keep contol on rows being inserted at different rate at different times.

    Thanks

    DBFinder

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use the load utility, and load from cursor (rows come from another table instead of a file), it will probaby only take about 10 mintues to load 25 million rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Ok, but not a choice for my situation.

    The table has foreign key to a huge table again, also there are indexes on the table itself. If I load all at once this will bring all players to a hault because these two tables are uses highly.

    I want to loat the tabe @ 500 rows per commit at busiest time whereas @ 50,000 rows per commit at easiest time. Again at some other time the load may be 1000 rows per commit.

    That means I have to be able to get DB2 to control my app according to current load.

    regards

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you can identify X number of records on your source then you can use load from cursor and only load X number at a time. That identifier might be a timestamp.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    We are doing that already ?

    So when I say LoadRows.bat 20000 them my job is easy and cpu and disk is under control. Suddenly another job starts ( because load has to run for hours ) which will freeze the system.

    Let's say if I do LoadRows.bat 200 then it will be too slow, may over a month to complete.
    Can someone tell me if I can use governor or anything else to throttle the load based on current available resources and reduces if an applicatin comes in with new request ??

    Regards

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    How many records do you need to LOAD. And are you really using LOAD command? Post a command.

    AS Marcus said it should nto be taking that much time. I am running a load from cursor as we speak on a busy system and it is writing at a rate of 25-35k per sec.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Not currently . I can not take a risk in Production.

    I need to test that on test system, What you think, if I have 10 indexes on the table and there are few rows having an exceptions. also assume what will happen if the production is ruuning at 250 transactions per second and this table is used for all players. I never see that table free on regular bussiness hours.

    If there is no facility that can allow me to regulate inserts at runtime, I must do it offline.

    Regards

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you check Workload Manager?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is possible to load data to a table such that the existing data not being loaded is accessable by other applications. See the LOAD command for more information.

    Another option is write a stored procedure that does what you are asking, and can vary the commit interval, based on the time of day.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Marcus,

    Thanks for your response, which is first to-the-point response.
    Both options are feasible.

    Can you elaborate how can I determine how busy is DB2 at present, I have successfully used SPs in past, I wonder if there is any query or function that can give me an index to total avaialbility. Any parameter that may help me to tune next commit count and/or change priority and/or adjust resource utilization.

    I think you may give me a clue, I will research the rest.

    Bunch of thanks,

    DBFinder

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Marcus_A View Post
    It is possible to load data to a table such that the existing data not being loaded is accessable by other applications. See the LOAD command for more information.
    Our table will be having inserts only during LOAD. This is used later for reporting.

    So SP seems to be the only option. I need to know some db2 system indicators that can tell me how busy is db2 at the time.

    Regards

  12. #12
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    You can use SYSIBMADM.ENV_SYS_RESOURCES to find out current cpu usage.

    select value from SYSIBMADM.ENV_SYS_RESOURCES
    where name = 'CPU_USAGE_TOTAL'

    Satya..

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Welcome Satya to the dBforums,

    and thanks. This is what I was looking for. Please let me know if there is any documentation available on similar stuff.

    regards

    DBFinder

  14. #14
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Refer to "Administrative Routines and Views" document from IBM website. URL is ftp://public.dhe.ibm.com/ps/products...-db2are971.pdf

    It has lot of information regarding monitoring DB2 using SQLs..


    Satya....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •