unable to unload millions of data from DB2

sravz chinnu · New User Joined: 07 Dec 2011 Posts: 26 Location: India

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.

dbzTHEdinosauer · Posted: Fri Mar 23, 2012 2:45 pm

Bill Woodger · Posted: Fri Mar 23, 2012 2:46 pm

Are you sure IKJEFT01 is a tool?

Have you looked up the 722?

What output are you writing....

sravz chinnu · New User Joined: 07 Dec 2011 Posts: 26 Location: India

dbzTHEdinosauer · Posted: Fri Mar 23, 2012 2:57 pm

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.

dbzTHEdinosauer · Posted: Fri Mar 23, 2012 2:59 pm

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.

Anuj Dhawan · Posted: Fri Mar 23, 2012 3:04 pm

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.

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?

sravz chinnu · New User Joined: 07 Dec 2011 Posts: 26 Location: India

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

Bill Woodger · Posted: Fri Mar 23, 2012 3:29 pm

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.

Anuj Dhawan · Posted: Fri Mar 23, 2012 3:30 pm

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.

rakesh1155 · New User Joined: 21 Jan 2009 Posts: 84 Location: India

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.

Parthiban DS · New User Joined: 07 Aug 2011 Posts: 5 Location: India

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.

Anuj Dhawan · Posted: Tue Mar 27, 2012 3:19 pm

sravz chinnu · New User Joined: 07 Dec 2011 Posts: 26 Location: India

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

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

Thanks,
sravani

Anuj Dhawan · Posted: Tue Mar 27, 2012 6:50 pm

With the info in hand - can you sub-divide the task in smaller logical-units? Your call.

don.leahy · Posted: Tue Mar 27, 2012 7:22 pm