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
 

 

sb37 while extracting data

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

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Tue Jun 05, 2012 11:54 pm    Post subject: sb37 while extracting data
Reply with quote

Hi,

I'm trying to unload huge data from a DB2 table to a flat file and it resulted in space issue.

1) Any idea how to calculate the size of the data in DB2 table so that I can declare my flat file properties accordingly?

2) Is their any limit in mainframe for a flat file size or a sequential dataset like GDG?

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

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Tue Jun 05, 2012 11:59 pm    Post subject:
Reply with quote

A gdg is, most often, a flat file. Nothing special about it - it is just a naming convention w]that allows you to give a shorthand name in your JCL.

To find out out how much space you require: row count * row length = number of bytes. Work it out from there. If you have a particularly large dataset then put it to tape.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Wed Jun 06, 2012 12:08 am    Post subject:
Reply with quote

Quote:
Is their any limit in mainframe for a flat file size or a sequential dataset like GDG?
Yes, there is. The actual limit depends upon whether you're allocating to tape or disk, as well as which release of the operating system is installed (and, potentially, your site storage management policies in place). Broadly speaking, a disk data set may not occupy more than 59 volumes, and (unless your version of z/OS allows and you specify the large data set dataclass when allocating) no more than 4,369 cylinders per volume.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Jun 06, 2012 12:13 am    Post subject:
Reply with quote

if you mean a true unload that generates sysrecs,
then look at the sysrec def for the record
which will show the offsets within the record for each field representing the columns

this image is also used by db2 during the load process
so that db2 can parse the input record properly.

then multiply the record length indicated by number of rows.
Back to top
View user's profile Send private message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Wed Jun 06, 2012 12:54 am    Post subject: Reply to: sb37 while extracting data
Reply with quote

Thanks all for youre replies

Robert,

Any idea how to see the max volumes a Z/oS version can take?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 06, 2012 1:02 am    Post subject:
Reply with quote

Also If you table is really huge and if it is partitioned you can unload in parts so that if your table is divided into ten parts you can submit 10 jobs parallely

Just a thought
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Wed Jun 06, 2012 1:04 am    Post subject:
Reply with quote

59 volumes for a single sequential data set on disk is the limit -- that does not depend upon the z/OS version -- check section 12.67.2 of the JCL Reference manual on the UNIT parameter for details. The 4,369 cylinders per volume limit does depend upon the z/OS version (and other factors -- Unix System Services files may be larger, for example).
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 06, 2012 7:16 am    Post subject:
Reply with quote

Hello,

Quote:
Any idea how to see the max volumes a Z/oS version can take?
What exactly is "a Z/oS version"? This is not a term with which i am familiar.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Wed Jun 06, 2012 9:19 am    Post subject:
Reply with quote

Hello D,
Hope I am not responding to a rhetorical question.
I think by "Z/OS version" poster meant OS levels (Z/OS 1.11 Z/OS 1.09 OS/390 2.10 etc..)

maverick05,
Quote:

To find out out how much space you require: row count * row length = number of bytes. Work it out from there.

As said by Nic, best way is to determine the size of table first and make decisions.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Jun 06, 2012 9:54 am    Post subject:
Reply with quote

Most possibly, the release of the operating system in use at the shop, Dick.

maverick05 wrote:
I'm trying to unload huge data from a DB2 table to a flat file and it resulted in space issue
Are you doing this in PROD or TEST region of your shop?

If in TEST, does this need this much considerable thought. Said that, using large enough space, viz. SPACE=(CYL,(1000,1000), RLSE), with RLSE would give you a go. If this does not work, possibly TAPE, no?

That might sound rookie but...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 06, 2012 6:59 pm    Post subject: Reply to: sb37 while extracting data
Reply with quote

Thanks guys,

Nope - not a rhetorical question icon_smile.gif

What you explained is kinda what i thought - i've just not heard of this in relation to max volumes icon_wink.gif

'Preciate the clarification.

d
Back to top
View user's profile Send private message
maverick05

New User


Joined: 14 Apr 2005
Posts: 54
Location: Earth

PostPosted: Thu Jun 07, 2012 1:48 am    Post subject: Reply to: sb37 while extracting data
Reply with quote

Their is also another option to compress the data when unloading to a flat file. DATACLAS=COMPRES.

my question is do we need to decompress again for loading to a DB2 table from a flat file?

How exactly this compression works and where exactly it compresses data so that the actual data size becomes smaller.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2012 8:35 am    Post subject:
Reply with quote

Hello,

When an unload is compressed, it is decompressed when re-loaded.

Do you have an understanding of data compression? If not, suggest you look on the web - i'm sure all that you want would even be found on Wikipedia.

By defintion, compression stores "all" of the data in considerably less space. Often more than 90% is realized - it just depends on the data content.
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
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


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