Batch SQL ececution with File input

ykishore · New User Joined: 12 Aug 2005 Posts: 53 Location: USA

Hi

Here is my requirement..

i have some set of SSN numbers in the i/p files and i want to delete all the records from the production table for those SSN. is there any way to do that thru Batch JCL? please help me out on this. and this is urgent.

dick scherrer · Posted: Sun Mar 04, 2007 10:37 am

Hello,

It is about a 10 minute program. . .

Read "the file"
Delete from table where the ssns are equal.

Very small bit of code

priyesh.agrawal · Posted: Sun Mar 04, 2007 11:50 am

ykishore · New User Joined: 12 Aug 2005 Posts: 53 Location: USA

Hi

Thanks for info!! But i want to get do it thru JCL? pls let me know ..

priyesh.agrawal · Posted: Sun Mar 04, 2007 12:31 pm

dick scherrer · Posted: Mon Mar 05, 2007 2:46 am

Hello,

If you want to solve your requirement quickly (by your post this was "urgent"), do the simple thing - not wait for "what you want".

If you just want to find (wait for) a particular solution, it cannot be urgent. That urgency is artificial. Urgency would not include multiple days looking for a solution.

If you wanted another type of jcl fix you could always close and backup the databasae, unload the table, match the ssn file with the employee file using sort and eliminate the "matches" from the output file. Then, reload the employee table. That would need no DB2 query and no program - not a very good choice (IMHO) but does do the task "thru JCL". Of course a major drawback of that solution is that the deletes would not be recorded in the DB2 logs.

Have the ssns been deleted?

vini_srcna · Posted: Tue Mar 06, 2007 3:42 pm

How many SSN's are there by the way...? If they are pretty less, you could write the SQL query to delete all records in a single query or an indivual query which ever is easy for you and execute it in SPUFI.

Thanks for info!! But i want to get do it thru JCL?

If you want that to happen through JCL, code the queries and execute it using one of the productivity aid sample programs like DSNTIAUL, DSNTEP2 or DSNTIAD etc. You could find multiple ways to execute the SQL dynamically. Let me know if this did not help you..!