echo 'info EXTDOWN showch' | ./ggsci > SRDC_GG_EXTINFO.txt echo 'info detail' | ./ggsci >> SRDC_GG_EXTINFO.txt stats ext1, totalsonly ABMS.* stats ext01 ,TOTALSONLY ACSSP.* stats ext01 ,TOTALSONLY OSBHUB.* stats ext01 ,TOTALSONLY ABMS.* stats ext01 ,TOTALSONLY MPI.* stats rep01 ,TOTALSONLY ACSSP.* stats ext01 ,TOTALSONLY OSBHUB.* stats rep01 ,TOTALSONLY ABMS.* stats rep01 ,TOTALSONLY ABMS.BUDGET_PERS ABMS"."BUDGET_PERS" stats extract ext01 TABLE MPI.* stats extract rep01 TABLE MPI.* stats extract rep01 TABLE MPI.* stats rep01 ,TOTALSONLY MPI.* Modified Oct 30 ========================================= Daily Commands to run at (ORACLE) Site ========================================= 1) GGSCI> info all 2) Make Sure all procesess are online 3) -- USE SQL - This will tell you when Replicate at DB2 was Suspend(Paused) or Resumed(Started) set linesize 300 select * from abms.KS_GG_ORACLE_EVENT order by 1; ========================================= Daily Commands to run at REPLICAT Side (DB2) ========================================= 1) - First Check that All processes are online GGSCI> INFO all 2) -- How to check Replicate Status Suspended or Resumed GGSCI> send REP1 STATUS 2a) -- If Suspended, Command to Resume the operation ( Only if Needed) GGSCI> send rep1 RESUME 3) -- Check lag GGSCI> lag rep1 4) -- Send Replicat report GGSCI> send rep1 REPORT 5) GGSCI> stats REP1 TABLE SRGGT11.ORACLE_HB TOTAL GGSCI> stats REP1 TABLE SRGGT11.KSD_CLIENT DAILY 6) Additional Commands if needed INFO Details ------------ info ext1,detail info pmp1,detail info rep1,detail Checking LAG ------------ send EXT1,getlag send REP1,getlag send PMP1,getlag send DPUMP,getlag ==================================================== ============== Statistics Regarding EXTRACT ======== ==================================================== stats extract ext1 reportrate hr stats extract ext1 TOTAL -- Displays totals since process startup stats extract ext1 DAILY -- Displays totals since the start of the current day. stats extract ext1 HOURLY -- Displays totals since the start of the current hour. stats extract ext1 LATEST -- Displays totals since the last RESET command. stats extract ext1 RESET -- Resets the counters in the LATEST statistical field. stats extract ext1 TABLE ABMS.KSD_CLIENT TOTAL stats extract ext1 TABLE ABMS.KSD_CLIENT DAILY stats extract ext1 TABLE ABMS.KSD_CLIENT HOURLY stats extract ext1 TABLE ABMS.KSD_CLIENT LATEST stats extract ext1 TABLE ABMS.KSD_CLIENT RESET stats extract ext1 TABLE ABMS.KSD_CLIENT REPORTRATE HR stats extract ext1 TABLE ABMS.KSD_CLIENT REPORTRATE MIN stats extract ext1 TABLE ABMS.KSD_CLIENT REPORTRATE SEC The following example displays total and hourly statistics per minute for a specific table, and it also resets the latest statistics and outputs fetch statistics. STATS EXTRACT EXT1, TOTAL, HOURLY, TABLE ABMS.KSD_CLIENT ,REPORTRATE MIN, RESET, REPORTFETCH STATS EXTRACT ext1, LATEST, REPORTFETCH ==================================================== ============== Statistics Regarding Replicat ======= ==================================================== stats REPLICAT REP1 TOTAL -- Displays totals since process startup stats REPLICAT REP1 DAILY -- Displays totals since the start of the current day. stats REPLICAT REP1 HOURLY -- Displays totals since the start of the current hour. stats REPLICAT REP1 LATEST -- Displays totals since the last RESET command. stats REPLICAT REP1 RESET -- Resets the counters in the LATEST statistical field. stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT TOTAL stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT DAILY stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT HOURLY stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT LATEST stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT RESET stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT REPORTRATE HR stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT REPORTRATE MIN stats REPLICAT REP1 TABLE ABMS.KSD_CLIENT REPORTRATE SEC The following example displays total and hourly statistics per minute for a specific table, and it also resets the latest statistics and outputs fetch statistics. STATS REPLICAT EXT1, TOTAL, HOURLY, TABLE ABMS.KSD_CLIENT ,REPORTRATE MIN, RESET, REPORTFETCH STATS REPLICAT rep1, LATEST, REPORTFETCH INFO ALL [TASKS | ALLPROCESSES] ##################################### ADVANCE COMMANDS ################################ Checking TCP =================== send extract pmp1, gettcpstats info pmp1, showch info ext1, showch TRANSACTION ========================= GGSCI > send ext1, SHOWTRANS Sending SHOWTRANS request to EXTRACT EXT1 ... No transactions found Oldest redo log files necessary to restart Extract are: Redo Thread 1, Redo Log Sequence Number 238338, SCN 2.250828303 (8840762895), RBA 29319696 Redo Thread 2, Redo Log Sequence Number 181321, SCN 2.250828321 (8840762913), RBA 153205776. select s.saddr,s.sid,s.username,s.osuser,s.status,s.PROGRAM,t.START_TIME from gv$session s,gv$transaction t where s.saddr = t.SES_ADDR and s.inst_id=t.inst_id order by t.start_time; GGSCI > SEND EXTRACT EXT1 SHOWTRANS ------------------------------------------------------------ XID: 4.15.1763613 Select * from gv$transaction where xidusn=4 and XIDSLOT =15 and XIDSQN =1763613; Select * from gv$transaction where xidusn=86 and XIDSLOT =23 and XIDSQN =131979; Long Running Transaction: XID 86.23.131979 ================================ ###### Output 1 ###### GGSCI (stksplkdb01.kees.ks.internal) 15> send extract ext1 status Sending STATUS request to EXTRACT EXT1 ... EXTRACT EXT1 (PID 21576) Current status: Recovery complete: Processing data with empty data queue Current read positions: Redo thread #: 1 Sequence #: 238338 RBA: 30626500 Timestamp: 2016-12-18 13:38:45.000000 SCN: 2.250834598 (8840769190) Redo thread #: 2 Sequence #: 181321 RBA: 155358208 Timestamp: 2016-12-18 13:38:44.000000 SCN: 2.250834595 (8840769187) Current write position: Sequence #: 10726 RBA: 72446640 Timestamp: 2016-12-18 13:38:39.002094 Extract Trail: ./dirdat/sa ###### Output 2 ###### GGSCI (stksplkdb01.kees.ks.internal) 17> send replicat rep1 statsus Sending STATSUS request to REPLICAT REP1 ... Start of Statistics at 2016-12-18 13:40:09. Replicating from SRGGT11.KSD_CLIENT_BASIC to ABMS.KSD_CLIENT_BASIC: *** Total statistics since 2016-12-13 08:32:16 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 ###### Output 3 ###### GGSCI (stksplkdb01.kees.ks.internal) 24> send ext1, SHOWTRANS TABULAR Sending SHOWTRANS request to EXTRACT EXT1 ... Oldest redo log files necessary to restart Extract are: Redo Thread 1, Redo Log Sequence Number 238338, SCN 2.250845199 (8840779791), RBA 34210832 Redo Thread 2, Redo Log Sequence Number 181321, SCN 2.250845415 (8840780007), RBA 159091728 XID Items Extract Redo Thread Start Time SCN Redo Seq Redo RBA Status ------------------------------------------------------------------------------------------------------------------------------------------------------ 865.17.23616 1 EXT1 1 2016-12-18:13:54:50 2.250845199 (8840779791) 238338 34210832 Running ========================= STATS Extract and Replicat ======================== stats rep1 totalsonly srggt11.* STATS EXTRACT group_name [, statistic] [, TABLE [container. | catalog.]schema.table] [, TOTALSONLY [container. | catalog.]schema.table] [, REPORTCHARCONV] [, REPORTFETCH | NOREPORTFETCH] [, REPORTRATE time_units] [, ... ] STATS REPLICAT group_name [, statistic] valid values TOTAL,DAILY,HOURLY,LATEST,RESET [, TABLE [container. | catalog.]schema.table] [, TOTALSONLY [container. | catalog.]schema.table] [, REPORTCDR] <-- Shows statistics for Conflict Detection and Resolution. Statistics include: [, REPORTCHARCONV] [, REPORTDETAIL | NOREPORTDETAIL] [, REPORTRATE {HR | MIN | SEC}] [, ... ] ====================================== ========= Skip Transaction =========== ====================================== send ext1, SHOWTRANS SEND EXTRACT ext1, SKIPTRANS 794.30.103141 SEND EXTRACT ext1, SKIPTRANS 118.14.68302 SEND EXTRACT ext1, SKIPTRANS 802.22.80394 SEND EXTRACT ext1, SKIPTRANS 799.4.59041 SEND EXTRACT ext1, SKIPTRANS 98.13.49126 select 'stats rep1 TABLE SRGGT41.'|| table_name || ' daily ' from dba_tables where table_name like 'KSD%' and owner='ABMS' order by 1 stats rep1 TABLE SRGGT41.KSD_ADDRESS daily stats rep1 TABLE SRGGT41.KSD_ALERTS_INTRFAC daily stats rep1 TABLE SRGGT41.KSD_BENEFITS daily stats rep1 TABLE SRGGT41.KSD_CASE_BASIC daily stats rep1 TABLE SRGGT41.KSD_CASE_COMP daily stats rep1 TABLE SRGGT41.KSD_CASE_STAGE daily stats rep1 TABLE SRGGT41.KSD_CLIENT daily stats rep1 TABLE SRGGT41.KSD_CLIENT_BASIC daily stats rep1 TABLE SRGGT41.KSD_CLIENT_MONTH daily stats rep1 TABLE SRGGT41.KSD_CLIENT_STAGE daily stats rep1 TABLE SRGGT41.KSD_COURT_HEARINGS daily stats rep1 TABLE SRGGT41.KSD_EP_SEGMENTS daily stats rep1 TABLE SRGGT41.KSD_FOSTERCARE_INC daily stats rep1 TABLE SRGGT41.KSD_INCOME_INTRFAC daily stats rep1 TABLE SRGGT41.KSD_INVOLVEMENT daily stats rep1 TABLE SRGGT41.KSD_ISS_CASE_STAGE daily stats rep1 TABLE SRGGT41.KSD_MED_BENEFITS daily stats rep1 TABLE SRGGT41.KSD_MMIS_CASE_STAGE daily stats rep1 TABLE SRGGT41.KSD_MPI_ADDRESS daily stats rep1 TABLE SRGGT41.KSD_MPI_PHONE daily stats rep1 TABLE SRGGT41.KSD_MPI_RELATION daily stats rep1 TABLE SRGGT41.KSD_PARTICIPATION daily stats rep1 TABLE SRGGT41.KSD_PLACEMENTS daily stats rep1 TABLE SRGGT41.KSD_PROGRAM_BASIC daily stats rep1 TABLE SRGGT41.KSD_PROGRAM_MONTH daily stats rep1 TABLE SRGGT41.KSD_PRSN_PGM_SNAP daily stats rep1 TABLE SRGGT41.KSD_REFERRAL daily stats rep1 TABLE SRGGT41.KSD_REFERRL_CH_INF daily stats rep1 TABLE SRGGT41.KSD_SYSTEM_PART daily select 'stats rep1 TABLE ABMS.'|| table_name || ' daily ' from dba_tables where table_name like 'KSD%' and owner='ABMS' order by 1 stats rep1 TABLE ABMS.KSD_ADDRESS daily stats rep1 TABLE ABMS.KSD_ALERTS_INTRFAC daily stats rep1 TABLE ABMS.KSD_BENEFITS daily stats rep1 TABLE ABMS.KSD_CASE_BASIC daily stats rep1 TABLE ABMS.KSD_CASE_COMP daily stats rep1 TABLE ABMS.KSD_CASE_STAGE daily stats rep1 TABLE ABMS.KSD_CLIENT daily stats rep1 TABLE ABMS.KSD_CLIENT_BASIC daily stats rep1 TABLE ABMS.KSD_CLIENT_MONTH daily stats rep1 TABLE ABMS.KSD_CLIENT_STAGE daily stats rep1 TABLE ABMS.KSD_COURT_HEARINGS daily stats rep1 TABLE ABMS.KSD_EP_SEGMENTS daily stats rep1 TABLE ABMS.KSD_FOSTERCARE_INC daily stats rep1 TABLE ABMS.KSD_INCOME_INTRFAC daily stats rep1 TABLE ABMS.KSD_INVOLVEMENT daily stats rep1 TABLE ABMS.KSD_ISS_CASE_STAGE daily stats rep1 TABLE ABMS.KSD_MED_BENEFITS daily stats rep1 TABLE ABMS.KSD_MMIS_CASE_STAGE daily stats rep1 TABLE ABMS.KSD_MPI_ADDRESS daily stats rep1 TABLE ABMS.KSD_MPI_PHONE daily stats rep1 TABLE ABMS.KSD_MPI_RELATION daily stats rep1 TABLE ABMS.KSD_PARTICIPATION daily stats rep1 TABLE ABMS.KSD_PLACEMENTS daily stats rep1 TABLE ABMS.KSD_PROGRAM_BASIC daily stats rep1 TABLE ABMS.KSD_PROGRAM_MONTH daily stats rep1 TABLE ABMS.KSD_PRSN_PGM_SNAP daily stats rep1 TABLE ABMS.KSD_REFERRAL daily stats rep1 TABLE ABMS.KSD_REFERRL_CH_INF daily stats rep1 TABLE ABMS.KSD_SYSTEM_PART daily