September 5, 2016

Updating jobs owners, creators and step users in tables TBTCO and TBTCP

In many projects I have been requested at some stage to update all jobs to use the proper batch user, and in some particular cases the clients decided that they wanted the owner and creator of the job to be updated too.

I have designed below queries to accomplish such task directly on TBTCO and TBTCP tables:


In almost all cases the names and filters on the where filter should be adjusted to match each situation, so take this queries as conceptual but rebuild them to match your particular case.

Take into account following possible status values when building the queries:
Active/Running 'R'
Ready 'Y'
Scheduled 'P'
Released 'S'
Aborted 'A'
Completed/Finished 'F'
Release/suspended 'Z'
Unknown 'X'

This first select should output all the jobs on the TBTCO that will get updated with the update below as the filter is identical, I recommend to review the output of this query to ensure no jobs that should be excluded will get changed  with the update:
select JOBNAME, JOBCOUNT, STATUS, SDLUNAME, LASTCHNAME from PRD.SAPSR3.TBTCO where STATUS!='A' and SDLUNAME!='WF-BATCH' and SDLUNAME!='BWREMOTE' and JOBNAME not like 'RDDIMPD%'

Once you ensure the filter is the proper one (And never before you are really sure) you get into the building of the update statement and get the TBTCO changed:
update PRD.SAPSR3.TBTCO set SDLUNAME='UC4BATCH', LASTCHNAME='UC4BATCH' where STATUS!='A' and SDLUNAME!='WF-BATCH' and SDLUNAME!='BWREMOTE' and JOBNAME not like 'RDDIMPD%'

In this particular case we will change TBTCP entries only where its linked TBTCO entry was changed, again this is just conceptual so you should experiment and rebuild this query to match your particular situation. For our situation we exclude some key users from our query, even these should not be on any job due to the structure of the query we are just doing it the safe way, and then we match each JOBCOUNT from both tables:
select JOBNAME, JOBCOUNT, SDLUNAME, AUTHCKNAM, PRREC from PRD.SAPSR3.TBTCP where JOBCOUNT IN (select JOBCOUNT from PRD.SAPSR3.TBTCO where STATUS!='A' and SDLUNAME!='WF-BATCH' and SDLUNAME!='BWREMOTE' and JOBNAME not like 'RDDIMPD%' and SDLUNAME='UC4BATCH') and AUTHCKNAM!='WF-BATCH' and AUTHCKNAM!='BWREMOTE'

Once you get sure enough that the where filter is the one you should use for your update you can get the job done by building the second where statement:
UPDATE PRD.SAPSR3.TBTCP
set AUTHCKNAM='UC4BATCH', PRREC='UC4BATCH', SDLUNAME='UC4BATCH'
WHERE JOBCOUNT IN (
select JOBCOUNT from PRD.SAPSR3.TBTCO where STATUS!='A' and SDLUNAME!='WF-BATCH' and SDLUNAME!='BWREMOTE' and JOBNAME not like 'RDDIMPD%' and SDLUNAME='UC4BATCH') and AUTHCKNAM!='WF-BATCH' and AUTHCKNAM!='BWREMOTE'