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
 
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: 1868
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: 8152
Location: East Dubuque, Illinois, USA

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: 6968
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: 1542
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: 8152
Location: East Dubuque, Illinois, USA

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: 1521
Location: Chennai

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 Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Extract Data till prev hour balaji81_k DB2 16 Fri Aug 25, 2017 9:25 pm
No new posts FTP data transfer from PC to Mainfram... AJAYREDDY All Other Mainframe Topics 8 Wed Aug 23, 2017 9:59 pm
No new posts Unable to copy data from spool in a d... srikant314 JCL & VSAM 8 Fri Jul 28, 2017 9:10 pm
No new posts Paasing symbolic parameter to instrea... Robert Sample JCL & VSAM 2 Thu Jul 27, 2017 1:22 pm

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