Vinay's DB2 blog - DB2 admin concepts, development concepts

SQL Interview Questions in ORACLE

noreply@blogger.com (Vinay Kumar) — Wed, 04 Mar 2020 16:38:00 +0000

1) What is the difference between ON DELETE SET NULL clause and ON DELETE CASCADE clause in ORACLE.

ON DELETE SET NULL:

drop table students;

create table Students

(

id int,
name varchar(10),

dept_id int constraint c1 primary key);

insert into students values(1,'vishnu',11);

insert into students values(2,'vinay',12);

insert into students values(3,'Teja',13);

insert into students values(4,'Ashwin',14);

drop table dept;

create table dept

(

dept_id int constraint c2 references students(dept_id) on delete set null,

dept_name varchar(10),

subject_name varchar(18));

insert into dept values(11,'science','Biology');

insert into dept values(11,'science','Physics');

insert into dept values(11,'science','Chemistry');

insert into dept values(12,'social','social studies');

insert into dept values(13,'languages','English');

insert into dept values(13,'languages','Spanish');

delete from students where id=1;

select * from dept;

ON DELETE CASCADE;

drop table students;

create table Students

(

id int,

name varchar(10),

dept_id int constraint c1 primary key);

insert into students values(1,'vishnu',11);

insert into students values(2,'vinay',12);

insert into students values(3,'Teja',13);

insert into students values(4,'Ashwin',14);

drop table dept;

create table dept

(

dept_id int constraint c2 references students(dept_id) on delete cascade,

dept_name varchar(10),

subject_name varchar(18));

insert into dept values(11,'science','Biology');

insert into dept values(11,'science','Physics');

insert into dept values(11,'science','Chemistry');

insert into dept values(12,'social','social studies');

insert into dept values(13,'languages','English');

insert into dept values(13,'languages','Spanish');

delete from students where id=1;

select * from dept;

2) What is Function based index.

Index is created on the column when a function is included in where clause of a sql query.

CREATE INDEX members_last_name_fi ON members(UPPER(last_name));

3) Decode function

drop table students;

create table Students (
id int,
name varchar(10),
dept_id int);

insert into students values(1,'vishnu',11);

insert into students values(2,'vinay',12);

insert into students values(3,'Teja',13);

insert into students values(4,'Ashwin',14);

select id,name, decode(id,
1,'science',
2,'maths',
3,'social'

) as subjects from students;

4) Tell us if NULL is equal to NULL in ORACLE ?

select decode(null,null,'true equal in oracle','false not equal in oracle') from dual;

5) Complex views in ORACLE.

CASE 1:

View created on more than one base table (i.e join of two tables) with key constraint.

drop table students;

create table Students

(

id int,

name varchar(10),

dept_id int constraint c1 primary key);

insert into students values(1,'vishnu',11);

insert into students values(2,'vinay',12);

insert into students values(3,'Teja',13);

insert into students values(4,'Ashwin',14);

drop table dept;

create table dept
(
dept_id int constraint c2 references students(dept_id),

dept_name varchar(10),

subject_name varchar(18));

insert into dept values(11,'science','Biology');

insert into dept values(11,'science','Physics');

insert into dept values(11,'science','Chemistry');

insert into dept values(12,'social','social studies');

insert into dept values(13,'languages','English');

insert into dept values(13,'languages','Spanish');

select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;

create view v1 as select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;

select * from v1;

update v1 set subject_name='Maths' where id=1;

select * from students;

select * from dept;

view updated successfully and base tables also updated as per where criteria.

CASE 2:

create view v2 as select b.subject_name,count(b.subject_name) as count from students a, dept b where a.dept_id=b.dept_id group by b.subject_name;

update v2 set subject_name='Telugu' where subject_name='Maths';

update is illegal on this view because update cannot be performed on the view which has aggregate function and group by clause.

select * from v2;

When can we cannot update a view?

The SELECT statement which is used to create the view should not include the following

1) GROUP BY clause or ORDER BY clause.
2) DISTINCT keyword.
3) An aggregate or analytic function.
4) A collection expression in a SELECT list.
5) A sub-query in a SELECT list.
6) The view should not be created using nested queries or complex queries.
7) view cannot update if view contains pseudo columns or expressions.

6) How to replace user defined values in place of NULL.

USING NVL function.

a) select NULL+100 from dual gives null as output

b) select 0+100 from dual gives 100 as output.

How to get 100 in the first query.

select NVL(NULL,0) + 100 from dual;

USING NVL2 function.

We can replace user defined value in place of NULL and exact value or not null in place of NOT Null value.

select(a1,a2,a3) from dual;

if a1 is NULL then it prints a3.

if a1 is NOT NULL then it prints a2.

7) What is the use of COALESCE function.

This function gives first not null value in the parameter list.

drop table students;

create table Students

(

id int,

name varchar(10),

phone_num int,

land_num int);

insert into students values(1,'vishnu',123,NULL);

insert into students values(2,'vinay',456,92345);

insert into students values(3,'Teja',NULL,95432);

select id,name,coalesce(phone_num,land_num) from students;

8) Mutating table error.

When a trigger is raised for performing the DML operations on the table and the same time DML operations are performed on the same table then this mutating table error occurs.

drop table Employee;

create table Employee(id int,name varchar2(10),sal int);

insert into Employee values(1,'Vinay',4500);

create or replace trigger mutating_trig
after insert or update or delete on Employee
for each row
enable
begin
if inserting then
insert into Employee(id,name,sal) values(:new.id,:new.name,:new.sal);
elsif deleting then
delete from employee where id=:old.id;
elsif updating then
update Employee set id=:old.id where id=:new.id;
end if;
end;

insert into Employee values(2,'Teja',45000);

delete from Employee where id=1;

update Employee set id=2 where id=1;

For all the above three DML statements, mutating table error occurs as below.

ORA-04091: table SQL_BDJTXDPJXHYOBFTDBMDGFJKON.EMPLOYEE is mutating, trigger/function may not see it ORA-06512: at "SQL_BDJTXDPJXHYOBFTDBMDGFJKON.THIRD_SAL", line 7

ORA-06512: at "SYS.DBMS_SQL", line 1721

9) select count(1) From Dual;What Is Output?

10) How to delete duplicate records from table without Distinct Clause

Using row id

drop table Employee;

create table Employee(id int,name varchar2(10),sal int);

insert into Employee values(1,'Vishnu',2300);

insert into Employee values(1,'Vinay',4500);

insert into Employee values(3,'Teja',4500);

insert into Employee values(4,'Ashwin',1100);

insert into Employee values(5,'Raj',45000);

select rowid,id,name,sal from Employee;

select max(rowid) from Employee group by id,name,sal;

delete from Employee where rowid not in ( select max(rowid) from Employee group by

id,name,sal);

11) Difference between Primary Key and Unique Key.

Primary key is the combination of unique key and not null constarint.

Unique key accepts multiple nulls and unique values.

Below is the example:

Table Employee has unique constraint on id column and primary key constraint on dept_id column.

drop table Employee;

create table Employee(id int ,
dept_id int,
name varchar2(10),
sal int,
constraint c1 UNIQUE(id),
constraint c2 primary key(dept_id));

Now try to insert below rows.

1) insert into Employee values(1,11,'Vishnu',2300);

row inserted successfully.

2) insert into Employee values(2,null,'Vishnu',2300);

cannot insert NULL in primary key column.

3) insert into Employee values(null,22,'Vishnu',2300);

unique key accepts NULL. Row inserted.

4) insert into Employee values(null,33,'Vishnu',2300);

row inserted as unique key column accepts multiple null values.

12) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?

Cursor is a pointer which points result set and retrieve each row at a time from the result set.

Types of Cursors:

Implicit
Explicit

Implicit cursor:

It is used when sql statement return only one row.

It has 4 attributes

SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.

Example:

drop table Employee;

create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));

insert into Employee values(1,11,'Vishnu',230);
insert into Employee values(2,22,'Vinay',130);
insert into Employee values(3,33,'Teja',24000);
insert into Employee values(4,44,'Ashwin',2500);

select * from Employee;

declare
v_row Employee%ROWTYPE;
begin
select * into v_row from Employee where id=1;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| '' ||v_row.sal);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
if SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No row selected');
elsif SQL%FOUND then
DBMS_OUTPUT.PUT_LINE('1 row selected');
end if;
end;

output:

Explicit cursor:

It is used when sql statement returns more than one row.

It has also 4 attributes.

CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.

Example:

declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
exit when c1%NOTFOUND;
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;

While Executing the above block i got two identical rows at the last.

1 11 Vishnu 230

2 22 Vinay 130

3 33 Teja 24000

4 44 Ashwin 2500

This is because when the cursor coming to the last row, fetch statement will give the last value to v_row,then prints v_row.
Notice at this time, %NOTFOUND returns false because there is a row,so loops one more time.
This time, fetch statement will not give anything to v_row,v_row stays the same as last time, which is the last row,then print out v_row again.
This time, %NOTFOUND returns true, terminate the loop.

To avoid the above failure change the order as below.

declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;

Expected output:

1 11 Vishnu 230

2 22 Vinay 130

3 33 Teja 24000

4 44 Ashwin 2500

13) What Is Exception? Types Of Exceptions?

An exception is an error which occurs at run time.

Predefined

User defined

Predefined exceptions are the exceptions in Oracle Standard Package maintained by Oracle server.

User defined Exceptions are raised explicitly in PL/SQL Program.

They are defined in three ways.

1) Using variable of Exception data type.
2) Using Raise_Application_Error method.
3) Using Pragram Exception_INIT function.

Lets see 1st way using variable of Exception data type.

declare
v_row Employee%ROWTYPE;

/*declaration of exception data type*/
v_excep exception;

cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
end if;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);

/* raise of exception block*/
if(c1%ROWCOUNT < 5)
then
raise v_excep;
END IF;

/* Exception handler bloc*/
exception when v_excep then
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || ' row count is less than 10');
close c1;

end;

Output:

Statement processed.
1 11 Vishnu 230
2 22 Vinay 130
3 33 Teja 24000
4 44 Ashwin 2500
4

4 row count Is less than 10

14) What Is Sqlcode And Sqlerr?

SQLCODE : Number of the error
SQLERR: message related to the SQL Code.

Example:

drop table Employee;
create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));

insert into Employee values(null,null,'Ashwin',2500);

In the above example , inserted Null value in the primary key column. Below is the error displayed.

ORA-01400: cannot insert NULL into ("SQL_RVHJEHXHUAGFREPISOPPDRICY"."EMPLOYEE"."DEPT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721

ORA-01400 : SQLCODE

cannot insert NULL into ("SQL_RVHJEHXHUAGFREPISOPPDRICY"."EMPLOYEE"."DEPT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721 : SQLERR

15) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?

Reference to a cursor. It is a user defined datatype in PL/SQL that refers to a special type of cursor variable.

strong ref cursor = creating ref cursor datatype with return type as record + creating cursor variable with ref cursor datatype

weak ref cursor= creating ref cursor datatype with no return type + creating cursor variable with ref cursor datatype

sys ref cursor = creating cursor variable with sys ref cursor data type

Types:

Strong Ref Cursor: Strong ref cursors have return type and return type should be record data type.

Weak Ref Cursor: Do not have any return type

Sys Ref cursor is an Oracle built in cursor variable.

Strong referential cursor:

/* define the user defined ref cursor data type as below */
declare
type my_refcur is ref cursor return Employee%ROWTYPE;

/* declare cursor variable with the user defined ref cursor data type as below */
c1 my_refcur;

/* declare a local variable same as a referetial cursor */
v_fetch Employee%ROWTYPE;

begin
open c1 for select * from Employee where id=1;
fetch c1 into v_fetch;
dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name);
close c1;

end;

Weak referential cursor:

declare

type my_refcur is ref cursor;

c1 my_refcur;

v_fetch Employee%ROWTYPE;

begin

open c1 for select * from Employee where id=1;

fetch c1 into v_fetch;

dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name);

close c1;

end;

sys referential cursor:

drop table Employee;

create table Employee(id int,dept_id int constraint c2 references Department(dept_id),name varchar2(10),sal int);

insert into Employee values(1,11,'Vishnu',230);

insert into Employee values(2,22,'Vinay',130);

insert into Employee values(3,33,'Teja',24000);

declare

my_refcur SYS_REFCURSOR;

v_fetch Employee%ROWTYPE;

begin

open my_refcur for select * from Employee where id=1;

fetch my_refcur into v_fetch;

dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name || v_fetch.sal);

close c1;

end;

16) How to Give Privilege to One Procedure In Package?

GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER

17) What Is %Type & %Rowtype? What Is Main Use?

% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.

declare

d_id department.dept_id%type;

v_row employee%ROWTYPE;

BEGIN

select dept_id into d_id from Department where dept_id=11;

select * into v_row from Employee where id=1;

dbms_output.put_line(v_row.id || ' ' || v_row.name || ' ' ||d_id);

end;

Statement processed.

1 Vishnu 11

13) BULK COLLECT

A PL/SQL Code contains SQL statements and PL/SQL Statements. PL/SQL code gets processed in PL/SQL Engine and SQL code gets processed in SQL Engine. While processing the block of code ,PL/SQL Engine comes across the SQL statements. Context switching happens between PL/SQL engine to SQL engine in order to process SQL Code. Performance of the code reduces if number of context switches increases between PL/SQL engine and SQL engine. To overcome this we use BULK Collect.

Example:

declare
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
end;

14) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?

Using BULK COLLECT, the query performance increases as it minimises the context switching between sql engine and pl/sql engine but performance of database decreases . This is because when the rows are fetched at once, memory overload happens which in turn degrades the performance of database despite the query performance. So LIMIT clause is used which limits the number of rows to be fetched or retrieved at once. Hence the memory over load decreases and performance of query and database increases.
BULK Collect clause can be used in FETCH into statement.

declare
cursor c1 is select name from Employee;
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
open c1;
fetch c1 bulk collect into nt_var limit 3;
for ix in 1..nt_var.count
loop
dbms_output.put_line(nt_var(ix));
end loop;
close c1;

end;

FORALL statement is also used to reduce the context switching between PL/SQL Engine and SQL Engine similar to BULK collect but in reverse manner.
1) BULK collect fetches records from table to collection where as FORALL fetches records from collection to tables.

2) The FORALL allows to perform the DML operations on data in bulk.

It is similar to that of FOR loop statement except in FOR loop things happen at the record level whereas in FORALL there is no LOOP. Instead the entire data present in the given bounds is processed at the same time.

Below example inserts data in table through collection variable called Associative Array.

drop table Employee;
create table Employee(id int);
declare
type col_var is table of number index by pls_integer;
usr_var col_var;
begin
for i in 1..10
loop
usr_var(i) := i;
end loop;
forall ix in 1..10
insert into Employee(id) values(usr_var(ix));
end;

select * from Employee;

15) What Is Merge Statement? Give Me Syntax?

drop table Employee;
create table Employee(id int,dept_id int,subject varchar(20));
insert into Employee values(1,11,null);
insert into Employee values(2,22,null);
insert into Employee values(3,22,null);
insert into Employee values(4,33,null);
insert into Employee values(5,33,null);
insert into Employee values(6,44,null);
create table dept(dept_id int, subject varchar(20));
insert into dept values(11,'Science');
insert into dept values(22,'Maths');
insert into dept values(33,'Chemistry');

Merge into Employee e1
using dept d1
on (d1.dept_id=e1.dept_id)
when matched then
update set e1.subject=d1.subject
when not matched then

insert values(d1.dept_id,d1.subject);

16)What Is View? Use Of View's?

View is a virtual table which can be derived based on a query

Uses:

Readability and confidentiality

19) Which Will Fire Default First Statement Level Or Row Level Trigger?

First, statement level triggers are fired, and then row level triggers are fired when more than one trigger is fired by a single SQL statement.

20) Can you alter procedure with in package?

All objects in a package are stored as a unit, the ALTER PACKAGE statement

re compiles all package objects. You cannot use the ALTER PROCEDURE statement or ALTER FUNCTION statement to recompile individually a procedure or function that is part of a package.

drop table dept;

create table dept(dept_id int, subject varchar(20));

insert into dept values(11,'Science');

insert into dept values(22,'Maths');

insert into dept values(33,'Chemistry');

create or replace package pkg is

function usr_fun return varchar;

procedure usr_proc(b int,ab varchar);

end pkg;

create or replace package body pkg is

function usr_fun return varchar is

begin

return 'vishnu';

end usr_fun;

procedure usr_proc(b int,ab varchar) is

begin

insert into dept values(b,ab);

end usr_proc;

end pkg;

begin

dbms_output.put_line(pkg.usr_fun);

pkg.usr_proc(44,'Physics');

end;drop table dept;

create table dept(dept_id int, subject varchar(20));

insert into dept values(11,'Science');

insert into dept values(22,'Maths');

insert into dept values(33,'Chemistry');

create or replace package pkg is

function usr_fun return varchar;

procedure usr_proc(b int,ab varchar);

end pkg;

create or replace package body pkg is

function usr_fun return varchar is

begin

return 'vishnu';

end usr_fun;

procedure usr_proc(b int,ab varchar) is

begin

insert into dept values(b,ab);

end usr_proc;

end pkg;

begin

dbms_output.put_line(pkg.usr_fun);

pkg.usr_proc(44,'Physics');

end;

21) Is it possible to open cursor which is in package in another procedure?

Yes its possible.

Below is the example.

drop table Employee;

create table Employee(id int,dept_id int,subject varchar(20));

insert into Employee values(1,11,null);

insert into Employee values(2,22,null);

Creation of package 1

create or replace package pkg1 is

cursor c_emp is select * from Employee;

r_emp c_emp%ROWTYPE;

end;

/

--Here is a different package that references the cursor

create or replace package pkg2 is
procedure usr_proc;
end pkg2;

create or replace package body pkg2 is

procedure usr_proc is

begin

open pkg1.c_emp;

loop

fetch pkg1.c_emp into pkg1.r_emp;

exit when pkg1.c_emp%NOTFOUND;

DBMS_OUTPUT.put_line(pkg1.r_emp.id);

end loop;

close pkg1.c_emp;

end;

end;

22) What is substr()&instr()?

INSTR and SUBSTR are string functions.

Position of a sub string in a main string.

SELECT INSTR('ORACLE','L') FROM DUAL;

Substring from a main string.

SELECT substr('ORACLE',3,2) FROM dual;

23) Difference between case and decode?

CASE is a statement where as DECODE is a function.

CASE can be used in both SQL and PLSQL . But DECODE can be used only in SQL.

CASE is used in where clause But you cant use DECODE in where clause.

Example 1:

drop table Employee;

create table Employee(id int,dept_id int);

insert into Employee values(1,11);

insert into Employee values(2,22);

select id,dept_id,
(case
when id=1 then 'Maths'
when id=2 then 'Science'
else 'no sub'
end) as subjects

from Employee;

Example 2:

select * from Employee where id<4 order by id,case when id=2 then dept_id else id end;

24) Can you use sysdate in check constraints? If no, why?

Answer:

A check constraint cannot refer a function like SYSDATE.

You can get a different result every time you call it.

25) Difference between column level constraints & table level constraints?

Column constraints and table constraints have the same function. the difference is in how

you specify them.

Table constraints allow you to specify constraints in more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition.

Column-level constraints (except for check constraints) refer to only one column.

26) What is optimiser?

The optimiser generates a set of plans for the SQL statement based on available access paths and hints.

The optimiser estimates the cost of each plan based on statistics in the data dictionary.

The optimiser compares the plans and chooses the plan with the lowest cost.

27) What is pivot operator?

PIVOT:

Pivot operator transposes an aggregated row of a table into a column.

The distinct row values become the columns in the output and aggregated column value

places itself under the appropriate pivoted column.

drop table Student;

create table Student(id int,dept_id int,results varchar(10));

insert into Student values(1,11,'Fail');
insert into Student values(2,22,'Fail');
insert into Student values(3,22,'pass');
insert into Student values(4,11,'Fail');
insert into Student values(5,33,'pass');

select * from Student pivot ( count(id) for dept_id in (11,22,33)) order by results;

28) SQL loader

SQL Loader loads data from an external flat file into a table in the database.

The input data file stores delimited or raw data like CSV, tab-delimited, and pipe-delimited.

The parameter file stores the location of the input/output files.

The control file specifies on how data is loaded.

The parameter file specify the user id that will connect to the Oracle database.

The email.bad file stores invalid data.

The discard file contains records that did not match any record-selection criteria specified in

the control file.

29) What is Raise_application_error.

It is a procedure of package DBMS_STANDARD that allows user to raise user_defined error messages from pl/sql block.

declare
age number;
begin
age := 20;
if(age <21)
then
raise_application_error(-20008,'Your age is below limit');
end if;

end;

Note: The 1st parameter of raise_application error procedure should always be in range between -20,000 to -20,999.

30) How many triggers can be applied to a table?

A maximum of 12 triggers applied to one table.

31) Difference between RANK() and Dense_Rank () function.

Rank Function provides rank to each value in a column based on the column specified by

order by clause.

drop table Student;

create table Student(id int,dept_id int,marks int);

insert into Student values(1,11,90);
insert into Student values(2,22,71);
insert into Student values(3,22,71);
insert into Student values(4,11,32);
insert into Student values(5,33,10);

select id,dept_id,marks,rank() over (order by marks desc) from Student;

Dense Rank function:

Similar to RANK function however the DENSE_RANK function does not skip any ranks

when the ranks are similar in the preceding rows.

select id,dept_id,marks,dense_rank() over (order by marks desc) from Student;

32) Can we avoid index by using hint?

Optimiser hint is a code written with in an sql query which controls or instructs the optimizer.

Instructs the optimiser to not to use the named index in determining a plan.

NO_INDEX(<table_name> < index_name>)

SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename FROM emp, dept WHERE

emp.deptno = dept.deptno;

33) Can we delete primary key of one table without changing foreign key of second

table?

Yes Using on delete cascade.

ON DELETE CASCADE;

drop table students;

create table Students

(

id int,

name varchar(10),

dept_id int constraint c1 primary key);

insert into students values(1,'vishnu',11);

insert into students values(2,'vinay',12);

insert into students values(3,'Teja',13);

insert into students values(4,'Ashwin',14);

drop table dept;

create table dept

(

dept_id int constraint c2 references students(dept_id) on delete cascade,

dept_name varchar(10),

subject_name varchar(18));

insert into dept values(11,'science','Biology');

insert into dept values(11,'science','Physics');

insert into dept values(11,'science','Chemistry');

insert into dept values(12,'social','social studies');

insert into dept values(13,'languages','English');

insert into dept values(13,'languages','Spanish');

delete from students where id=1;

select * from dept;

34) How to print all dates of particular month in a table (if it is not present in column)

select to_date('01.12.2019','dd.mm.yyyy')+level-1 from dual

connect by level <= TO_CHAR(LAST_DAY(to_date('01.12.2019','dd.mm.yyyy')),'DD');

35) How can we print 1 to 10 numbers in single query.

select level from dual connect by level<=10

36) If the view is updated, is it updated in base table.

Yes, base table is updated

drop table emp;

create table emp(id int,name varchar2(10),location varchar2(10));

insert into emp(id,name,location) values(1,'vishnu','chennai');

insert into emp(id,name,location) values(2,'priya','US');

drop view v1;

create view v1 as select id,location from emp;

update v1 set location='Bhuva' where id=2;

select * from emp;

1 vishnu chennai

2 priya Bhuva

37) What are collections in Oracle.

A collection is an ordered group of elements having the same data type.

There are two types of collection:

1) Persistent.

2) Non Persistent.

Persistent collection are stored physically in database and can be accessed when needed.

where as Non Persistent collection are available only for one session.

PL/SQL provides three collection types.

Persistent collection:

Nested table

Variable-size array or Varray

Non Persistent collection:

Associative array

38) How to create a Nested table collection.

It is a Persistent collection which can be create as a seperate database object or can be created in PL/SQL block. This is an unbounded collection.

create table Employee(id int,dept_id int,name varchar2(10),sal int);

insert into Employee values(1,11,'Vishnu',230);

insert into Employee values(2,22,'Vinay',130);

insert into Employee values(3,33,'Teja',24000);

declare

type nt_type is table of varchar(10);

nt_var nt_type;

begin

select name bulk collect into nt_var from Employee;

dbms_output.put_line(nt_var(1));

end;

39) How to create a Varray collection.

It is a Persistent collection which can be create as a seperate database object or can be created in PL/SQL block. They are bounded collection. Hence in the below example the varray holds only fixed amount of elements as highlighted.

declare

type nt_type is varray(5) of varchar(10);

nt_var nt_type;

begin

select name bulk collect into nt_var from Employee;

dbms_output.put_line(nt_var(1));

end;

40) How to create an Associative Array

It is a Non Persistent collection which cannot be create as a separate database object. It can be created in PL/SQL block. They are unbounded collection similar to Nested table with an additional clause index by.

select * from Employee;

41) Difference between CHAR,VARCHAR,VARCHAR2

VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2

can stores the 4000 bytes of characters.

VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not

occupy the space.

CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded

NULL and more over the VARCHAR2 is variable length and no storage space is taken for null values.

42) Difference between Raise ,Raise_application_error and pragma exception_init in oracle.

RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-

defined exception. It is also used to raise standard oracle exception. Without showing an

actual message of exception, RAISE will just raise an exception and fails the execution of

the code.

RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an

error with number and user understandable error message. Exception includes both ORA

error and Application error.

The pragma EXCEPTION_INIT associates an exception name with an Oracle error

number.Exception includes ORA error.

43) How to raise pragma Exception INIT.

declare
excep_age exception;
age number := 17;
pragma exception_init(excep_age,-20008);
begin
if (age<18)
then
raise_application_error(-20008,'age is below limit');
end if;

Exception when excep_age then
dbms_output.put_line(SQLERRM);
end;
/

44) Example of predefined exception in pl/sql.

NO_DATA_FOUND

ZERO_DIVIDE

TOO_MANY_ROWS

INVALID_CURSOR

CURSOR_ALREADY_OPEN.

45) What are INSTEAD of triggers?

The INSTEAD OF triggers are the triggers written especially for modifying views, which

cannot be directly modified through SQL DML statements.

drop table employee;

drop table dept;

create table Employee(id int);

create table dept(dept_name varchar(10));

insert into Employee values(1);

insert into dept values('maths');

create view v1 as select id,dept_name from Employee,dept;

insert into v1 values(2,'Science');

create or replace trigger instead_trig

instead of insert on v1

for each row

begin

insert into Employee values(:new.id);

insert into dept values(:new.dept_name);

end;

insert into v1 values(2,'Science');

select * from v1;

select * from Employee;

select * from dept;

46) Difference between Raise_application_error and pragma exception_init in

oracle.

Both the procedures are used to raise user defined exceptions but in raise_application_error procedure we cannot name the exception where as in pragma exception_init we can name the exception. We cannot name the exception in raise_application_error instead we use others as the exception name like below example.

Below are the examples of both.

pragma exception_init

declare

excep_age exception;

age number := 17;

pragma exception_init(excep_age,-20008);

begin

if (age<18)

then

raise_application_error(-20008,'age is below limit');

end if;

Exception when excep_age then

dbms_output.put_line(SQLERRM);

end;

raise_application_error

declare

age number := 17;

begin

if (age<18)

then

raise_application_error(-20008,'age is below limit');

end if;

Exception when others then

dbms_output.put_line(SQLERRM);

end;

Interview Questions and Answers of ORACLE PL/SQL

noreply@blogger.com (Vinay Kumar) — Thu, 26 Dec 2019 15:44:00 +0000

1) Select Count(1) From Dual;,What Is Output?
Answer : 1

2) How to delete duplicate records from table without Distinct Clause
Answer: Using row id
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vinay',4500);
insert into Employee values(3,'Teja',4500);
insert into Employee values(4,'Ashwin',1100);
insert into Employee values(5,'Raj',45000);
select rowid,id,name,sal from Employee;
select max(rowid) from Employee group by id,name,sal;
delete from Employee where rowid not in ( select max(rowid) from Employee group by
id,name,sal);

3)How To Retrieve Second Highest Salary From Table?
Answer:
select id,name,sal from Employee a where 2=(select count(distinct sal) from Employee b
where a.sal<=b.sal);

4) Diff B/W Primary Key & Unique Key?
Answer:

Primary Key	Unique Key
Unique + not null	Unique+ one null
creates clustered index	creates non clustered index
only one primary key in a table	more than one unique key in a table.

5) Diff B/W Procedure & Package?
Answer:
A procedure is a stored program in Oracle that is written down when a particular task has to
be done.
A package is a database object that can contain procedures or functions that perform tasks
that are related to each other.

6)Which Is Better Procedure Or Package?
Answer:
Better Performance:
Package: Packages which contains procedures and functions can be compiled and are
loaded into memory at once.
Procedures: Procedures are compiled and loaded separately for each procedure.

7) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?
Answer:
Cursor is a pointer which points result set and retrieve each row at a time from the result
set.
Types of Cursors:
Implicit
Explicit
Implicit cursor:
It used when sql statement return only one row.
It has 4 attributes
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.
Explicit cursor:
It is used when sql statement returns more than one row.
It has also 4 attributes
CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.

9) What Is Exception? Types Of Exceptions?
Answer:
An exception is an error which occurs at run time.
Predefined
User defined

10) What Is Sqlcode And Sqlerr?
Answer:
Sqlcode: Number of the error
Sqlerr: message related to the SQL Code.

11) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?
Answer:
Ref_Cursor:
Reference to a cursor.
It is a datatype in PL/SQL.
Special type of cursor variable.
Ref cursor gets associated with multiple SELECT statements in a single PL/SQL block.
Types:
Strong Ref Cursor: Strong ref cursors have return type and return type should be record
data type.
DECLARE
TYPE cursor_variable_name IS REF CURSOR
RETURN (return type);
Weak Ref Cursor: Do not have any return type
DECLARE
TYPE ref_cursor_name IS REF CURSOR;
Sys_RefCursor
Sys Ref cursor is an Oracle built in cursor variable.

12) How to Give Privilege to One Procedure In Package?
Answer:
GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER

13) What Is %Type & %Rowtype? What Is Main Use?
Answer:
% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.
The advantages are :
1) Need not know about variable's data type.
2) If the database definition of a column in a table changes, the data type of a variable
changes accordingly.
%TYPE is used to declare a field with the same type as that of a specified table's column:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE is used to declare a record with the same types as found in the specified
database table, view or cursor:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

14) How To Improve Performance?
Answer:
Make sure you have appropriate indexes.
Make sure you have up-to-date statistics on all the tables, using the subprograms in the
DBMS_STATS package.
Analyse the execution plans and performance of the SQL statements, using:
EXPLAIN PLAN statement.

15) BULK COLLECT
Answer
This keyword has the functionality of fetching the records in bulk rather than fetching one by
one.
It is a type of collection variable.

16) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?
Answer
BULK COLLECT keyword has the functionality of fetching the records in bulk rather than
fetching one-by-one.
The main advantage of using BULK COLLECT is it increases the
performance by reducing the interaction between database and PL/SQL engine.
LIMIT:
The bulk collect concept loads the entire data into the target collection variable as a bulk.
This size limit can be easily achieved by introducing the ROWNUM condition in the
'SELECT' statement, whereas in the case of cursor this is not possible.
Oracle has provided 'LIMIT' clause that defines the number of records that needs to be
included in the bulkin case of cursor.
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;
FORALL:
The FORALL allows to perform the DML operations on data in bulk.
It is similar to that of FOR loop statement except in FOR loop things happen at the record-
level whereas in FORALL there is no LOOP concept. Instead the entire data present in the
given range is processed at the same time.

17) What Is Merge Statement? Give Me Syntax?
Answer
Oracle MERGE statement selects data from one or more source tables and updates or
inserts it into a target table.

18) What Is View? Use Of View's?
Answer
View is a virtual table which can be derived based on a query
Uses:
Readability and confidentiality

19) Which Will Fire Default First Statement Level Or Row Level Trigger?
Answer:
Oracle runs triggers of each type in a planned firing sequence when more than one trigger
is fired by a single SQL statement. First, statement level triggers are fired, and then row
level triggers are fired.

20) What is functional based index? Write syntax?
Answer:
CREATE INDEX members_last_name_fi ON members(UPPER(last_name));
A function-based index calculates the result of a function that involves one or more columns
and stores that result in the index.
A function-based index speeds up the query by giving the optimiser more chance to
perform an index range scan instead of full index scan.

21) How to update complex view?
Answer:
When can we update a view?
The view must include the PRIMARY KEY of the table based upon which the view has been
created.
It should not have any field made out of aggregate functions.
It must not have any DISTINCT clause in its definition.
It must not have any GROUP BY or HAVING clause in its definition.
It must not have any SUB-QUERIES in its definitions.
If the view you want to update is based upon another view, the later should be updatable.
Any of the selected output fields (of the view) must not use constants, strings or value
expressions.
When can we cannot update a view?
The SELECT statement which is used to create the view should not include the following
GROUP BY clause or ORDER BY clause.
DISTINCT keyword.
NOT NULL values.
An aggregate or analytic function.
A collection expression in a SELECT list.
A sub-query in a SELECT list.
A sub-query designated WITH READ ONLY.
The view should not be created using nested queries or complex queries.
The view should not be updated when it doesn’t contain a key preserved table.
view cannot update if view contains pseudo columns or expressions.

22) Can you alter procedure with in package?
Answer:
All objects in a package are stored as a unit, the ALTER PACKAGE statement
re compiles all package objects. You cannot use the ALTER PROCEDURE statement or
ALTER FUNCTION statement to recompile individually a procedure or function that is part
of a package.

23) Is it possible to open cursor which is in package in another procedure?
Answer:
Yes its possible.
create or replace package pkg_Util is
cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE;
end;
/
--Here is a different package that references the cursor
create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end;

24) What is substr()&instr()?
Answer:
INSTR and SUBSTR are string functions..
Position of a sub string in a main string.
SELECT INSTR('CLUBORACLE','L') RES FROM DUAL;
RES
---
2
Subsring from a main string.
SELECT substr('CLUBORACLE',3,2) RES FROM dual;
RES
----
UB

25) Difference between case and decode?
Answer:
CASE allows you to perform IF-THEN-ELSE logic in your SQL statements, similar to
DECODE.
Difference between CASE and DECODE is
CASE is a statement where as DECODE is a function.
CASE can be used in both SQL and PLSQL . But DECODE can be used only in SQL.
CASE is used in where clause But you cant use DECODE in where clause.
CASE is used in relational operators where as DECODE is used in equality operators.

26) Can you use sysdate in check constraints? If no, why?
Answer:
A check constraint cannot refer a function like SYSDATE.
You can get a different result every time you call it.

27) Difference between column level constraints & table level constraints?
Answer:
Column constraints and table constraints have the same function; the difference is in where
you specify them.
Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE,
CHECK, or FOREIGN KEY constraint definition.
Column-level constraints (except for check constraints) refer to only one column.

28) What is optimiser?
The database can execute a SQL statement in multiple ways, such as full table scans,
index scans, nested loops etc.
The optimiser generates a set of plans for the SQL statement based on available access
paths and hints.
The optimiser estimates the cost of each plan based on statistics in the data dictionary.
The optimiser compares the plans and chooses the plan with the lowest cost.

29) What is pivot operator?
Answer
PIVOT:
Pivot operator transposes an aggregated row of a table into a column.
The distinct row values become the columns in the output and aggregated column value
places itself under the appropriate pivoted column.

30) SQL loader
Answer:
SQL*Loader loads data from an external flat file into a table in the database.
The input data file stores delimited or raw data like CSV, tab-delimited, and pipe-delimited.
The parameter file stores the location of the input/output files.
The control file specifies on how data is loaded.
The parameter file specify the user id that will connect to the Oracle database.
The email.bad file stores invalid data.
The discard file contains records that did not match any record-selection criteria specified in
the control file.

31) What is Raise_application_error.
Answer:
It is a procedure of package DBMS_STANDARD that allows user to raise user_defined
error messages from database trigger or stored sub-program.

32) Explain mutating table error.
Answer:
It occurs when a trigger tries to update a row that it is currently being used. It is resolved by
using views or temporary tables, so database selects one and updates the other.

33) How many triggers can be applied to a table?
Answer:
A maximum of 12 triggers applied to one table

34) Difference between RANK() and Dense_Rank () function.
Answer:
Rank Function provides rank to each value in a column based on the column specified by
order by clause.
Dense Rank function:
Similar to RANK function however the DENSE_RANK function does not skip any ranks
when the ranks are similar in the preceding rows.

35) Can we avoid index by using hint?
Answer:
Yes we can avoid index using no_index optimizer hint. Oracle hints are also called as
optimiser hints.
optimizer hint is a code written with in an sql query which controls or instructs the optimizer.
NO_INDEX(<table_name> < index_name>): Instructs the optimizer to not to use the
named index in determining a plan.
SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename FROM emp, dept WHERE
emp.deptno = dept.deptno;

36) Can we delete primary key of one table without changing foreign key of second
table?
Answer:
No, you cannot delete the primary key from one table without removing all the
dependencies that other tables have on that key.
ON DELETE CASCADE:
When we create a foreign key using this ON DELETE CASCADE, it deletes the
referencing rows in the child table when the referenced row is deleted in the parent table
which has a primary key.

37) How to avoid Mutating error?
Answer:
If we insert a row in table X and a for each trigger executes a query on table x.Oracle
throws errors called mutating error.
Using compound trigger we can avoid this.

38) How to print all dates of particular month in a table (if it is not present in column)
select to_date('01.12.2019','dd.mm.yyyy')+level-1 from dual
connect by level <= TO_CHAR(LAST_DAY(to_date('01.12.2019','dd.mm.yyyy')),'DD')

39) How can we print 1 to 10 numbers in single query.
Answer:
select level from dual connect by level<=10

40) If the view is updated, is it updated in base table.
Answer:
Yes, base table is updated
drop table emp;
create table emp(id int,name varchar2(10),location varchar2(10));
insert into emp(id,name,location) values(1,'vishnu','chennai');
insert into emp(id,name,location) values(2,'priya','US');
drop view v1;
create view v1 as select id,location from emp;
update v1 set location='Bhuva' where id=2;
select * from emp;
1 vishnu chennai
2 priya Bhuva

41) What are collections in Oracle.
Answer:
A collection is an ordered group of elements having the same data type.

There are two types of collection:
1) Persistent.
2) Non Persistent.

Persistent collection are stored physically in database and can be accessed when needed.
where as Non Persistent collection are available only for one session.

PL/SQL provides three collection types.

Persistent collection:

Nested table

Variable-size array or Varray

Non Persistent collection:

Associative array

42) Difference between CHAR,VARCHAR,VARCHAR2
Answer:
VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2
can stores the 4000 bytes of characters.
VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not
occupy the space.
CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded
NULL and more over the VARCHAR2 is variable length.
No storage space is taken for null values.

43) Difference between Raise ,Raise_application_error and pragma exception_init in
oracle.
Answer:
RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-
defined exception. It is also used to raise standard oracle exception. Without showing an
actual message of exception, RAISE will just raise an exception and fails the execution of
the code.
RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an
error with number and user understandable error message. Exception includes both ORA
error and Application error.
The pragma EXCEPTION_INIT associates an exception name with an Oracle error
number.Exception includes ORA error.

44) Example of predefined exception in pl/sql.
Answer:
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
INVALID_CURSOR
CURSOR_ALREADY_OPEN.

45) What are INSTEAD of triggers?
Answer:
The INSTEAD OF triggers are the triggers written especially for modifying views, which
cannot be directly modified through SQL DML statements.

Real Time Scenarios of ORACLE SQL Development - 4

noreply@blogger.com (Vinay Kumar) — Thu, 05 Dec 2019 04:07:00 +0000

How to find Nth Highest salary in a Team.

Queries:

drop table Employee;

create table Employee(id int,name varchar2(10),sal int);

insert into Employee values(1,'Vishnu',2300);

insert into Employee values(2,'Vinay',4500);

insert into Employee values(3,'Teja',4500);

insert into Employee values(4,'Ashwin',1100);

insert into Employee values(5,'Raj',45000);

select * from Employee;

2nd maximum salary in Employee table.

select id,name,sal from Employee a where 2=(select count(distinct sal) from Employee

b where a.sal<=b.sal);

Note : Distinct key word is used in the sub query as there are same salaries for two employees in the above table.

Real Time Scenarios of ORACLE SQL Development - 3

noreply@blogger.com (Vinay Kumar) — Mon, 02 Dec 2019 05:45:00 +0000

Dealing with Duplicate records from a table using Row Number Function.

drop table student;

create table student(id int,name varchar2(10),dept int);

insert into student values(1,'Vishnu',111);

insert into student values(1,'Vishnu',111);

insert into student values(2,'Teja',222);

insert into student values(3,'Ashwin',222);

insert into student values(3,'Ashwin',222);

select * from student;

ROWNUM and ROWID keywords in ORACLE.

ROWNUM is a temporary numeric sequence number of a row.

ROWID is a physical address location of the row.

Assigning numeric number using ROW_NUMBER Function to each record in the table based on duplicates

Using rownum keyword.

select id,name,dept,rownum rnum,row_number() over (partition by id,name,dept order by id) as dup from student;

Using rowid keyword.

select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student;

Extracting the rows with row num's which are duplicates

select rnum from
(select id,name,dept,rownum rnum,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1;

Extracting the rows with rowid's which are duplicates

select rid from

(select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1;

Finally deleting the duplicate records from the table student.

delete from student where rowid in
(select rid from (select id,name,dept,rowid rid,row_number() over (partition by id,name,dept order by id) as dup from student) where dup>1);

select * from student;

Note: Delete operation cannot be performed with rownum because the rownum is a Pseudo column that in assigned in an incremental order after the where clause but before group by or order clause.

Simple Example using ROW ID with out ROW_NUMBER function.

delete from student where rowid not in (select max(rowid) from student group by id,name,dept);

Real Time Scenarios of ORACLE SQL developement - 2

noreply@blogger.com (Vinay Kumar) — Fri, 29 Nov 2019 05:38:00 +0000

How to extract the Successor and Predecessor of a student based on marks.

Tables:

drop table student;
create table student(id int,name varchar(10),dept int,marks int);
insert into student values(1,'Vishnu',111,95);

insert into student values(2,'Ashwin',111,26);
insert into student values(3,'Vinay',111,56);
insert into student values(4,'Ajay',222,48);
insert into student values(5,'Teja',222,92);
insert into student values(6,'Venkat',333,28);
insert into student values(7,'Maneesh',333,12);
select * from student;

To extract the successor and predecessor of each student based on the marks ORACLE supports LEAD and LAG functions.

LEAD Function:

Allows to access the following row from the current row with out join.

Syntax:

Lead(column name) over (partition by column name order by column name)

LAG Function:

Allows to access the prior row from the current row with out join.

Syntax:

Lag(column name) over (partition by column name order by column name)

Note: Returns NULL, if no value after or before the current value.

Query:

select id,name,dept,marks,lag(name) OVER (partition by dept ORDER BY marks desc) AS predecessor, lead(name) OVER (partition by dept ORDER BY marks desc) AS successor from student;

Real Time Scenarios of ORACLE SQL developement - 1

noreply@blogger.com (Vinay Kumar) — Wed, 27 Nov 2019 18:58:00 +0000

Display Credit and Debit Amount in an account for a given date in ORACLE.

Table : Transaction

Queries:

drop table Transaction;

create table Transaction (Account_Number varchar2(10),

Transaction_Type varchar2(10),

Amount integer, Transaction_date date);

insert into Transaction values ('111','Credit', 1000, '11-Nov-27');

insert into Transaction values ('111','Credit', 1200, '11-Nov-27');

insert into Transaction values ('222','Debit', 2000, '11-Nov-29');

insert into Transaction values ('333','Credit', 1000, '11-Nov-28');

insert into Transaction values('222','Credit', 1000, '11-Nov-29');

insert into Transaction values ('111','Debit', 1200, '11-Nov-27');

Sum of credits and debits of an account for the given dates.

select

Transaction_date,

Transaction_Type,

Account_Number,

sum(Amount) amt

from Transaction

group by Transaction_date, Transaction_Type,Account_Number;

Sum of credits and debits of an account for the given dates in two separate columns like below (credit_amt,debit_amt).

Note: Sub queries are marked in RED

select
Transaction_date,
Account_Number,
max(case when Transaction_Type = 'Credit' then amt end) credit_amt,
max(case when Transaction_Type = 'Debit' then amt end) debit_amt
from
(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number)
group by Transaction_date,Account_Number;

Substituting '0' in place of NULL values in the above table.

The NVL() function replaces null with an alternative in the results of a query.

Syntax: NVL(e1, e2)

In the below example Null will be replaced by 0

select

Transaction_date,

Account_Number,

NVL(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt,

NVL

(max(case when Transaction_Type = 'Debit' then amt end),0) debit_amt

from

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number)

group by Transaction_date,Account_Number;

Total Amount in the account for the given date.

select

Transaction_date,

Account_Number,

(credit_amt-debit_amt) as total_amt

from

(select Transaction_date,Account_Number, nvl(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt, NVL(max(case when Transaction_Type = 'Debit'

then amt end),0) debit_amt

from

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction

group by Transaction_date, Transaction_Type,Account_Number)

group by Transaction_date,Account_Number);

How to connect and fetch result in DB2 through python?

noreply@blogger.com (Vinay Kumar) — Sun, 10 Nov 2019 15:57:00 +0000

Till now I have automated db2 tasks using shell script, now I'm using python as automation tool.

To use db2 in python, we must install ibm_db2 plugin in python.

apt install python-pip
pip install ibm_db

Then we need to import ibm_db function in our python script.

Now I'll show you a simple script to connect and fetch result using Python script.

import ibm_db

conn = ibm_db.connect("sample","db2inst1","db2inst1")
stmt = ibm_db.exec_immediate(conn, "select * from db2inst1.employee")
print("number of rows: ",ibm_db.fetch_both(stmt))

Save this commands in file_name.py

Result:

db2inst1@vinay-Lenovo-Ideapad-Z510:~$ python connect_db2.py
('number of rows: ', {0: u'000010', 1: u'CHRISTINE', 2: u'I', 3: u'HAAS', 4: u'A00', 5: u'3978', 6: datetime.date(1995, 1, 1), 7: u'PRES ', 8: 18, 'LASTNAME': u'HAAS', 10: datetime.date(1963, 8, 24), 11: '152750.00', 12: '1000.00', 13: '4220.00', 'BIRTHDATE': datetime.date(1963, 8, 24), 'SALARY': '152750.00', 'MIDINIT': u'I', 'BONUS': '1000.00', 'FIRSTNME': u'CHRISTINE', 'SEX': u'F', 'JOB': u'PRES ', 9: u'F', 'PHONENO': u'3978', 'WORKDEPT': u'A00', 'EDLEVEL': 18, 'EMPNO': u'000010', 'COMM': '4220.00', 'HIREDATE': datetime.date(1995, 1, 1)})

Installation of DB2 V11.5 software using db2_install

noreply@blogger.com (Vinay Kumar) — Sat, 09 Nov 2019 16:01:00 +0000

Before preparing to install DB2,we should check the required library files on OS.

Note: I installed DB2 on Ubuntu so used apt-get to install libraries.

Installation of library files:

To verify whether the required packages are installed on OS use below command:

dpkg --get-selections | grep -i <package-name>

dpkg --add-architecture i386 #Command will enable i386 Architecture

apt-get update

We must install below library files:

apt-get -y install libaio1
apt-get -y install binutilsapt-get -y install zlib1g-dev
apt-get -y install liblogger-syslog-perl
apt-get -y install libpam0g:i386
apt-get -y install libstdc++6:i386
apt-get update

After installation of libraries few will go to /lib/x86_64-linux-gnu these files won't recognize by db2prereqcheck so we should link files from /lib/x86_64-linux-gnu/libpam.so.0 to /lib/libpam.so.0
ln -s /lib/x86_64-linux-gnu/libpam.so.0 /lib/libpam.so.0

Checking prerequisites:

./db2prereqcheck -v 11.5.0.0

Group and User creation:

groupadd -g 999 db2iadm1
groupadd -g 998 db2fadm1
groupadd -g 997 dasadm1

useradd -u 1004 -g db2iadm1 -m -d /db2home/db2inst1 db2inst1
useradd -u 1003 -g db2fadm1 -m -d /db2home/db2fenc1 db2fenc1
useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1

passwd db2inst1
passwd db2fenc1
passwd dasusr1

Installation of Software using db2_install:

root@vinay-Lenovo-Ideapad-Z510:~/db2V11.5/server_dec# ./db2_install
Read the license agreement file in the db2/license directory.

***********************************************************
To accept those terms, enter "yes". Otherwise, enter "no" to cancel the install process. [yes/no]
yes

Default directory for installation of products - /opt/ibm/db2/V11.5

***********************************************************
Install into default directory (/opt/ibm/db2/V11.5) ? [yes/no]
yes

Specify one of the following keywords to install DB2 products.

SERVER
CONSV
CLIENT
RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
SERVER
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no]
no
DB2 installation is being initialized.

Total number of tasks to be performed: 58
Total estimated time for all tasks to be performed: 2819 second(s)

Task #1 start
Description: Checking license agreement acceptance
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: Base Client Support for installation with root privileges
Estimated time 3 second(s)
Task #2 end

Task #58 start
Description: Registering DB2 Update Service
Estimated time 30 second(s)
Task #58 end

Task #59 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #59 end

The execution completed with warnings.

For more information see the DB2 installation log at
"/tmp/db2_install.log.6899".

Creating DAS:

root@vinay-Lenovo-Ideapad-Z510:/opt/ibm/db2/V11.5/instance# ./dascrt -u dasusr1
DBI1070I Program dascrt completed successfully

Instance creation:

./db2icrt -a server -p 50001 -u db2fenc1 db2inst1

root@vinay-Lenovo-Ideapad-Z510:/opt/ibm/db2/V11.5/instance# ./db2iauto -on db2inst1

Starting DAS and Instance:

root@vinay-Lenovo-Ideapad-Z510:/opt/ibm/db2/V11.5/instance# su - dasusr1
$ db2admin start
SQL4409W The DB2 Administration Server is already active.

root@vinay-Lenovo-Ideapad-Z510:/opt/ibm/db2/V11.5/instance# su - db2inst1
$ db2set -all
[i] DB2COMM=TCPIP
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=vinay-Lenovo-Ideapad-Z510
[g] DB2ADMINSERVER=dasusr1
$ db2start
11/09/2019 20:24:50 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

$ db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".

$ db2licm -l
Product name:                     "IBM DB2 Developer-C Edition"
License type:                     "Community"
Expiry date:                      "Permanent"
Product identifier:               "db2dec"
Version information:              "11.5"
Max amount of memory (GB):        "16"
Max number of cores:              "4"
Max amount of table space (GB):   "100"

$ db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V11.5               11.5.0.0        0                            Sat Nov 9 20:14:14 2019 IST             0

Steps to ask yourself while troubleshooting in DB2.

noreply@blogger.com (Vinay Kumar) — Sun, 03 Nov 2019 07:20:00 +0000

First important step is to describe the problem completely. You will not know where to start and what investigation you need to do without a problem description.

Basic questions you should ask yourself:

==> What are the symptoms?

==> Where is the problem happening?

==> When does the problem happen?

==> Under which condition problem happen?

==> Is the problem reproducible?

What are the symptoms?

When analyzing symptoms you should think about below questions.

- Who or what is reporting the problem?

- Error codes and error messages?

- How does it fail?

- How it is effecting business?

What is the problem happening?

Important step to resolve a problem is to find origin, but it is not very easy. Network, disks, and drivers are only a few components to consider when you investigate problems.

- Is the problem platform specific, or common to multiple platforms?

- Environment running locally on the database server or on a remote machine?

- Any gateway involved?

- Data stored on individual disks, or on a RAID disk array?

If you find problem reported on one layer, that might not be the only reason or root cause.

- To identify a problem you should understand the environment where it exists.

- You should always spend time to understand environment like, OS, its version, all corresponding software & versions, and hardware information.

When does the problem happen?

For problem analysis time line is very important factor. Start at the time an error was reported and work backwards through available logs & information.

The diagnostic information we need to look for is as below:

- The problem only happen at a certain time of day or night?

- How often does it happen?

- What are the sequence of events upto the time the problem is reported?

- Does the problem happen after an upgrade or installing new software & hardware.

Under which condition does the problem happen?

Knowing what else is running at the time of a problem is important. If a problem occurs in a certain environment or under certain conditions, that can be a key indicator of the problem cause.

- Does the problem always occur when performing same task?

- Certain sequence if events need to occur for the problem to surface?

- Do other applications fails at the same time?

Is the problem reproducible?

The "ideal" problem is one that is reproducible. Reproducible problems are usually easier to debug and solve.

If problem causes business impact, you don't want it recurring. If possible create the problem in a test or dev environment is often preferable in this case.

- Can the problem be recreated on a test machine?

- Multiple users or applications encountering the same type of problem?

- Can the problem be recreated by running a single command, a set of commands, or a particular existing application.

Which is better db2expln or db2exfmt?

noreply@blogger.com (Vinay Kumar) — Sun, 14 Oct 2018 13:00:00 +0000

As a database administrator sometimes we definitely face query slowness issues. For this IBM provided tools to analyse the query and get few details about the plan how it is accessing the table and indices of the table. The main tools provided by IBM in DB2 are db2expln & db2exfmt. Both these tool gives the same output such as how the table and index scans are? how much time it is taking to get the results back. But the which one is better and what is difference between these two tools? why db2 provided two tools for single purpose.

Lets see how these two works.

DB2EXPLN:

Before using db2expln we need to check whether the tables related to this tool are exists or not. If these tables does not exist we need to create the tables as below:

connect to <database>

db2 -tvf C:\IBM\SQLLIB\MISC\EXPLAIN.DDL

The above script creates the below explain related tables in instance owner schema.

ADVISE_INDEX

ADVISE_INSTANCE

ADVISE_MQT

ADVISE_PARTITION

ADVISE_TABLE

ADVISE_WORKLOAD

EXPLAIN_ACTUALS
EXPLAIN_ARGUMENT
EXPLAIN_DIAGNOSTIC
EXPLAIN_DIAGNOSTIC_DATA
EXPLAIN_FORMAT_OUTPUT
EXPLAIN_INSTANCE
EXPLAIN_OBJECT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STATEMENT
EXPLAIN_STREAM
OBJECT_METRICS

Here to use this db2expln tool I'm creating a file with below select query. File name is select.sql

SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept = d.deptno AND e.workdept = p.deptno

Now let use analyse the select statement with db2expln.

db2expln -database SAMPLE -stmtfile select.sql -g -o explain.out

My database name is SAMPLE. Explain result will be stored in "explain.out" file.

Let see the output.

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"ADMINISTRATOR"
Statement:

SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept =d.deptno AND e.workdept =p.deptno
Section Code Page = 1208
Estimated Cost = 20.474272
Estimated Cardinality = 105.000000
Access Table Name = ADMINISTRATOR.PROJECT ID = 2,9
| #Columns = 2
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Inner Row
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.DEPARTMENT ID = 2,5
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.EMPLOYEE ID = 2,6
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 5
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost

105
RETURN
( 1)
20.4743
|
105
HSJOIN
( 2)
20.4743
/---/ \--\
42 20
TBSCAN HSJOIN
( 3) ( 4)
6.82855 13.6424
| / \
42 14 20
Table: TBSCAN TBSCAN
ADMINISTRATOR ( 5) ( 6)
EMPLOYEE 6.81944 6.82139
| |
14 20
Table: Table:
ADMINISTRATOR ADMINISTRATOR
DEPARTMENT PROJECT

In the above output you can see the below values. These values gives the time taken to retrieve the data. This is better if it is less. Depends on the data quantity we have.

Estimated Cost = 20.474272

Estimated Cardinality = 105.000000

At the end we can see the access plan.

Now lets see how db2exfmt tool will work.

DB2EXFMT:

What is db2exfmt?

This tool gives us the result to understand better in a simple format.

This tool retrieves the result of a query once it has the explain information in the explain tables or if we run the query on database with explain mode is explain.

Lets see how it works.

We already has the explain for the query above on the explain tables. Lets run the db2exfmt now.

db2exfmt -d SAMPLE -1 -o select.sql.exfmt

Output:

DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012

Licensed Material - Program Property of IBM

IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 10.05.3

FORMATTED ON DB: SAMPLE

SOURCE_NAME: SQLC2K26

SOURCE_SCHEMA: NULLID

SOURCE_VERSION:

EXPLAIN_TIME: 2018-10-13-18.41.03.686000

EXPLAIN_REQUESTER: ADMINISTRATOR

Database Context:

----------------

Parallelism: None

CPU Speed: 1.889377e-007

Comm Speed: 100

Buffer Pool size: 250

Sort Heap size: 256

Database Heap size: 600

Lock List size: 4096

Maximum Lock List: 22

Average Applications: 1

Locks Available: 28835

Package Context:

---------------

SQL Type: Dynamic

Optimization Level: 5

Blocking: Block All Cursors

Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 201 ----------------

QUERYNO: 6

QUERYTAG: CLP

Statement Type: Select

Updatable: No

Deletable: No

Query Degree: 1

Original Statement:

------------------

SELECT

e.lastname,

e.job,

d.deptname,

d.location,

p.projname

FROM

employee AS e,

department AS d,

project AS p

WHERE

e.workdept =d.deptno AND

e.workdept =p.deptno

Optimized Statement:

-------------------

SELECT

Q3.LASTNAME AS "LASTNAME",

Q3.JOB AS "JOB",

Q2.DEPTNAME AS "DEPTNAME",

Q2.LOCATION AS "LOCATION",

Q1.PROJNAME AS "PROJNAME"

FROM

ADMINISTRATOR.PROJECT AS Q1,

ADMINISTRATOR.DEPARTMENT AS Q2,

ADMINISTRATOR.EMPLOYEE AS Q3

WHERE

(Q3.WORKDEPT = Q1.DEPTNO) AND

(Q1.DEPTNO = Q2.DEPTNO)

Access Plan:

-----------

Total Cost: 20.4743

Query Degree: 1

Rows

RETURN

( 1)

Cost

I/O

105

HSJOIN

( 2)

20.4743

/----------+----------\

42 20

TBSCAN HSJOIN^

( 3) ( 4)

6.82855 13.6424

1 2

| /--------+--------\

42 14 20

TABLE: ADMINISTRATOR TBSCAN TBSCAN

EMPLOYEE ( 5) ( 6)

Q3 6.81944 6.82139

1 1

| |

14 20

TABLE: ADMINISTRATOR TABLE: ADMINISTRATOR

DEPARTMENT PROJECT

Q2 Q1

Operator Symbols :

------------------

Symbol Description

--------- ------------------------------------------

>JOIN : Left outer join

JOIN< : Right outer join

>JOIN< : Full outer join

xJOIN : Left antijoin

JOINx : Right antijoin

^JOIN : Left early out

JOIN^ : Right early out

Extended Diagnostic Information:

--------------------------------

Diagnostic Identifier: 1

Diagnostic Details: EXP0062W The following MQT or statistical view was

not eligible because one or more columns or

expressions referenced in the query were not found

in the MQT: "ADMINISTRATOR"."ADEFUSR".

Diagnostic Identifier: 2

Diagnostic Details: EXP0148W The following MQT or statistical view was

considered in query matching: "ADMINISTRATOR".

"ADEFUSR".

Plan Details:

-------------

1) RETURN: (Return Result)

Cumulative Total Cost: 20.4743

Cumulative CPU Cost: 313719

Cumulative I/O Cost: 3

Cumulative Re-Total Cost: 20.4743

Cumulative Re-CPU Cost: 313719

Cumulative Re-I/O Cost: 3

Cumulative First Row Cost: 20.4743

Estimated Bufferpool Buffers: 1

Arguments:

---------

BLDLEVEL: (Build level)

DB2 v10.5.300.125 : s140203

HEAPUSE : (Maximum Statement Heap Usage)

128 Pages

PREPTIME: (Statement prepare time)

378 milliseconds

STMTHEAP: (Statement heap size)

8192

Input Streams:

-------------

8) From Operator #2

Estimated number of rows: 105

Number of columns: 5

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q4.PROJNAME+Q4.LOCATION+Q4.DEPTNAME+Q4.JOB

+Q4.LASTNAME

2) HSJOIN: (Hash Join)

Cumulative Total Cost: 20.4743

Cumulative CPU Cost: 313719

Cumulative I/O Cost: 3

Cumulative Re-Total Cost: 20.4743

Cumulative Re-CPU Cost: 313719

Cumulative Re-I/O Cost: 3

Cumulative First Row Cost: 20.4743

Estimated Bufferpool Buffers: 1

Arguments:

---------

BITFLTR : (Hash Join Bit Filter used)

FALSE

EARLYOUT: (Early Out flag)

NONE

HASHCODE: (Hash Code Size)

24 BIT

HASHTBSZ: (Number of hash table entries)

TEMPSIZE: (Temporary Table Page Size)

8192

TUPBLKSZ: (Tuple Block Size (bytes))

4000

Predicates:

----------

2) Predicate used in Join,

Comparison Operator: Equal (=)

Subquery Input Required: No

Filter Factor: 0.0714286

Predicate Text:

--------------

(Q2.DEPTNO = Q3.WORKDEPT)

Input Streams:

-------------

2) From Operator #3

Estimated number of rows: 42

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT

7) From Operator #4

Estimated number of rows: 20

Number of columns: 5

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.PROJNAME+Q1.DEPTNO+Q2.LOCATION+Q2.DEPTNAME

+Q2.DEPTNO

Output Streams:

--------------

8) To Operator #1

Estimated number of rows: 105

Number of columns: 5

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q4.PROJNAME+Q4.LOCATION+Q4.DEPTNAME+Q4.JOB

+Q4.LASTNAME

3) TBSCAN: (Table Scan)

Cumulative Total Cost: 6.82855

Cumulative CPU Cost: 124639

Cumulative I/O Cost: 1

Cumulative Re-Total Cost: 0.0139901

Cumulative Re-CPU Cost: 74046

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 6.81488

Estimated Bufferpool Buffers: 1

Arguments:

---------

CUR_COMM: (Currently Committed)

TRUE

JN INPUT: (Join input leg)

OUTER

LCKAVOID: (Lock Avoidance)

TRUE

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

NONE

ROWLOCK : (Row Lock intent)

SHARE (CS/RS)

SCANDIR : (Scan Direction)

FORWARD

SKIP_INS: (Skip Inserted Rows)

TRUE

SPEED : (Assumed speed of scan, in sharing structures)

FAST

TABLOCK : (Table Lock intent)

INTENT SHARE

TBISOLVL: (Table access Isolation Level)

CURSOR STABILITY

THROTTLE: (Scan may be throttled, for scan sharing)

TRUE

VISIBLE : (May be included in scan sharing structures)

TRUE

WRAPPING: (Scan may start anywhere and wrap)

TRUE

Input Streams:

-------------

1) From Object ADMINISTRATOR.EMPLOYEE

Estimated number of rows: 42

Number of columns: 4

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q3.$RID$+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT

Output Streams:

--------------

2) To Operator #2

Estimated number of rows: 42

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT

4) HSJOIN: (Hash Join)

Cumulative Total Cost: 13.6424

Cumulative CPU Cost: 171474

Cumulative I/O Cost: 2

Cumulative Re-Total Cost: 13.6424

Cumulative Re-CPU Cost: 171474

Cumulative Re-I/O Cost: 2

Cumulative First Row Cost: 13.6424

Estimated Bufferpool Buffers: 1

Arguments:

---------

BITFLTR : (Hash Join Bit Filter used)

FALSE

EARLYOUT: (Early Out flag)

RIGHT

HASHCODE: (Hash Code Size)

24 BIT

HASHTBSZ: (Number of hash table entries)

JN INPUT: (Join input leg)

INNER

TEMPSIZE: (Temporary Table Page Size)

8192

TUPBLKSZ: (Tuple Block Size (bytes))

4000

Predicates:

----------

4) Predicate used in Join,

Comparison Operator: Equal (=)

Subquery Input Required: No

Filter Factor: 0.0714286

Predicate Text:

--------------

(Q1.DEPTNO = Q2.DEPTNO)

Input Streams:

-------------

4) From Operator #5

Estimated number of rows: 14

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO

6) From Operator #6

Estimated number of rows: 20

Number of columns: 2

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.PROJNAME+Q1.DEPTNO

Output Streams:

--------------

7) To Operator #2

Estimated number of rows: 20

Number of columns: 5

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.PROJNAME+Q1.DEPTNO+Q2.LOCATION+Q2.DEPTNAME

+Q2.DEPTNO

5) TBSCAN: (Table Scan)

Cumulative Total Cost: 6.81944

Cumulative CPU Cost: 76423

Cumulative I/O Cost: 1

Cumulative Re-Total Cost: 0.00488026

Cumulative Re-CPU Cost: 25830

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 6.81488

Estimated Bufferpool Buffers: 1

Arguments:

---------

CUR_COMM: (Currently Committed)

TRUE

JN INPUT: (Join input leg)

OUTER

LCKAVOID: (Lock Avoidance)

TRUE

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

NONE

ROWLOCK : (Row Lock intent)

SHARE (CS/RS)

SCANDIR : (Scan Direction)

FORWARD

SKIP_INS: (Skip Inserted Rows)

TRUE

SPEED : (Assumed speed of scan, in sharing structures)

FAST

TABLOCK : (Table Lock intent)

INTENT SHARE

TBISOLVL: (Table access Isolation Level)

CURSOR STABILITY

THROTTLE: (Scan may be throttled, for scan sharing)

TRUE

VISIBLE : (May be included in scan sharing structures)

TRUE

WRAPPING: (Scan may start anywhere and wrap)

TRUE

Input Streams:

-------------

3) From Object ADMINISTRATOR.DEPARTMENT

Estimated number of rows: 14

Number of columns: 4

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q2.$RID$+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO

Output Streams:

--------------

4) To Operator #4

Estimated number of rows: 14

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO

6) TBSCAN: (Table Scan)

Cumulative Total Cost: 6.82139

Cumulative CPU Cost: 86755

Cumulative I/O Cost: 1

Cumulative Re-Total Cost: 0.00683237

Cumulative Re-CPU Cost: 36162

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 6.81488

Estimated Bufferpool Buffers: 1

Arguments:

---------

CUR_COMM: (Currently Committed)

TRUE

JN INPUT: (Join input leg)

INNER

LCKAVOID: (Lock Avoidance)

TRUE

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

NONE

ROWLOCK : (Row Lock intent)

SHARE (CS/RS)

SCANDIR : (Scan Direction)

FORWARD

SKIP_INS: (Skip Inserted Rows)

TRUE

SPEED : (Assumed speed of scan, in sharing structures)

FAST

TABLOCK : (Table Lock intent)

INTENT SHARE

TBISOLVL: (Table access Isolation Level)

CURSOR STABILITY

THROTTLE: (Scan may be throttled, for scan sharing)

TRUE

VISIBLE : (May be included in scan sharing structures)

TRUE

WRAPPING: (Scan may start anywhere and wrap)

TRUE

Input Streams:

-------------

5) From Object ADMINISTRATOR.PROJECT

Estimated number of rows: 20

Number of columns: 3

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.$RID$+Q1.PROJNAME+Q1.DEPTNO

Output Streams:

--------------

6) To Operator #4

Estimated number of rows: 20

Number of columns: 2

Subquery predicate ID: Not Applicable

Column Names:

------------

+Q1.PROJNAME+Q1.DEPTNO

Objects Used in Access Plan:

---------------------------

Schema: ADMINISTRATOR

Name: ADEFUSR

Type: Materialized View (reference only)

Schema: ADMINISTRATOR

Name: PROJACT

Type: Table (reference only)

Schema: ADMINISTRATOR

Name: DEPARTMENT

Type: Table

Time of creation: 2018-08-24-23.02.37.749002

Last statistics update: 2018-10-13-18.38.58.822002

Number of columns: 5

Number of rows: 14

Width of rows: 57

Number of buffer pool pages: 1

Number of data partitions: 1

Distinct row values: No

Tablespace name: USERSPACE1

Tablespace overhead: 6.725000

Tablespace transfer rate: 0.080000

Source for statistics: Single Node

Prefetch page count: 32

Container extent page count: 32

Table overflow record count: 0

Table Active Blocks: -1

Average Row Compression Ratio: 0

Percentage Rows Compressed: 0

Average Compressed Row Size: 0

Schema: ADMINISTRATOR

Name: EMPLOYEE

Type: Table

Time of creation: 2018-08-24-23.02.38.313005

Last statistics update: 2018-10-13-18.38.58.817000

Number of columns: 14

Number of rows: 42

Width of rows: 60

Number of buffer pool pages: 1

Number of data partitions: 1

Distinct row values: No

Tablespace name: USERSPACE1

Tablespace overhead: 6.725000

Tablespace transfer rate: 0.080000

Source for statistics: Single Node

Prefetch page count: 32

Container extent page count: 32

Table overflow record count: 0

Table Active Blocks: -1

Average Row Compression Ratio: 0

Percentage Rows Compressed: 0

Average Compressed Row Size: 0

Schema: ADMINISTRATOR

Name: PROJECT

Type: Table

Time of creation: 2018-08-24-23.02.39.427012

Last statistics update: 2018-10-13-18.38.58.801000

Number of columns: 8

Number of rows: 20

Width of rows: 73

Number of buffer pool pages: 1

Number of data partitions: 1

Distinct row values: No

Tablespace name: USERSPACE1

Tablespace overhead: 6.725000

Tablespace transfer rate: 0.080000

Source for statistics: Single Node

Prefetch page count: 32

Container extent page count: 32

Table overflow record count: 0

Table Active Blocks: -1

Average Row Compression Ratio: 0

Percentage Rows Compressed: 0

Average Compressed Row Size: 0

One of the great things with db2exfmt is it gives us the optimized query for the query which we are trying to analyze. We easily understand the analyze the result.

Please leave you instructions and advises in the comments of my post.

How to resolve SQL20249N The statement was not processed error?

noreply@blogger.com (Vinay Kumar) — Tue, 28 Nov 2017 15:23:00 +0000

Hello All, recently we cloned the OS layer to build the DEV environment same as QA. As part of this, we prepared to restore the old DEV database backup into newly build DEV database. Then after the system support team will shut down the old DEV machine.

We restored the database to the new dev database and changed the hostname of the new server same as old one. So, the hostname change activity has to be done from DB side too. We did all the process and tried to connect from the Data Studio to test the connection establishment. We succeeded in connectivity, but while trying to get the list of tables Data Studio throws an error such as "Fetching the Schema failed"

Then, we tried to list the tables from the putty connection. It throws an error like "SQL20249N The statement was not processed because the package named "NULLID.SQLC2K26" needs to be explicitly rebound."

Issue:

SQL20249N The statement was not processed because the package named "NULLID.SQLC2K26" needs to be explicitly rebound

Solution:

According to my research, I came to know that we need to rebind the package which is there in the error description.

db2 rebind <package name> RESOLVE CONSERVATIVE;

Once we rebind the above package we will be able to query the tables and all otherwise we cannot retrieve the data.

Next, we need to rebind the other packages using the below query.

db2 connect to SAMPLE

db2 -x "select 'REBIND PACKAGE ' || rtrim(pkgschema) || '.' || rtrim(pkgname) || ';' as command from syscat.packages" > rebind.sql

db2 -tvf rebind.sql

Script to Reduce the tablespace size and reduce the free pages

noreply@blogger.com (Vinay Kumar) — Tue, 03 Oct 2017 16:23:00 +0000

#!/bin/bash

# Establishing database connection
db2 "connect to itgdb"

# Gathering the tablespaces other than temporary and placing them in a file
db2 list tablespaces show detail | grep -i -A2 name | tac | sed '/Temporary/I,+2 d' | tac | grep -i name | awk '{print $3}' > /tmp/TablespaceList.out

# Using for loop to get the tablespace name one by one
for i in `cat /tmp/TablespaceList.out`
do

# Gathering free pages count for each tablespace
db2 list tablespaces show detail | grep -i -A9 $i | grep -i 'free pages' | awk '{print $4}' > /tmp/FreePages.out

FreePages=`cat /tmp/FreePages.out`

# Comparing whether the free pages are more than 1000 or not
if [ $FreePages -gt 1000 ]
then

# If the free pages are more than 1000 then reducing them
db2 "alter tablespace $i reduce max"
fi
done

Monitoring database backup performance in db2

noreply@blogger.com (Vinay Kumar) — Mon, 02 Oct 2017 03:43:00 +0000

Monitoring backup performance:

Sometimes it is difficult to know why the backup process is slow down. So for that we need continuous monitoring on backup process. Here we present some scenarios and monitoring steps to monitor the backup process in db2.

Starting with DB2 V10.1 FixPack 2, every successful backup or restore operation logged into the db2diag.log file. This feature also exists in DB2 V9.7, but must be enabled by using the DB2_BAR_STATS registry variable.

The meanings of the various columns are as follows:

==> BM - The db2bm EDU ID.

==> Total - EDU existed time.

==> I/O - Time that was spent performing read or write I/O.

==> MsgQ - Waiting time for an I/O buffer.

==> WaitQ - Waiting time for a state machine control message.

==> Buffers - Number of I/O buffers that were processed.

==> Kbytes - Quantity of data that was processed.

==> MC - The db2med EDU ID.

If the backup was performed using the COMPRESS option:

==> Compr - Time spent to perform the compression operation.

==> Compr Bytes - Quantity of uncompressed data that was compressed.

Scenario - 1:

The following example depicts a scenario in which the db2bm EDUs spend more than optimal time waiting on a free buffer:

In this scenario the db2bm EDUs spend 27% (31.95/116.66) of the time waiting on a free buffer (MsgQ). There are two potential options to address this issue:

1. Increase the number of buffers. Result in more, smaller I/O operations due to reducing the size of the buffers.

2. By specifying more devices in the TO clause of the backup database command we have to allocate more db2med EDUs.

Scenario - 2:

The following example depicts a scenario in which the db2bm EDUs spend more than optimal time compressing data:

In this scenario, the db2bm EDUs are spending 53% (28.28/53.58) of the time compressing data. To eliminate the need of compression during every backup we have the alternative to use table-level compression so that the tables are stored in compressed format in the database.

Scenario - 3:

The following example depicts a scenario in which the db2bm EDUs spend too much time in WaitQ:

In this scenario, 2 out of 3 db2bm EDUs spend almost all of the time in WaitQ. A high WaitQ value indicates that the db2bm EDU was idle for most of the backup. The number of Buffers processed is also heavily skewed to one db2bm EDU, which indicates that there was one table space in this database that was significantly larger than the rest. Redistributing data so that the table spaces are equally sized improves backup performance.

Basic concepts that are useful to design and implement a successful backup strategy in db2

noreply@blogger.com (Vinay Kumar) — Sun, 24 Sep 2017 10:13:00 +0000

The following concepts are critical to designing and implementing a successful backup strategy:

--> Balanced table spaces are the primary factor
--> Difference between system managed space (SMS) and database managed space (DMS) tablespaces
--> What pages are included in an incremental or delta backup

Balanced tablespaces:

1. For good backup performance we must make sure that data should be evenly distributed between the tablespaces.

2. In the backup process the db2bm process will be assigned to each tablespace separately. So, if the data in the database is stored in large amount in a single tablespace will cause the other serialisation.

3. If the data evenly distributed between the multiple tablespaces the maximum parallelism will be done throughout the backup.

Difference between SMS and DMS tablespaces:

1. All the pages in the SMS table spaces are backed up when we perform a backup.

2. The pages that are marked as Used will be backed up while backing up DMS table spaces.

What pages that are included in an incremental or delta backup:

1. The pages that are modified since the last full backup will be backed up in incremental backup.

2. A delta backup includes the pages that are modified since the last backup of any type.

3. The LOB, long varchar, and longgraphic(LONG) are also included in the above two backups.

4. If we perform one of the above backups, DB2 keeps track of modified pages since the last backup. If a tablespace has no modified pages since the last backup this will be skipped. At the same time if a tablespace has a single modified page then the the entire tablespaces will be scanned and this will cause that the db2med process to stay in idle state for long time.

5. Incremental and delta backups are designed to be smaller than full backup, but not faster.

6. In some cases these backups are fast, where the scanning of tablespaces were skipped. Otherwise the incremental or delta backup will take longer time than full backup.

What happened in the background while taking backup in DB2?

noreply@blogger.com (Vinay Kumar) — Thu, 21 Sep 2017 07:39:00 +0000

Important Configuration Parameters in DB2 part-1

noreply@blogger.com (Vinay Kumar) — Tue, 12 Sep 2017 04:20:00 +0000

How to resolve error SQL30082N reason code 42 when Connect to a database through client

noreply@blogger.com (Vinay Kumar) — Mon, 17 Apr 2017 11:07:00 +0000

Receiving error SQL30082N reason code 42 when Connect to a database via client

Once I've received a complaint from our internal team, that they are unable to connect to the database from the DB2 Client terminal.

Operating System: Windows

DB2 Client Version: V9.7 AESE

DB2 Server Version: V9.7 AESE

Error:

C:\Users\test>db2 connect to sampledb user test1 using test1

SQL30082N Security processing failed with reason "42" ("ROOT CAPABILITY REQUIRED"). SQLSTATE=08001

Cause:

Any changes on DB2 files such as db2ckpwd that require root permissions can result in this error message.

Diagnosing the problem:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050516.html

Resolving the problem:

Often a simple update ran against the instance using the db2iupdt command will correct permissions on DB2 files.

As root run the command "db2iupdt <instance name>".

(OR)

1) cd to sqllib/security

2) run: ls -l

3) Check the permission's on the db2chpw and db2ckpw they should be:

-r-s--x--x 1 root inst1 51195 Dec 1 15:33 db2chpw

-r-s--x--x 1 root inst1 3534773 Dec 1 15:33 db2ckpw

If you need to change the permission's of those files run the following commands (this needs to be done as root, since the files are owned by root):

chmod 4511 db2chpw

chmod 4511 db2ckpw

Stop and restart the instance.

Step by Step installation of DB2 V10.5 on Linux

noreply@blogger.com (Vinay Kumar) — Mon, 10 Apr 2017 06:28:00 +0000

Installation:

There are total three types of installations in DB2. Those are:

==> Using DB2SETUP (GUI)

==> Using Command Prompt

==> Using Response file

This article lets you know steps to install DB2 using db2setup (GUI).

Disk and memory requirements:

Each product need to have some disk and memory space for the installation.

Lets take a look at that.

Disk requirements:

The disk space may vary for wer product depends on the type of installation we choose and the type of file system we have. The DB2 Setup wizard provides dynamic size estimates based on the components selected during a typical, compact, or custom installation.

On Linux and UNIX operating systems, if the temporary directory (default: /tmp) is a mounted drive, all users must be able to directly execute binaries.

The /tmp space that is required for a DB2 pureScale® environment is 2 GB

and for environments other than a DB2 pureScale environment the space required is 512 MB.

The /var directory requires 512 MB of free space.

On Linux and UNIX operating systems, the disk space that is required for the instance home directory is calculated at run time and varies. Approximately 1 to 1.5 GB of free space in the /home directory is normally required.

Memory requirements:

Memory requirements are affected by the size and complexity of your database system, the extent of database activity, and the number of clients accessing your system. At a minimum, a DB2 database system requires 256 MB of RAM1. For a system running just a DB2 product and the DB2 GUI tools, a minimum of 512 MB of RAM is required. However, 1 GB of RAM is recommended for improved performance. These requirements do not include any additional memory requirements for other software that is running on your system. For IBM data server client support, these memory requirements are for a base of five concurrent client connections. For every additional five client connections, an additional 16 MB of RAM is required.

Paging space requirements:

DB2 requires paging, also called swap to be enabled. This configuration is required to support various functions in DB2 which monitor or depend on knowledge of swap/paging space utilization. A reasonable minimum swap/paging space configuration for most systems is 25-50% of RAM. Solaris and HP systems with many small databases or multiple databases tuned by STMM might require a paging space configuration of 1 x RAM or higher.

Package requirements

==> libpam.so.0 (32-bit) is required for DB2 database servers to run 32-bit non-SQL routines and JDBC type 2 driver applications in 32-bit JDK.
==> libpam.so.0 is required by IBM data server clients for DB2 user authentication.
==> libaio.so.1 is required for DB2 database servers using asynchronous I/O.
==> libstdc++.so.5 is required for Linux 390, installed under /usr/lib and /usr/lib64.
==> libstdc++.so.6.0.8 is required for DB2 database servers and clients.
==> libstdc++33 and libstdc++43 are required for SLES 11.
==> The ksh93 Korn shell is required for all DB2 database systems.

Installation Steps:

Step-1: Locate "DB2_Svr_10.5.0.3_Linux_x86-64.tar.gz"

Step-2: Untar the zip file DB2_Svr_10.5.0.3_Linux_x86-64.tar.gz

Step-3: Then a new directory appears if you list the items.

Step-4: Set the DISPLAY variable to run the GUI setup.

export DISPLAY=:0.0

You can test this was success or not as below:

Step-5: Goto Server Directory

Step-6: If you list the files there you can find the installation files.

Step-7: Run the db2setup as "./db2setup".

Step-8: Click on "Install a Product" on the left side options.

Step-9: Click on "Install New"

Step-10: Click "Next"

Step-11: Then accept the terms and click "Next"

Step-12: Select Installation type. By default it is "Typical" and then click "Next".

Step-13: Under select Installation, response file or both window, you have to select the appropriate option by defalut it is "Install DB2 Server Edition on this computer and save my settings in a response file".

Step-14: Next "Select installation Directory" by default it is "/opt/ibm/db2/V10.5". Where V10.5 may vary depends on the version you are trying to install. If you are installting 9.7 it will be V9.7.

Step-15: Setting user information for "DB2 Administration Server". Installing DB2 using db2setup it automatically creates the users on its own. Just we need to provide the password. The default user for "DB2 Administration Server" is "dasusr1" and group is "dasadm1".

Step-16: Set up a DB2 instance. Check the "create a DB2 instance" option and then click "Next".

Step-17: Setting up the partition options. Here you can find two option one is Singel Partition Instance and another one Multi Partition Instance. For this demo I'm using Single Partition Instance.

Step-18: Setting up instance owner. Provide password for instance owner user. By default db2inst1 is the user and db2iadm1 is the group.

Step-19: Setting up fenced user. Provide password for fenced user. By defalut db2fenc1 is the user and db2fadm1 in the group

Step-20: Setting up notifications. Check "Do not set up your DB2 Server to send notifications at this time". And then click on "Next".

Step-21: Then Click on "Finish".

Step-22: Click "Finish" again.

Performance information that we have to take from users

noreply@blogger.com (Vinay Kumar) — Sat, 18 Feb 2017 14:40:00 +0000

Performance information that users can provide

We can notice the system tuning is required when first we get a complaint from the users.

When we do not have enough time to change the performance tuning objectives, we can know the performance statistics by asking few questions to the users who complains us about the performance degrade.

we can usually determine where to start looking for a problem by asking a few simple questions.

Those are..

==> What do you mean by “slow response”? Is it 10 % slower than you expect it to be, or tens of times slower?
==> When did you notice the problem? Is it recent or has it always been there?
==> Do other users have the same problem? Are these users one or two individuals or a whole group?
==> If a group of users is experiencing the same problems, are they connected to the same local area network?
==> Do the problems seem to be related to a specific transaction or application program?
==> Do you notice any pattern in the problem occurrence? For example, does the problem occur at a specific time of day, such as during lunch hour, or is it more or less continuous?

How Will Logging Process happen in DB2?

noreply@blogger.com (Vinay Kumar) — Fri, 27 Jan 2017 09:10:00 +0000

What is a Log:

Log is nothing but a transaction log. A transaction log is a file which stores the history of actions executed by a database management system, which is used to recover at the time of crashes.

All databases maintain log files that keep records of database changes.

Types of logging strategy choices:

1. Circular logging
2. Retain log records

Circular Logging:

In circular logging, the log files are filled with log records and overwrite the initial log records when there is no active transaction on the initial log file.

For example, let's consider a database which is using circular logging strategy. The number of log files in that database is limited to four. Let's assume an application is connected to the database and making some changes. The transaction occupies all the four log files but the work not yet completed. There are no log files left for the transaction, according to our circular logging rule it should overwrite the initial log file, but it will not be allowed here because the 1st log file is still in an active state. Once the transaction is committed then only the transaction can overwrite the log file in circular logging.

In a huge database where huge transactions happen, there this circular logging doesn't work. So to overcome this we have another logging strategy that is Retain log records.

Retain log records:

In this, once the log files are filled with log records, it will be archived and new log files are made available for the transaction. Retaining log files enables rollforward recovery. With roll forward recovery we can reapply changes to the database based on completed units of work which are recorded in the log.

When log data are written to disk:

Regardless of the logging strategy, all changes to regular data and index pages are written to the log buffer. The data in the log buffer is written to disk by the logger process. In the following circumstances, query processing must wait for log data to be written to disk:

==> On COMMIT
==> Before the corresponding data pages are written to disk because DB2 uses write-ahead logging. The benefit of write-ahead logging is that when a transaction completes by executing the COMMIT statement, not all of the changed data and index pages need to be written to disk.
==> Before some changes are made to metadata, most of which result from executing DDL statements
==> On writing log records into the log buffer, if the log buffer is full

In order to minimise processing delay, db2 manages writing logs to disk.
In an environment in which many short concurrent transactions occur, most of the processing delay is caused by COMMIT statements that must wait for log data to be written to disk. As a result, the logger process frequently writes small amounts of log data to disk, with an additional delay caused by log I/O overhead. To balance application response time against such logging delay, set the mincommit database configuration parameter to a value greater than 1. This setting might cause a longer delay for COMMIT from some applications, but more log data might be written in one operation.

Changes to large objects (LOBs) and LONG VARCHARs are tracked through shadow paging. LOB column changes are not logged unless you specify log retain and the LOB column is defined on the CREATE TABLE statement without the NOT LOGGED clause. Changes to allocation pages for LONG or LOB data types are logged like regular data pages.

How to resolve this Query Optimization issue?

noreply@blogger.com (Vinay Kumar) — Tue, 27 Sep 2016 13:55:00 +0000

Query Optimization Issue:

Assume that a query is executing by a application developer or a client. The query has all the indexes on the columns which are required and performed well upto certain time, suddenly got complaints about the same query that it is not performing well taking too much time to retrieve. Please leave your valuable comments how you people can resolve this issue.

Share views to resolve the below reorg issue

noreply@blogger.com (Vinay Kumar) — Sun, 25 Sep 2016 03:36:00 +0000

Difference between Online Reorg and Offline Reorg in DB2

noreply@blogger.com (Vinay Kumar) — Sun, 25 Sep 2016 03:00:00 +0000

Online reorg vs Offline reorg:

Offline or Classical Reorg:

So many people think that Offline reorg quiesces the database, so no applications are able to connect or access the tables. But while performing offline reorg applications can access tables, but they have limited access. Read access only at the time of SORT and BUILD phases.

It needs some extra space in the same tablespace where the table resides or in temp_tablespace, because it copies the entire table into that extra space and reorganizes there.

Online or Inplace reorg:

User can access tables there are no restrictions, but the performance becomes slow. But in Online reorg we can access data in all the phases except truncation phase. Creates Lock-waits. No temp_tablespace required, because this process reorganizes table data part by part.

Please find the remaining differences below:

What are the different phases of Online & Offline Reorg in DB2 ?

noreply@blogger.com (Vinay Kumar) — Wed, 21 Sep 2016 19:58:00 +0000

Different Phases of Reorg:

Basically Reorg sorts the data using index scan or table scan and moves the records from one page to a previous page which has some free space due to deletion of rows from a table to reclaim the space.

There are "TWO" types of reorgs:

==> Offline reorg or Classic reorg
==> Online reorg or Inplace reorg

Each reorg type consists of different phases of execution.

Phases of Offline or Classic reorg:

There are total "FOUR" phases in Offline or Classic reorg.

SORT:

In this phase the rows of the table sorted by using an index scan if there is any cluster index on the table or if we specify index with reorg command or else it uses the table scan to sort the rows. This phase is applicable for only clustering reorg.

BUILD:

Builds the copy of the reorganizing table in the tablespace where the table resides or in a temporary tablespace specified in reorg command.

REPLACE:

By copying the organized data from the temporary tablesapce or by pointing to the new build objects in the tablespace where the table is being reorganized.

RECREATE ALL INDEXES:

In this phase all the indexes defined on the table are recreated.

Phases of Online or Inplace reorg:

Online reorganization also have "FOUR" phases

SELECT n PAGES:

The database manager selects 'n' pages for the reorganization, which is minimum of 32 sequential pages.

VACATE THE RANGE:

In this phase database manager moves rows within this range to the free space on the table. In this phase it uses Reorg Pointer to point the rows new location using RID. After moving some data the utility waits for the applications to be finished, the current applications accesses the data using old RIDs. The application which starts newly is going to use the new RIDs to access the data.

Fill the range:

After vacating the rows from the selected range database manager starts writing data in an organized manner, sorted according to the indexes. Once the all the pages in the range have been rewritten, the process repeated for the next 'n' sequential rows.

Truncate the table:

Once all pages reorganized in the table, to reclaim the space the database manager truncates the table by default. If you want the table not to be truncated, then you can use NOTRUNCATE option.

What are the DB2 LUW DBA daily activities?

noreply@blogger.com (Vinay Kumar) — Thu, 08 Sep 2016 08:49:00 +0000

DB2 DBA Daily Activities:

Daily Activities:

==> Verify all the instances and databases are running.
==> Check the backup status of the databases.
==> Check the archiving of database logs.
==> Verify the pruning of database backups and archival logs.
==> Check the status of Reorg/Runstats jobs if you have enabled.
==> Check the db2 diagnostic logs and notification logs.
==> Check the size/status of each mount database server is using.
==> Verify that you have enough pages available in the tablespaces if auto increment is off.
==> Capture the database and SQLs snapshots during the specific workloads.
==> Advising the indexes and other recommendations for the SQL queries.
==> Check the database and database manager config parameters which might need a tuning.
==> Handle the adhoc requests from developers and other team members database perspective.
==> Try to automate the database jobs as much as possible to ease the work.
==> Try to learn new features, methods, tricks, functionalities.

Weekly Activities:

==> Look for new objects.
==> Run db2look and write the report to a file every week.
-- Check for differences between the new output and the previous week's output.
==> Select object names from SYSCAT.TABLES, SYSCAT.INDEXES, SYSCAT.PROCEDURES
-- Check for differences between the new output and the previous week's output.
==> Look for new or changed applications.
==> Look for tables and/or indexes needing REORG.
==> Look for tables and indexes needing RUNSTATS.
==> Look for the 10 most active tables.
==> Archive all alert logs and DB2DIAG.LOG files.

Monthly Activities:

==> Look for indicators of exceptional growth.
==> Project future performance based on projected growth.

Sources:

1. The DB2 for Linux, UNIX, and Windows DBA Checklist

2. What should be the daily routine tasks of an IBM DB2 database administrator to keep the system running optimally?