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
 

 

How To Use DSNUPROC(unload) Efficiently?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How To Use DSNUPROC(unload) Efficiently?
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

Site Director


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

PostPosted: Mon Apr 14, 2008 7:56 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Tue Apr 15, 2008 11:39 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Apr 17, 2008 10:47 am    Post subject:
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    Post subject: Can u unload data in sorted order using DSNUPROC .
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Fri Aug 01, 2008 4:16 pm    Post subject: Reply to: How To Use DSNUPROC(unload) Efficiently?
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    Post subject:
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    Post subject: Reply to: How To Use DSNUPROC(unload) Efficiently?
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    Post subject:
Reply with quote

This link provides the several examples for using DSNUPROC.
http://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: 1288
Location: Chennai, India

PostPosted: Wed Aug 06, 2008 11:55 am    Post subject: Reply to: How To Use DSNUPROC(unload) Efficiently?
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    Post subject:
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    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 Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm
No new posts How to suppress retun code of partil ... mkk157 DB2 9 Fri May 27, 2016 6:59 am
No new posts FAST UNLOAD and LOAD issue GaganGarg DB2 1 Mon Feb 15, 2016 2:30 pm


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