update SQL database on non-mf server using DB or DS on MF

[Chris Allamon]

I am new to ESP, I see in the APPLIB that we use there are instances where SQL_JOB will insert a single record in a non-MF database.   Is there a way to read a flat file or VSAM and insert thousands of records in a non-MF db?  We do have OPS/MVS and see that we can use OPSQL, but this may be 30-40k records per run and would stress the RDF DB.


------------------------------
Chris Allamon
Navy Federal CU
------------------------------

[Lucy Zhang]

Hi Chris,

If the non-MF db is MS SQL server, you may consider MSSQL_JOB instead.

And you can use ESP REXX and TEMPLATE, to generate one SQL_JOB for every entry in the flat file. See example below:

https://techdocs.broadcom.com/us/en/ca-mainframe-software/automation/ca-workload-automation-esp-edition/12-0/examples-cookbook/schedule-job-multiple-times-within-time-range.html

If more help is needed, you may open a support case.

Hope this helps,

Lucy

[Lucy Zhang]

Hi Chris,

If the non-MF db is Microsoft SQL server, then you may consider to use MSSQL_JOB instead. It can run the JOB defined on MS SQL server.

For SQL_JOB, it can only execute one SQL statement. One possible solution is that you can set up ESP REXX code with TEMPLATE, which will set up one SQL_JOB for every entry in the flat file. Following is for explanation purpose, you may refer to related docs or open a support case if more help is needed:

TEMPLATE INSERT (1 JOBNAME SQL())

SQL_JOB %JOBNAME

AGENT AGENTSQL
SQL %SQL
RUN DAILY
ENDJOB

ENDTEMPL

REXXON 

<read the flat file>

"ALLOCX DSN('flatfilename') SH"
ADDRESS "MVS" "EXECIO * DISKR" DDNAME "(STEM LINE. FINIS"
"FREEX FILE(" DDNAME ")"
DO I = 1 TO LINE.0

JOBN='JOB'||I

"INSERT " JOBN "SQL("LINE.I")"

END

REXXOFF

----------------

See example below for more detail:

https://techdocs.broadcom.com/us/en/ca-mainframe-software/automation/ca-workload-automation-esp-edition/12-0/examples-cookbook/schedule-job-multiple-times-within-time-range.html

Hope this helps,

Lucy

[Chris Allamon]

Thank you Lucy, I will play around with this and see if I can figure it out.

------------------------------
Chris Allamon
Navy Federal CU
------------------------------