Netezza emulator download link

https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=swg-im-ibmndn

Advertisements

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.

 

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

 

 

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:

DROP TABLE IF EXISTS  table_name;

 

 

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:

https://blogs.oracle.com/UPGRADE/entry/log_writer_slave_issues_in

 

 

 

 

Checkpoint process (CKPT)

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

  1. When checkpoint occurs, Oracle Database must update the headers of all datafiles to record the details of the checkpoint.
  2. The CKPT process doesn’t write blocks to disk; DBWn always perform that work
  3. When log switch happens, checkpoint is called
  4. Checkpoint is the position in the log file, from which instance recovery begins in case of crash!

The below parameters decides checkpoint interval:

(a) LOG_CHECKPOINT_INTERVAL

(b) LOG_CHECKPOINT_TIMEOUT

(c) FAST_START_MTTR_TARGET

Types of checkpoints:

As per Jonathan Lewis, below link has various types of checkpoints:

https://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/

Check point priority:

As per Jonathan Lewis:

The checkpoints started by “alter system checkpoint” are high priority and synchronous – so you wait for the checkpoint to complete and you will see dirty buffers being copied to disc, and both start and end of background checkpoint will be incremented.  The checkpoint started by “alter system switch logfile” is (in all but very old – possibly pre 8.1 – databases) a low priority checkpoint which is asynchronous, so your session returns immediately, the background start is recorded, but no action need be taken, but over a period of time you will see buffers written.

If you have N log files and do N-1 switches you’ll see Oracle suddenly very desperate to catch up (at least a bit) on the background checkpoints because of the “unable to switch logfile checkpoint not complete” problem that appears.

 

 

 

Database writer process(DBWn)

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

  1. Buffer cache  ==DBWn==>  datafiles
  2. dirty buffer: When buffer in the buffer cache is modified
  3. Cold buffer: as per LRU, the buffer that hasn’t been used in some time
  4. DBWn writes cold,dirty buffers to datafiles
  5. DB_WRITER_PROCESS: specifies number of DBWn processes
  6. When DBWn writes automatically?

(a) When server process cannot find a clean reusable buffer after scanning a threshold number of buffers.

(b) DBWn periodically writes buffers to advance the checkpoint

 

touch command to preserve files original timestamp

Touch has two major usages:

(1) Creates ZERO byte file

(2) Changes file timestamp to current timestamp

However, you can use Touch command to preserve files current timestamp as well. This can be achieved using -r option of touch command.

-r RefFile
Uses the corresponding time of the file specified by the RefFile variable instead of the current time.

Example:

$ ls -ltr 2.sql
-rw-rw-r– 1 ravi ravi 3081 May 20 2016 2.sql

Note: 2.sql file is created on 20/May/2016. Size of 2.sql is 3081

$ touch -r 2.sql 2.sql.origtime

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3081 May 20 2016 2.sql

Note: 2.sql.origtime is created with same timestamp as 2.sql

$ vi 2.sql

Note: Modified 2.sql

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3129 Jan 24 09:55 2.sql

Note: 2.sql size is changed to 3129. Also timestamp of 2.sql is modified

$ touch -r 2.sql.origtime 2.sql

Note: Run touch command to reverse timestamps

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3129 May 20 2016 2.sql

Note: You can see from above that 2.sql file’s timestamp is modified to original