Monthly Archives: July, 2016

db2 luw: Running SQL Script from Command Prompt

Question) Sometimes developers provide an SQL file to DBA to promote to Production. How to run such SQL file from Unix/Linux environment?

Solution:

You can use “db2” command with below options: (db2 list command options)

-t    Set statement termination character (default is semicolon)

-s    Stop execution on command error

-v    Echo current command

-f    Read from input file

Scenario:1   When we receive an .sql file without any line termination (i.e., New Line as line termination)

[db2inst1@rhel1 tmp]$ cat a1.sql
connect to testdb3
select * from t1

$ db2 -vsf ./a1.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Scenario:2  When we receive an .sql file without semicolon (;)

$ cat a2.sql
connect to testdb3;
select * from t1;
[db2inst1@rhel1 tmp]$ db2 -tvsf ./a2.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: By default -t take semicolon as line terminator

Scenario:3  When we receive an .sql file without different terminator(@)

$ cat a3.sql
connect to testdb3@
select * from t1@
$ db2 -td@ -vsf ./a3.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: To define termination characters 1 or 2 characters in length, use -td followed by the chosen character or characters. For example, -td%% sets %% as the statement termination characters.

Scenario:4  Define more than 1 termination character

$ cat a4.sql
connect to testdb3@$
select * from t1@$
[db2inst1@rhel1 tmp]$ db2 -td@$ -vsf ./a4.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Advertisements

Find out DB2 instance running port number (UNIX/Linux)

(Question): How to find out port number of my DB2 instance?

Answer: Port number is defined at instance level through SVCENAME configuration variable.

/mnt/db2home:> db2 get instance

The current database manager instance is:  db2inst1

/mnt/db2home:> db2 get dbm cfg | grep SVCENAME

TCP/IP Service name                          (SVCENAME) = db2c_db2inst1

/mnt/db2home:> grep db2c_db2inst1 /etc/services

db2c_db2inst1        50000/tcp

 

From above, 50000 is the port number. Sometimes you directly find port number given to SVCENAME as below:

/mnt/db2home:> db2 get dbm cfg | grep SVCENAME

TCP/IP Service name                          (SVCENAME) = 50000

 

Nickname creation & load data

This is continuation to following post: https://rk.guru/2016/07/29/db2-luw-federation-between-2-databases-within-same-instance/

The below example talks about

(1) Nickname creation, and

(2) Loading data using nickname created

****

db2 => connect to feddb2

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => create table t1_fed2 (c1 char(30))
DB20000I The SQL command completed successfully.

db2 => create nickname db2inst1.t1_fed1_nick for server1.db2inst1.t1
DB20000I The SQL command completed successfully.

db2 => declare c cursor for select * from db2inst1.t1_fed1_nick
DB20000I The SQL command completed successfully.

db2 => load from c of cursor replace into db2inst1.t1_fed2 NONRECOVERABLE
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL1193I The utility is beginning to load data from the SQL statement ”
select * from db2inst1.t1_fed1_nick”.

SQL3500W The utility is beginning the “LOAD” phase at time “07/29/2016
06:01:48.276446”.

SQL3519W Begin Load Consistency Point. Input record count = “0”.

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. “2” rows were read from the
input file.

SQL3519W Begin Load Consistency Point. Input record count = “2”.

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the “LOAD” phase at time “07/29/2016
06:01:48.692010”.
Number of rows read = 2
Number of rows skipped = 0
Number of rows loaded = 2
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 2

db2 => drop nickname db2inst1.t1_fed1_nick
DB20000I The SQL command completed successfully.

db2 => runstats on table db2inst1.t1_fed2 ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL
DB20000I The RUNSTATS command completed successfully.

db2 => commit work
DB20000I The SQL command completed successfully.
db2 => load query table db2inst1.t1_Fed2
Tablestate:
Normal

 

DB2 LUW Federation between 2 databases within same instance

The objective of this post is to establish communication between two databases within SAME INSTANCE. For example: I have two databases – FEDDB1, FEDDB2 – within db2inst1. The below setup talks about accessing T1 table in FEDDB1 from FEDDB2 database.

Step1: Enable Data Federation at instance level

$ db2 update dbm cfg using FEDERATED YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

$ db2stop
07/29/2016 05:33:07 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.

$ db2stop force
07/29/2016 05:34:06 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

$ db2start
07/29/2016 05:34:39 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

Step2:  Create two databases (FEDDB1, FEDDB2)

$ db2 “create database FEDDB1”

$ db2 “create database FEDDB2”

$ db2 =>

db2 => connect to feddb1

db2 => create table t1(c1 char(30))

db2 => insert into t1 values (‘From FEDDB1’)

db2 => connect to FEDDB2

db2 => create wrapper drda

db2 => create server SERVER1 type db2/udb version 11.1 wrapper drda authorization “db2inst1” password “password” options (DBNAME ‘FEDDB1’)

db2 => create user mapping for db2inst1 server SERVER1 options (remote_authid ‘db2inst1’, remote_password ‘password’)

db2 => connect

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => select * from server1.db2inst1.t1

C1
——————————
From FEDDB1

1 record(s) selected.

db2 => create nickname DB2INST1.T1_NICK FOR SERVER1.DB2INST1.T1
DB20000I The SQL command completed successfully.

db2 => select * from t1_nick

C1
——————————
From FEDDB1

1 record(s) selected.

db2 => connect to feddb1

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB1

db2 => insert into t1 values (‘From FEDDB1: Testing again’)
DB20000I The SQL command completed successfully.
db2 => connect to feddb2

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => select * from t1_nick

C1
——————————
From FEDDB1
From FEDDB1: Testing again

2 record(s) selected.

 

 

 

 

Oracle Row_number analytical function with partition by multiple columns

The below example explains below

(1) Usage of row_number function in oracle

(2) How to partition result set using multiple columns.

***

drop table t1;
create table t1(owner char(10), segment_name char(20), num_rows number, insert_date date);

insert into t1 values (‘SYS’, ‘T1’, 10, sysdate);
insert into t1 values (‘SYS’, ‘T2’, 20, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 30, sysdate);
commit;
insert into t1 values (‘SYS’, ‘T1’, 11, sysdate);
insert into t1 values (‘SYS’, ‘T2’, 21, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 31, sysdate);
commit;
insert into t1 values (‘SYS’, ‘T1’, 12, sysdate);
insert into t1 values (‘SYS’, ‘T3’, 32, sysdate);
commit;

— select owner, segment_name, num_rows, to_char(INSERT_DATE, ‘YYYYMMDD’) from t1;

select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1;

select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

insert into t1 values (‘SYS’, ‘T4’, 40, sysdate);
commit;

select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

create view v_t1 as
select * from
(
select owner, segment_name, num_rows, insert_date,
row_number() over (partition by segment_name, to_char(INSERT_DATE, ‘YYYYMMDD’) order by insert_date desc) rno
from t1
) ST1
WHERE ST1.rno = 1;

select * from v_t1;

insert into t1 values (‘SYS’, ‘T5’, 50, sysdate);
insert into t1 values (‘SYS’, ‘T4’, 41, sysdate);
commit;

select * from v_t1;

***

db2 luw: Grant connect privilege on database

Below is the syntax to grant PROD user the CONNECT privilege on a database named PRODDB:

db2 => connect to PRODDB

Database Connection Information

Database server = DB2/AIX64 10.1.4
SQL authorization ID = DB2INST1
Local database alias = PRODDB

db2 => grant connect on database to user PROD
DB20000I The SQL command completed successfully.
db2 =>

DB2 LUW: OFFLINE (vs) ONLINE Load

Problem statement:

I am seeing below from output of “db2 list utilities show detail”. Can you please explain why it’s OFFLINE load?

OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE PROD.CUSTOMER

Answer:

User is trying to do “LOAD INSERT” with out allowing READ Access, something like below:

db2 “LOAD from <filename> MODIFIED BY COLDEL<column delimiter> MESSAGES <messages.out> INSERT INTO PROD.CUSTOMER”

(or)
db2 “LOAD from <filename> MODIFIED BY COLDEL<column delimiter> MESSAGES <messages.out> INSERT INTO PROD.CUSTOMER ALLOW NO ACCESS”

Note: By default LOAD INSERT restricts access to the table. This is the same scenario here.

To convert load into ONLINE mode, we have to add “ALLOW READ ACCESS” clause as below:

db2 “LOAD from <filename> MODIFIED BY COLDEL<column delimiter> MESSAGES <messages.out> INSERT INTO PROD.CUSTOMER ALLOW READ ACCESS

SQL3340N Unable to perform a load with concurrent read access to the table

Problem Statement:

I am receving bSQL3340N error while trying to LOAD REPLACE in DB2 LUW

[dbserver1]/mnt/loads:> db2 “load from 10.out of DEL MODIFIED BY coldel, messages msg.out replace INTO db2inst1.customer NONRECOVERABLE ALLOW READ ACCESS”
SQL3340N Unable to perform a load with concurrent read access to the table.
Reason code = “1”.

db2 ? SQL3340N
SQL3340N Unable to perform a load with concurrent read access to the
table. Reason code = “<reason-code>”.

Explanation:

The ALLOW READ ACCESS option of the LOAD command is not supported in the
following cases as specified in the “<reason-code>”:
1. When using LOAD REPLACE.
2. When using INDEXING MODE DEFERRED.
3. When the target table is in the Set Integrity Pending state and is
not in the Read Access only state.
4. When the indexes are marked invalid.
5. When using LOAD TERMINATE or LOAD RESTART on a load that did not use
the ALLOW READ ACCESS option, or when temporary files from the
original load are missing.

User response:

Resubmit the command using the ALLOW NO ACCESS option.
Solution:
We need to resubmit the load by taking out “ALLOW READ ACCESS”

Oracle Recycle bin

Problem statement:

This morning I dropped close to 100GB of unnecessary data from one of my non production systems and was hoping SUM(BYTES) from dba_segments will show 100GB lesser than previous check.

But to my surprise, SUM(BYTES) was showing the same size even after dropping tables worth of 100GB.

Reason:

The reason is RECYCLE BIN, which is ON by default.

SQL> show parameter recyclebin

NAME TYPE VALUE
———————————— ———– ————————–
recyclebin string on

 

When I dropped those tables, DBMS doesn’t immediately remove the space allocated to these tables. Instead, instance renamed these tables and kept in recycle bin. This looks like a nice feature, because if we drop a table by mistake then we can reclaim the table back from recycle bin. This feature is called Flashback Drop, and FLASHBACK TABLE statement is used to restore the table.

To purge entire recycle bin (i.e., recycle bin for all users), you need to run below statement:

SQL> purge dba_recyclebin;

Note 1: How older data you can recover is depends upon db_flashback_retention_target parameter. This parameter is in minutes. By default 24 hours.

SQL> show parameter retention

NAME TYPE VALUE
———————————— ———– ——————-
db_flashback_retention_target integer 1440

Note 2: How to drop a table with out keeping in recycle bin

Example: DROP TABLE <SCHEMA_NAME>.<TABLE_NAME> purge;

 

 

DB2 LUW: Primary server maintenance in HADR scenario

Scenario: I am using HADR on 2 boxes in primary and standby scenario.

Current Primary: box1

Current Standby: box2

We have some maintenance activity on box1. Please advise on what to do?

Solution:

At high level ,you need to make current standby as primary, take box1 out of HADR pari, perform your maintenace activity on box1, and then make box1 as primary.

Below are detailed steps you need to follow:

Step1: Make box2 as primary

(a) Login to box2 server
(b) db2 takeover hadr on database <dbname>
(c) You can confirm HADR status using: db2pd -db <Dbname> -hadr
Step2: Login to box1 and perform below steps to take it out of HADR pair

(a) Confirm hadr on box1 is STANDY: db2pd -db <Dbname> -hadr
(b) deactivate the database using: db2 deactivate database <DBNNAME>
(c) db2 stop hadr on database <dbname>
(d) db2pd -db <dbname -hadr

Step3: Once your maintenance activity on box1 is complete, you need to bring it as standby and then takeover

db2 start hadr on database <dbname> as standby
db2pd -db <dbname> -hadr
db2 takeover hadr on database <dbname>
db2pd -db <dbname> -hadr