-- DB2 / OS / Utilities

-- DB2 -------------------------------------------------------------------------------------------------
-- Find all tables by table names:
select * from sysibm.systables where name like 'TIK%' and type = 'T'; -- 50 TIK-Tables
select * from sysibm.systables where name like 'TPV%' and type = 'T'; -- 41 TPV-Tables

-- Find all columns by name:
select * from syscat.columns where tabschema='VISTA' and colname like '%DOCREF%';

-- Table IDs:
SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME
FROM SYSCAT.TABLES AS T,
SYSCAT.COLUMNS AS C
WHERE T.TBSPACEID = 303
AND T.TABLEID = 3
AND C.COLNO = 2
AND C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME;
/*
TABSCHEMA  TABNAME     COLNAME    
---------  ----------  ---------  
VISTA      TPVADDRESS  KPVPARTID
*/

-- DB / Federated DB - UPI ------------------------------------------
select current server as db, servername, create_time, option, setting
from syscat.serveroptions
where option in ( 'DBNAME' , 'NODE' )
order by option;
/*
DB        SERVERNAME     CREATE_TIME          OPTION  SETTING   
--------  -------------  -------------------  ------  --------  
VIDPDBGR  ZASDBSERVER    2020-10-21 10:19:55  DBNAME  vidpdbza  
VIDPDBGR  GILAIDBSERVER  2020-08-08 12:26:55  DBNAME  vidpdbex  
VIDPDBGR  ZASDBSERVER    2020-10-21 10:19:55  NODE    vidpinza  
VIDPDBGR  GILAIDBSERVER  2020-08-08 12:26:54  NODE    vidpingr  
*/

-- OS ----------------------------------------
-- 177267 - SO - IG_PV_DELA_V nicht produziert in prodSO (aber in EduSO)
select distinct f.REC_IDE, f.HDR_IDE, f.PROD_STATE, f.CREATETIME, f.DESCRIPTION
from dope.dop_fd f
where date(f.CREATETIME) >= current date - 10 days and f.HDR_IDE like 'IG_PV_DELA_V';
-- PROD:
/*
REC_IDE              HDR_IDE       PROD_STATE  CREATETIME           DESCRIPTION   
-------------------  ------------  ----------  -------------------  ------------  
1812808540006336711  IG_PV_DELA_V  S           2018-05-08 08:54:13  IG_PV_DELA_V  
1812808540006336714  IG_PV_DELA_V  S           2018-05-08 08:54:26  IG_PV_DELA_V  
1812810250006337105  IG_PV_DELA_V  S           2018-05-08 10:25:08  IG_PV_DELA_V  
1812810250006337107  IG_PV_DELA_V  S           2018-05-08 10:25:43  IG_PV_DELA_V  
1812814430006338184  IG_PV_DELA_V  S           2018-05-08 14:43:43  IG_PV_DELA_V  
1812814430006338186  IG_PV_DELA_V  S           2018-05-08 14:43:50  IG_PV_DELA_V  
1812906050006340352  IG_PV_DELA_V  S           2018-05-09 06:05:40  IG_PV_DELA_V  
1812906280006340291  IG_PV_DELA_V  S           2018-05-09 06:28:14  IG_PV_DELA_V  
1812908570006340918  IG_PV_DELA_V  S           2018-05-09 08:57:53  IG_PV_DELA_V  
1813115040006346693  IG_PV_DELA_V  S           2018-05-11 15:04:15  IG_PV_DELA_V  
*/
select fd.REC_IDE, var.PARMNAME, var.VALUE
from dope.dop_fd fd
join dope.dop_fd_var var
on fd.id=var.id
where fd.REC_IDE in '1813115040006346693'
--and var.parmname in ('DOPDOCID', 'DOPDOCSTAT', 'IG_EXPRESSDRUCK', 'OS_PROCESSINGDATE', 'IG_VERARBEITUNGSDATUM', 'IG_ELAR_PID')
with ur;
/*
REC_IDE              PARMNAME               VALUE     
-------------------  ---------------------  --------  
1813115040006346693  IG_EXPRESSDRUCK        J         
1813115040006346693  IG_VERARBEITUNGSDATUM  20180511  
1813115040006346693  DOPDOCSTAT             NEW       
1813115040006346693  IG_ELAR_PID            2935140            
*/
-- EDU:
select distinct f.REC_IDE, f.HDR_IDE, f.PROD_STATE, f.CREATETIME, f.DESCRIPTION
from dope.dop_fd f
where date(f.CREATETIME) >= current date - 10 days and f.HDR_IDE like 'IG_PV_DELA_V';
/*
REC_IDE              HDR_IDE       PROD_STATE  CREATETIME           DESCRIPTION   
-------------------  ------------  ----------  -------------------  ------------  
1812908340006216486  IG_PV_DELA_V  F           2018-05-09 08:34:46  IG_PV_DELA_V  
1812908360006216491  IG_PV_DELA_V  F           2018-05-09 08:36:11  IG_PV_DELA_V  
*/
select fd.REC_IDE, var.PARMNAME, var.VALUE
from dope.dop_fd fd
join dope.dop_fd_var var
on fd.id=var.id
where fd.REC_IDE in '1812908360006216491'
and var.parmname in ('DOPDOCID', 'DOPDOCSTAT', 'IG_EXPRESSDRUCK', 'OS_PROCESSINGDATE', 'IG_VERARBEITUNGSDATUM', 'IG_ELAR_PID')
with ur;
/*
REC_IDE              PARMNAME               VALUE           
-------------------  ---------------------  --------------  
1812908360006216491  IG_ELAR_PID            3999081         
1812908360006216491  IG_VERARBEITUNGSDATUM  20180509        
1812908360006216491  IG_EXPRESSDRUCK        J               
1812908360006216491  DOPDOCSTAT             FINISHED        
1812908360006216491  DOPDOCID               IG_PV_DELA_V    
1812908360006216491  OS_PROCESSINGDATE      20180509083555  
1812908360006216491  IG_EXPRESSDRUCK        J               
1812908360006216491  DOPDOCSTAT             FINISHED                                                          
*/

-- 263224 - LI - GILAI Replication
SELECT CURRENT SERVER AS DB, SERVERNAME, CREATE_TIME, OPTION, SETTING
FROM SYSCAT.SERVEROPTIONS
WHERE OPTION IN ( 'DBNAME' , 'NODE' )
ORDER BY OPTION;
/*
DB        SERVERNAME     CREATE_TIME          OPTION  SETTING   
--------  -------------  -------------------  ------  --------  
VIDPDBLI  ZASDBSERVER    2021-07-24 10:59:29  DBNAME  vidpdbza  
VIDPDBLI  GILAIDBSERVER  2021-07-24 10:18:46  DBNAME  vidpdbex  
VIDPDBLI  ZASDBSERVER    2021-07-24 10:59:29  NODE    vidpinza  
VIDPDBLI  GILAIDBSERVER  2021-07-24 10:18:46  NODE    vidpinli  
*/
select * from vista.AAIREPLICATION rep
where rep.LASTUPDATED >= current timestamp - 30 day order by lastupdated desc;
select * from vista.AAIREPLICATION rep
where rep.LASTUPDATED >= current timestamp - 30 day and cstatus <> 'Status#DONE' order by lastupdated desc;