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

sb37 while extracting data


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 05, 2012 11:59 pm
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: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Jun 06, 2012 12:08 am
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
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
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

Global Moderator


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

PostPosted: Wed Jun 06, 2012 1:02 am
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: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Jun 06, 2012 1:04 am
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

Moderator Emeritus


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

PostPosted: Wed Jun 06, 2012 7:16 am
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: 1742
Location: Tirupur, India

PostPosted: Wed Jun 06, 2012 9:19 am
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

Superior Member


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

PostPosted: Wed Jun 06, 2012 9:54 am
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

Moderator Emeritus


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

PostPosted: Wed Jun 06, 2012 6:59 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Jun 07, 2012 8:35 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
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
Search our Forums:

Back to Top