SQLを使用してレポートを置き換える
この頃、レポート プログラムを書いている人はいらっしゃるでしょうか。もしそうならば、なぜでしょうか。レポートは、20世紀の遺物となっています。顧客から求められるのは、スプレッドシート、Webページ、携帯電話アプリ、およびその他の最新ツールです。必要なのは、レポート プログラムに大きく手を加える必要なく、同じ情報を最新インターフェースに書き込ませる簡単な方法です。
10年ほど前、私は、スプール ファイルをストリーム ファイルにCSV形式で再フォーマットできるようにするスクリプト言語を作成しました。私はそのユーティリティーを BASSと呼んでいます。これは成功したプロジェクトと言えるでしょうし、今もそうであり続けています。
しかし、スプール ファイルを、区切り文字で区切られたデータに変換する方法はBASSだけではありません。その後、IBMのデータベースの達人たちが、スプール ファイルのデータを読み取るのに使用できる別のツールを作成しています。それは、 SPOOLED_FILE_DATAという表関数であり、SYSTOOLSライブラリーにあります。
SPOOLED_FILE_DATAは、スプール ファイルを読み取って、レポートの1行ごとに1行を生成します。2つの列、すなわち、ORDINAL_POSITION（通し番号）とSPOOLED_DATA（レポートからのデータ）があります。
このツールを、インポート・ファイルへのコピー（CPYTOIMPF）コマンドのような、IFSへの書き込みを行うツールと組み合わせて、ストリーム ファイルを生成することができます。それでは、どのように動作するか見てみましょう。
以下は、CUSTRPTという単純なRPGプログラムを使用して、QIWS/QCUSTCDTファイルを基に生成したカラム レポートです。
Customer Report Page 1
Name ------- Customer City ---------- Balance Credit
Henning G K 938472 Dallas TX 75217 37.00 .00
Jones B D 839283 Clay NY 13041 100.00 .00
Vine S 392859 Broton VT 05046 439.00 .00
Johnson J A 938485 Helen GA 30545 3,987.50 33.50
Tyron W E 397267 Hector NY 14841 .00 .00
Stevens K L 389572 Denver CO 80226 58.75 1.50
Alison J S 846283 Isle MN 56342 10.00 .00
Doe J W 475938 Sutter CA 95685 250.00 100.00
Thomas A N 693829 Casper WY 82609 .00 .00
Williams E D 593029 Dallas TX 75218 25.00 .00
Lee F L 192837 Hector NY 14841 489.50 .50
Abraham M T 583990 Isle MN 56342 500.00 .00
**End of report**customer name（顧客名）、customer account number（顧客アカウント番号）、balance due（未払い残高）の3つのフィールドがあるCSVファイルを作成しましょう。1行目に列見出しを置きます。それらの列に、「Name」（顧客名）、「Account」（アカウント）、「Balance」（未払い残高）という名前を付けます。
まず、スプール ファイルからデータを読み取るためのSQLが必要です。
select * from (values (0, 'Name', 'Account', 'Balance'))
as h (ordinal_position,Name, Account, Balance)
union all
select d.ordinal_position,
substr(d.spooled_data, 1, 12) as Name,
substr(d.spooled_data, 15, 6) as Account,
substr(d.spooled_data, 40, 8) as Balance
from table(
systools.spooled_file_data(job_name => '*',
spooled_file_name => 'QSYSPRT')
) as d
where substr(d.spooled_data, 20, 1) >= '0')
select Name, Account, Balance
from temp
order by ordinal_positionこれはどのように動作するのでしょうか。2つのSELECT節があります。1つ目は列見出しを生成し、2つ目はレポート データを取得します。UNION ALLは、それら2つを結合します。
もちろん、レポートからのすべての行が必要なのではなく、詳細データを持つ行のみが必要です。それらの行では、顧客アカウント番号の最後の桁（行の20桁目）には数値があり、一方、レポート他の行では、その桁には別のデータがあることに注目してください。
2つ目のSELECT節では、サブストリング関数を使用して、関心のあるフィールドを取得します。さらに別の関数（VARCHAR_FORMAT、TO_NUMBERなど）を使用して、出力データを文字以外のタイプに変換することもできますが、そこまでの複雑さは、この記事の目的には必要ありません。
このSQL照会は、関心のある列を取得しますが、データをCSV形式でストリーム ファイルに格納するわけではありません。実際、この照会はデータをどこにも格納しません。データを物理ファイルQTEMP/SFDに格納しましょう。
declare global temporary table SFD as
(with temp as
(select * from (values (0, 'Name', 'Account', 'Balance'))
as h (ordinal_position,Name, Account, Balance)
union all
select d.ordinal_position,
substr(d.spooled_data, 1, 12) as Name,
substr(d.spooled_data, 15, 6) as Account,
substr(d.spooled_data, 40, 8) as Balance
from table(
systools.spooled_file_data(job_name => '*',
spooled_file_name => 'QSYSPRT')
) as d
where substr(d.spooled_data, 20, 1) >= '0')
select Name, Account, Balance
from temp
order by ordinal_position
)
with data
with replace;このSQLステートメントは、SQLSRCというソース物理ファイルのメンバーCUSTRPTに格納しました。
以下は、すべてを組み合わせるジョブ ストリームです。
call CustRpt
RunSqlStm SrcFile(SQLSRC) SrcMbr(CustRpt) Commit(*NONE) +
option(*ERRLIST)
CpyToImpF FromFile(QTEMP/SFD) +
ToStmf('Customer-list.csv') +
MbrOpt(*REPLACE) +
StmFCCSID(*PCASCII) RcdDlm(*CRLF) +
RmvBlank(*TRAILING)
DltSplF file(QSYSPRT) job(*) SplNbr(*LAST)以下がそのプロセスです。
- CALLは、スプール ファイルを生成します。
- RUNSQLSTMは、スプール ファイルを読み取り、データを物理ファイルQTEMP/SFDに格納します。
- CPYTOIMPFは、QTEMP/SFDをIFSへコピーします。
非常に単純です。
それでは、もう少し掘り下げてみましょう。ご承知の通り、SQLは、直接またはビューを通じて、リレーショナル表からデータを読み出すのみです。そして、スプール ファイルはリレーショナルではありません。では、どのようにすれば、SQLはスプール ファイルを読み取ることができるのでしょうか。この疑問に対する答えを見つけるために、Access Client Solutions（ACS）の「SQLスクリプトの実行」機能を使用します。以下の手順では、表関数SPOOLED_FILE_DATAのソース コードが取得されます。
- 「Edit（編集(E)）」メニューを開きます。
- 「Insert Generated SQL ...（生成したSQLの挿入...）」を選択します。
- 「Add ... 」ボタンをクリックします。
- 「Schemas ... 」ボタンをクリックします。
- 「Enter schemas names:」ボックスに「SYSTOOLS」と入力します。
- 「Add -> 」ボタンをクリックします。
- 「OK」ボタンをクリックします。
- 「SYSTOOLS」を展開します。
- 「Functions」を展開します。
- 「SPOOLED_FILE_DATA」をチェックして、「OK」ボタンをクリックします。
- 「Generate」ボタンをクリックします。
以下は、生成されるSQL/PLソース コードです。
-- Generate SQL
-- Version: V7R4M0 190621
-- Generated on: 06/13/21 08:00:00
-- Relational Database: S10604YM
-- Standards Option: Db2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM" ;
CREATE FUNCTION SYSTOOLS/SPOOLED_FILE_DATA (
JOB_NAME VARCHAR(28) ,
SPOOLED_FILE_NAME VARCHAR(10) DEFAULT 'QPJOBLOG' ,
SPOOLED_FILE_NUMBER VARCHAR(6) DEFAULT '*LAST' )
RETURNS TABLE (
ORDINAL_POSITION INTEGER ,
SPOOLED_DATA VARCHAR(200) FOR SBCS DATA )
LANGUAGE SQL
SPECIFIC SYSTOOLS/SPOOL_FILE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = QSYS2 ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
MONITOR = *SYSTEM ,
SRTSEQ = *HEX
BEGIN
DECLARE ERROR_V BIGINT DEFAULT 0 ;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_V = 1 ;
CALL QSYS2 / QCMDEXC (
'QSYS/CRTPF FILE(QTEMP/QIBM_SFD) RCDLEN(200) ' CONCAT
' MBR(*NONE) MAXMBRS(*NOMAX) SIZE(*NOMAX)' ) ;
END ;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_V = 2 ;
CALL QSYS2 / QCMDEXC ( 'QSYS/CPYSPLF FILE(' CONCAT SPOOLED_FILE_NAME CONCAT
') TOFILE(QTEMP/QIBM_SFD) JOB(' CONCAT JOB_NAME CONCAT
') MBROPT(*REPLACE) SPLNBR(' CONCAT SPOOLED_FILE_NUMBER CONCAT ') OPNSPLF(*YES)' ) ;
END ;
IF ERROR_V > 1 THEN
SIGNAL SQLSTATE '42704'
SET MESSAGE_TEXT = 'FAILURE ON CPYSPLF' ;
END IF ;
RETURN SELECT RRN ( JL ) , JL . * FROM QTEMP / QIBM_SFD JL ORDER BY RRN ( JL ) ASC ;
END ;
COMMENT ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE
IS 'DB2 FOR IBM i SUPPLIED OBJECT VERSION 07400010002' ;
GRANT EXECUTE
ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE
TO PUBLIC ;
GRANT ALTER , EXECUTE
ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE
TO QSYS WITH GRANT OPTION ;2つのQCMDEXCの呼び出しに注目してください。この素晴らしい表関数は、スプール・ファイル・コピー（CPYSPLF）コマンドのラッパーであることが分かります。言い換えれば、最初からずっと、SQLを使用してスプール ファイルを読み取ることができていたのです。そして、 そのことを知らなかったということです 。もっと前に行うことができたはずだったのです。
CrtPF qtemp/splfdata rcdlen(200)
MonMsg cpf7302 /* already exists */
CpySplF file(QSYSPRT) ToFile(QTEMP/splfdata) job(*) +
SplNbr(*LAST) MbrOpt(*REPLACE)
RunSqlStm SrcFile(SQLSRC) SrcMbr(CustRpt) Commit(*NONE) +
option(*ERRLIST)
CpyToImpF FromFile(QTEMP/SFD) +
ToStmf('Customer-list.csv') +
MbrOpt(*REPLACE) +
StmFCCSID(*PCASCII) RcdDlm(*CRLF) +
RmvBlank(*TRAILING)
DltSplF file(QSYSPRT) job(*) SplNbr(*LAST)SQLは少し微調整する必要があります。SPOOLED_FILE_DATA表関数を経由する代わりに、QTEMP内のファイルを読み取るからです。
declare global temporary table SFD as
(with temp as
(select * from (values (0, 'Name', 'Account', 'Balance'))
as h (ordinal_position,Name, Account, Balance)
union all
select rrn(d) as ordinal_position,
substr(d.splfdata, 1, 12) as Name,
substr(d.splfdata, 15, 6) as Account,
substr(d.splfdata, 40, 8) as Balance
from qtemp.splfdata as d
where substr(d.splfdata, 20, 1) >= '0')
select Name, Account, Balance
from temp
order by ordinal_position
)
with data
with replace;しかし、結果は同じです。
"Name","Account","Balance"
"Henning G K","938472"," 37.0"
"Jones B D","839283"," 100.0"
"Vine S S","392859"," 439.0"
"Johnson J A","938485"," 3,987.5"
"Tyron W E","397267"," .0"
"Stevens K L","389572"," 58.7"
"Alison J S","846283"," 10.0"
"Doe J W","475938"," 250.0"
"Thomas A N","693829"," .0"
"Williams E D","593029"," 25.0"
"Lee F L","192837"," 489.5"
"Abraham M T","583990"," 500.0"IBMが行ってくれた、ありがたいことは次の3つです。
- スプール ファイルを読み取るのに使用できるツールを作成してくれた。
- ジョブ ストリームを表形式データに変換する方法を教えてくれた。
- レポートをより最新式のインターフェースに変換しないことへの言い訳をできなくしてくれた。
それらのすべてを変換する時間はないかもしれませんが、最も重要なものを変換することはできます。
