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

How To Use DSNUPROC(unload) Efficiently?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dannis

New User


Joined: 14 Apr 2008
Posts: 4
Location: New York

PostPosted: Mon Apr 14, 2008 12:14 pm
Reply with quote

Dear All
There are some tables with millions of rows.
I just write some JCLs to unload these tables ,when these JCLs are submitted at one time, the jobs cost a very long time. So could any one tell me How To Use DSNUPROC(Unload) Efficiently?

Thanks & Regards
Dannis
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: Mon Apr 14, 2008 7:56 pm
Reply with quote

Hello Dannis and welcome to the forums,

Quote:
There are some tables with millions of rows.
How many millions of rows?
Quote:
the jobs cost a very long time
What is considered long?

Depending on the sheer volume, the unload may take a while.

If you post your unload jcl and control info, we may be able to offer suggestions.
Back to top
View user's profile Send private message
dannis

New User


Joined: 14 Apr 2008
Posts: 4
Location: New York

PostPosted: Tue Apr 15, 2008 8:24 am
Reply with quote

Hi dick scherrer,
thanks for your replay,my jcl as flows,

//UNLOAD EXEC DSNUPROC,UID='AAA',UTPROC='',SIZE=128M,
// SYSTEM=&DB2SYS
//SYSPRINT DD SYSOUT=*
//TEMPAAA DD DSN=&&TEMPAAA,DISP=(NEW,PASS),VOL=(,,,50),
// SPACE=(CYL,(500,300))
//ULDCAAA DD DSN=&&ULDCAAA,DISP=SHR
//SYSIN DD *
UNLOAD FROM TABLE AAA
UNLDDN TEMPAAA PUNCHDDN ULDCAAA
/*

By the way,the table AAA has 100millions rows,the table BBB has 80millions rows,the table CCC has 100million rows.When those 3 JCLs are submitted at one time,it will cost a very long time.what's the problem with my JCL?
Thanks a lot!

Regards
Dannis
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: Tue Apr 15, 2008 11:39 am
Reply with quote

Hello Dannis,

As i asked before - what is considered a "very long time"?

You didn't mention the length each row - this will make a difference.

How will the unloaded data be used?

Is there some reason to submit all 3 concurrently rather than sequentially?
Back to top
View user's profile Send private message
dannis

New User


Joined: 14 Apr 2008
Posts: 4
Location: New York

PostPosted: Thu Apr 17, 2008 9:04 am
Reply with quote

Hi dick,
Thanks a lot for your reply.The length of each row is 1600K,so I need to unload entire 3 tables to separate 3 Qsam. I must submit all 3 concurrently rather than sequentially.


Thanks & Regards
Dannis
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 Apr 17, 2008 10:47 am
Reply with quote

Hello Dannis,

Quote:
I must submit all 3 concurrently rather than sequentially
Why? This may be a factor in your performance concern.

If the numbers you posted are accurate and i did not make an error in calculation, you are unloadng 4,480,000,000,000 (280,000,000 rows * 16,000 bytes per row) bytes. This will take considerable time no matter how it is done.

Speaking of which, please re-read my earlier reply and answer the questions not yet answered.

How much media does this unload require?
Back to top
View user's profile Send private message
Benoy

New User


Joined: 25 Jul 2008
Posts: 10
Location: Chennai

PostPosted: Thu Jul 31, 2008 10:38 am
Reply with quote

Hi all

Can we unload data from tables using DSNUPROC in sorted order??
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Jul 31, 2008 11:46 am
Reply with quote

Try unload from Query with Order by.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 31, 2008 11:56 am
Reply with quote

Quote:

Speaking of which, please re-read my earlier reply and answer the questions not yet answered.


why should OP answer questions? He wants a solution.
Back to top
View user's profile Send private message
Benoy

New User


Joined: 25 Jul 2008
Posts: 10
Location: Chennai

PostPosted: Thu Jul 31, 2008 12:06 pm
Reply with quote

I tried order by..but not working ...tha data card is copied below.


PROCESSING SYSIN AS EBCDIC
- UNLOAD DATA
- EBCDIC CCSID(01146,01146,65534)
- FROM TABLE <table name>- (
- INTERNAL_CUST_ID
- ,COVENANT_ORDINAL
- ,ASSET_COVER_FORMULA_CODE
- ,PREF_CREDITOR_FLAG
- ,UTILIZATION_RATIO
- ,STOCK_PERCENTAGE
- ,TRADE_DEBTOR_AGE
- ,STOCKS_COMMENT
- ,DEBTORS_COMMENT
- ,LAST_MOD_USER
- ,LAST_MOD_TS
- )
- ORDER BY INTERNAL_CUST_ID
- INVALID OPERAND 'ORDER' FOR KEYWORD 'FROM'
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 31, 2008 12:15 pm
Reply with quote

i would try changing:

-,LAST_MOD_TS
-)


TO:

-,LAST_MOD_TS) -


Unfortunately I have not bothered to look at the manual (like you) and have no idea what syntax is for the control cards.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Fri Aug 01, 2008 4:16 pm
Reply with quote

dbzTHEdinosauer,

I have got a similar requirement and I am getting the same error when I tried to use ORDER BY with DSNUPROC.

what exactly is the syntax?

I searched in the manuals and was not able to find it.

Thanks in advance.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Fri Aug 01, 2008 6:22 pm
Reply with quote

Code:
//STEP010  EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DB2S)
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS ('SQL')
 END
//SYSPRINT DD SYSOUT=*
//LISTING  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=LMCD.CARS.MCDCO4P.MCDV023.D001215,
//                      DISP=(,CATLG),UNIT=SYSDA
//SYSPUNCH DD DUMMY
//SYSIN    DD *
   Your query here
/*


Hope, you can find a similar job in your shop too....
Back to top
View user's profile Send private message
Benoy

New User


Joined: 25 Jul 2008
Posts: 10
Location: Chennai

PostPosted: Mon Aug 04, 2008 5:09 pm
Reply with quote

But we had a restriction like ,
our table is Unicoded,so even the IBM or BMC wont work ..

We have to use DSNPROC ..
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Mon Aug 04, 2008 6:36 pm
Reply with quote

This link provides the several examples for using DSNUPROC.
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNUGK10/2.30.4?DT=20070125001207

Hope this helps.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Wed Aug 06, 2008 11:55 am
Reply with quote

Thanks Srihari for the link. I had a look at the link before posting but was not able to find the equivalent keyword.

All I wanted to know is the equivalent keyword for "ORDER BY" in DSNUPROC.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Aug 06, 2008 3:07 pm
Reply with quote

Aaru,
Example 2 provides an equivalent of ORDER BY.
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 Load new table with Old unload - DB2 DB2 6
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts changing defaults in db2 admin - Unlo... DB2 0
No new posts DB2 Table - Image copy unload IBM Tools 2
Search our Forums:

Back to Top