zOs/SQL/PLANFIX

$#@
$=fun = l   $*( l = alter log yes
                0 = drop TS with nTables = 0
                e = drop TS in EBCDIC
            $*)
$=dbSys = DP4G
$=db = DB2PLAN
call sqlConnect $dbSys
if $fun == 'l' then $@[
    $<=[
    select 'alter tableSpace $db.' || name || ' LOG YES;'
      from sysibm.sysTablespace
      where dbName = '$db' and type = 'O' and log = 'N'
    $] call sqlStmts
$] else if $fun == 0 then $@[
    $<=[
    select 'drop tableSpace $db.' || name || '; commit;'
      from sysibm.sysTablespace
      where dbName = '$db' and nTables = 0
    $] call sqlStmts
$] else if $fun == 'e' then $@[
    $<=[
-- drop all plan_tables in a db2 Subsystem with ebcdic encoding
with m as           -- select model table set
(
  select t.creator mCr, t.name mTb
      , t.encoding_scheme mEnc
      , t.recLength mRecL, t.colCount mCols, c.name mCol
    from sysibm.sysTables t
      left join sysibm.sysColumns c
        on t.creator = c.tbCreator and t.name = c.tbName
            and t.colCount = c.colNo
    where type = 'T'
         and creator = 'A540769' and dbName = 'DB2PLAN'
)
select 'drop tablespace ' || strip(dbName)
       || '.' || strip(tsName) || ';commit;'
       ||  ' -- ' || min(strip(t.creator) || '.' || t.name) mTb
     from sysibm.systables t
      join m
        on t.name = m.mTb and t.encoding_scheme <> 'U'
  group by dbName, tsName
  order by min(creator), min(name)
$] call sqlStmts
$]
$#out                                              20150223 08:44:28
C
0 rows fetched: select 'alter tableSpace DB2PLAN.' || name || ' LOG YES;' ...
$#out                                              20150223 08:44:20
C
0 rows fetched: select 'drop tableSpace DB2PLAN.' || name || '; commit;' f...
$#out                                              20150223 08:44:14
sqlCode 0: drop tableSpace DB2PLAN.AOCULS01
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.AOCULS02
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.AOCULS03
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.AOCUTS11
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.AOCUTS19
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.EPTS1012
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.EPTS1014
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.EPTS1015
sqlCode 0: commit
sqlCode 0: drop tableSpace DB2PLAN.EPTS1016
sqlCode 0: commit
$#out                                              20150223 08:44:01
COL1
drop tableSpace DB2PLAN.AOCULS01; commit;
drop tableSpace DB2PLAN.AOCULS02; commit;
drop tableSpace DB2PLAN.AOCULS03; commit;
drop tableSpace DB2PLAN.AOCUTS11; commit;
drop tableSpace DB2PLAN.AOCUTS19; commit;
drop tableSpace DB2PLAN.EPTS1012; commit;
drop tableSpace DB2PLAN.EPTS1014; commit;
drop tableSpace DB2PLAN.EPTS1015; commit;
drop tableSpace DB2PLAN.EPTS1016; commit;
9 rows fetched: select 'drop tableSpace DB2PLAN.' || name || '; commit;' f...
$#out                                              20150223 08:43:53
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$#out                                              20150223 08:43:43
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$#out                                              20150223 08:43:35
C
0 rows fetched: select 'drop tableSpace DB2PLAN.' || name || '; commit;' f...
$#out                                              20150223 08:43:28
C
0 rows fetched: select 'alter tableSpace DB2PLAN.' || name || ' LOG YES;' ...
$#out                                              20150216 16:42:41
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$#out                                              20150216 16:42:03
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$#out                                              20150216 16:41:59
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$#out                                              20150216 16:41:49
$#out                                              20150211 11:43:31
M
0 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding_...
$]
$#out                                              20150216 16:18: