IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to commit while loading data using DSNUTILB?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kkrishnakanth

New User


Joined: 27 Oct 2007
Posts: 7
Location: India

PostPosted: Mon Jul 20, 2009 6:41 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jul 20, 2009 8:07 pm
Reply with quote

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
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Mon Jul 20, 2009 11:58 pm
Reply with quote

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
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Tue Jul 21, 2009 12:23 am
Reply with quote

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
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Jul 22, 2009 12:29 am
Reply with quote

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
View user's profile Send private message
kkrishnakanth

New User


Joined: 27 Oct 2007
Posts: 7
Location: India

PostPosted: Wed Jul 22, 2009 6:54 pm
Reply with quote

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
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jul 24, 2009 10:48 pm
Reply with quote

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
View user's profile Send private message
kkrishnakanth

New User


Joined: 27 Oct 2007
Posts: 7
Location: India

PostPosted: Mon Jul 27, 2009 6:32 pm
Reply with quote

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
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 28, 2009 1:00 am
Reply with quote

Why were you running a reorg to remove copypend?
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Tue Jul 28, 2009 9:51 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top