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

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.

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

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.

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

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.

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

Did you check Workload Manager?

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.

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

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

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..

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

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....