Guru: SQL Writes CL
November 30, 2020 Ted Holt
SQL is the one tool I cannot work without. Take it away from me and I’ll start driving a truck for a living. Naturally I’m eager to find more ways to make SQL work for me. Today I’d like to share how I recently used SQL to write a huge CL command for me. This is a technique that’s good to know.
My challenge was to copy all of the several hundred physical data files in a library to a save file so that those files could be loaded onto another IBM i system. The Save Library (SAVLIB) command was out of the question. It would have saved non-file objects, logical files, and device files. That would have greatly increased the size of the save file. The Save Object (SAVOBJ) command was exactly what I needed, as it allows up to 300 objects to be saved by one command. However, keying hundreds of physical file names would have been unwieldy, unreliable, and unreasonable, not to mention “unfun”. Fortunately, I was easily able to make SQL do the work for me. Here’s how it’s done.
The magic begins and ends with two little words: begin and end. In between are an SQL query and a loop to process the result set. Here it is:
begin
declare v_Cmd varchar(2048);
declare v_Schema varchar( 10) default 'MYLIB';
call qcmdexc('clrsavf mylib/mysavf');
set v_Cmd = 'SAVOBJ OBJ(';
for One_file as
select t.Table_Name
from systables as t
where table_schema = 'MYLIB'
and table_type = 'P'
and file_type = 'D'
and substr(table_name, 1, 3) <> 'SUM'
order by table_name
do
set v_Cmd = v_Cmd concat ' ' concat One_file.Table_Name;
end for;
set v_Cmd = v_Cmd concat ') LIB(' concat v_Schema concat
') DEV(*SAVF) OBJTYPE(*FILE) SAVF(mylib/mysavf) TGTRLS(V7R3M0) SAVACT(*LIB)';
call systools.lprintf('v_Cmd=/' concat v_Cmd concat '/');
call qcmdexc (v_Cmd);
end
This is what’s known as dynamic compound statement. Mike Sansoterra introduced us to this powerful tool in this august publication a few years ago. I ran my dynamic compound statement inside the Run SQL Scripts tool within Access Client Solutions (ACS).
Think of a dynamic compound statement as a program with no name. Like any program, it can have variables:
declare v_Cmd varchar(2048); declare v_Schema varchar( 10) default 'MYLIB';
I follow the advice of more experienced practitioners of SQL PL and start variable names with v_. That is not a requirement.
I have a FOR loop.
for One_file as
select t.Table_Name
from systables as t
where table_schema = 'MYLIB'
and table_type = 'P'
and file_type = 'D'
and substr(table_name, 1, 3) <> 'SUM'
order by table_name
do
set v_Cmd = v_Cmd concat ' ' concat One_file.Table_Name;
end for;
The FOR loop is an abbreviated form of a cursor. The SET command executes for each row of the result set and builds the list of files to be saved to the save file.
I called QCMDEXC twice — the first time to clear the save file and the second time to execute the SAVOBJ command.
call qcmdexc('clrsavf mylib/mysavf');
set v_Cmd = 'SAVOBJ OBJ(';
set v_Cmd = v_Cmd concat ' ' concat One_file.Table_Name;
set v_Cmd = v_Cmd concat ') LIB(' concat v_Schema concat
') DEV(*SAVF) OBJTYPE(*FILE) SAVF(mylib/mysavf) TGTRLS(V7R3M0) SAVACT(*LIB)';
call qcmdexc (v_Cmd);
I also included a call to LPRINTF to write the value of v_Cmd to the job log. Since there is no object to debug, this is one way to debug a script.
call systools.lprintf('v_Cmd=/' concat v_Cmd concat '/');
ACS has an easy way to view the job log.
The command looked something like this:
SAVOBJ OBJ( ACCOUNT CUSTOMER DEPT . . . YTDSLS)
LIB(MYLIB)
DEV(*SAVF)
OBJTYPE(*FILE)
SAVF(mylib/mysavf)
TGTRLS(V7R3M0)
SAVACT(*LIB
And there you have it.
Think for a moment. Thanks to Scott Forstie and his team, we have all these wonderful IBM i Services and Db2 for i Services that give us easy ways to query what’s defined to the operating system. However, these wonderful services don’t let us change what’s defined to the operating system. Do you see where I’m going with this? You can use one of those wonderful services to access data, and with a dynamic compound statement, you can build the appropriate CL command to change that data. For a one-shot task, you can’t beat it.
If you decide that that one-shot task needs to run regularly, turning your dynamic compound statement into a permanent object is easy. Just add CREATE PROCEDURE, the appropriate parameter list, and other appropriate clauses before begin and you have a debugged stored procedure.
create or replace procedure mylib.myproc
(in p_Schema char(10), in p_Savf char(10), in p_SavLib char(10))
begin
declare v_Cmd varchar(2048);
call qcmdexc('clrsavf ' concat rtrim(p_SavLib) concat
'/' concat ltrim(p_Savf));
set v_Cmd = 'SAVOBJ OBJ(';
for One_file as
select t.Table_Name
from systables as t
where table_schema = p_Schema
and table_type = 'P'
and file_type = 'D'
and substr(table_name, 1, 3) <> 'SUM'
order by table_name
do
set v_Cmd = v_Cmd concat ' ' concat One_file.Table_Name;
end for;
set v_Cmd = v_Cmd concat ') LIB(' concat p_Schema concat
') DEV(*SAVF) OBJTYPE(*FILE) SAVF(' concat
rtrim(p_SavLib) concat
'/' concat ltrim(p_Savf) concat
') TGTRLS(V7R3M0) SAVACT(*LIB)';
call qcmdexc (v_Cmd);
end;
A dynamic compound statement made easy work of a challenging task for me. It appears I won’t be driving a truck any time soon.
Nice Ted! I will be using this!
Thanks. Interesting and worth knowing, but is there an advantage to this technique compared to traditional CL?
Ted:
Always a pleasure to read your articles. In response to “SQL Writes CL”, here is another way to get your list (there are always options):
select LISTAGG(t.Table_Name,’ ‘) WITHIN GROUP(ORDER BY t.Table_Name) AS filelist
from qsys2.systables as t
where table_schema = ‘MYLIB’
and table_type = ‘P’
and file_type = ‘D’
and substr(table_name, 1, 3) ‘SUM’
The variable “filelist” would replace the variable “One_file.Table_Name” in the “do” loop. In fact the “do” loop would no longer be needed as “filelist” would contain the entire list. But you knew that, right? 😉
And where did I learn about the LISTAGG function? From you, of course! (https://www.itjungle.com/2018/02/12/guru-combine-related-rows-using-sql/)
Keep up the great work!
Bruce
You could do this task with traditional CL, i-fan. You could run DSPOBJD into an outfile. But if you want to use an SQL interface, as I did here, and as you’d do to take advantage of all those nice IBM i services and Db2 for i services, this would be the way to do it.