RSS

Category Archives: DB2

All I know about DB2

2 Ways of Updating/Deleting the bulk data from the table

Being a developer of DB2 LUW, I had written many Migration scripts where I needed to perform data manipulation on bulk data. Most known problem for such operations on bulk data is that most of the time we face transaction log full problem:

“SQL0964C The transaction log for the database is full”

This problem occurs frequently when the amount of data is huge. For example, consider a performance database or production database table having millions of customers and we need to manipulate all the customer data for some value.

To resolve this problem, DBAs usually configure the database parameters such as LOGBUFSZ, LOGFILSIZ, LOGPRIMARY and LOGSECOND. This approach is trail and error approach where we need to set the particular values and try for the operations.

But along with this, over the time I learned ways to perform such bulk operations using the PL/SQL operations.

Note: I use the sample database while providing the example. These examples are just for reference and can be used in different scenarios. For SAMPLE database, we don’t need to use these methods since the data is very low there.

1) Update n records at a time using GET DIAGNOSTICS:

We can write a stored procedure Where inside while Loop we can use the update statement in little different way as follows:

WHILE nCount <> 0
DO
   UPDATE (SELECT * FROM Trnsct_tab WHERE isUpdated = 0 FETCH FIRST 10000 ROWS ONLY) SET Transactky = pmtlineitemky;
   GET DIAGNOSTICS nCount = ROW_COUNT;
    COMMIT;
END WHILE;

In above example, After every 10000 records COMMIT operation will be performed and the transaction log will get cleared. I used 10000 records at a time. We can use any number depending on our environment and parameter values.

Read the rest of this entry »

 
1 Comment

Posted by on June 6, 2014 in Databases, DB2

 

[ SQL2216N] REORG Failure – “Error Code ‘-968’ while reorganization of the database”

I observed this issue recently when I was monitoring the output of the shell script that performs REORG and RUNSTATS operation periodically on all the tables of our UAT database.

We perform continuous performance tests on our application. Therefore, periodic REORG and RUNSTATS becomes mandatory for better performance.

When I found error in the output file, I observed that for the couple of high cardinality tables the REORG operation failed with the following error message:

SQL2216N Error Code ‘-968’ while reorganization of the database

One of such table was having 8.5 millions rows with 170 columns including couple of CLOB columns. For such huge tables, records were getting inserted during every transaction and it was getting bigger and bigger after every performance test.

With such large tables in place, file system was almost FULL and FAILURES increased at an alarming rate while testing.

Read the rest of this entry »

 
Leave a comment

Posted by on May 14, 2014 in Databases, DB2

 

Tags: , , , ,

#DB2-9 The DB2Night Show #131: DB2’s GOT TALENT Grand Finale!

This post is reference to my presentation in the Grand Finale of DB2’s Got Talent 2014 at DB2Night Show season. This was my last presentation in this season and the show theme was DB2 Success Story. I have selected one of my favourite topic i.e. Federated Database Performance. I have been working on the Federated setup since last 1.5 years. I faced many performance issues on production for which resolutions were difficult that is the reason I spent most of my time in searching and learning different ways for performance improvement in such distributed systems. I had given the presentation on Federated Query performance before on The DB2Night Show #128. This one is the extension of my previous presentation. You can check out the Federation basics and recap of the Episode #128 presentation at this link.

I discussed how distributed data across multiple databases can be accessed using the Federated database systems. Also specified the components of the federation like wrappers, servers, nicknames etc. Here are the few steps to setup the federated database.

CREATE WRAPPER NET8
LIBRARY ‘libdb2net8.a’ ;

CREATE SERVER ora_server TYPE oracle
VERSION 11.2.0 WRAPPER net8
OPTIONS (NODE ‘ORCL’);

CREATE USER MAPPING FOR userName
SERVER ora_server
OPTIONS (REMOTE_AUTHID ‘username’, REMOTE_PASSWORD ‘pwd) ;

CREATE NICKNAME ora_emp FOR ora_server.scott.emp;

Read the rest of this entry »

 
Leave a comment

Posted by on April 15, 2014 in Databases, DB2, db2nightshow

 

Tags: , , , , , ,

#DB2-8 The DB2Night Show #130: DB2’s GOT TALENT Top 7 Finalists Compete!

This post is based on the Multi-Temperature Storage Management Feature Introduced in DB2 10.1. I presented this topic at DB2Night Show on March 21, 2014. The theme for this episode was Storage. Since I am obsessed with the new fabulous features introduced in this version, without any second thought I chose this topic for this round of Finale.

This episode decided our progression for the Grand Finale and fortunately based on votes I qualified for the Grand Finale.
Here is the quick overview of the Multi-Temperature Storage Management Feature.

Storage Groups:

Storage groups are the logical groupings of the automatic storage paths. The storage paths grouped by a particular are identified by the same storage path characteristics like latency, overhead, transfer-rate etc. Once the storage group is created the, we can assign the Automatic Storage tablespaces to the respective storage group. This association of the tablespaces with the storage group is completely dynamic and we can change it whenever required. Here is the visual illustration of how storage groups are formed.

 

storageGroup

 

 

Multi-Temperature Storage Management:

Read the rest of this entry »

 
Leave a comment

Posted by on April 8, 2014 in Databases, DB2, db2nightshow

 

Tags: , , , , , , ,

#DB2-5 DB2Night Show #125: DB2’s GOT TALENT Contestant Search #2

This article is based on the “Online Tablespace Migration Using ADMIN_MOVE_TABLE Procedure” presentation for the The DB2Night Show #125: DB2’s GOT TALENT Contestant Search #2 held on Friday, 14th February 2014. I made it through the qualifying round but frankly speaking it was heavy competition out there in the Round 1 itself. Total 7 ideas presented and all of them had done their job extremely well. 3 of us – Michael, Raja and myself got a chance to present into the finals and rest of the contestants got chance to present again on 28th February 2014. Here are few outlines of my presentation in Round 1.

PROBLEM STATEMENT
Table with more than 100K records needs to be migrated from 8K tablespace to 16K with minimum downtime and minimum risk of data loss.

I found out that to resolve this problem we have two possible solutions
1. EXPORT AND LOAD METHOD
In this we need to export all the data and then drop the table and re-create the table in new tablespace and re-create the constraints,indexes, views and MQTs referring to that table. And for the DB2 versions prior to DB2 9.7, probably this is the only solution we have.

But here are few Bottlenecks:
a. Migration needs to be OFFLINE.
b. There is always a risk of data loss.
Regarding data loss, I have this experience on our UAT environment. Since we have constraints on our disk space on database server. Developer executed the script by this method and due to disk space only half of the records got exported and those many only imported into the new table which cause a real trouble for me at work. 😦
So to avoid such mishaps, there is another way to all those who are using DB2 versions 9.7 and higher.

2. ONLINE MIGRATION USING ADMIN_MOVE_TABLE PROCEDURE
Here are the steps how this will work.
a. Drop MQTs, referential constraints and Views referencing the subject table.
b. Migrate the table to 16K tablespace using SYSPROC.ADMIN_MOVE_TABLE procedure.
c. Re-create the MQTs, referential constraints and Views.

ADMIN_MOVE_TABLE: SYNTAX
Schema for this routine is SYSPROC.

db2 “CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename> , <data_tbsp>, <index_tbsp>,
<lob_tbsp>, <mdc_col_list>, <hash_key_list>,<range_part_key_list>, <column-definitions>,<options-list>,’MOVE’)”

For online tablespace migration we can ignore the few input parameters and pass them as NULL. Here is sample example:

—db2 “CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename>, <data_tbsp>, <index_tbsp>, <lob_tbsp>, NULL, NULL, NULL, NULL , <options-list>,’MOVE’)”

Read the rest of this entry »

 
2 Comments

Posted by on March 15, 2014 in DB2

 

Tags: , , , , ,

#DB2-3 The current transaction was rolled back because of error “-430”. SQLCODE=-1476, SQLSTATE=40506,DRIVER=3.51.90

Few days back during Load Testing on performance environment I came across one issue. The issue was as follows:

When I connect to the application then initially it works fine. But in performance environment as the number of users connecting to the applications
increases at one point all the connections got terminated giving the following error:

com.s1.arch.persistence.exception.PersistenceException: The current transaction was rolled back because of error “-430”.. SQLCODE=-1476, SQLSTATE=40506, DRIVER=3.51.90

Environment Details:
IBM InfoSphere Federation Server with IBM DB2 9.7 FP6

In this case the federated tables are not being able to access through nicknames.While searching out for this
I came across the following possible causes that might be responsible for this situation:

1. The Temporary Tablespace might be full.
2. The application Heap might got full because of which application was unable to process the sql statements.

But both these was not the cause since temporary tablespace was system managed and the APP_HEAP was AUTO-RESIZE.
Tablespace can be cross-verified using the syscat.tablespaces and APP_HEAP was checked in the database configuration parameters.
When I delved more into db2diag.log, I came to know about the actual cause behind this.
The issue was that the resources memory used by the processes was very low.
Due to limited resources db2fmp process was unable to create the new db2fmp thread which were causing the db2fmp unstable.
Because of which we were unable to login to the application after sometime.

Read the rest of this entry »

 
Leave a comment

Posted by on February 26, 2014 in DB2

 

#DB2-2 ONLINE TABLESPACE MIGRATION USING SYSPROC.ADMIN_MOVE_TABLE

Recently I faced couple of issues while migrating the table from one table space to another table space.

As per my knowledge the way that is commonly followed everywhere is:
1) export the data
2) Drop constraints and indexes
2) drop the table
3) Create table in new tablespace
4) Re-create the constraints
5) Import the data

In this way the bottleneck might be:

1) We need to push the table down i.e. the migration need to be performed OFFLINE.
2) Export and import operation might be costly and time consuming in case of big tables.
While doing POC on this I came across this new method to move the table ONLINE in few steps.
This procedure (SYSPROC.ADMIN_MOVE_TABLE) help us to move the table online in more better way.

This stuff is very cool 🙂
SYSPROC.ADMIN_MOVE_TABLE:

In this while copying the intermediate table called staging table is used to record any changes those are occurring during the migration. 4 triggers are used to record these changes.

There are 4 important stages in this procedure:

1) INIT STAGE – Check if the table is in the state to perofrm the given operation
2) COPY STAGE – Copy data from source table to target table. In this there is access to the source table while copy is in progress
3) REPLAY STAGE – We scan staging table and replay these changes in the target table
4) SWAP STAGE – Rename the target table to source table and the original table is removed.

STEPS FOR THE MIGRATION OF THE TABLE USING SYSPROC.ADMIN_MOVE_TABLE:

1) Drop all unique indexes and foreign key constraints on the table.
2) Drop Primary Key constraint as well as unique index on the primary key
3) Create a temporary table in the new tablespace
4) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’INIT’)”
5) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’COPY’)”
6) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’REPLAY’)”
7) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’SWAP’)”
8) Re-create all the constraints and indexes.

NOTE:

1. Instead of performing the INIT, COPY, REPLAY and SWAP stage individually, we can perform this all at once in single step as follows:

db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’MOVE’)”

2. While testing this in few cases, I faced the following issue:
SQL2104N The ADMIN_MOVE_TABLE procedure could not be completed at this time
by this user. Reason code: “4”. SQLSTATE=5UA0M

3. I had written the following Stored Procedure to take care of Error#2. This will by pass this error by using the flag.

This error occurs if the table is not in the state to get migrated or if the table is in any intermediate state.

CREATE PROCEDURE ADMIN_MOVE_TABLE_SP
(
IN pSchemaName VARCHAR(50),
IN pTableName VARCHAR(50),
IN pTargetDataTbspace VARCHAR(100),
IN pTargetIndTbspace VARCHAR(100),
IN pTargetLOBTbspace VARCHAR(100)
)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0000;
DECLARE c_duplicate INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘5UA0M’
SET c_duplicate = 1;

CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,

pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’MOVE’);

IF (c_duplicate = 1) THEN
CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’CANCEL’);

CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’MOVE’);
SET c_duplicate =0;
END IF;

END

4. This procedure is introduce in DB2 9.7 so will be applicable in all the versions after DB2 9.7.

5. I follow the attached document and the following blog which are very helpful:

http://blog.joachim-selke.de/2009/12/how-to-move-a-table-to-a-different-tablespace-in-db2/

6. To understand the thorough working of this procedure following pdf  I feel is handy:

http://www.dbisoftware.com/media/IBM-Ergin-Babani-DB2LUW-Online-Table-Moves.pdf

Limitations:
One of the limitation I found while studying this is that, All the parameters we are passing to the stored procedure need to
be in the Capital letters. I am not sure why it is so but if we mentioned it in small case or mixed case it will not work.

Thanks

 
3 Comments

Posted by on October 23, 2013 in DB2