Hi Guys,
We have a requirement wherein when the value in the column of a particular DB2 table changes to a certain value, a job must be triggered which will execute a program and generate a report. This is in a Z/OS environment and we use DB2 for Z/OS version 8.
I had thought of the following solution for this –
Ø Create an ‘after update’ DB2 trigger which would call a DB2 stored procedure (could be written in REXX or COBOL) when the column changes to the designated value.
Ø To ensure that the stored procedure can submit a batch job, a "//filename DD SYSOUT=(,INTRDR)" - statement must be provided within the WLM startup procedure JCL.
Ø The stored procedure must then write the jobstream (which contains the execution of the desired program to create the reports for the user) to the file named ‘filename’ which is associated with the JES internal reader (INTRDR) within the DB2 stored procedure address space where it will be executed then.
Now the issue is that this is easily done if the stored procedure is written in REXX but REXX stored procedures are not allowed by the client. To write the stored procedure in COBOL, one has to manually move the contents of the JCL to be executed to the INTRDR file (and this is not ideal both from a maintenance standpoint as well as an aesthetic one). In any case, the REXX procedure (even though it is much shorter) has the same constraint as above (explicitly writing the actual contents of the JCL to INTRDR). As far as I’m aware, DB2 stored procedures don’t allow file handling in the normal sense as in COBOL programs (there are SQL user defined functions which can do that but I don’t think those are applicable here)
My questions are –
Ø Is there a way wherein we can read a file containing the JCL to be executed within a stored procedure written in COBOL and then write to the output INTRDR file (I think not)? If required, we can add the file in the WLM startup procedure so that the procedure can reference it. As mentioned earlier, I don’t think this sort of thing is allowed in stored procedures though.
Ø Is there any alternative way to handle this problem more optimally?
Thanks in advance for your help.