Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

unable to unload millions of data from DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sravz chinnu

New User


Joined: 07 Dec 2011
Posts: 26
Location: India

PostPosted: Fri Mar 23, 2012 2:41 pm    Post subject: unable to unload millions of data from DB2
Reply with quote

Team,

I am using IKJEFT01 utility to unload 50+ millions of records in to a flat file further splitting the flat file to multiple files. The major concern is after extracting some records say 4000 the job is getting abended automatically by giving S722 return code. Can you please help me to get this much data using IKJEFT01 tool.

My SQL is having casting and formatting of data to be extracted to load into other data base.

Thanks,
Sravani.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Mar 23, 2012 2:45 pm    Post subject:
Reply with quote

Quote:
I am using IKJEFT01 utility to unload


since this indicates you have no idea what you are doing,
why not show us what you are actually doing,
as well as jesmsg/sysout displays of 'error'.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Mar 23, 2012 2:46 pm    Post subject: Reply to: unable to unload millions of data from DB2
Reply with quote

Are you sure IKJEFT01 is a tool?

Have you looked up the 722?

What output are you writing....
Back to top
View user's profile Send private message
sravz chinnu

New User


Joined: 07 Dec 2011
Posts: 26
Location: India

PostPosted: Fri Mar 23, 2012 2:50 pm    Post subject:
Reply with quote

Code:
$HASP375 SVEMULAN ESTIMATED  LINES EXCEEDED
IEF450I SVEMULAN STEP01S - ABEND=S722 U0000 REASON=00000000  014
        TIME=03.55.06
-SVEMULAN          STEP01S  *S722   1259    .03    .00    .55  22708
-SVEMULAN          STEP04S  FLUSH      0    .00    .00    .00      0
IEF404I SVEMULAN - ENDED - TIME=03.55.07
U11-607 JOBNAME=SVEMULAN,PRODJOB#=0000,TERMINATED UNSUCCESSFULLY
IEFACTRD  JOB ENDED: JOB=SVEMULAN       HIGHEST CC= S722


I am using this JCL :
Code:
//STEP01S  EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//LISTING  DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*
//SYSREC00 DD  DSN=SVEMULA.SCOPTRC.DATA.SCOPTRC,DISP=(,CATLG),
//             SPACE=(CYL,(400,400),RLSE),UNIT=SYSDA,
//             DCB=(MODEL)
//SYSTSIN  DD  DSN=SVEMULA.SCOE.TEST.CTC(DSNTIAU1),DISP=SHR
//SYSIN    DD  DSN=SVEMULA.SCOE.TEST.CTC(RCUNLOAD),DISP=SHR


Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Mar 23, 2012 2:57 pm    Post subject:
Reply with quote

here is an explanation of the S722 error code

nice to know that you are using DSNTIAUL to unload your DB.
once you get the S722 corrected,
if you have further problems,
let us know what the SYSTSIN and SYSIN ds contain

for future reference IBM's LookAt site is an easy way to determine error codes.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Mar 23, 2012 2:59 pm    Post subject:
Reply with quote

the jesmsgs DSID is where to look for info on your error.
something that was asked for, but you did not bother to post,
so now, you can look at it and solve your problem, yourself.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Mar 23, 2012 3:04 pm    Post subject:
Reply with quote

Try changing the "tool" - well, to answer what Bill has asked for, hint is - IKJEFT01 is not a tool. I'm not sure what is CAF and DSN in your line of work. icon_rolleyes.gif

So how many steps do you have in the Job in question, as you say data needs to be loaded in other DB? Do you get the abend in the step you specify?

S722 - What do you see in the SYSOUT? Some mention about "output limit" or OUTLIM?
Back to top
View user's profile Send private message
sravz chinnu

New User


Joined: 07 Dec 2011
Posts: 26
Location: India

PostPosted: Fri Mar 23, 2012 3:11 pm    Post subject:
Reply with quote

I tried using this line in JCL by doing Google.

/*JOBPARM LINES=999999

The problem here is i am able to extract those many records only after that again S722 abend. The limit here is up to six 9s.
Please let me know is there any other procedure to do this.

Thanks,
Sravani
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Mar 23, 2012 3:29 pm    Post subject: Reply to: unable to unload millions of data from DB2
Reply with quote

You are writing something to the spool, lots of something. Why? What does it say?

Talk to your site support if you have such an amount of stuff in the spool which you need.

I, and I'm sure many others, have had programs writing many times 999,999 lines of output.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Mar 23, 2012 3:30 pm    Post subject:
Reply with quote

Check if support group at your site is planning to kill and hid your body -- you should not be doing this. Site limits are established after careful consideration of many factors and changing them may cause unexpected side effects. I hope you're doing this in TEST LPAR, no, go to first sentence in this para.

First, ACS routines won't allow you to issue that override. Second, let's face it - if your job is writing so big spool that you try to override the OUTLIM limit and ACS routines are by-passed some how, what next will happen? JES spool fills up, sooner or later. When the JES spool fills up, JES stops submitting jobs and/or jobs stop running until the spool free space hits a certain %age. Spool space is also needed to sign onto TSO so nobody can get into TSO until spool space is freed up. Long story short - they can't risk this. And that's why you still get S722.

With what you've posted I'm not sure which SYSOUT but try using a file with big enough sapce for SYSPRINT and execute the job again.
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue Mar 27, 2012 10:07 am    Post subject: Reply to: unable to unload millions of data from DB2
Reply with quote

At our site, we have lesser limit than 999,999 so I face this error S722 quite often.

In such cases, I normally write the SYSOUT/SYSPRINT onto a dataset with big enough space to hold it.
Back to top
View user's profile Send private message
Parthiban DS

New User


Joined: 07 Aug 2011
Posts: 5
Location: India

PostPosted: Tue Mar 27, 2012 12:03 pm    Post subject:
Reply with quote

You know the exact number of records that are going to be unloaded. I would suggest you to calculate the DCB parameters and provide a reasonable volume in SYSREC00.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Mar 27, 2012 3:19 pm    Post subject:
Reply with quote

Parthiban DS wrote:
You know the exact number of records that are going to be unloaded. I would suggest you to calculate the DCB parameters and provide a reasonable volume in SYSREC00.
The JCL shown by "sravz chinnu" already has a data-set for DD SYSREC00. Also, s/he is geeting S722 - so, I believe that's not the problem here.

However, OP seems to disappear anyways.
Back to top
View user's profile Send private message
sravz chinnu

New User


Joined: 07 Dec 2011
Posts: 26
Location: India

PostPosted: Tue Mar 27, 2012 6:20 pm    Post subject:
Reply with quote

This has been resolved by modifying the complete query.because we are unable to get proper data from DB2. So we are using so many cast and coalese for the appropriate fields.

Now i am facing the problem with unload time. it is exeeding more than 10 hours to extract but with out any abend. If i do this for this much time then my FTP also takes lot of time

icon_sad.gif icon_sad.gif icon_sad.gif

Note: number of records to extracted is 54 million records.

Thanks,
sravani
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Mar 27, 2012 6:50 pm    Post subject:
Reply with quote

With the info in hand - can you sub-divide the task in smaller logical-units? Your call.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Tue Mar 27, 2012 7:22 pm    Post subject:
Reply with quote

sravz chinnu wrote:
This has been resolved by modifying the complete query.because we are unable to get proper data from DB2. So we are using so many cast and coalese for the appropriate fields.

Now i am facing the problem with unload time. it is exeeding more than 10 hours to extract but with out any abend. If i do this for this much time then my FTP also takes lot of time

icon_sad.gif icon_sad.gif icon_sad.gif

Note: number of records to extracted is 54 million records.

Thanks,
sravani
Well, if you'd listened to the original advice you were given you wouldn't be in this fix. You should go back to your original query and try to figure out why so much crap was written to SYSPRINT. You will probably find a *lot* warning messages in there. Eliminate the messages, and you'll be fine. As a last resort, if you are unable to tweak your query to eliminate them and you are sure the warning messages are having no impact, you can dummy out SYSPRINT.
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 ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm
No new posts FTP Server and FTP.DATA vasanthz All Other Mainframe Topics 2 Wed Oct 12, 2016 11:49 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us