zOs/SQL/BINDSEL1

select rz, dbSys
     , char(left(strip(cmRes) || ' ' || strip(result) || ' ' || case
           when errMsg is null or errMsg = '' then errMsg
           when posStr(errMsg, 'SQLCODE=') > 0
               then substr(errMsg, posStr(errMsg, 'SQLCODE='), 40)
           when posStr(errMsg, 'RESOURCE UNAVAILABLE') > 0
                then 'unAvail: '
              || case
              when posStr(substr(errMsg
                  , posStr(errMsg, 'RESOURCE UNAVAILABLE') + 20)
                  , ' NAME ') < 1
                then substr(errMsg
                    , posStr(errMsg, 'RESOURCE UNAVAILABLE') + 22)
                else substr(errMsg
                    , posStr(errMsg, 'RESOURCE UNAVAILABLE')
                    + posStr(substr(errMsg
                      , posStr(errMsg, 'RESOURCE UNAVAILABLE') + 20)
                      , ' NAME ')+ 25)
              end
           else '? ' || errMsg
       end, 40), 40) "c b error"
     , g.* --rz, dbSys, errMsg, g.*
    from oa1p.TQZ043BINDGEN g
 -- where pgm = 'TR011@I'
 -- where (result is null or result <> '0' )
    where result not in ('0') -- , '8')    -- or cmRes > '0'
 -- where                    cmRes <> '0'
 -- where rz = 'RZX'
 -- where cmJob like 'CSBI%'
--  where gentst < '2014-09-19-00.17.48.142237'
    order by gentst desc
    fetch first 500 rows only
;;
select (select count(*)
      from oa1p.TQZ044BINDLine l where l.genId = g.genid) lines
      , g.*
    from oa1p.TQZ043BINDGen g
    order by gentst desc
;
select *
    from oa1p.TQZ044BINDLine
    where genid = 1498
    order by seq
;x;
select count(*), ssid, location
    from oa1p.bind_exceptions
    group by ssid, location
;;;;;;;;;;;;
select * from final table
(
insert into oa1p.bind_exceptions (APPL, PGM, COLLID, INSTALLDATE
                                 ,ssid, location)
select APPL, PGM, COLLID, INSTALLDATE
       , 'DEVG' ssid
       , case when location = '' then ''
              when location = 'CHSKA000DBOF' then 'CHROI00ZDE0G'
              else '??????????' end location
    from  oa1p.bind_exceptions
    where ssid = 'DVBP'
)
;
select count(*), ssid, location
    from oa1p.bind_exceptions
    group by ssid, location
;
commit
;;;;;;;;;
delete from  oa1p.bind_exceptions
    where installdate  > '31.12.2149'
;
delete from oa1p.bind_parameters
    where installdate  > '31.12.2149'
;
select *
    from oa1p.bind_exceptions
    where pgm = 'XBIM03'
;
commit
;;;
select appl, pgm, count(*), count(distinct collid),
    min(collid), max(collid)
    from  OA1P.BIND_PARAMETERS
    group by appl, pgm
    order by 4 desc
;
x
 select * from oa1p.BIND_exceptions
 where pgm     = 'TP0900'