zOs/SQL/GBGRFUN1

     -- versuch gbGr schwellwert Abfrage zu beschleunigen
     -- durch Konkatinierung aller schwellwerte und
     --        auswahl der richtigen mittels funktion
     -- gescheitert, weil Loop innerhalb der Funktion
     --        zuviel cpu braucht (aufruf der funktion war schnell)
     --
with i1 as
(
   select left(rz, 3) || left(dbSys, 4) || left(db, 8) || left(ts, 8)
          || left('' || schwelle, 6) i
       ,         translate(rz, '%', '*')       rzT
       ,  posStr(translate(rz, '%', '*'), '%') rzLen
       ,  posStr(translate(dbSys, '%', '*'), '%') syLen
       ,  posStr(translate(db   , '%', '*'), '%') dbLen
       ,  posStr(translate(ts   , '%', '*'), '%') tsLen
       , s.*
       from oa1p.tqz008gbGrSchwelle s
)
, i2 as
(
  select row_number() over(order by prio desc
           , case when rzLen = 0 then 9 else rzLen end desc
           , case when syLen = 0 then 9 else syLen end desc
           , case when dbLen = 0 then 9 else dbLen end desc
           , case when tsLen = 0 then 9 else tsLen end desc
           ) r, i1.*
    from i1
)
, i3 (i, l) as
(
  select varchar(i, 20000), 1  from i2 where r = 1
  union all select i3.i || i2.i
  , i3.l+1 from i3 join i2 on i2.r = i3.l+1
       where i3.l < 99999
)
, i as
(
  select i from i3 order by l desc fetch first row only
)
, s as
(
select A540769.gbgrSchwelle('RZZ', 'DE0G', 'MF01A1P', 'A150A'
         ,  i.i) si
    from sysibm.sysDummyE, i
)
select int(left(si, 6)) sw, si from s
;
select * from i
;
;x;
$#@
call sqlConnect dp4g
$<>
call sqlStmts
$<=[
set current sqlid = 'S100447';
drop view OA1P.VQZ006GBGRENZE;
drop   function a540769.gbGrSchwelle(rz char(3), dbSys char(4)
        ,db char(8), ts char(8), all varchar(32000));
terminator^;
create function a540769.gbGrSchwelle(rz char(3), dbSys char(4)
        ,db char(8), ts char(8), all varchar(32000))
                returns varchar(80)
      deterministic
begin
  declare r1, s1, d1, t1 varchar(10) default '?';
  declare cx, px int default -28;
  one: $** loop begin
      set cx = cx + 29;
      if cx > length(all) then
      $** return '  2      defaut no search ??????';
          return '-- notfound ' || length(all) || ' cx ' || cx
            || ' la ' || r1 || '/' || s1 || ':' || d1
            || ' arg ' || rz || '/' || dbSys || ':' || db;
          end if;
  $*( if cx > 9975 then
      return 'len' || length(all) || ' cx ' || cx;
      end if;
  $*) set r1 =       substr(all, cx   , 3);
      set px = posStr(r1, '%');
      if (px > 0 and left(r1, px-1) <> left(rz, px-1))
          or (px <= 0 and rz <> r1) then
          goto one;
      end if;
      set s1 =       substr(all, cx+ 3, 4);
      set px = posStr(s1, '%');
      if (px > 0 and left(s1, px-1) <> left(dbSys, px-1))
        or (px <= 0 and dbSys <> s1) then
          goto one;
      end if;
 $*(  if cx > 9990 then
      return 'x ' || cx ||': ' || r1 || '/' || s1 || ':' || d1
          || ' arg ' || rz || '/' || dbSys || ':' || db;
      end if;
 $*)  set d1 =       substr(all, cx+ 7, 8);
      set px = posStr(d1, '%');
      if (px > 0 and left(d1, px-1) <> left(db, px-1))
        or (px <= 0 and db <> d1) then
          goto one;
      end if;
      set t1 =       substr(all, cx+15, 8);
      set px = posStr(t1, '%');
      if (px > 0 and left(t1, px-1) <> left(ts, px-1))
        or (px <= 0 and t1 <> ts) then
          goto one;
      end if;
      return substr(all, cx+23, 6)
           || ' key ' || '/' || s1 ||':'|| d1 ||'.'||t1;
      return 'f ' || cx ||': ' || r1 || '/' || s1 ||':'|| d1 ||'.'||t1;
$**   end; end loop one;
  return '--- notfound ---';
end
^
terminator;^
with s as
(
select A540769.gbgrSchwelle('RZZ', 'DE0G', 'MF01A1P', 'A150A'
         ,  'RZ2DBOF%2345678%2345678001   '
         || 'RZ2%           %2345678   002'
         || 'RZZDEVG%       %2345678   3  '
         || 'RZZDE0GNONO    %2345678  4   '
         || 'RZZDE0GMF01%   A150A     5   '
         || '%  %   %       %         6   '
         ) si
    from sysibm.sysDummyE
)
select int(left(si, 6)) sw, si from s
$]
$#out                                              20140714 20:02:01
sqlCode 0: set current sqlid = 'S100447'
sqlCode 0: drop view OA1P.VQZ006GBGRENZE
sqlCode 0: drop function a540769.gbGrSchwelle(rz char(3), dbSys char(4) ,d...
sqlCode 0: create function a540769.gbGrSchwelle(rz char(3), dbSys char(4) ...
S SI
5   5    key /DE0G:MF01%   .A150A
1 rows fetched: with s as ( select A540769.gbgrSchwelle('RZZ', 'DE0G', 'MF...
$#out                                              20140714 19:56:19