ESP Workload Automation

 View Only
  • 1.  How do I delete DB trigger from ESP command line

    Posted Mar 01, 2018 05:48 AM

    Hello,

     

    Is there a way to delete DB trigger from command line instead mark completing ? I have the below job setup and when I run it, it is creating a trigger on database and now, I need to add another LINK/TASK job to delete the DB trigger instead mark completing the job.

     

    DB_TRIG TEST_JOB
       AGENT %AGENT
       RUN ANY
       USER %DB_USER
       DB_URL '%DB_URL'
       TABLE_NAME ops.ops_run_status
       TRIG_TYPE UPDATE
       TRIG_COND 'new.as_relates_to=''eis_lnp'' and +
    new.process_name=''db_watcher'' and new.status=''data received'''

    ENDJOB



  • 2.  Re: How do I delete DB trigger from ESP command line

    Posted Mar 01, 2018 09:37 AM

    Hi Santhosh, 

    I am not sure that I understand the question so let me know if I "Miss the boat". 

     

    You can issue a AJ command to complete the job/database trigger. If it is in the same appl you can use some variables to complete it. 

    ESPNOMSG AJ TEST_JOB COMPLETE APPL(%ESPAPPL..%ESPAPGEN)

     

    In most cases I think it is best to leave it always running and control what ESP does when the trigger happens. 

    For example:

    If you only want the trigger to work between 8am and 5pm then let the trigger happen at other times but don't build the appl in the off hours. 

    By forcing complete and restarting the database trigger each time it is need, it is possible that it will not get canceled in Oracle in every case. Then it is possible that there could be multiple database triggers in Oracle for the same trigger. 

     

    Don



  • 3.  Re: How do I delete DB trigger from ESP command line

    Posted Mar 01, 2018 09:43 AM

    Hello Don,

     

    I do not want to complete job/APPL. I want to issue delete trigger for DB_TRIG from wob.

     

    I can do this by right clicking on job in Workstation and select Delete Trigger option. Is it possible to do this by issuing a command also ?



  • 4.  Re: How do I delete DB trigger from ESP command line

    Posted Mar 01, 2018 10:22 AM

    Hi, 

    Help me to understand the need here.  The database trigger will continue to show active even though it is not.

    Nothing will be triggering since it is not active. Later on when the wob is complete the command will fail since it can't find the trigger. 

     

    To compare:

    This would be like having a UNIX jobs show as running that already ended. I don't see the benefit/need. I only see confusion. 

     

    We can find the command if we need to. 



  • 5.  Re: How do I delete DB trigger from ESP command line

    Posted Mar 01, 2018 10:41 AM

    Ok, let me put it in this way.

     

    I have the DB_TRIG job which created trigger session on the database. I can delete this session from database by issuing delete trigger option when right clicking on DB_TRIG job in CA workstation.

     

    Is it possible to delete the trigger from database in same way by issuing a wob command ?



  • 6.  Re: How do I delete DB trigger from ESP command line
    Best Answer

    Posted Mar 01, 2018 10:52 AM

    Just so we are on the same page.

    The W command can also be issued from CSF to do the same as the Workstation

     

    Completing the job will issue the delete command to Oracle. This will keep both sides in sync. 

     

    To find the command that is issued, issue it in workstation and look at the receiver.log in the agent.