View previous topic :: View next topic
|
Author |
Message |
kkrishnakanth
New User
Joined: 27 Oct 2007 Posts: 7 Location: India
|
|
|
|
Hi,
In our job we are loading a table using DSNUTILB. We are loading around 33 million records into the table at one go using this utility. As we are loading a huge number of records with out issuing commit in between, DB2 logs are getting full and we are getting the following waring message "DB2G DSNJW001 WARNING - UNCOMMITTED UR HAS WRITTEN 5300000 LOG RECORDS".
We are planning to take commit after some particular number of records. Is there any way to take explicit commits while loading the table using DSNUTILB? If so, please let me know how to do that.
If we can't take explicit commits during load, please let me know the alternate way for achieving this.
Thanks,
Krishna Kanth K. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well, we are getting into an area for which I have little expertise,
but,
I have often seen log=no...
don't think you can 'specify' commit after so many. seems as if you could.
I would look thru the documentation and read about logging. as I said,
almost everyone goes log=no - really shortens the backout time when an error happens.
but, the document tends to go on about logging and what you can do.
since it is a load process, and you want to log, I would make it several steps----that is an easy way out on not reading the manual. |
|
Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
Hi Krish
Quote: |
As we are loading a huge number of records with out issuing commit in between, DB2 logs are getting full and we are getting the following waring message "DB2G DSNJW001 WARNING - UNCOMMITTED UR HAS WRITTEN 5300000 LOG RECORDS".
|
As DB2 log is getting full, use LOG NO as Dick suggest. After LOAD, you should do a RUNSTATS on the tablespace.
As of my knowledge, in LOAD utility there is no option for COMMIT.
Correct me if my knowledge in inferior.
Regards
Raghu |
|
Back to top |
|
|
satyender
New User
Joined: 05 Jan 2008 Posts: 50 Location: Olympia, WA, USA
|
|
|
|
kkrishnakanth,
Use the option LOG NO NO COPYPENDING in the SYSPUNCH card.
Syntax:
LOAD DATA REPLACE LOG NO NOCOPYPEND INDDN SYSREC INTO TABLE
<table name>
Generally, if you you LOG YES -- logging takes place.
If you say LOG NO --there wont be any logging but the table may need a full Image Copy.
To avoid any Image Copy restricted state use NO COPYPEND parameter in the SYSPUNCH card. |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
Load utility automatically takes checkpoints for restartability.
If you post here your entire load statement. we will have a better idea what you are doing. |
|
Back to top |
|
|
kkrishnakanth
New User
Joined: 27 Oct 2007 Posts: 7 Location: India
|
|
|
|
Thanks all for the info..
But in the load card we are using LOG NO only. Please find below the exact load card we are using.
LOAD DATA LOG NO REPLACE INTO Table_Name PART 2.
But still the db2 logs are getting written. I am not sure why the DB2 logs are getting written evenif we are using LOG NO. Please help me in this regard.
Thanks,
Krishna Kanth K. |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
Sorry for late reply,
With Log No logs are not written.
Usually after the Uncommited UR warning, in the *MSTR job also shows the correlation, connection and planname etc. Did you check that they are yours?
Also, did your job put the tablespace in copy pending? |
|
Back to top |
|
|
kkrishnakanth
New User
Joined: 27 Oct 2007 Posts: 7 Location: India
|
|
|
|
Yes, we checked the details mentioned and they belong to our job only.
Yes, our table is going to copypend status after the load, so we are running a reorg job to remove the copypend status.
Thanks,
Krishnakanth K. |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
Why were you running a reorg to remove copypend? |
|
Back to top |
|
|
satyender
New User
Joined: 05 Jan 2008 Posts: 50 Location: Olympia, WA, USA
|
|
|
|
kkrishnakanth,
As your SYSPUNCH card (LOAD DATA LOG NO REPLACE INTO Table_Name PART 2) does not have NOCOPYPEND, the table will be in COPY pending status.
You need to use NOCOPYPEND in the SYSPUNCH card or take full IMAGECOPY for that table. |
|
Back to top |
|
|
|