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
 

 

Deleting data of entire TS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Jimy Carol

New User


Joined: 31 Aug 2012
Posts: 25
Location: Australia

PostPosted: Mon Jun 17, 2013 6:53 pm    Post subject: Deleting data of entire TS
Reply with quote

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
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 17, 2013 7:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Jimy Carol

New User


Joined: 31 Aug 2012
Posts: 25
Location: Australia

PostPosted: Mon Jun 17, 2013 10:36 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 17, 2013 11:25 pm    Post subject:
Reply with quote

Quote:
I need to do this dynamically


Dynamically means what?
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 Jun 18, 2013 12:55 am    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Jun 18, 2013 10:46 am    Post subject:
Reply with quote

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
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Thu Jun 20, 2013 2:39 am    Post subject:
Reply with quote

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
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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