Hi, I would like to know what could be the impact of mass insert in DB2. We have inserted around 9 lakhs plus rows in each table and no of tables was 8 (around 72 lakhs rows) thru REXX programs over the weekend (this is the only activity happened over the weekend) and since Monday 1/12 our system has been behaving very strange. Our online services are down and Mainframe utilization is full. IBM suggessted Recycling of DB2, it works but only for few hours.
Currently we are recycling DB2 every day to get the sytem going.

Does mass insert impact on the DB2 access paths? Any info would be appreciated....

Appreciate your help...

did you do reorg, runstats and rebind after the inserts? Documentation suggest it if from 10 to 20% of the data have been changed...

we did not do reorg, runstats and rebind after the inserts...do you think this is causing the issue....

try it. manual says you should. I do it for our applications every time we have an issue and surprisingly in 80% of cases the problem goes away

Pls let me know if it worked

thnx Mark...as I am a developer i dont have necessary authority to do that but i will ask my DBA to try this and see what happens. I am wondering if this is causing the issue then why the DBA did not do it early as every technical person in my organization is involved in this issue, we have contacted IBM also and they asked us to do recycling of DB2 everyday until they find out the issue (they didnt ask us to reorg,rebind,runstats)...

it is usually miscommunication between dbas and programmers. I am as a DBA do not really know when developers do updates, inserts, etc. so when they run into a problem, my 1st step is always run maintenance on a db to make sure it's in tact.

Dont you think recycling of DB2 will make the db in tact so after the first recycling we should have been okay but today is the sixth day in a row when we recycle the DB2 to get the system going....please correct me if i am wrong..

Additional info..thought you should know to get better idea about the issue:

The 8 tables where we have inserted data were having the old data already..we emptied all the 8 tables first and then inserted our new data...

even if recycling works, what are you going to do next time you do mass updates? recycle again? You need to run maintenance on all those tables.

From DB2 manual :
It is recommended to run the RUNSTATS command:
On tables that have been modified considerably (for example, if a large number of updates have been made, or if a significant amount of data has been inserted or deleted or if LOAD has been done without the statistics option during LOAD)
On tables that have been reorganized (using REORG, REDISTRIBUTE DATABASE PARTITION GROUP)
When a new index has been created.
Before binding applications whose performance is critical.
When the prefetch quantity is changed

Btw, I suggest to use LOAD instead of INSERT if you need to add many records to the table. And RUNSTATS should be the next after LOAD step in the batch which does it.

I will ask my DBA to run the RUNSTATS and REORG to see what happens...will get back to you on results....

Appreciate your help Mark....

As MarkhamDBA has suggested RUNSTATS needs to be done.
Ask your dba to do it .. in the meantime you can check the CARD and STATSTIME columns in the sysibm.systables. The CARD column gives you then number of rows in the table at runstats time.

Make sure runstats is followed by rebind. you can check the bindtime (not sure about the col name) in sysibm.sysplan and sysibm.syspackage tables.

IBM Information Management Software for z/OS Solutions Information Center


By the way, what sort of application do you have? web appls using say db2 connect or cics or .....?

Sathya,We have web applications which is impacted and also our batch jobs are taking so much time because of 100% utilization of Mainframe...We also did IPL last night I hope this will clear up the things....i will ask my DBA to run the runstats and rebind....

Appreciate your help Sathya...

Since you explicity asked for my input (via a PM), I'll climb into the fray to see what I can do to help. For most mainframe systems, 72 lakh rows is barely enough to climb from trivial to insignificant in terms of load. I agree that the idea of doing a reorg/runstats/rebind combination is probably a good idea, but you'll have to find a "window of opportunity" when you can do this, since the tables will be offline for any practical purposes while this happens... It should only take a few minutes, but depending on your environment finding those few minutes could be challenging!

To get to the bottom of your problem, you need to understand a lot about both the nature of the problem (the database design) and the environment that your design runs within... I'll asume since you are a developer that you intimately understand the problem domain (database design), so I'll focus on the environmental issues.

What performance tools are you running on you mainframe, and what do they show you when you are having performance problems? What version of DB2 are you running? What OS is DB2 running on? How many indicies are on each of the 8 tables? How are the rows added to the tables (you get a very different response from DB2 when you use a REXX script to add one row 900,000 times than you get if you use a REXX script to insert 900,000 rows one time).

Take a look at these factors, and try to determine how they figure into your environment. Every environment is different, so there isn't a "cookie cutter" solution to resolving problems like these.

-PatP

I checked the sysibm.systables and found the STATSTIME 2008-11-16 for all the 8 tables and the column CARD value is same what we deleted from the tables prior to inserting new data on 1/9.

I aksed my DBA to run the RUNSTATS and REBIND on the DB2 tables but he believe this is not causing the issue and not ready to run the RUNSTATS and REBIND.

DBA told me that they have pulled data from 1/5 and Monday 1/12, Thurs 1/15 and seeing issues opp check to racf we are overrunning the system with work,cirext from 1/5 and 1/12 - 10Xworkload for this process id,huge flood of work that is impossible to service-overrunning the threads. taking a look at 5 most commonly used process id's ,ibm - setting trace on gateway servers and letting it wrap.

Not sure what is happening....do you have clue...

appreciate your help...

hi i would like to know that how can we use massinsert in db2 and cics
Hi, I would like to know what could be the impact of mass insert in DB2. We have inserted around 9 lakhs plus rows in each table and no of tables was 8 (around 72 lakhs rows) thru REXX programs over the weekend (this is the only activity happened over the weekend) and since Monday 1/12 our system has been behaving very strange. Our online services are down and Mainframe utilization is full. IBM suggessted Recycling of DB2, it works but only for few hours.
Currently we are recycling DB2 every day to get the sytem going.

Does mass insert impact on the DB2 access paths? Any info would be appreciated....

Appreciate your help...[/QUOTE]