CA Workload Automation ESP Edition

Expand all | Collapse all

Best way update SQL DB from ESP ?

Jump to Best Answer
  • 1.  Best way update SQL DB from ESP ?

    Posted 07-18-2016 01:44 PM

    Hi, i would like to know if any of you are using ESP scheduler job to update an SQL database.

     

    as far as i read on the SQL_JOB statement it seems like its only query that its able to do and that DB_JOB is not supported after release 7.

     

    Let me know your best way to make Update to a SQL database !

     

    Also: if you got any other DB that you are making update to  from ESP to the DB, all different idea are welcomed !

     

    Thanks everyone



  • 2.  Re: Best way update SQL DB from ESP ?

    Posted 07-18-2016 02:06 PM

    We are not using any Database agents, but that is not necessarily by choice.  All of our programmers are either scripting SQL statements directly (in Korn shell scripts or vbs or whatever) or skipping ESP all together and using stored procs or other database automation.



  • 3.  Re: Best way update SQL DB from ESP ?

    Posted 07-18-2016 02:33 PM

    Thanks Jonathan, unfortunately i need to use ESP for that one instance, i can use the powershell way but would like to see if there is a better method.



  • 4.  Re: Best way update SQL DB from ESP ?
    Best Answer

    Posted 07-18-2016 02:45 PM

    Hi Alex,

     

    Sorry that I misled you for your earlier call. SQL_JOB is the new replacement for DB_JOB. Actually the SQL for SQL_JOB is not limited to query, see page 1529 of the "Command Reference Guide" for "SQL Statement—Specify SQL to Run Against a Database Table (CA WA Database Agent Only)":

    Note: The value can contain any SQL statement including SELECT, DELETE, UPDATE or INSERT.

     

    Also Enhancement RO67952 introduced a new wob type MSSQL_JOB, it can be used to run a job (which can do any update) defined on SQL side.

    Here is an example:

    MSSQL_JOB TEST_1

    AGENT MSSQL

    PWDNAME lodca0002

    DOMAIN lodca0002

    TARGET lodca0002

    SERVER lodca0002

    STEPNAME ‘Step 2’

    JOBNAME 'Test Job 1'

    RUN DAILY

    ENDJOB

     

    Lucy



  • 5.  Re: Best way update SQL DB from ESP ?

    Posted 07-18-2016 02:57 PM

    Oh good to know, the definition on the SQL_JOB might have put me on a wrong track since it was referring a lot to query only.

     

    Thank you very much Lucy !



  • 6.  Re: Best way update SQL DB from ESP ?

    Posted 07-18-2016 04:27 PM

    Here are some other examples that might help out while you are investigating the agent:

     

    SQL_JOB Backup                                                   

       AGENT JPRR113_DB                                              

       USER cawa                                                     

       DB_URL 'jdbc:sqlserver://roojo02W7:1433;'                     

       SQL 'BACKUP DATABASE dSeries TO dSeries113_backup_device'     

       RUN ANY                                                       

       RELEASE (SQL_Select_1)                                        

    ENDJOB                  

     

    SQL_JOB SQL_Select_1                                             

       AGENT JPRR113_DB                                              

       USER cawa                                                     

       DB_URL 'jdbc:sqlserver://roojo02W7:1433;databasename=pubs'    

       SQL 'Select * from stores;'                                   

       RUN ANY                                                       

       RELEASE (DBUpdate.Master)                                     

    ENDJOB                                                          

     

    SQL_JOB DBUpdate.Master                                          

       AGENT JPRR113_DB                                              

       USER cawa                                                     

       DB_URL 'jdbc:sqlserver://roojo02W7:1433;databasename=pubs'    

       SQL 'INSERT INTO stores VALUES(''8044'', ''newstore'', ''1313 +

    Mockingbird Ln.'', ''Cinnaminson'', ''NJ'', ''08077'')'          

       RUN DAILY                                                     

       OUTPUT_FILE '%APPLoutputdir\DBInsert_output.txt'              

       RELEASE (SQL_Select_2)                                        

    ENDJOB                                                           

                                                                     

    SQL_JOB Cleanup                                              

       AGENT JPRR113_DB                                          

       USER cawa                                                 

       DB_URL 'jdbc:sqlserver://roojo02W7:1433;databasename=pubs'

       SQL 'DELETE From stores Where stor_id=''8044'''           

       RUN DAILY                                                 

       OUTPUT_FILE '%APPLoutputdir\DBDelete_output.txt'          

       EXPEDITE HOTWIN                                           

       RELEASE (SQL_Select_3)                                    

       RELDELAY  1                                               

    ENDJOB