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
 

 

Unload a table using SELECT-Statement

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

New User


Joined: 10 Jan 2006
Posts: 24
Location: Germany

PostPosted: Fri Jun 29, 2007 3:06 pm    Post subject: Unload a table using SELECT-Statement
Reply with quote

Hi, I hope someone of you can solve my problem:

I have to unload specific rows of one table that correspond to the content
of a second table. So here is my SELECT-Statement:

SELECT * FROM firsttable A WHERE EXISTS (
SELECT * FROM secondtable B WHERE
B.INBRVSNR = A.DXBRVSNR
);

The result will be a very large amount of records.
While the table rows content a varchar-column of 32000 bytes,
if I use DSNTIAUL, I'm running out of volume space. Because
DSNTIAUL only creates FB-datasets, so that will use the full 32016 Bytes for each result record.

Is there a way to force DSNTIAUL to create VB-datasets?

Or can I use DB2-Unload-Utility using my SELECT?
(I think WHEN-Clause won't work with keyword EXISTS or table-joins)
Back to top
View user's profile Send private message

wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jun 29, 2007 11:15 pm    Post subject: Re: Unload a table using SELECT-Statement
Reply with quote

What is the expected size of the record assuming that varchars are downloaded with full length?

The maximum LRECL possible in DSNTIAUL is 32760 bytes and, for that metter in any dataset. So you have around 2760 bytes for your rest of the columns.

Since the file will be too big, you can use multiple volumes of tape if possible.

And perhaps changing your query to the following will speed up things.

SELECT * FROM firsttable A WHERE EXISTS (
SELECT 1 FROM secondtable B WHERE
B.INBRVSNR = A.DXBRVSNR
);

Hope you have an index on B.INBRVSNR in secondtable.
Back to top
View user's profile Send private message
MichaelKBS

New User


Joined: 10 Jan 2006
Posts: 24
Location: Germany

PostPosted: Sat Jun 30, 2007 1:31 pm    Post subject: Re: Unload a table using SELECT-Statement
Reply with quote

Hello wanderer,

thank you for your answer!

The maximum record length is 32016 Bytes (includes 32000 bytes varchar). I expect up to 500 000 rows that match the select-conditions.
The tables are indexed. The performance is not a problem.
But space request is. Even multivolume is not a solution for I can't grap enough in my departement. And I tried to avoid using tapes.
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: Sat Jun 30, 2007 5:00 pm    Post subject:
Reply with quote

Hello,

How will the unloaded file later be used?

How much of the 32k varchar field is usually not used?

If this were my task, i would probably use a bit of programming code and be able to control the creation of the output file. I'd most likely create some kind of delimited (tab, backslash, tilde) output which is quite easy to use as input to many different types of processes later.
Back to top
View user's profile Send private message
MichaelKBS

New User


Joined: 10 Jan 2006
Posts: 24
Location: Germany

PostPosted: Mon Jul 02, 2007 6:53 pm    Post subject:
Reply with quote

Hi Dick,

how you suggested, I've written a small COBOL-program for unloading the table, using VB output dataset.
That saved over 90 % of required volume space, cause the average size
of 32 k varchar was about 150 Bytes.

But I still wonder if there's no DB2-Utility.

However, Wanderer and Mr. Scherrer, thank you for your answers.
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: Mon Jul 02, 2007 7:18 pm    Post subject:
Reply with quote

You're welcome icon_smile.gif

There are possibly add-on utility products (other than what are part of the actual DB2 product) that would do what you want.

Also, possibly, someone here in the forums may know of a "DB2" way.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 12:37 am    Post subject: Re: Unload a table using SELECT-Statement
Reply with quote

One crude method will be use the SUBSTR function on that column in SELECT of DSNTIAUL and get only 150 bytes from beginning. Although this '150' needs to be changed if needed after table data changes anytime in future.

There is an option in the UNLOAD utility called NOPAD which is supposed to not pad variable length records and output file is VB. You may give that a try. I haven't tried it so far.
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: Tue Jul 03, 2007 1:05 am    Post subject:
Reply with quote

Hello,

The SUBSTR may cause problems as the 150 is the average output record length.

The NOPAD sounds promising. . .
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Jul 03, 2007 1:15 am    Post subject:
Reply with quote

Oops, missed the 'average' in Michael's post. Thanks Dick for pointing out. I meant the length of the longest populated varchar string. This off-course is not a very good way especially if you have even only one very long string.
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 how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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