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 !
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.
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.
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:
STEPNAME ‘Step 2’
JOBNAME 'Test Job 1'
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 !
Here are some other examples that might help out while you are investigating the agent:
SQL 'BACKUP DATABASE dSeries TO dSeries113_backup_device'
SQL 'Select * from stores;'
SQL 'INSERT INTO stores VALUES(''8044'', ''newstore'', ''1313 +
Mockingbird Ln.'', ''Cinnaminson'', ''NJ'', ''08077'')'
SQL 'DELETE From stores Where stor_id=''8044'''