How to Cancel Running Load Jobs, especially when blocking deploy model changes

If you are asking yourself :

"Why do we keep getting this message when there are no jobs running?"

Or "why can't I deploy my model change because loads are running? I don't see any loads!"

Or, "I cancelled all the loads I know about but I cannot see some of the loads so I don't know how to cancel them because I can't see the load IDs from the Workbench Data Location Load logs".

Step-by-step guide to Canceling Load jobs

1. Check the Latest Loads section to see if you have any Load jobs with RUNNING status. You might think you don't have jobs running, but if you grabbed some load IDs from xDM and then did not submit those jobs or did not cancel, then you have "running" loads even if you are not actively loading data into them using SSIS/ODI/ETL. 

   xDM is telling you that since these loads are still open, you better cancel them in case you change the model out from under somebody who is trying to load data, assuming the old model is in place.

   1. How to do it? 
   2. Data Location logs
      Keep drilling until you see Latest Loads.
   3. 
2. Call the SEMARCHY_REPOSITORY.INTEGRATION_LOAD.CANCEL_LOAD procedure to cancel these RUNNING jobs. You cannot cancel the jobs from the workbench.
3. If you still see Load jobs that are running, it's because older loads are no longer showing up on the latest loads list. How can you find them? 

   1. You will have to query the repository.

      select loadid
      from SEMARCHY_REPOSITORY.mta_integ_load
      where status = 'RUNNING' and classname='IntegrationLoad';

   2. Grab those load IDs and cancel them.

   3. Here's a nice cheat/tip from Mike L. 

      select loadid, creator, 'exec SEMARCHY_REPOSITORY.INTEGRATION_LOAD.CANCEL_LOAD(' || loadid ||  ', ''' || creator || ''');'
      from SEMARCHY_REPOSITORY.mta_integ_load
      where status = 'RUNNING' and classname='IntegrationLoad'
      order by loadid;