Posts

Postgres: To spool to a file and call a SQL script

 cfprod-> \o h:/ps2.txt cfprod-> \i h:/input.sql cfprod-> \o
Post a Comment
Read more

Db2: Get DB size

 [prd1ins1@db2prd601  ~] $ db2 connect to wcprd1    Database Connection Information  Database server        = DB2/LINUXX8664 11.5.8.0  SQL authorization ID   = PRD1INS1  Local database alias   = WCPRD1 [prd1ins1@db2prd601  ~] $ db2 "call get_dbsize_info(?,?,?,-1)"   Value of output parameters   --------------------------   Parameter Name  : SNAPSHOTTIMESTAMP   Parameter Value : 2023-12-07-14.49.14.111291   Parameter Name  : DATABASESIZE   Parameter Value : 117597786112   Parameter Name  : DATABASECAPACITY   Parameter Value : 412211273728   Return Status = 0 [prd1ins1@db2prd601  ~]
Post a Comment
Read more

Oracle: Drop redo log files

set linesize 200 col MEMBER format a60 select group#, status, member from v$logfile order by group#; ALTER DATABASE DROP LOGFILE GROUP 22; alter database drop logfile  '/usr/opt/app/oracle/admin/hrprod/redo01/redo01g22.log'; alter database drop logfile '/usr/opt/app/oracle/admin/hrprod/redo02/redo02g22.log';
Post a Comment
Read more

Oracle: Increase redo log size

 select group#,sequence#,bytes,archived,status from v$log; ALTER DATABASE DROP LOGFILE GROUP 15; alter database add logfile group 15 ('/usr/opt/app/oracle/admin/hrprod/redo01/redo01g15.log', '/usr/opt/app/oracle/admin/hrprod/redo02/redo02g15.log') size 1032M reuse; SQL>  alter system switch logfile; Based on the current redo log state and need make the old groups inactive  SQL>   alter system archive log group 1;
Post a Comment
Read more

DB2: To get list of indexes on a table

 select * from syscat.indexes where tabname='CATENTREL' and tabschema='WCS';
Post a Comment
Read more

db2: to get role name and grantee

 db2 "select substr(rolename,1,18) as rolename, substr(grantee,1,18) as grantee from syscat.roleauth order by rolename, grantee with ur" Example: TIBCO              ESBGCPDB2PRDUSER   UC4                UC4GCPDB2PRDUSER  
Post a Comment
Read more

DB2: to export a table

 $ db2 "select count(*) from wcs.XGCPMWSTOREINVENTORY" 1 -----------     3604155   1 record(s) selected. [prd1ins1@db2prd601  prd1ins1] $ db2 -v "export to /usr/opt/app/dbdump/prd1ins1/XGCPMWSTOREINVENTORY_prod.del of del lobs to /usr/opt/app/dbdump/prd1ins1 select * from wcs.XGCPMWSTOREINVENTORY" export to /usr/opt/app/dbdump/prd1ins1/XGCPMWSTOREINVENTORY_prod.del of del lobs to /usr/opt/app/dbdump/prd1ins1 select * from wcs.XGCPMWSTOREINVENTORY SQL3104N  The Export utility is beginning to export data to file "/usr/opt/app/dbdump/prd1ins1/XGCPMWSTOREINVENTORY_prod.del". SQL3105N  The Export utility has finished exporting "3604155" rows. Number of rows exported: 3604155
Post a Comment
Read more