Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to commit while loading data using DSNUTILB?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to commit while loading data using DSNUTILB?
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: 6968
Location: porcelain throne

PostPosted: Mon Jul 20, 2009 8:07 pm    Post subject:
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    Post subject: Reply to: How to commit while loading data using DSNUTILB?
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    Post subject:
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    Post subject:
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    Post subject: Reply to: How to commit while loading data using DSNUTILB?
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    Post subject:
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    Post subject: Reply to: How to commit while loading data using DSNUTILB?
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    Post subject: Reply to: How to commit while loading data using DSNUTILB?
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us