View previous topic :: View next topic
|
Author |
Message |
dannis
New User
Joined: 14 Apr 2008 Posts: 4 Location: New York
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dannis
New User
Joined: 14 Apr 2008 Posts: 4 Location: New York
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dannis
New User
Joined: 14 Apr 2008 Posts: 4 Location: New York
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Benoy
New User
Joined: 25 Jul 2008 Posts: 10 Location: Chennai
|
|
|
|
Hi all
Can we unload data from tables using DSNUPROC in sorted order?? |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Try unload from Query with Order by. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Benoy
New User
Joined: 25 Jul 2008 Posts: 10 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
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 |
|
|
Benoy
New User
Joined: 25 Jul 2008 Posts: 10 Location: Chennai
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Aaru,
Example 2 provides an equivalent of ORDER BY. |
|
Back to top |
|
|
|