View previous topic :: View next topic |
Author |
Message |
naren_ab Beginner
Joined: 07 Jan 2003 Posts: 32 Topics: 10
|
Posted: Mon Oct 27, 2003 10:56 pm Post subject: BMC UNLOAD PLUS |
|
|
Hi all I am using BMC UNLOAD PLus utility, I have coded it like this:- Code: | UNLOAD CNTLCARDS DB2LOAD 'LOG NO REPLACE ENFORCE NO'
SHRLEVEL CHANGE
DIRECT NO
SELECT * FROM Table A
WHERE col1 = 'X'
OR Col2 < CURRENT DATE + 60 DAYS |
but it gives error saying cannot use shrlevel Change with Direct No.But if i remove DIRECT No then i am getting this error.
Code: | OR Col2 < CURRENT DATE + 60 DAYS
@ | UNEXPECTED TOKEN '+' ENCOUNTERED IN COMMAND I have to use shrlevel change so that my unload will not be bottle neck for the database.how do i solve this. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Tue Oct 28, 2003 6:46 am Post subject: |
|
|
Naren_ab,
UNLOAD PLUS does not support the SHRLEVEL option when you specify DIRECT NO. Instead, you can use ISOLATION levels in BIND parameters or in the DB2 SQL WITH clause to control access to objects during the unload process.
As far as I know you can only substract an integer with The CURRENT DATE option. The current date minus a duration in either days, months, or years.
Hope this helps...
cheers
kolusu |
|
Back to top |
|
|
naren_ab Beginner
Joined: 07 Jan 2003 Posts: 32 Topics: 10
|
Posted: Tue Oct 28, 2003 10:17 am Post subject: |
|
|
Kolusu thanx for the reply,
Can i use DSNTIAUL in this case and use shrlevel change and current date + 20 days thing.
And when you said "As far as I know you can only substract an integer with The CURRENT DATE option." did you mean in the case of BMC unload only or any sql.
If it is only BMC unload, then is there a way to handle this differently, because when we can subtract, why cannot we add? |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Tue Oct 28, 2003 10:54 am Post subject: |
|
|
Naren_ab,
My shop does not have BMC unload Plus utility. My previous shop(5 years ago) had it and I remembered that there is a problem with addition of dates with BMC UNLOAD UTILITY.I don't recollect the exact problem. Sorry about that.why don't you just change the "+" to "-" sign and see if it works.
I Cannot judge BMC UNLOAD PLUS utility performance but I can say that DSNTIAUL is quite powerful and faster. We have a table around 22 million, 609 LRECL and the whole table is unloaded in just 20 minutes.
yes DSNTIAUL can be used to unload the data. The only hitch is that you can delimit your data which BMC unload plus/Platinum unload offers.
Here is a sample Unload JCl which will give you the desired results.
Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXXX.RUNLIB.LOAD')
//SYSREC00 DD DSN=YOUR UNLOAD FILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=CYL,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT * FROM Table A
WHERE col1 = 'X'
OR Col2 < CURRENT DATE + 60 DAYS
/*
|
If you have DB2 version which is less than 7 then you may want to change the query to
Code: |
SELECT * FROM Table A
WHERE col1 = 'X'
OR Col2 < (SELECT DATE(CURRENT DATE + 60 DAYS)
FROM SYSIBM.SYSDUMMY1)
|
check this link for a detailed explanation of DSNTIAUL with examples.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/APPENDIX1.3.1?DT=20010710165542
Hope this helps...
cheers
kolusu |
|
Back to top |
|
|
naren_ab Beginner
Joined: 07 Jan 2003 Posts: 32 Topics: 10
|
Posted: Tue Oct 28, 2003 11:55 am Post subject: |
|
|
That helps a lot, Thank you.
FYI - minus sign works, but not plus sign, the Ironic thing is plus sign works if i specify DIRECT NO.
thanks
krishna |
|
Back to top |
|
|
|
|