View previous topic :: View next topic
|
Author |
Message |
Jimy Carol
New User
Joined: 31 Aug 2012 Posts: 25 Location: Australia
|
|
|
|
Hello ,
I need to delete the whole date in table space XYZ . the TS holds table tbl_xyz . Researched the forum and found that LOAD REPLACE is efficient way of doing this , I got the below excerpt :
Code: |
2.8.1.3.5 Deleting All the Data in a Table Space
Specifying LOAD REPLACE without loading any records is an efficient way of
clearing a table space. To achieve this, the input data set should be
specified in the JCL as DD DUMMY. LOAD REPLACE is efficient because:
1. LOAD REPLACE does not log any rows.
2. LOAD REPLACE redefines the table space.
3. LOAD REPLACE retains all views and privileges associated with a table
space or table.
4. LOG YES can be used to make the LOAD REPLACE recoverable.
LOAD REPLACE will replace ALL TABLES in the table space. |
However i am still not sure of the complete syntax to write it correctly .
I thought below :
LOAD INDDN REPLACE
INDDN (DUMMY)
INTO TABLE SPACE XYZ .
Kindly suggest the right synatx .
regards ,
carol |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Why do you need to LOAD REPLACE the table space?
What kind of table space is xyz?
Note : If you are doing this for the first time please talk to your DBA's first |
|
Back to top |
|
|
Jimy Carol
New User
Joined: 31 Aug 2012 Posts: 25 Location: Australia
|
|
|
|
Quote: |
What kind of table space is xyz? |
Table space xyz is Partition by growth .
Actually for some reason I have to ALTER the MAXPARTITION of Table space .
The sequence of operation that I need to do :
1) empty the Tables space
2) ALTER tables space , MAXPARTIION = number
3) Reorg
4) Load the Table space with data from ( some other TS )
Now , for empying the TS , i thought LOAD REPLACE
Problem is I am not sure of the correct syntax for LOAD REPLACE .
Also , I need to do this dynamically , so I have to generate this as LOAD card utility . |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Quote: |
I need to do this dynamically |
Dynamically means what? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Possibly i am missing something, but could you not empty the table using TRUNCATE TABLE?
As PB asked - What does Dynamically mean to you for this case? |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi Carol,
Below is sample loadcard to dummy load the tablespace,
Code: |
//SYSIN DD *
TEMPLATE DVTXJNOO
DSN(NULLFILE)
DISP(OLD,KEEP,KEEP)
LOAD DATA REPLACE LOG NO NOCOPYPEND DISCARDS 1
INDDN DVTXJNOO
SORTDEVT SYSDA SORTNUM 24
INTO TABLE LLOYSBBT.MAP_ADHOC
|
You said you need to do this dynamically meaning you have lots of tablespaces to dummy load and you don't want to generate load card for all those tables, is that what you mean ?
regards,
Sushanth |
|
Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
|
|
|
|
Below is one possible syntax. This will empty the table with no logging. If you want logging then remove the "LOG NO NOCOPYPEND"
In your jcl add a dd SYSREC with a DUMMY as shown below.
Code: |
//SYSIN DD *
LOAD REPLACE LOG NO NOCOPYPEND INTO TABLE ???
//SYSREC DD DUMMY
|
When you do the load create an image copy afterwards so that logging can continue. |
|
Back to top |
|
|
|