RSS

#DB2-4 DB2NightShow – Delicious Dinner For Fridays

This post is about my experience at db2nightshow as a participant for DB2’s Got Talent 2014. I am into the finals of the contest and my journey ahead solely depends on the votes of DB2 Lovers all over the World. Check out my presentations of DB2’s Got Talent at the DB2 Night Show Replays and vote me and my friends to move ahead. Here is my experience and thoughts on db2nightshow and DB2’s Got Talent contest.

The day I came to know about this show, I never miss a single episode of it. New Ideas, New concepts, new technologies…this is what db2nightshow is all about. It’s a perfect training place for any newbie in the world of DB2. Here we meet new people,experts in the DB2 World. I met many such experts like @ember_crooks @srhayes @MKrafick @susvis @klaas_brant and many more.

I came across this wonderful show when I was hunting for one of my issue on Online Tablespace Migration and guess what I got the gem.  You won’t believe but the db2nightshow changed my routine on weekends at some point. I am a big lazy man and love sleeping on the weekends unlike all. But When I came to know about this, every weekend I spend in watching all my pending episodes of db2nightshow and I hope I will complete the backlog very soon. One more change in me is I am getting Twitter addicted instead of Facebook..Hahaha

Diverse topics, Limited words but Precise solutions, interactive graphics while presentation these are the stuffs that impressed me a lot. And yes miss one important thing- Scott’s commentary. He makes the show soo interactive and watchful. Thanks to Scott for hosting such an amazing show. I like the tag line of the db2nightshow: Educate – Inform – Entertain.

Every year db2nightshow conducts a DB2 Olympics called DB2’s Got Talent. I participated in the Contest this year and happy to be a part of this contest. I enjoyed many presentations on DB2 Performance, DB2 Utilities, Many unknown topics for me like Column masking, Temporal Tables etc. I presented at the show twice once during the qualification round on Online Tablespace Migration Using ADMIN_MOVE_TABLE utility and in the first round of Finals on Query Tuning in the Federated Systems. Along with the DB2, I learned many more things here in this Olympics of DB2 like presenting your point precisely in limited time of 4 minutes, how to improve the presentation skills. Helpful comments by expert judges always help us to improve the presentation and present a good show next time. Typical comments on topic contents, presentation graphics is going to help not only in the finals of the contest but at many more incidents coming in my future. The most important part I loved after presenting any presentation at db2nightshow is the most wonderful comments and the analysis by Ember on every presentation of the episode on www.db2commerce.com – Big Warehouse of the DB2.

I am not sure what’s gonna happen in the upcoming Finals of the contest but I am going to give my best try everytime and whatever are the results I am going to be here again and again. I like the t-shirt of Mariana @db2nightshow. It’s tag line is – KEEP CALM AND USE IBM DB2. I love db2nightshow and Love IBM DB2. I will come up with more details about the topics I presented in coming posts.

 
Leave a comment

Posted by on March 8, 2014 in Memoir

 

#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

 

#ORACLE-1 Steps For Manual Database Creation for Oracle

This is my first post for oracle. I am learning it and here is the very basic work Creation Of Oracle Database. Ohh God So much of Work..!! I always miss at least some of it. 😛

DB2 database creation is way simpler than the oracle one.

So documenting steps I followed for the database creation:

Follow the following steps

1. Prepare the pfile File:-

    a. When an Oracle Instance is started, the characteristics of the Instance are established by parameters          specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or    SPFILE. We commonly refer it as the INIT.ORA file. Specifically  I used the PFILE for my database creation.

    b. In pfile path for the parameters like control_file,db_recovery_file_dest,audit_file_dest,diagnostic_dest etc is     mentioned. During the manual db creation we need to manually create these paths as mentioned in pFile.

2. Setting the environment:-
Set the Oracle instance environment by exporting the ORACLE_SID,ORACLE_HOME etc.
e.g. : export ORACLE_SID=
export ORACLE_HOME=
export LD_LIBRARY_PATH=

Read the rest of this entry »

 
Leave a comment

Posted by on November 21, 2013 in Oracle

 

Tags: , , , ,

#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

 

#DB2-1 PROCEDURE TO REDUCE THE SIZE OF DB2 DATABASE

Here is my attempt to write the stored procedure which will reduce the database size by reducing the size of all the Database Managed
Table-spaces. I purposely exclude the system managed table-space as well as USER managed table-space including
MEDIUM_TSD and MEDIUM_TSI (for my requirement).

I make the AUTORESIZE option ON and INCREASESIZE because in case if any after reducing the table-space size, it got full then it should automatically
increase the tablespace size by say 5 or 10 percent. (In my case it is 5%).
Actual logic for reducing the tablespace size:

‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ REDUCE (ALL ‘ || TRIM(CHAR(v_TBSP_FREE_PAGES – 100)) || ‘)’;

It will reduce the the tablespace size by reducing the number of free pages. It will keep only 100 free pages per tablesapce.
I make the use of the prepared statement to make it dynamic by selecting the tablespace name and the number of free pages (TBSP_FREE_PAGES)
from views syscat.tablespaces and SYSIBMADM.TBSP_UTILIZATION.

One can find the view details at the standard IBM links.

Following is the Stored Procedure:
— ———————————————————————————————————————————
CONNECT TO @DBNAME@
/

SET CURRENT SCHEMA = ‘@SCHEMANAME@’
/

SET CURRENT PATH = @SCHEMANAME@, SYSFUN, SYSIBM, SYSPROC, SYSIBMADM, @USERNAME@
/
DROP PROCEDURE REDUCE_DB_SIZE
/

CREATE PROCEDURE REDUCE_DB_SIZE
(
)
LANGUAGE SQL
SPECIFIC REDUCE_DB_SIZE
BEGIN

DECLARE SQLCODE INTEGER;
DECLARE v_TBSPACE VARCHAR(128);
DECLARE sql_stmt VARCHAR(500);
DECLARE sql_stmt1 VARCHAR(500);
DECLARE v_TBSP_FREE_PAGES BIGINT;
DECLARE v_notFound INT DEFAULT 0;
DECLARE c_notFound CONDITION FOR SQLSTATE ‘02000’;
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE v_stmt1 VARCHAR(500);
DECLARE v_stmt2 VARCHAR(500);

DECLARE C1 CURSOR WITH HOLD FOR
SELECT a.TBSPACE, b.TBSP_FREE_PAGES FROM SYSCAT.TABLESPACES a, SYSIBMADM.TBSP_UTILIZATION b
WHERE a.TBSPACE = b.TBSP_NAME
AND a.TBSPACETYPE = ‘D’
AND TBSPACE NOT LIKE ‘%SYS%’
AND TBSPACE NOT LIKE ‘%USER%’
AND b.TBSP_FREE_PAGES > 100
AND a.TBSPACE NOT IN (‘MEDIUM_TSD’,’MEDIUM_TSI’);

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_no_data=1;

DECLARE CONTINUE HANDLER FOR c_notFound
SET v_notFound = 1;

OPEN C1;
FETCH C1 INTO v_TBSPACE,v_TBSP_FREE_PAGES;
WHILE ( v_notFound = 0 ) DO
SET sql_stmt = ‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ AUTORESIZE YES INCREASESIZE 5 PERCENT’;
SET sql_stmt1 = ‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ REDUCE (ALL ‘ || TRIM(CHAR(v_TBSP_FREE_PAGES – 100)) || ‘)’;
PREPARE v_stmt1 FROM sql_stmt;
EXECUTE v_stmt1;
COMMIT;
PREPARE v_stmt2 FROM sql_stmt1;
EXECUTE v_stmt2;
FETCH C1 INTO v_TBSPACE,v_TBSP_FREE_PAGES;
END WHILE;
CLOSE C1;
END
/

GRANT EXECUTE ON PROCEDURE REDUCE_DB_SIZE TO USER @USERNAME@
/

COMMIT
/

CONNECT RESET
/

— ———————————————————————————————————————————-
This procedure is not responsible for reducing the database backup size but it will also reduce the actual database size.
We can check the actual database size using the following command:

CALL GET_DBSIZE_INFO(<snapshot-timestamp>, <dbsize>, <dbcapacity>, <refresh-window>);
Where,
snapshot-timestamp – Output parameter of type TIMESTAMP that returns the time at which dbsize and dbcapacity were calculated.
dbsize – An output parameter of type BIGINT that returns the size of the database (in bytes)
dbcapacity –  An output parameter of type BIGINT that returns the database capacity (in bytes).
refresh-window – An input argument of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed.
(-1 for default which is 30 minutes)

If there are any suggestions to improve it more. ALWAYS WELCOME. 🙂

 
Leave a comment

Posted by on October 15, 2013 in DB2

 

Tags: , ,