Groovy DBA

  1. Current available snapshots in database: select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc; 2. Generate a new snapshot: EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; PL/SQL procedure successfully completed.

 $ db2 "describe table wcs.XGCPMSPBUSINESSPRODUCT"                                 Data type                     Column Column name                     schema    Data type name      Length     Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ COMMERCE_PRODUCT_ID             SYSIBM    VARCHAR                    100     0 No PRODUCTASSOCIATION              SYSIBM    VARCHAR                    100     0 Yes MAIN_IMAGE                      SYS...

 db2 "ALTER TABLE wcs.XGCPMSPBUSINESSPRODUCT ALTER COLUMN STYLE SET DATA TYPE VARCHAR(1000)" 04/12/2022 12:09:23     0   0   SQL1063N  DB2START processing was successful. SQL1063N  DB2START processing was successful.

 $ db2 "select count(*) from wcs.CACHEIVL" 1 -----------   138631855   1 record(s) selected. [stg1ins1@db2stg601 stg1ins1 ~] $ db2 "truncate table wcs.CACHEIVL drop storage ignore delete triggers immediate" DB20000I  The SQL command completed successfully. [stg1ins1@db2stg601 stg1ins1 ~] $ db2 "select count(*) from wcs.CACHEIVL" 1 -----------           0   1 record(s) selected.

 db2 "SELECT TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) as size FROM SYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA"

db2 "SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) as size FROM SYSIBMADM.ADMINTABINFO where tabschema='WCS' and tabname='STAGLOG' group by tabschema,tabname"

db2 "SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) AS SIZE FROM SYSIBMADM.ADMINTABINFO where TABSCHEMA='WCS' group by tabname,tabschema ORDER BY SIZE DESC" SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) + SUM(COL_OBJECT_P_SIZE) +  SUM(INDEX_OBJECT_P_SIZE) + SUM(LONG_OBJECT_P_SIZE) +  SUM(LOB_OBJECT_P_SIZE) + SUM(XML_OBJECT_P_SIZE) AS SIZE_IN_KB  FROM SYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA, TABNAME ORDER BY 3 DESC ;