Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file. It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file. Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.
| CREATE PROCEDURE MYLIB.KILLJOBSGIVENFILE ( ) | |
| LANGUAGE SQL | |
| SPECIFIC MYLIB.KILLJOBSSP | |
| BEGIN | |
| —Variable to control loop | |
| DECLARE END_TABLE INT DEFAULT 0; | |
| —If there's no record update END_TABLE to 1 to end the loop | |
| DECLARE CONTINUE HANDLER FOR NOT FOUND | |
| SET END_TABLE = 1; | |
| —Get Job Names based on library and file | |
| DECLARE C1 CURSOR FOR | |
| SELECT JOB_NAME | |
| FROM QSYS2.RECORD_LOCK_INFO | |
| WHERE SYS_DNAME = 'MYLIB' | |
| AND SYS_TNAME = 'MYFILE' | |
| OPEN C1 ; | |
| —optional lock the user that is trying to access | |
| CALL QSYS2.QCMDEXC('ALCOBJ OBJ((QSYS/WEBUSER *USRPRF *EXCL))'); | |
| FETCH C1 INTO JOB_NAME; | |
| WHILE END_TABLE = 0 DO | |
| —Kill jobs | |
| CALL QSYS2.QCMDEXC('ENDJOB JOB(' || JOB_NAME || ') OPTION(*IMMED)'); | |
| FETCH C1 INTO JOB_NAME; | |
| END WHILE; | |
| —Wait 10 seconds for jobs to clear | |
| CALL QSYS2.QCMDEXC('DLYJOB DLY(10)'); | |
| —Do what you want with the file | |
| —HERE! | |
| —Let the user back in | |
| DLCOBJ OBJ((QSYS/WEBUSER *USRPRF *EXCL)) | |
| CLOSE C1; | |
| RETURN ; | |
| END ; | |
| —Run Stored Proc | |
| CALL MYLIB.KILLJOBSGIVENFILE (); | |
| —https://www-01.ibm.com/support/knowledgecenter/ssw_i5_54/cl/endjob.htm | |
| —https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20Services%20-%20System%20names%20for%20new%20files%20in%20QSYS2 |