db2: find out agent id from PID

Scenario: The below steps explain to find out AGENT ID for a process id: 20513654

Step1: Get snapshot for all applications running

db2 “connect to TESTDB1”

db2 “get snapshot for applications on TESTDB1” > /tmp/err2

Now, open /tmp/err2 and search for 20513654. Then you see content as below in /tmp/err2:


Application Snapshot

Application handle = 7978
Application status = UOW Waiting
Status change time = 04/11/2017 07:36:23.948027
Application code page = 819
Application country/region code = 1
DUOW correlation token = *LOCAL.db2inst1.170411082659
Application name = cicsas
Application ID = *LOCAL.db2inst1.170411082659
Sequence number = 00001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 04/11/2017 04:26:59.284367
Connect request completion timestamp = 04/11/2017 04:26:59.284827
Application idle time = 29 minutes 18 seconds
CONNECT Authorization ID = CICS
Client login ID = cics
Configuration NNAME of client = SERVER1
Client database manager product ID = SQL10014
Process ID of client application = 20513654
Platform of client application = AIX
Communication protocol of client = Local Client

Inbound communication address = *LOCAL.db2inst1


Step2: From above, the agent id is 7978

/mnt/db2home:> db2 list applications | grep -i 7978
CICS cicsas 7978 *LOCAL.db2inst1.170411082659 TESTDB1 1

Step3: Kill the application id 7978

/mnt/db2home:> db2 “force application (7978)”
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.





Oracle: RMAN connections (vs) sqlplus connection

RMAN connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database.

The only difference is that RMAN connections to a target or auxiliary database require either the SYSDBA or SYSBACKUP privilege. Any user can be granted this privilege.


$ rman

Recovery Manager: Release – Production on Mon Apr 10 09:31:25 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target “ravi@TESTDB1 as sysbackup”

target database Password:
connected to target database: TESTDB1 (DBID=123456789)


As a best practise: As shown above, enter passwords in RMAN only when requested by an RMAN prompt. Good security practise requires that you not enter passwords in plain text on command line.


Ref: https://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89348




Oracle: Central inventory is not locked

Today while installing Oracle 12c R1, I have got an error that says “Central inventory is not locked”

Solution: It failed to acquire a lock on Inventory.

$ cat /etc/oraInst.loc

Then, went to /u01/app/ directory and removed “locks” directory. This fixed the issue.

Netezza emulator download link


informix: list all database servers running

Step1: Every informix server has /INFORMIXTMP folder

[ravin@test1 ~]$ cd /INFORMIXTMP
[ravin@test1 INFORMIXTMP]$ ls -la
total 12
drwxrwxr-t. 2 informix informix 4096 Feb 21 20:52 .
dr-xr-xr-x. 23 root root 4096 Feb 1 18:26 ..
-rw-rw-r–. 1 root informix 21 Jan 25 20:29 .infxdirs
srwxrwxrwx. 1 informix informix 0 Feb 20 21:26 VP.ids13.10100s
[ravin@test1 INFORMIXTMP]$ cat .infxdirs

step2: Set INFORMIXDIR environment variable to above output

[ravin@test1 INFORMIXTMP]$ export INFORMIXDIR=/mnt/informix/IDS121
[ravin@test1 INFORMIXTMP]$ cd $INFORMIXDIR/bin
[ravin@test1 bin]$ ./onstat -g dis
Your evaluation license will expire on 2017-04-25 00:00:00
shared memory not initialized for INFORMIXSERVER ‘<NULL>’
There are 2 servers found
Server : ids121
Server Number : 0
Server Type : IDS
Server Status : Down
Server Version: IBM Informix Dynamic Server Version 12.10.FC5TL
Shared Memory : 0x44000000
INFORMIXDIR : /mnt/informix/IDS121
ONCONFIG : /mnt/informix/IDS121/etc/onconfig.ids121
SQLHOSTS : /mnt/informix/IDS121/etc/sqlhosts
Host : test1

Server : ids13
Server Number : 1
Server Type : IDS
Server Status : Up
Server Version: IBM Informix Dynamic Server Version 12.10.FC5TL
Shared Memory : 0x44000000
INFORMIXDIR : /mnt/informix/IDS121
ONCONFIG : /mnt/informix/IDS121/etc/onconfig.ids13
SQLHOSTS : /mnt/informix/IDS121/etc/sqlhosts
Host : test1

Note: Just knowing /INFORMIXDIR exists is enough to find out Informix database server information.


query to map table and dbspace

The below query can be used to map a table and dbspace in IBM Informix:

database sysmaster;
select dbinfo( “DBSPACE” , pe_partnum ) dbspace,
from sysptnext, outer systabnames
where pe_partnum = partnum
and tabname = ‘CUSTOMER’
order by dbspace;


database sysmaster;
select s2.name,s1.tabname,s1.owner from systabnames s1,sysdbspaces s2
where s2.dbsnum=trunc(s1.partnum/1048576)
and tabname = ‘CUSTOMER’
order by name,tabname,owner

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Problem: When trying to truncate a table, getting below error

SQL> truncate table PROD.CUSTOMER;
truncate table PROD.CUSTOMER

ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution: This error comes when we are trying to truncate a table which is a parent in a referential integrity. Before you truncate this parent table, you have to disable constraints on the child tables.

The below query will help you to get information about dependent constraints:

select ‘ALTER TABLE ‘ || OWNER || ‘.’ || TABLE_NAME||’ DISABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;’ from dba_constraints
where R_CONSTRAINT_NAME='<Primary Key Constraint name from CUSTOMER table>’;

Once the above query returns disable commands, run the disable constraints, then try to drop Truncate table. It should work at this time!

Credits: The query is taken from http://stackoverflow.com/questions/4812146/truncate-table-in-oracle-getting-errors



db2 luw: rename a column

Below is an example to rename a column:

alter table prod.TEST_line_2016 rename “SHIP_” to “SHIP_$_MONTH”

informix: drop if table exists

In an SQL script, the below 2 scenarios will through an error:

(1) trying to drop a temp table that doesn’t exist

(2) Creating a temp table that already exists.

The below IF EXISTS feature helps to solve this problem:




Log Writer Process (LGWR)

This is one background process that you can’t ignore for Oracle Certification Exam (1z0 062). Below are important points on LGWR from certification point of view:

Two important terms we need to understand before we learn more about LGWR:

(a) Redo log buffer: Buffer always refer memory. This buffer exists in memory. We have only one Redo Log Buffer. Server processes write change details (DDL/DML) into this Redo log buffer

(b) Redo log file: File always refers to data on disk.

DDL/DML  ==Server processes=> Redo Log Buffer  ==LGWR==> Redo Log file

When LGWR writes data from Redo log buffer to Redo log file?

  1. When user process commits a transaction
  2. Every three seconds
  3. When redo log buffer is one-third full
  4. When log switch
  5. When a DBWn process writes modified buffers to disk , if necessary

write-ahead protocol: Before DBWn can write a modified buffer, all redo records associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it write out the data buffers.

fast commit: When a transaction commits, LGWR write entries to log buffer and then to disk immediately. But, changed blocks are deferred until it is more efficient to write them.

System Change Number (SCN): When a user commits a transaction, the transaction is assigned a SCN. These SCN numbers are recorded in redo log

Oracle 12c: From 12c, you start seeing more than one log writer process. The Log Writer (LGWR) creates worker processes (slaves, LGnn) to improve the performance of writing to the redo log.

$ ps -ef | grep ora_lg
prod 5505318 1 0 Dec 10 – 51:14 ora_lg02_prod
prod 6095354 1 0 Dec 10 – 546:27 ora_lg01_prod
prod 3998342 1 0 Dec 10 – 15:24 ora_lg03_prod
prod 5309302 1 8 Dec 10 – 3113:41 ora_lg00_prod
prod 5505940 1 2 Dec 10 – 1083:48 ora_lgwr_prod
prod 11470186 60293956 0 07:48:39 pts/0 0:00 grep ora_lg

However, these multiple log writer slaves might cause a log writer deadlock as mentioned in below link. Temporary solution is to set _use_single_log_writer to TRUE, but applying patch is a permanent solution: