View previous topic :: View next topic
|
Author |
Message |
Vidhya Karthi
New User
Joined: 28 Aug 2007 Posts: 18 Location: Chennai
|
|
|
|
Hi All,
Can anyone explain me how to perform MASS DELETE using JCL?
I have a PS file with 1000 records , which are to be deleted from the table.
Can this be done thru JCL. Please help out. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
You could process the PS file and generate sql delete statements for each "key" to be deleted.
As there are only 1000, you could easily do this by editing also. |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
1) Using IKJEFT01
-> Reformat the data to be appended to an 'IN' clause in delete the query(Can get lot of ex for this from DFSORT forum)
-> Use IKJEFT01 to run the delete query(sysin should have the delete query and reformated data append)
-> Should have a primary key column here. Otherwise it will be difficult.
2) Using QMF Proc
-> Reformat the data in QMF format and QMF header,
-> import the data to a temporary table
-> Run a delete query against your table and tem table
-> Erase tem table
Please correct me If I am wrong.
Thanks,
Prajesh |
|
Back to top |
|
|
Vidhya Karthi
New User
Joined: 28 Aug 2007 Posts: 18 Location: Chennai
|
|
|
|
Hi,
I don't understand reformatting data. Can you please provide the sample JCL for doing mass delete? |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
Suppose you have the emp numbers inthe file as below:
12003
12323
34344
create a DSN DSN2 in the following format:
12003,
12323,
34344);
Have a permanent DSN DSN1 as below:
Delete from emp_tbl
where emp_no in(
Now append DSN1 and DSN2 to the sysin of your IKJEFT01 step:
Please see the post http://ibmmainframes.com/viewtopic.php?t=19045&highlight=query for further info.
Thanks,
Prajesh |
|
Back to top |
|
|
Vidhya Karthi
New User
Joined: 28 Aug 2007 Posts: 18 Location: Chennai
|
|
|
|
Hi Prajesh,
Still i'm not clear with this...
create a DSN DSN2 in the following format:
12003,
12323,
34344);
I can append a comma to each record thru syncsort. But, How can we append the ); in the last record. Can u plz help with sort card?
Thanks in advance. |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
Use a sort with below sort card:
INREC IFTHEN=(WHEN=INIT,OVERLAY=(6:C',',8:SEQNUM,3,ZD)),
IFTHEN=(WHEN=(8,3,CH,EQ,C'001'),OVERLAY=(6:C');'))
SORT FIELDS=(8,3,CH,D)
OUTREC BUILD=(1,7)
Input:
12003
12323
34344
Output:
34344,
12323,
12003);
Thanks,
Prajesh V P |
|
Back to top |
|
|
Vidhya Karthi
New User
Joined: 28 Aug 2007 Posts: 18 Location: Chennai
|
|
|
|
Hi Prajesh,
Thanks for u'r help. |
|
Back to top |
|
|
|