Best way update SQL DB from ESP ?

[Legacy User]

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

[Jonathan Calloway]

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.

[Legacy User]

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.

[Lucy Zhang]

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

[Legacy User]

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 !

[Legacy User]

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