Posts

Showing posts from October, 2024

Postgres: To see when table was last vacuumed or analyzed

 select * from pg_stat_all_tables
Post a Comment
Read more

Postgres: To reorg table and reclaim space

  VACUUM full cfaudit.batch_job_execution; analyze cfaudit.batch_job_execution;
Post a Comment
Read more

Db2: To bring down database and kick off the application

 -- TO KICK OFF THE APPLICATION AND IMMEDIATELY BRING DOWN THE DATABASE AND KEEP IT DOWN $  db2set db2comm= ;db2 force applications all;db2stop force
Post a Comment
Read more

DB2: To bring down standby in preparation for patching

  [tst3ins3@db2tst601   ~] $ db2pd -db wctst10 -hadr   Database Member 0 -- Database WCTST10 -- Standby -- Up 9 days 18:43:52 -- Date 2024-04-04-10.50.50.493594                               HADR_ROLE = STANDBY                           REPLAY_TYPE = PHYSICAL                         HADR_SYNCMODE = NEARSYNC                            STANDBY_ID = 0                  ...
Post a Comment
Read more

Oracle PeopleSoft: To restart PUM

 [root@pshrpum602 ~]# su - psadm2 Last login: Thu Feb 10 03:31:02 CST 2022 [psadm2@pshrpum602 ~]$ psadmin PSADMIN -- PeopleTools Release: 8.59.04 Copyright (c) 1996, 2021, Oracle and/or its affiliates. -------------------------------- PeopleSoft Server Administration --------------------------------   PS_CFG_HOME            /usr/opt/app/psoft/psadm2/psft/pt/8.59   PS_HOME                /usr/opt/app/psoft/pt/ps_home8.59.04   PS_APP_HOME            /usr/opt/app/psoft/pt/hcm_app_home   1) Application Server   2) Process Scheduler   3) Web (PIA) Server   4) Switch Config Home   5) Replicate Config Home   6) Refresh Config Home   q) Quit Command to execute (1-6, q): 3 Picked up _JAVA_OPTIONS: -Djava.security.egd=file:/dev/./urandom ----------------------------- PeopleSoft PIA Administration -----------------------------   PIA Home:...
Post a Comment
Read more

db2: list tables in specific tablespace

You can change 0 (TBS=0) to any tablespace ID in the database:  with TBSSIZE as (select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.TBSPACEID as tbs,DATA_OBJECT_P_SIZE + COL_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d  join SYSIBMADM.ADMINTABINFO t  on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID  union  select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.INDEX_TBSPACEID as tbs,INDEX_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d  join SYSIBMADM.ADMINTABINFO t  on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID  union  select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.LONG_TBSPACEID as tbs,LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d  join SYSIBMADM.ADMINTABINFO t  on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID )  select char(TABSCHEMA,20),ch...
Post a Comment
Read more

Postgres: Table list ordered by row count

  select nspname as schema, relname as tablename,     to_char(reltuples, '999,999,990') as row_count   from pg_class c JOIN pg_catalog.pg_namespace n    ON n.oid = c.relnamespace where relkind='r'     order by reltuples desc SELECT psu.schemaname as "schema", psu.relname AS table_name     , to_char(sum(pg_total_relation_size(psu.relid)), '999,999,999,990') AS include_ndx     , to_char(sum(pg_table_size(psu.relid)), '999,999,999,990') as whole_table     , to_char(sum(pg_relation_size(psu.relid)), '99,999,999,990') AS not_toasts     , to_char(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid)), '999,999,999,990') AS toasts     , to_char(sum(pg_indexes_size(psu.relid)), '99,999,999,990') AS indexes     , to_char(cl.reltuples, '999,999,990') as row_count FROM pg_catalog.pg_statio_user_tables psu     , pg_catalog.pg_class cl where psu.relid = cl.oid group by psu.schema...
Post a Comment
Read more

Postgres: Table list ordered by table size in GB

 SELECT     table_schema || '.' || table_name AS table_full_name,     pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY     pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC; for one table:  SELECT     table_schema || '.' || table_name AS table_full_name,     pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_name = 'batch_job_execution_params';
Post a Comment
Read more

Postgres: Grant all for a schema

 grant usage on schema cfaudit  to cfaudit; grant select on all tables in schema cfaudit to cfaudit; grant all on schema cfaudit to cfaudit;
Post a Comment
Read more

Postgres: to get disk space usage for all tables

 SELECT     table_schema || '.' || table_name AS table_full_name,     pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY     pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC; for one table:  SELECT     table_schema || '.' || table_name AS table_full_name,     pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_name = 'batch_job_execution_params';
Post a Comment
Read more

Postgres: To add replication role to a user

  alter user pgcfadmin with replication; \du <-- to check privileges granted
Post a Comment
Read more

db2: to shutdown and restart the database

 -- TO KICK OFF THE APPLICATION AND IMMEDIATELY BOUNCE THE DATABASE $  db2set db2comm= ;db2 force applications all;db2stop force; db2set db2comm=tcpip;db2start
Post a Comment
Read more

DB2: to disconnect your session

 db2 terminate
Post a Comment
Read more