Groovy DBA

-- To bring down the database  $ db2 force application all; db2stop -- To deactivate the database [dev1ins1@dev601  dev1ins1] $ db2 deactivate db wcdev1 -- To verify there are no applications [db2admin@dev601 dev1ins1 gm15] $ db2 list applications    SQL1611W  No data was returned by Database System Monitor. -- To connect to the database [dev1ins1@dev601  dev1ins1] $ db2start -- To kick off the backup [db2admin@dev601 dev1ins1 gm15] $ /usr/local/db2/admin_scripts/db2.backup_db.ksh -i dev1ins1 -d wcdev1 -o Actual backup command in script: db2.backup_db.ksh db2 backup database "$database" to "$backup_dir" compress without prompting 

 TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS') update       odsadmin.ods_uv_co set       opening_physical =  to_date('01-JAN-1990','DD-MON-YYYY') where       store_srcnum in (9099,0000,0001,0002,0047,0995,0104,0927) /

 SQL> execute dbms_workload_repository.modify_snapshot_settings (interval => 10, retention => 20160); PL/SQL procedure successfully completed. 10 mins and 14 days in mins: execute dbms_workload_repository.modify_snapshot_settings ( interval => 60,  retention => 1576800);    <-- 3 years in minutes To get the current settings: select        extract( day from snap_interval) *24*60+        extract( hour from snap_interval) *60+        extract( minute from snap_interval ) "Snapshot Interval",        extract( day from retention) *24*60+        extract( hour from retention) *60+        extract( minute from retention ) "Retention Interval" from dba_hist_wr_control;

 db2 grant connect on database to user  username ;  db2 grant role db2read  to user username ;

 db2 grant connect on database to user username ; And this is to revoke below? $ db2 "revoke connect on database from user nk152" DB20000I  The SQL command completed successfully.

 [prd1ins1@db2prd601] $ dsmtop -d wcprd1

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...

[prd1ins1@db2prd601] $ dsmtop -d wcprd1

  db2 DROP SCHEMA joeschma RESTRICT;

syscat . tables  example SQL:  (1)  db2 select tabname , tabschema , tbspace from syscat . tables; (2) select TABNAME,CREATE_TIME from syscat.tables where owner='WCS' order by tabname; (3) select tabname, tbspace from syscat.tables where owner='WCS' and tabname like 'X_%'; (4) select tabname, tbspace from syscat.tables where owner='WCS' order by tbspace; $ db2 "describe table syscat.tables"                                 Data type                     Column Column name                     schema    Data type name      Length     Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ TABSCHEMA                       SYSIBM  ...

 In DB2 , the database log is called db2diag.log Once logged in: >db2diag -H 5m  Examples:       1. To display severe errors logged for the last 3 days, enter:               db2diag -gi "level=severe" -H 3d       2. To display log messages recorded during the last 30 minutes, enter:               db2diag -H          This command is equivalent to               db2diag -H 30  or  db2diag -history 30m       3. To display log messages recorded within two hours after midnight on          April 10, 2003, enter:               db2diag -H '+2h:2003-04-10'       4. To display log messages recorded within two hours before midnight on          April 10, 2003, enter:           ...

 select current timestamp as local, current timezone as timezone from sysibm.sysdummy1; Output should look like: 2021-11-10 12:02:38.256947 -60000

> db2set db2_compatibility_vector=08 *restart database for change to take effect >db2 force application all; db2stop >db2start >db2set -all <-- to see the setting is done

 >db2 list applications

SELECT * FROM SYSCAT.TABAUTH WHERE GRANTOR  = 'GRANTOR'   example:   SELECT * FROM SYSCAT.TABAUTH WHERE GRANTOR = 'WSDBA';  SELECT * FROM SYSCAT.TABAUTH WHERE GRANTOR = 'DEV2INS2';

 SELECT * FROM SYSCAT.TABAUTH WHERE GRANTEE  = ' GRANTEE '; example SELECT * FROM SYSCAT.TABAUTH WHERE GRANTEE  = 'HVR';   <-- to see list of tables HVR has access to;

 >/usr/bin/mutt -s "subject " $TO -a attachment < /dev/null

 sqlplus / as sysdba SET lines 140 COL owner_name FORMAT a10; COL job_name FORMAT a20 COL state FORMAT a12 COL operation LIKE owner_name COL job_mode LIKE owner_name SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs; drop table sys.tablename; drop table fullexport.tablename;

 du -ak . | sort -n or just the directory , ignoring other mounted filesystems: du -akx . | sort -n  <-- this gives the largest files    , which is most valuable du -sk * | sort -n      <-- this gives the largest folders 

# free -h               total        used        free      shared  buff/cache   available Mem:           62Gi       3.4Gi       7.2Gi        26Gi        52Gi        31Gi Swap:          30Gi          0B        30Gi

 set linesize 200 select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc;

 Stop tcpip connections like so: db2set db2comm= db2 force applications all; db2stop db2 list applications  <-- to see if there are any connections db2start **note WSDBA is an account that has SYSADM, SYSCTRL or SYSMAINT privileges [wsdba@dev601 dev1ins1 ~]      $ db2 backup database wcdev1 to /usr/opt/app/dbdump  compress without prompting when complete do the following db2set db2comm=tcpip db2stop db2start ps -ef | grep db2sysc  <-- to see which databases are up/down confirm that applications reconnect