Monthly Archives: February, 2017

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
/mnt/informix/IDS121

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.

 

Advertisements

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,
dbsname[1,10],
tabname
from sysptnext, outer systabnames
where pe_partnum = partnum
and tabname = ‘CUSTOMER’
order by dbspace;

(or)

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