Think of this blog entry as an example of converting an existing process into an RTC language definition and translator. For some reason, after a recent work trip I had made a note to test whether I could build a translator that would run SQL. Not sure why I had that note on a scrap of paper, but there must have been a reason for it. With the usual "challenges" that DB2 throws up with SCMs, especially when it comes to Binding, I was not anticipating a simple task. Couple with that that most DB2 requests run through IKJEFT01 it meant that we would have to factor in the ISPF Gateway as well to invoke this translator.
In the end, it turned out not to be too challenging, but a couple of peculiar tweaks needed to be adhered to.
Running SQL means calling DSNTEP2 and giving it the SQL in a SYSIN DD allocation. So the easy part of the task was creating an SQL data set definition that would hold my various SQL members.
The translator and language definition seemed pretty straight forward as well. Calling some REXX that invoked DSNTEP2 and allocating SYSIN to the source being built.
The question was how to get DSNTEP2 working as a REXX exec. Normally DSNTEP2 is invoked as a TSO command through the SYSTSIN. However because of the way RTC invokes TSO/ISPF commands, through the ISPF Gateway, as well as how SYSPRINT is allocated, we need to provide a REXX that does the DSNTEP2 invocation. The DSNTEP2 REXX that we run from the TSO command or exec looks like this:
/* REXX */
"alloc F(SYSPRINT) tracks space(5,5) unit(VIO) new"
queue "RUN PROGRAM(DSNTEP2) PLAN(DSNTEP91) LIB('DB9WK1.RUNLIB.LOAD')"
"DSN SYSTEM (DSN9)"
db2_rc = rc
"EXECIO * DISKR SYSPRINT (STEM db2out. FINIS "
do i = 1 to db2out.0;say db2out.i;end
We need to read the allocated SYSPRINT DD and using the REXX "say" statement the contents will be written to the log produced by the ISPF gateway.
The contents of our SQL member is just normal SQL such as:
SELECT * FROM CCM4001X_JAZZ.TABLE_MAP;
We need to associate the member with the "Run SQL" language definition, then ensure that that language definition is picked up by our build definition. When the build runs, the SQL is executed.