Skip to content

Instantly share code, notes, and snippets.

@forstie
Created November 4, 2020 18:28
Show Gist options
  • Save forstie/3805d74a06d3349a00deef704e40b219 to your computer and use it in GitHub Desktop.
Save forstie/3805d74a06d3349a00deef704e40b219 to your computer and use it in GitHub Desktop.
IBM i Access Client Solutions (ACS) includes many nifty features, like being able to save a spooled file as a PDF. This Gist shows how SQL can be used to progammatically take one or more spooled files and generate PDFs for them into the Integrated File System (IFS).
--
-- title: Generate PDF(s) from Spooled Files
-- This scalar function allows the SQL programmer to identify Spooled File(s) and generate pdfs for them in the IFS.
--
-- minvrm: V7R2M0
--
CREATE or replace FUNCTION SYSTOOLS.Generate_PDF(
job_name varchar(28) for sbcs data,
file_name varchar(10) for sbcs data,
file_number integer,
PATH_NAME DBCLOB(5000) CCSID 1200)
returns integer
SPECIFIC SYSTOOLS.GENPDF
MODIFIES SQL DATA
not deterministic
no external action
SET OPTION
COMMIT = *NONE ,
DYNUSRPRF = *USER ,
USRPRF = *USER
BEGIN
declare local_sqlcode integer;
declare local_sqlstate char(5) for sbcs data;
declare v_message_text varchar(2000) for sbcs data;
declare v_failure_text varchar(2000) for sbcs data;
declare software_product_info_exists integer;
declare Transform_Services_exists integer;
-- Count must be 1 to be able to check on software pre-req
select count(*) into software_product_info_exists
from qsys2.services_info
where service_name = 'SOFTWARE_PRODUCT_INFO'
and service_schema_name = 'QSYS2';
if (software_product_info_exists = 0) then
call systools.lprintf('Generate_PDF: Cannot check whether this software is installed: 5770TS1 - Option 1 - Transform Services - AFP to PDF Transform');
else
-- Count must be 1 to be able to generate pdfs into the IFS
select count(*) into Transform_Services_exists
from qsys2.software_product_info
where product_id = '5770TS1' and product_option = '1' and installed = 'YES' and load_error = 'NO';
if (Transform_Services_exists = 0) then
set v_failure_text = 'Generate_PDF: Missing prerequisite software: 5770TS1 - Option 1 - Transform Services - AFP to PDF Transform';
call systools.lprintf(v_failure_text);
signal sqlstate 'VSP01'
set message_text = v_failure_text;
return 0;
end if;
end if;
begin
declare v_cmdtxt varchar(10000) for sbcs data;
declare exit handler for sqlexception
begin
get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode,
local_sqlstate = returned_sqlstate,
v_message_text = message_text;
set v_failure_text = 'Generate_PDF: FAILED WITH SQLCODE=' concat
local_sqlcode concat ' SQLSTATE=' concat local_sqlstate concat
' MESSAGE= ' concat v_message_text concat ' for command: ' concat v_cmdtxt;
call systools.lprintf(v_failure_text);
signal sqlstate local_sqlstate set message_text = v_failure_text;
return 0;
end;
set v_cmdtxt = 'QSYS/CPYSPLF FILE(' concat file_name concat ') TOFILE(*TOSTMF) JOB(' concat job_name concat ') SPLNBR(' concat file_number concat ') TOSTMF('''
concat path_name concat ''') WSCST(*PDF) STMFOPT(*REPLACE)';
call qsys2.qcmdexc(v_cmdtxt);
return 1;
END;
END;
stop;
--
-- Take a specific spooled file and generate a pdf into the IFS
--
select * from qsys2.output_queue_entries_basic
where status = 'READY'
order by create_timestamp desc limit 4;
values SYSTOOLS.Generate_PDF(
job_name => '885864/JAVA/QPRTJOB',
file_name => 'QPJOBLOG',
file_number => 6199,
path_name => '/home/scottf/firstPDF');
select *
from table (
qsys2.ifs_object_statistics(start_path_name => '/home/scottf/firstPDF')
);
stop;
--
-- Discover spooled files and generate pdfs for them into the IFS
--
select job_name, spooled_file_name, file_number,
SYSTOOLS.Generate_PDF(
job_name => job_name,
file_name => spooled_file_name,
file_number => file_number,
path_name => '/home/scottf/' concat regexp_replace(job_name, '/', '_')
concat '_' concat spooled_file_name concat '_' concat file_number)
as "pdf_created?",
'/home/scottf/' concat regexp_replace(job_name, '/', '_')
concat '_' concat spooled_file_name concat '_' concat file_number
as pdf_path
from qsys2.output_queue_entries_basic where status = 'READY'
order by create_timestamp desc
limit 4;
select *
from table (
qsys2.ifs_object_statistics(start_path_name => '/home/scottf/')
);
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment