View previous topic :: View next topic
|
Author |
Message |
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
Hi,
I have a job which tries to load 2.5 million records in to UDB DB2 data base. From long time the job used to take 2 to 3 hours for completion.
But all of suddent JCL is taking 11 to 12 hours for completion. I am trying to find out what could be the possible reason.
Data Flow
connection
Mainframes -->TCP/IP----> UDB DB2
We have no recent program changes.
Updates from network support and reports from DBA states that there is no issue from their side.
We tried to recreate the issue in test region and it is working fine with which it proves that it is not a network issue.
Please let me know of some had faced this type of issue and what was the solution used.
Thanks and Regards,
Rajkumar Nerella |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
We have no recent program changes. |
What about other changes in the environment? Is the database more full now than when this ran acceptably?
Do both the test and production databases have the same size and volume of content?
Quote: |
We tried to recreate the issue in test region and it is working fine with which it proves that it is not a network issue |
Proves? Maybe. . . Is the test environment on the same server as production or is on a different server? Is the routing nearly the same or the servers on different parts of the lan?
Does the process download the file and then run the load locally or does the process try to send individual inserts?
Is there a record of the timings going back to when the process ran acceptably? Did the run-time spike or did it gradually get worse? |
|
Back to top |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
Hi,
Thanks a lot for your response.
There are no environmental changes. I am not sure whether the test and production tables are residing on the same server.
The process was going fine all days but this issue occured all of sudden.
[quote]Do both the test and production databases have the same size and volume of content?
Yes we tried to load same number of records in both test and production, end result was the same, Job took long time than regular
We tried send the extract file to the destimation where we are trying to load and the FTP process took 15 minutes only, this confirms that there are no connection issues.
Coming to the important point, on daily basis we are cleaning up the data and loading it again.
DBA has done his job executing reorg utility,runstat utility etc but not able to figure out the issue.
Thanks and Regards,
Rajkumar Nerella |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Yes we tried to load same number of records in both test and production, end result was the same,Job took long time than regular |
If i understand, both test and production now take a very long time?
Quote: |
There are no environmental changes. |
Something surely has changed somewhere. . . One of the biggest obstacles to trouble shooting is prematurely eliminating something from consideration.
Does your dba control the server(s) or is there a system administration group who may make system level changes?
Is it possible that previously a significantly smaller volume was loaded rather than the current volume? Do you have load statistics going back from now to when the process ran in less time? |
|
Back to top |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
The issue was resolved.
To resolve this issue a static route(with fibre optic cable line) was added to the mainframe connection box(LPAR) though the OSA connection.
This used the optimal path the transactions went very fast.
Generally the job used to take 2.5 hours but after this change we save 40 minutes.
Thanks for all your support.
Thanks and Regards,
Rajkumar Nerella |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Quote: |
The issue was resolved. |
Good to hear - thank you for letting us know
d |
|
Back to top |
|
|
|