On 12/26/2024 3:53 PM, Rob Berendt wrote:
Here's the scenario. You're checking a few things in your effort to switch
from one system to another. This involves close to restricted state but
not quite. ENDTCP and ENDSBS *ALL are out. Client/server or http based
tooling are also out. I thought about a CL based process but I have a lot
of SQL I want to use and I'm tired of jumping through hoops to interact
with SQL with CL. I may end up with RPG. I thought about using Stored
Procedures but prompting from that would be new to me (but I'm open to the
enlightenment). I'm concerned that prompting from Stored procedures would
be as much jumping through hoops as using SQL from CL.
I'm not sure what 'prompting' means, so I apologise if this only adds to
the stress. Many years ago, when we had to pay the princely sum of $250
for STRSQL, we didn't. In retaliation, I cobbled together a utility that
allowed me to do something like
rs 'select * from custmast where zip=60609'
I used it a lot, and still do. The good news is that I often made
scripts out of it; so maybe a half dozen of these one after the other so
I don't have to type them over and over during testing. Or, in your
case, between machine A and machine B.
I put the code (such as it is) in the wiki at
https://wiki.midrange.com/index.php/SQL
Maybe it'll help, but it might be easier to use ReXX for this particular
purpose. ReXX is on every IBM i, and you can put the ReXX 'program' in a
source member that you can edit with SEU if need be.
/* Use the DB2 service to find the spooled files to be deleted */
ADDRESS EXECSQL
EXECSQL "SET OPTION COMMIT=*NONE"
/* Interesting columns: */
/* output_queue_name */
/* output_queue_library_name */
/* spooled_file_name */
/* user_name */
/* user_data */
/* create_timestamp */
stmt = "select spooled_file_name, job_name, file_number",
"from qsys2.output_queue_entries",
"where output_queue_name = 'QPRINT'",
" and output_queue_library_name = 'QGPL'",
" and spooled_file_name = 'QPDSPJNA'",
" and user_name = 'QSECOFR'",
" and date(create_timestamp) <= '2017-01-01'"
execsql "prepare S1 from :stmt"
execsql "declare C1 cursor for S1"
EXECSQL "open c1"
/* loop through result set */
records = 0
do while 1 = 1
EXECSQL "fetch from c1 into :splf_name, :job_name, :splnbr"
if SQLSTATE = '02000' then leave
if SQLSTATE <> '00000' then do
say "Abnormal SQLSTATE =" SQLSTATE
leave
end
records = records + 1
cmd =,
"dltsplf file(" || strip(splf_name) || ")",
"job(" || strip(job_name) || ")",
"splnbr(" || strip(splnbr) || ")";
say cmd;
address command cmd;
end /* repeat loop */
EXECSQL "close c1"
say records "records processed."
EXIT
midrange.com
