zOs/SQL/MF7800
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table ;
delete from A540769.dsn_predicat_table;
select *
declare global temporary table session.Tkey_Ver_1 as
(select MF150001
from oa1p.tmf150a1
)
with no data
on commit delete rows
;
explain plan set queryno = 3 for
insert into oa1p.tmf150h1
(MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A)
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from oa1p.tmf150a1
where MF150001 in
(select MF150001 from session.Tkey_Ver_1)
;
explain plan set queryno = 7 for
delete from oa1p.tmf150a1 a
where MF150001 in
(select MF150001 from session.Tkey_Ver_1)
;
select * from plan_view1
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by collid, progName, explain_time,
queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
with ur
;
rollback
;;;;
/********************************************************************/
/* */
/* Letzte Source-Änderung: 21. May. 2012 09:12 F542850 */
/* */
/********************************************************************/
/* ----------------------------------------------------------------- * 00010002
! vvv ! 00020002
! Package.....: MFR_sfl_history ~~~~~ ! 00030002
! ( O O ) ! 00040002
+ -----------------------------------------------oooo--(_)--oooo--- + 00050002
! ! 00060002
! Autor.......: Corrado Garbagnati KGCG 21 ! 00070002
! Datum.......: 10/09/2010 ! 00080002
! .oooO ! 00090002
+ -------------------------------------------------( )-Oooo.----- + 00100002
! \ ( ( ) ! 00110002
! \_) ) / ! 00120002
! (_/ ! 00130002
+ ----------------------------------------------------------------- + 00140002
! ! 00160002
! Exports.....: MF7800 ! 00160002
! ! 00160002
+ ----------------------------------------------------------------- + 00140002
! Beschreibung: ! 00150002
! ! 00160002
! Das Programm historisiert Flüsse, welcher älter als ein Jahr ! 00160002
! sind. Dieser Prozess koptiert die Daten in TMF150H1 und löscht ! 00160002
! sie in TMF150A1. ! 00160002
! Um regelmässiges Absetzen von Commits zu ermöglichen, werden ! 00160002
! die Daten in Blöcken kopiert. Die Blockgrösse ist in ! 00160002
! Makro @blocksize (1000) definiert. Dieser Wert kann ! 00160002
! von "aussen" nicht verändert werden. ! 00160002
! ! 00160002
! Das Programm läuft wöchentich jeweils am Sonntag. ! 00160002
! ! 00160002
! Es stehen mehrere Transfer-Algorithmen zur Verfügung, die ! 00160002
! unterschiedliche Strategien verfolgen: ! 00160002
! ! 00160002
! Version 0 und 1: ! 00160002
! ! 00160002
! - Ermitteln der zu transferrierenden Flüsse und speichern ! 00160002
! deren Schlüssel in temporärer DB2-Tabelle ! 00160002
! ! 00160002
! - Kopieren dieser Flüsse in TMF150H1 anhand dieser Schlüssel ! 00160002
! ! 00160002
! - Löschen dieser Flüsse in TMF150A1 ! 00160002
! ! 00160002
! Vorteil: Daten verbeiben innerhalb von DB2 ! 00160002
! Nachteil: Daten können nicht mit Garantie in Clustering- ! 00160002
! Reihenfolge in TMF150H1 eingefügt werden ! 00160002
! (Fragmentierung). ! 00160002
! Datensuche erfolgt sowohl beim Kopieren als auch ! 00160002
! beim Löschen. ! 00160002
! ! 00160002
! Version 2: ! 00160002
! ! 00160002
! - Lesen der gelöschten Daten (SELECT FROM DELETE) mittels ! 00160002
! Cusrorverarbeitung. ! 00160002
! ! 00160002
! - Einfügen der Flüsse in TMF150H1 (FETCH and INSERT). ! 00160002
! ! 00160002
! Vorteil: Einfügen der Daten in TMF150H1 in Clustering- ! 00160002
! Reihenfolge. ! 00160002
! Nachteil: Daten verbleiben nicht innerhalb von DB2 (kopieren ! 00160002
! in den Adressraum des Programmes). ! 00160002
! ! 00160002
! Version 3 (Walters Vorschlag): ! 00160002
! ! 00160002
! - Ermitteln der zu transferrierenden Flüsse und speichern ! 00160002
! deren Schlüssel in temporärer DB2-Tabelle ! 00160002
! ! 00160002
! - Kopieren dieser Flüsse in TMF150H1 anhand dieser Schlüssel ! 00160002
! ! 00160002
! - Löschen dieser Flüsse in TMF150A1 ! 00160002
! ! 00160002
! Vorteil: Daten verbeiben innerhalb von DB2 ! 00160002
! Nachteil: Daten können nicht mit Garantie in Clustering- ! 00160002
! Reihenfolge in TMF150H1 eingefügt werden ! 00160002
! (Fragmentierung). ! 00160002
! Datensuche erfolgt sowohl beim Kopieren als auch ! 00160002
! beim Löschen. ! 00160002
! Anzahl der ermittelten Flüsse ist variable. ! 00160002
! ! 00160002
! Version 4: ! 00160002
! ! 00160002
! - Lesen der eingefügten Daten (SELECT FROM INSERT) mittels ! 00160002
! Cusrorverarbeitung (ROWSET FETCH). ! 00160002
! ! 00160002
! - Kopieren der UUID in temp. Tabelle (ROWSET INSERT) ! 00160002
! Cusrorverarbeitung (ROWSET FETCH). ! 00160002
! ! 00160002
! - Löschen in TMF150A1 anhand der temp. Tabelle. ! 00160002
! ! 00160002
! Nachteil: Daten verbleiben nicht innerhalb von DB2 (kopieren ! 00160002
! in den Adressraum des Programmes). ! 00160002
! Daten werden auch in temp. Tabelle kopiert ! 00160002
! (FINAL TABLE). ! 00160002
! ! 00160002
! Durch Performancetests soll der effektivere Algorithmus ! 00160002
! evaluiert werden. ! 00160002
! ! 00160002
! Berechnung des "älter als" Datums: ! 00160002
! ! 00160002
! - Aktuelles Datum - 365 Tage ! 00160002
! ! 00160002
! - Falls dieses Datum nicht auf einen Sonntag fällt, muss ! 00160002
! der vorangegangene Sonntag ermittelt werden. ! 00160002
! ! 00160002
! Programmparameter (aus Konfigurationsdatei): ! 00160002
! ! 00160002
! <?xml version="1.0" encoding="ebcdic-cp-ch" standalone="yes"?> ! 00160002
! <|--configuration of programm MF7800--> ! 00160002
! <configuration> ! 00160002
! <parameter-list> ! 00160002
! <parameter name="use-version"> 2</parameter>! 00160002
! <parameter name="print-after-#sfl"> 5000</parameter>! 00160002
! <parameter name="stop-after-#sfl"> 50000</parameter>! 00160002
! <parameter name="older-than-date"> 01.09.2009</parameter>! 00160002
! </parameter-list> ! 00160002
! </configuration> ! 00160002
! ! 00160002
! ! 00160002
! use-version: Verwende Version 2 (Default: Version 1) ! 00160002
! ! 00160002
! print-after-#sfl: Schreibe Meldung nach 5000 Flüssen ! 00160002
! (Default: 50000) ! 00160002
! ! 00160002
! stop-after-#sfl: Stoppe Verarbeitung nach 50000 Flüssen ! 00160002
! (Default: -1 - kein Stopp) ! 00160002
! ! 00160002
! older-than-date: Verwende dises "älter als" Datum ! 00160002
! (Default: berechnetes Dat.) ! 00160002
! ! 00160002
+ ----------------------------------------------------------------- + 00170002
! Bemerkung...: ! 00180002
! ! 00160002
! Die ersten Unittests wurden im DBAF mit dem Schema GDB0389 ! 00160002
! gemacht (siehe Makro @schema). Für den üblichen ET sollte ! 00160002
! kein Schema angegeben werden (Blank). Wird durch den Promote ! 00160002
! eingefügt. ! 00160002
! ! 00160002
+ ----------------------------------------------------------------- + 00170002
! Aenderungen.: ! 00180002
! ! 00190002
! 18.05.2012 Requirements: MFR-FoF_nfr_CS_Standard Ph.Franzos ! 00190002
! CR : 7079 PF0512 ! 00190002
! EPLI V4.2: SQL-Statements dürfen nur noch ! 00190002
! innerhalb einer Procedur deklariert werden. ! 00190002
! ! 00190002
! 10.09.2010 Intial !
! !
! CR 7021 (NEUVA) !
! Requirement MFR-FoF_nfr_Table_0001 !
! MFR-FoF_nfr_Table_0002 !
! Deployment: DC 4-2010 !
! !
* ----------------------------------------------------------------- */00200002
1/* ----------------------------------------------------------------- * 00930002
! Package ! 00940002
* ----------------------------------------------------------------- */00950002
MFR_sfl_history: package exports (MF7800);
%;
%/* Macros */;
%;
%/* Condition codes */;
%dcl @cc_ok char; %@cc_ok = '0';
%dcl @cc_err char; %@cc_err = '999';
%;
%/* SQL-States */;
%xinclude YMFWS00H;
%dcl @sqlDuplicate char; %@sqlDuplicate = '''23505''';
%;
%/* DB2-Schema GDB0389 (wird für Unittests verwendet: sonst Blank) */;
%dcl @schema char; %@schema = '';
%;
%/* setze Schema vor Tabellennamen */;
%@table: proc ($schema, $tableName) returns (char);
dcl $schema char;
dcl $tableName char;
/* Aufruf ohne Schema */
if length (trim ($schema)) < 1 then return (trim ($tableName));
return (trim ($schema) || '.' || trim ($tableName));
%end @table;
%activate @table;
%;
%/* Anzahl Historisierungen in einer Unit of Work */;
%dcl @blocksize char; %@blocksize = '500';
%dcl @blocksize_3 char; %@blocksize_3 = '10';
%;
%/* Format Buchungsdatum */;
%dcl @dateFmt char; %@dateFmt = '''DD.MM.YYYY''';
%;
%/* Default Datum */;
%dcl @dfltDate char; %@dfltDate = '''01.01.0001''';
%;
%/* sonstiges */;
%xinclude MFRMACH;
%;
1/* -Built-In's------------------------------------------------------ */01430002
dcl ( 01440002
addr 01590002
,datetime 01590002
,days 01590002
,daystodate 01590002
,edit 01590002
,float 01590002
,handle
,hbound
,hex
,huge
,lbound 01590002
,length 01590002
,lowercase
,mod 01590002
,omitted 01590002
,oncode 01590002
,onloc 01590002
,packagename 01590002
,plidelete 01590002
,plidump 01590002
,plifill
,present
,procedurename
,ptrvalue
,round
,sqrt
,sysnull
,uppercase
,validdate
,weekday
) builtin; 01600002
01010002
/* -External(s)----------------------------------------------------- */01430002
/* -Define(s)------------------------------------------------------- */01430002
define structure
1 rtSta_t /* Runtime Statistik */
,2 sumX float dec (16) /* Summe */
,2 sumX2 float dec (16) /* Summe der Quadrate */
,2 maxX float dec (16) /* Maximum */
,2 minX float dec (16) /* Minimum */
,2 nn fixed bin (31) /* Anzahl Messungen */
,2 text char (256) varz /* Freitext */
;
define alias fTransfer_t /* Transferfunktion A1 ---> H1 */
limited entry (file variable nonasgn
,handle sqlca_t nonasgn
,char (10) byaddr nonasgn
,char (10) byaddr nonasgn
,(*) handle rtSta_t byaddr nonasgn
)
returns (byvalue fixed bin (31))
options (byvalue)
variable;
01010002
/* -Constant(s)----------------------------------------------------- */01430002
/* YXRRSAF: verwendete Befehle */
dcl cConnect char value ('CONNECT');
dcl cDisconnect char value ('DISCONNECT');
dcl cCommit char value ('COMMIT');
dcl cRollback char value ('ROLLBACK');
/* Packagename */
dcl cPkg char value (packagename ());
/* Anzahl Transfers bevor Meldung */
dcl cMsgInterval char value (50000);
/* Anzahl Transfers bevor Statistik */
dcl cStaInterval char value (-1);
/* -Module(s)------------------------------------------------------- */01430002
%xinclude YMFSU0XH; /* support */
%xinclude YMFCF0XH; /* configuration */
%xinclude YMFMI0XH; /* misc.: timer, ...*/
%include YXRRSAF; /* recoverable attach facility */
/* -Based Variable(s)----------------------------------------------- */01430002
1/* ----------------------------------------------------------------- * 00930002
! MF7800 ! 00940002
* ----------------------------------------------------------------- */00950002
MF7800: proc ($args) options (MAIN); 00960002
dcl $args char (*) var nonasgn parm;
/* innerhalb PROC PF0512 */
1/* ----------------------------------------------------------------- * 01430002
! DB2 Tabellendeklarationen !
* ----------------------------------------------------------------- */
EXEC SQL
declare @table (@schema, tmf150a1) table
%include TMF150D; /* hat Strichpunkt im Copybook */
%;
EXEC SQL
declare @table (@schema, tmf150h1) table
%include TMF150D; /* hat Strichpunkt im Copybook */
%;
01010002
/* -External(s)----------------------------------------------------- */01430002
dcl SYSPRINT file print stream output ext;
/* -Constant(s)----------------------------------------------------- */01430002
dcl cPgm char value (procedurename ());
dcl ddConf char value ('file://dd:ddconf');
/* -Module(s)------------------------------------------------------- */01430002
/* -Variable(s) (automatic storage)--------------------------------- */01430002
dcl starttimePgm type tod_t init (@getTod ()) nonasgn;
dcl startDateTime type datetime_t init (datetime ()) nonasgn;
dcl starttime type tod_t init (@getTod ());
dcl doTransfer type fTransfer_t init (doTransferVersion_1);
dcl nMsgInterval fixed bin (31) init (cMsgInterval);
dcl nStaInterval fixed bin (31) init (cStaInterval);
dcl nTransfer fixed bin (31) init (0);
dcl nStop fixed bin (31) init (-1);
dcl useVersion fixed bin (31) init (1);
dcl nFlow fixed bin (31) init (0);
dcl nn fixed bin (31);
dcl fromDate char (10) init (@dfltDate);
dcl toDate char (10) init (calcToDate ());
dcl stopped bit (1) aligned;
dcl sqlca type sqlca_t;
dcl hSqlca handle sqlca_t init (handle (sqlca));
dcl 1 ausl, /* RETD und PARMD */
%include RETURND;,
2 $@rkeyl char (4),
2 $@rkeyf char (76),
%include PARMD;,
2 $@ssid char(4), /* DB2-Subsystem */
2 $@plan char(8) /* Plan */
;
dcl hRtStaTbl (10) handle rtSta_t init ((8) null ()
,newRtSta ('transfer total')
,newRtSta ('commit'));
/* -Error-Handling-------------------------------------------------- */
%include PGMANFA;
%;
1/* -Prologue-------------------------------------------------------- */01430002
call pliretc (@cc_err); /* init: error */
put edit
((@nRep) @repChar)
(skip, a)
('Package', cPkg, 'Compiletime', @compiletime)
(skip, a, x(5), a, col(45), a, x(5), a)
('Program', cPgm)
(skip, a, x(5), a)
;
call @suppPkg (SYSPRINT);
call @printTimeAndInfo (SYSPRINT, 'entering prologue');
put skip list ((@nRep) @repChar);
/* berechne Von-Datum
* (Von- bzw. Bis-Datum kann noch überschrieben werden
* in @getConfiguration - transfer-Funktion)
*/
fromDate = calcFromDate (toDate);
/* lese Parameter (PARM-Member)
*/
%include YYCNTRL; /* enthält auch READ-call */
put edit
((@nRep) @repChar)
(skip, a)
('SSID=', $@ssid, 'Plan=', $@plan)
(skip, a, a, x(5), a, a)
((@nRep) @repChar)
(skip, a)
;
/* lese Parameter (Konfiguration)
*/
call @getConfiguration (transfer, ddConf, *, SYSPRINT);
put skip list ((@nRep) @repChar);
/* überprüfe Von-Bis-Datum
*/
call checkHistPeriod (SYSPRINT, fromDate, toDate);
put edit
('transferring flows, between', fromDate, 'and', toDate)
(skip, a, x(1), a, x(1), a, x(1), a)
;
1 /* baue Verbindung zur DB auf (establish thread)
*/
call execRRSAFCmd (SYSPRINT, cConnect, $@ssid, $@plan);
select (useVersion);
when (0) do;
put skip list ('using verion 0 (with temporary table)');
put skip list ((@nRep) @repChar);
doTransfer = doTransferVersion_0; );
hRtStaTbl (1) = newRtSta
('fill UUID in temporary table');
hRtStaTbl (2) = newRtSta ('insert flows into TMF150H1');
hRtStaTbl (3) = newRtSta ('delete flows in TMF150A1');
/* erzeuge Global Temporay Table für UUID
*/
call @printTimeAndInfo (SYSPRINT
,"declare table 'Tkey_Ver_0'");
EXEC SQL
declare global temporary table session.Tkey_Ver_1 as
(select MF150001
from @table (@schema, tmf150a1)
)
with no data
on commit delete rows
;
if ^@chkSQL (@sqlOk) then
call exitPgm (SYSPRINT,"declare table 'Tkey_Ver_0' failed"
,hSqlCa);
end;
when (1) do;
put skip list ('using verion 1 (with temporary table)');
put skip list ((@nRep) @repChar);
doTransfer = doTransferVersion_1; );
hRtStaTbl (1) = newRtSta
('fill UUID, CIF, bookingdate and OU into temporary table');
hRtStaTbl (2) = newRtSta ('insert flows into TMF150H1');
hRtStaTbl (3) = newRtSta ('delete flows in TMF150A1');
/* erzeuge Global Temporay Table für UUID, Buchungsdatum und OE
*/
call @printTimeAndInfo (SYSPRINT
,"declare table 'Tkey_Ver_1'");
EXEC SQL
declare global temporary table session.Tkey_Ver_1 as
(select MF150001
,MF150023
,MF150013
,MF150067
from @table (@schema, tmf150a1)
)
with no data
on commit delete rows
;
if ^@chkSQL (@sqlOk) then
call exitPgm (SYSPRINT,"declare table 'Tkey_Ver_1' failed"
,hSqlCa);
end;
1 when (2) do;
put skip list ('using verion 2 (select from delete - insert)');
put skip list (' ---> data shuffling');
put skip list ((@nRep) @repChar);
doTransfer = doTransferVersion_2; );
hRtStaTbl (1) =
newRtSta ('open cursor - delete flows in TMF150A1');
hRtStaTbl (2) =
newRtSta ('fetch and insert flows into TMF150H1');
hRtStaTbl (3) = newRtSta ('close cursor');
end;
when (3) do;
put skip list ("using verion 3 (walther's solution)");
put skip list ((@nRep) @repChar);
doTransfer = doTransferVersion_3; );
hRtStaTbl (1) = newRtSta
('fill bookingdate and OU into temporary table');
hRtStaTbl (2) = newRtSta ('insert flows into TMF150H1');
hRtStaTbl (3) = newRtSta ('delete flows in TMF150A1');
/* erzeuge Global Temporay Table für Buchungsdatun und OE
*/
call @printTimeAndInfo (SYSPRINT, 'declare table Tkey_Ver_3');
EXEC SQL
declare global temporary table session.Tkey_Ver_3 as
(select MF150013
,MF150067
from @table (@schema, tmf150a1)
)
with no data
on commit delete rows
;
if ^@chkSQL (@sqlOk) then
call exitPgm (SYSPRINT,"declare table 'Tkey_Ver_3' failed"
,hSqlCa);
end;
1 when (4) do;
put skip list ('using verion 4 (select from insert - delete)');
put skip list ((@nRep) @repChar);
doTransfer = doTransferVersion_4; );
hRtStaTbl (1) = newRtSta
('open cursor - insert flows in TMF150H1');
hRtStaTbl (2) = newRtSta
('fetch and delete flows in TMF150A1');
hRtStaTbl (3) = newRtSta ('fill UUID into temporary table');
hRtStaTbl (4) = newRtSta ('delete SFL in TMF150A1');
hRtStaTbl (5) = newRtSta ('close cursor');
/* erzeuge Global Temporay Table für UUID
*/
call @printTimeAndInfo (SYSPRINT
,"declare table 'Tkey_Ver_4'");
EXEC SQL
declare global temporary table session.Tkey_Ver_4 as
(select MF150001
from @table (@schema, tmf150a1)
)
with no data
on commit delete rows
;
if ^@chkSQL (@sqlOk) then
call exitPgm (SYSPRINT,"declare table 'Tkey_Ver_4' failed"
,hSqlCa);
end;
other call exitPgm (SYSPRINT, 'not a valid version specified|');
end;
call @printElapsedTime (SYSPRINT, starttime
,'ending prologue');
1/* -MAINline-------------------------------------------------------- */01430002
call @printTimeAndInfo (SYSPRINT, 'entering MAINline');
put skip list ((@nRep) @repChar);
/* counting # of flows to be transferred
*/
nFlow = #ofSFL (SYSPRINT, hSqlca, fromDate, toDate);
put edit
('# of flows expected:', nFlow)
(skip, a, col(30), p'zzz,zzz,zzz,zz9')
;
if nStop > 0 then
put edit
('ATTENTION: stopping after # of flows transferred:'
, nStop)
(skip, a, x(3), p'zzz,zzz,zzz,zz9')
;
if useVersion ^= 3 then
put edit
('transferring flows in blocks of '
,trim (edit (@blocksize, 'zzzzz9')))
(skip, a, x(1), a)
;
put skip list ((@nRep) @repChar);
/* transferring single flows: TMF150A1 ---> TMF150H1
*/
startTime = @getTod ();
nn = doTransfer (SYSPRINT, hSqlca, fromDate, toDate, hRtStaTbl);
call add2RtStat (hRtStaTbl (9), starttime);
do while (nn > 0)
until (stopped);
nTransfer += nn;
/* print message
*/
if mod (nTransfer, nMsgInterval) = 0 then
call @printTimeAndInfo (SYSPRINT
,'# of transfers --->'
|| edit (nTransfer, 'z,zzz,zzz,zz9'));
stopped = (nStop > 0 & nStop <= nTransfer);
if stopped then
put skip list ('ATTENTION: processing stopped|');
else do;
/* committing changes
*/
startTime = @getTod ();
call execRRSAFCmd (SYSPRINT, cCommit, *, *);
call add2RtStat (hRtStaTbl (10), starttime);
/* print statistics
*/
if nStaInterval > 0
& mod (nTransfer, nStaInterval) = 0 then
call printRtStaTbl (SYSPRINT, hRtStaTbl);
/* transferring single flows: TMF150A1 ---> TMF150H1
*/
startTime = @getTod ();
nn = doTransfer (SYSPRINT
,hSqlca, fromDate, toDate, hRtStaTbl);
call add2RtStat (hRtStaTbl (9), starttime);
end;
end;
put skip list ((@nRep) @repChar);
/* last commit
*/
startTime = @getTod ();
call execRRSAFCmd (SYSPRINT, cCommit, *, *);
call add2RtStat (hRtStaTbl (10), starttime);
1/* -Epilog---------------------------------------------------------- */01430002
call @printTimeAndInfo (SYSPRINT, 'entering epilogue');
put skip list ((@nRep) @repChar);
put edit
('Program - Statistics', (@nRep) @repChar)
(skip, a, skip, a)
(' Expected flow(s)', nFlow)
(skip, a, col(30), p'zzz,zzz,zzz,zz9')
(' Transfer(s)', nTransfer)
(skip, a, col(30), p'zzz,zzz,zzz,zz9')
(' Blocksize', @blocksize)
(skip, a, col(30), p'zzz,zzz,zzz,zz9')
;
/* drucke Runtime-Statistik aus
* und rufe Destruktoren auf: gebe Heap-Memory frei
*/
call printRtStaTbl (SYSPRINT, hRtStaTbl);
call deleteRtStaTbl (hRtStaTbl);
/* baue Verbindung zur DB ab (also commits changes)
*/
call execRRSAFCmd (SYSPRINT, cDisconnect, *, *);
/* Zeitmessung (elpased time)
*/
call @printStartEndTime (SYSPRINT, startDateTime);
call @printElapsedTime (SYSPRINT, starttimePgm, 'program');
close file (*);
/* Condition Code
*/
call pliretc (@cc_ok); /* ok */
1/* ----------------------------------------------------------------- *
! Prozeduren/Funktionen INTERN !
* ----------------------------------------------------------------- */
/* -transfer 'getConfiguration'------------------------------Level 2 */
transfer: proc ($type, $name, $value)
options (nodescriptor);
dcl $type type cf_t parm;
dcl $name type cf_name_t nonasgn parm;
dcl $value type cf_name_t nonasgn optional parm;
dcl nn fixed bin (31);
dcl dateTmp char (10);
if $type ^= cf_parameter then return; /* falscher Typ */
if omitted ($value) then return; /* kein Wert angegeben */
if length ($value) = 0 then return; /* kein Wert mitgegeben */
/* Parameter(s) (Name/Value Pair)
*/
select (lowercase ($name));
/* use version
*/
when ('use-version') do;
nn = @char2Int ($value);
if nn > 0 & nn < 5 then useVersion = nn;
end;
/* use this from date
*/
when ('from-date') do;
dateTmp = $value;
if validdate (dateTmp, @dateFmt) then p;
fromDate = dateTmp; p;
end;
/* use this to date
*/
when ('to-date') do;
dateTmp = $value;
if validdate (dateTmp, @dateFmt) then p;
toDate = dateTmp; p;
end;
/* print message to SYSPRINT after # of SFLs transfered
*/
when ('print-msg-after-#sfl') do;
nn = @char2Int ($value);
if nn > 0 then nMsgInterval = nn;
end;
/* print statistcs SYSPRINT after # of SFLs transfered
*/
when ('print-sta-after-#sfl') do;
nn = @char2Int ($value);
if nn > 0 then nStaInterval = nn;
end;
/* stop processing after # of SFLs transfered
*/
when ('stop-after-#sfl') do;
nn = @char2Int ($value);
if nn > 0 then nStop = nn;
end;
other;
end;
end transfer;
end MF7800; 07640002
1/* ----------------------------------------------------------------- * 01430002
! Prozeduren/Funktionen EXTERN !
* ----------------------------------------------------------------- */
/* -transfer SFL TMF150A1 --> TMF150H1-----------------------Level 1 */
doTransferVersion_0: proc ($ddPrint
,$hSqlCa, $fromDate, $toDate, $hRtStaTbl)
returns (byvalue fixed bin (31))
options (byvalue);
dcl $ddPrint file variable nonasgn parm; /* Printstream */
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t byaddr nonasgn parm;
dcl fromDate_a char (10) init ($fromDate);
dcl toDate_a char (10) init ($toDate);
dcl starttime type tod_t;
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
starttime = @getTod ();
EXEC SQL
insert into session.Tkey_Ver_1
(mf150001
)
select MF150001 from @table (@schema, tmf150a1)
where MF150013 between :fromDate_a
and :toDate_a
order by MF150013
,MF150067
fetch first @blocksize rows only
;
if ^(@chkSql (@sqlOk)
| @chkSql (@sqlNoData)) then
call exitPgm ($ddPrint
,"insert 'session.Tkey_Ver_1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (1), starttime);
if @chkSql (@sqlNoData) then return (0);
1 starttime = @getTod ();
EXEC SQL
insert into @table (@schema, tmf150h1)
(MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A)
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from @table (@schema, tmf150a1)
where MF150001 in
(select MF150001 from session.Tkey_Ver_1)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"insert into 'TMF150H1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (2), starttime);
starttime = @getTod ();
EXEC SQL
delete from @table (@schema, tmf150a1) a
where MF150001 in
(select MF150001 from session.Tkey_Ver_1)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"delete flows in 'TMF150A1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (3), starttime);
return (sqlca.sqlerrd (3)); /* # of deletes in TMF150A1 */
end doTransferVersion_0;
1/* -transfer SFL TMF150A1 --> TMF150H1-----------------------Level 1 */
doTransferVersion_1: proc ($ddPrint
,$hSqlCa, $fromDate, $toDate, $hRtStaTbl)
returns (byvalue fixed bin (31))
options (byvalue);
dcl $ddPrint file variable nonasgn parm; /* Printstream */
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t byaddr nonasgn parm;
dcl fromDate_a char (10) init ($fromDate);
dcl toDate_a char (10) init ($toDate);
dcl starttime type tod_t;
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
starttime = @getTod ();
EXEC SQL
insert into session.Tkey_Ver_1
(mf150001
,mf150023
,mf150013
,mf150067)
select MF150001
,MF150023
,MF150013
,MF150067 from @table (@schema, tmf150a1)
where MF150013 >= :fromDate_a
and MF150013 <= :toDate_a
order by MF150013
,MF150067
fetch first @blocksize rows only
;
if ^(@chkSql (@sqlOk)
| @chkSql (@sqlNoData)) then
call exitPgm ($ddPrint
,"insert 'session.Tkey_Ver_1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (1), starttime);
if @chkSql (@sqlNoData) then return (0);
1 starttime = @getTod ();
EXEC SQL
insert into @table (@schema, tmf150h1)
(MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A)
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from @table (@schema, tmf150a1)
where MF150001 in
(select MF150001 from session.Tkey_Ver_1
order by MF150013
,MF150067)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"insert into 'TMF150H1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (2), starttime);
starttime = @getTod ();
EXEC SQL
delete from @table (@schema, tmf150a1) a
where (MF150023
,MF150013) in
(select MF150023
,MF150013 from session.Tkey_Ver_1)
and MF150001 in
(select MF150001 from session.Tkey_Ver_1)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"delete flows in 'TMF150A1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (3), starttime);
return (sqlca.sqlerrd (3)); /* # of deletes in TMF150A1 */
end doTransferVersion_1;
1/* -transfer SFL TMF150A1 --> TMF150H1-----------------------Level 1 */
doTransferVersion_2: proc ($ddPrint
,$hSqlCa, $fromDate, $toDate, $hRtStaTbl)
returns (byvalue fixed bin (31))
options (byvalue);
dcl $ddPrint file variable nonasgn parm; /* Printstream */
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t byaddr nonasgn parm;
dcl nn fixed bin (31) init (0);
dcl fromDate_b char (10) init ($fromDate);
dcl toDate_b char (10) init ($toDate);
dcl starttime type tod_t;
dcl 1 ioT150_b,
%include TMF150P; /* hat Strichpunkt im Copybook */
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
EXEC SQL
declare cur_getAndDelete_b cursor for
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from old table
(delete from @table (@schema, tmf150a1) a
where a.MF150001 in
(select b.MF150001
from @table (@schema, tmf150a1) b
where b.MF150013 >= :fromDate_b
and b.MF150013 <= :toDate_b
order by MF150013
,MF150067
fetch first @blocksize rows only
)
)
order by MF150013
,MF150067
;
1 starttime = @getTod ();
EXEC SQL
open cur_getAndDelete_b;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"open cursor 'cur_getAndDelete_b' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (1), starttime);
starttime = @getTod ();
do while (getData ());
EXEC SQL
insert into @table (@schema, tmf150h1)
values (:ioT150_b)
;
if ^(@chkSql (@sqlOk)
| @chkSql (@sqlDuplicate)) then
call exitPgm ($ddPrint
,"insert flows into 'TMF150H1' failed"
,$hSqlCa);
nn += 1;
end;
call add2RtStat ($hRtStaTbl (2), starttime);
starttime = @getTod ();
EXEC SQL
close cur_getAndDelete_b;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"close cursor 'cur_getAndDelete_b' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (3), starttime);
return (nn);
/* -lese Daten ein------------------------------------------Level 2- */
getData: proc returns (byvalue bit (1) aligned);
EXEC SQL
fetch cur_getAndDelete_b
into :ioT150_b
;
if @chkSql (@sqlOk) then return (@true);
if @chkSql (@sqlNodata) then return (@false);
call exitPgm ($ddPrint
,"fetch cursor 'cur_getAndDelete_b' failed"
,$hSqlCa);
end getData;
end doTransferVersion_2;
1/* -transfer SFL TMF150A1 --> TMF150H1-----------------------Level 1 */
doTransferVersion_3: proc ($ddPrint
,$hSqlCa, $fromDate, $toDate, $hRtStaTbl)
returns (byvalue fixed bin (31))
options (byvalue);
dcl $ddPrint file variable nonasgn parm; /* Printstream */
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t byaddr nonasgn parm;
dcl fromDate_c char (10) init ($fromDate);
dcl toDate_c char (10) init ($toDate);
dcl starttime type tod_t;
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
starttime = @getTod ();
EXEC SQL
insert into session.Tkey_Ver_3
(mf150013
,mf150067)
select MF150013
,MF150067 from
(select MF150013
,MF150067 from @table (@schema, tmf150a1)
where MF150013 >= :fromDate_c
and MF150013 <= :toDate_c
fetch first @blocksize_3 rows only
) i
group by MF150013
,MF150067
;
if ^(@chkSql (@sqlOk)
| @chkSql (@sqlNoData)) then
call exitPgm ($ddPrint
,"insert into 'session.Tkey_Ver_3' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (1), starttime);
if @chkSql (@sqlNoData) then return (0);
1 starttime = @getTod ();
EXEC SQL
insert into @table (@schema, tmf150h1)
(MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A)
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from @table (@schema, tmf150a1)
where (MF150013
,MF150067) in
(select MF150013
,MF150067 from session.Tkey_Ver_3)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"insert flow into 'TMF150H1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (2), starttime);
starttime = @getTod ();
EXEC SQL
delete from @table (@schema, tmf150a1)
where (MF150013
,MF150067) in
(select MF150013
,MF150067 from session.Tkey_Ver_3)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"delete flows in 'TMF150A1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (3), starttime);
return (sqlca.sqlerrd (3)); /* # of deletes in TMF150A1 */
end doTransferVersion_3;
1/* -transfer SFL TMF150A1 --> TMF150H1-----------------------Level 1 */
doTransferVersion_4: proc ($ddPrint
,$hSqlCa, $fromDate, $toDate, $hRtStaTbl)
returns (byvalue fixed bin (31))
options (byvalue);
dcl $ddPrint file variable nonasgn parm; /* Printstream */
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t byaddr nonasgn parm;
dcl nn fixed bin (31) init (0);
dcl nRow fixed bin (31);
dcl is@End bit (1) aligned init (@false);
dcl fromDate_d char (10) init ($fromDate);
dcl toDate_d char (10) init ($toDate);
dcl starttime type tod_t;
dcl starttime_i type tod_t;
dcl uuidTbl (@blocksize) char (16) init ((@blocksize)(''));
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
EXEC SQL
declare cur_getAndInsert_d cursor
with rowset positioning for
select MF150001 from final table (
insert into @table (@schema, tmf150h1)
(MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A)
select
MF150001, MF150002, MF150003, MF150004, MF150005, MF150006
,MF150007, MF150008, MF150009, MF150010, MF150011, MF150012
,MF150013, MF150014, MF150015, MF150016, MF150017, MF150018
,MF150019, MF150020, MF150021, MF150022, MF150023, MF150024
,MF150025, MF150026, MF150027, MF150028, MF150029, MF150030
,MF150031, MF150032, MF150033, MF150034, MF150035, MF150036
,MF150037, MF150038, MF150039, MF150040, MF150041, MF150042
,MF150043, MF150044, MF150045, MF150046, MF150047, MF150051
,MF150052, MF150053, MF150054, MF150055, MF150056, MF150059
,MF150060, MF150062, MF150063, MF150064, MF150065, MF150066
,MF150067, MF150068, MF150069, MF150070, MF150071, MF150072
,MF150073, MF150074, MF150075, MF150076, MF150077, MF150078
,MF150079, MF150080, MF150081, MF150082, MF150083, MF150084
,MF150085, MF150086, MF150087, MF150088, MF150089, MF150090
,MF150091, MF150092, MF150019A
from @table (@schema, tmf150a1) a
where (a.MF150001
,a.MF150013
,a.MF150067) in
(select b.MF150001
,b.MF150013
,b.MF150067
from @table (@schema, tmf150a1) b
where b.MF150013 >= :fromDate_d
and b.MF150013 <= :toDate_d
order by b.MF150013
,b.MF150067
fetch first @blocksize rows only
)
) order by input sequence
;
1 starttime = @getTod ();
EXEC SQL
open cur_getAndInsert_d;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"open cursor 'cur_getAndInsert_d' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (1), starttime);
starttime = @getTod ();
nRow = getData (is@End);
do while (nRow > 0);
/* insert UUID into session table
*/
starttime_i = @getTod ();
EXEC SQL
insert into session.Tkey_Ver_4
values (:uuidTbl)
for :nRow rows
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,'insert into session.Tkey_Ver_4 failed'
,$hSqlCa);
call add2RtStat ($hRtStaTbl (3), starttime_i);
/* delete flows in TMF150A1
*/
starttime_i = @getTod ();
EXEC SQL
delete from @table (@schema, tmf150a1)
where MF150001 in
(select MF150001 from session.Tkey_Ver_4)
;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"delete flows in 'TMF150A1' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (4), starttime_i);
nn += nRow;
if is@End then nRow = 0; /* no more data */
else nRow = getData (is@End);
end;
call add2RtStat ($hRtStaTbl (2), starttime);
starttime = @getTod ();
EXEC SQL
close cur_getAndInsert_d;
if ^@chkSql (@sqlOk) then
call exitPgm ($ddPrint
,"close cursor 'cur_getAndInsert_d' failed"
,$hSqlCa);
call add2RtStat ($hRtStaTbl (5), starttime);
return (nn);
/* -lese Daten ein------------------------------------------Level 2- */
getData: proc ($is@End)
returns (byvalue fixed bin (31));
dcl $is@End bit (1) aligned;
EXEC SQL
fetch next rowset from cur_getAndInsert_d
for @blocksize rows
into :uuidTbl
;
if ^(@chkSql (@sqlOk)
| @chkSql (@sqlNoData)) then
call exitPgm ($ddPrint
,"fetch cursor 'cur_getAndInsert_d' failed"
,$hSqlCa);
$is@End = (@chkSql (@sqlNoData));
return (sqlca.sqlerrd (3)); /* # of rows in rowset */
end getData;
end doTransferVersion_4;
1/* -commit changes-------------------------------------------Level 1 */
#ofSFL: proc ($ddPrint, $hSqlca, $fromDate, $toDate)
options (byvalue)
returns (byvalue fixed bin (31));
dcl $ddPrint file variable byvalue nonasgn parm;
dcl $hSqlca handle sqlca_t nonasgn parm; /* SQLCA */
dcl $fromDate char (10) byaddr nonasgn parm;
dcl $toDate char (10) byaddr nonasgn parm;
dcl nn_c fixed bin (31) init (0);
dcl fromDate_z char (10) init ($fromDate);
dcl toDate_z char (10) init ($toDate);
dcl starttime type tod_t init (@getTod ());
dcl sqlca type sqlca_t based (ptrvalue ($hSqlCa));
EXEC SQL
select count (*)
into :nn_c
from @table (@schema, tmf150a1)
where MF150013 >= :fromDate_z
and MF150013 <= :toDate_z
with ur
;
if @chkSql (@sqlOk) then do;
call @printElapsedTime ($ddPrint, starttime
,"select 'count (*)'");
return (nn_c);
end;
call exitPgm ($ddPrint
,"select 'count (*)' failed"
,$hSqlCa);
end #ofSFL;
/* -commit changes-------------------------------------------Level 1 */
execRRSAFCmd: proc ($ddPrint, $cmd, $ssid, $plan)
options (byvalue);
dcl $ddPrint file variable byvalue nonasgn parm;
dcl $cmd char (12) byaddr parm;
dcl $ssid char (4) byaddr optional parm;
dcl $plan char (8) byaddr optional parm;
dcl rc fixed bin (31);
if $cmd = cConnect then rc = YXRRSAF ($cmd, $ssid, $plan);
else rc = YXRRSAF ($cmd);
if rc ^= 0 then
call exitPgm ($ddPrint
,"YXRRSAF: "
|| $cmd
|| " failed (RC=x'"
|| hex (rc)
|| "')"
);
end execRRSAFCmd;
1/* -calculate to-date----------------------------------------Level 1 */
calcToDate: proc
returns (char (10));
dcl nDays fixed bin (31) init ((days () - 365));
dcl ii fixed bin (31);
do ii = 1 to 7;
if weekday (nDays) = 1 then /* Sunday */
return (daystodate (nDays, @dateFmt));
nDays -= 1;
end;
return ('01.01.0001');
end calcToDate;
/* -calculate from-date--------------------------------------Level 1 */
calcFromDate: proc ($toDate)
returns (char (10));
dcl $toDate char (10) nonasgn parm;
dcl nDays fixed bin (31) init (days ($toDate, @dateFmt));
return (daystodate (nDays - 6, @dateFmt));
end calcFromDate;
/* -check period---------------------------------------------Level 1 */
checkHistPeriod: proc ($ddPrint, $fromDate, $toDate);
dcl $ddPrint file variable;
dcl $fromDate char (10) nonasgn parm;
dcl $toDate char (10) nonasgn parm;
if days ($fromDate, @dateFmt) <= days ($toDate, @dateFmt) then
return;
call exitPgm ($ddPrint
,'from-date '
|| $fromDate
|| ' greater then to-date '
|| $toDate
);
end checkHistPeriod;
1/* -new runtime statistics container-------------------------Level 1 */
newRtSta: proc ($text)
returns (byvalue handle rtSta_t);
dcl hRtStat handle rtSta_t init (new (:rtSta_t:)) nonasgn;
dcl $text char (*) varz;
call plifill (ptrvalue (hRtStat), '00'x, size (:rtSta_t:));
hRtStat=>sumX = 0.0;
hRtStat=>sumX2 = 0.0;
hRtStat=>minX = huge (hRtStat=>minX);
hRtStat=>maxX = 0.0;
hRtStat=>text = $text;
return (hRtStat);
end newRtSta;
/* add sample to runtime statistics--------------------------Level 1 */
add2RtStat: proc ($hRtStat, $tod)
options (byvalue inline);
dcl $hRtStat handle rtSta_t nonasgn parm;
dcl $tod type tod_t byaddr nonasgn parm;
call addSample2RtStat ($hRtStat, @calcTodDiff (@getTod (), $tod));
end add2RtStat;
/* add sample to runtime statistics--------------------------Level 1 */
addSample2RtStat: proc ($hRtStat, $sampleX)
options (byvalue);
dcl $hRtStat handle rtSta_t nonasgn parm;
dcl $sampleX float dec (16) nonasgn parm;
$hRtStat=>sumX += $sampleX;
$hRtStat=>sumX2 += $sampleX * $sampleX;
if $sampleX > $hRtStat=>maxX then $hRtStat=>maxX = $sampleX;
if $sampleX < $hRtStat=>minX then $hRtStat=>minX = $sampleX;
$hRtStat=>nn += 1;
end addSample2RtStat;
1/* print runtime statistics----------------------------------Level 1 */
printRtStat: proc ($ddPrint, $hRtStat)
options (byvalue);
dcl $ddPrint file variable nonasgn parm;
dcl $hRtStat handle rtSta_t nonasgn parm;
dcl covariance float dec (16) init (0.0);
dcl deviation float dec (16) init (0.0);
dcl mean float dec (16) init (0.0);
if $hRtStat=>nn > 1 then do;
/* calculate covariance (Varianz)
*/
covariance = ($hRtStat=>sumX2 -
($hRtStat=>sumX * $hRtStat=>sumX /
float ($hRtStat=>nn, 16))) /
float (($hRtStat=>nn - 1), 16);
/* calculate standard deviation (Standardabweichung)
*/
deviation = sqrt (covariance);
end;
/* calculate mean (Mittelwert)
*/
if $hRtStat=>nn > 0 then
mean = $hRtStat=>sumX / float ($hRtStat=>nn, 16);
put file ($ddPrint) edit
('Runtime statistic - ', $hRtStat=>text)
(skip, a, a)
('# of samples', $hRtStat=>nn)
(skip, a, col(30), p'zzz,zzz,zz9')
('minimum', round ($hRtStat=>minX, 3))
(skip, a, col(30), p'zzz,zzz,zz9V.999')
('maximum', round ($hRtStat=>maxX, 3))
(skip, a, col(30), p'zzz,zzz,zz9V.999')
('deviation', round (deviation, 3))
(skip, a, col(30), p'zzz,zzz,zz9V.999')
('covariance', round (covariance, 3))
(skip, a, col(30), p'zzz,zzz,zz9V.999')
('mean', round (mean, 3))
(skip, a, col(30), p'zzz,zzz,zz9V.999')
;
end printRtStat;
1/* print runtime statistics table----------------------------Level 1 */
printRtStaTbl: proc ($ddPrint, $hRtStaTbl)
options (byvalue);
dcl $ddPrint file variable nonasgn parm;
dcl $hRtStaTbl (*) handle rtSta_t nonasgn parm;
dcl ii fixed bin (31);
do ii = lbound ($hRtStaTbl) to hbound ($hRtStaTbl);
if $hRtStaTbl (ii) ^= null () then do;
put skip file ($ddPrint) list ((@nRep) @repChar);
call printRtStat ($ddPrint, $hRtStaTbl (ii));
end;
end;
put skip file ($ddPrint) list ((@nRep) @repChar);
end printRtStaTbl; 07640002
/* delete runtime statistics table---------------------------Level 1 */
deleteRtStaTbl: proc ($hRtStaTbl)
options (byvalue);
dcl $hRtStaTbl (*) handle rtSta_t nonasgn parm;
dcl ii fixed bin (31);
do ii = lbound ($hRtStaTbl) to hbound ($hRtStaTbl);
if $hRtStaTbl (ii) ^= null () then
call plidelete ($hRtStaTbl (ii));
end;
end deleteRtStaTbl; 07640002
end MFR_sfl_history; 07640002
/* -CS-KGCG 21----------------------------------------------------- */07650002