How to create federation between two DB2 databases

The other day developer were asking if they access production table in their test databases. Here when we suggested them that we have Federation support across multiple RDMS vendors.

So lets get started quickly on how to create federation between two DB2 databases

It involves 5 steps Enable database to support federation, Create wrapper, server, usermapping, nicknames . First we need to check whether Federation support is switched on for our database, If you see that FEDERATION is set to off go head and update to YES and recycle the instance

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = NO

db2inst1@test-machine:~$ db2 update dbm cfg using FEDERATED YES
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.

db2inst1@test-machine:~$ db2stop
19/02/2014 17:41:06     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

db2inst1@test-machine:~$ db2start
02/19/2014 17:41:10     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = YES

Wrapper : The CREATE WRAPPER statement registers a wrapper with a federated server. A wrapper is a mechanism by which a federated server can interact with certain types of data sources,The default wrapper name for the DB2 family data sources is DRDA.

Syntax : CREATE WRAPPER <wrappername> 

Server : The CREATE SERVER statement defines a data source to a federated database.

Syntax : CREATE SERVER <servername> TYPE <datasourcename> VERSION <serverversion> WRAPPER <wrappername> AUTHORIZATION "<remoteauthid>" PASSWORD "<password>" OPTIONS (DBNAME 'dbname') 

Usermapping : The CREATE USER MAPPING statement defines a mapping between an authorization ID that uses a federated database and the authorization ID and password to use at a specified data source.

Syntax : CREATE USER MAPPING for <authname> SERVER <servername> OPTIONS (REMOTE_AUTHID 'username',REMOTE_PASSWORD 'password')

Nicknames : The CREATE NICKNAME statement defines a nickname for a data source object. we use this nick name to fetch records from the federated database.

Syntax: : CREATE NICKNAME <nickname> for <servername>.<schenaname.tablename>

Now let me take a scenario and explain it using two db2 databases. My aim is to access table in TESTA database from the database MURALI 


murali@test-machine:~$ db2 connect to murali

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = MURALI
 Local database alias   = MURALI

create wrapper drda
DB20000I  The SQL command completed successfully.

create server fedserver TYPE DB2/UDB VERSION 9.7 WRAPPER drda  AUTHORIZATION "murali" password "murali" OPTIONS(DBNAME 'TESTA')
DB20000I  The SQL command completed successfully.

create user mapping for murali SERVER fedserver OPTIONS(REMOTE_AUTHID 'db2inst1',REMOTE_PASSWORD 'db2inst1')
DB20000I  The SQL command completed successfully.

create nickname mytable for fedserver.db2inst1.tab1
DB20000I  The SQL command completed successfully.

murali@test-machine:~$ db2 connect to murali

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = MURALI
 Local database alias   = MURALI

murali@test-machine:~$ db2 "select * from mytable"

ID      NAME      
------- ----------
     1. murali    
     2. sharath   
     3. balaji    
     4. praks     

  4 record(s) selected.

Post your comments and doubts about this topic here.

How to Drop schema including its contents in DB2

One day i was doing a schema refresh activity that's when this point came in, I first have to drop the schemas including content and then do a copy.

DB2 has provided us, with a admin procedure which drop's a specific schema and also all the objects contained in it. 

ADMIN_DROP_SCHEMA procedure

Syntax: ADMIN_DROP_SCHEMA(<schema>,<dropmode>,<errortabschema>,<errortab>)

<schemname> - Mention the name of the schema to be dropped in CAPTITAL LETTER

<dropmode> - Reserved for future use and should be set to NULL

<errortabschema> - specify the schema name of a table containing error information for objects that could not be dropped 

<errortab> - specify the name of a table containing error information for objects that could not be dropped.

Example: db2 "call ADMIN_DROP_SCHEMA('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"

db2inst1@test-machine:~$ db2 "call admin_drop_schema('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"

  Value of output parameters

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

  Parameter Name  : ERRORTABSCHEMA

  Parameter Value : -

  Parameter Name  : ERRORTAB

  Parameter Value : -

  Return Status = 0

Return status=0 so it means success. Just query the schemaname in database and could see it created ERRORSCHEMA and as it didn't have any issues no errortable is created. If a schema doesn't contain any objects it easy to drop a schema using simple command

db2 "drop schema <schemaname> RESTRICT"

RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database

Let me use it on the empty schema which is just created when we tried to drop SAMPLE schema using ADMIN_SCHEMA_DROP procedure, Below is the example for it.

db2inst1@test-machine:~$ db2 "list tables for schema errorschema"

Table/View                      Schema          Type  Creation time             

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

  0 record(s) selected.

db2inst1@test-machine:~$ db2 "drop schema errorschema restrict"

DB20000I  The SQL command completed successfully.

I dropped the schema using admin_drop_schema and it has given me return status 0, still i could able to see it in catalog. After some research i understood that there are some dependants which are dropped so i was unable to drop it from catalog. I then took

DB2LOOK for the specific schema and then was able to the USER DEFINED DATA TYPES which are there so i was unable to drop the schema from catalog. I then dropped the user defined dataypes and executed the command db2 "drop schema schemaname restrict" this time

i was able to drop it from catalog. Before dropping the user defined data types i saved them in some file so if needed i can create them again. Also to make sure if your left with any more take a DB2LOOK and check again, Below is the example for the same.

db2 "drop type userdefined1"

DB20000I  The SQL command completed successfully.

db2 "drop type userdefined2"

DB20000I  The SQL command completed successfully.

db2 "drop schema schemaname restrict"

DB20000I  The SQL command completed successfully.

References from IBM : ADMIN_DROP_SCHEMA procedure V9.7

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0022036.html

Important points to be noted when doing DB2 Database Refresh to UAT or DEV environments

Hey all, today i would discuss on the important points that needs to be kept in mind when refreshing from PROD to UAT or DEV environments. Below are the issues that i recently faced when doing a refresh activity from PROD to UAT.

1) Bufferpools unavailability, only hidden bufferpools started.
2) Server hangup during rollforward phase, even doesn't allow other users to login and kill it.
3) Rollforward fails saying no enough memory to allocate.
4) Rollforward fails due to archive log location is not valid.

Overall why are these incidents happened ? Answer for this is we have different physical configurations and different settings in PROD when noted to UAT or DEV. So before doing a refresh take a backup of all the DB CFG parameters for all the databases also DBM cfg of the instance for which you intend the refresh.

1) INSTANCE_MEMORY parameter
2) DATABASE_MEMORY parameter
3) Bufferpools size
4) LOGARCHMETH1 path

My  Scenario.

We have 120 GB phsyical memory available in prod so we used 110 GB for INSTANCE_MEMORY parameter. Also for one of the db we have 50 GB set to DATABASE_MEMORY parameter so what happened when did a backup and restore in UAT .

Restore was successful, then started rollforward of db then suddenly our prompt got hanged, not responding . When even tried to login from different session its not allowing to login. So we contacted system admin, and he told that total memory being used by DB2 and due to which its not allowing the users to ssh also. He then hard reboot the machine and then I checked and could see only 62 GB of physical RAM available on the server, but our DATABASE_MEMORY was set to 110 GB automatic so we understood that this is the main reason for server hangup. We then updated the DATABASE_MEMORY to 10 GB with automatic settings. A new issue started when trying to activate the db, as bufferpools memory settings in PROD are very high so only DB started only with hidden bufferpools which we didn't observe and started to rollforward again, which resulted in failure. So for time being i used this method, I made the bufferpools started with only 3000 pages initially, Here is the environment variable to set the BP intital pages to some number of pages, use command DB2SET DB2_OVERRIDE_BPF=3000 , after setting it check if reflected or not DB2SET -ALL , this command show all the environment variables that are set. Then tried rollforward again and this time it again resulted in failure as we are having LOGARCHMETH1 in prod location not matching in the UAT server which resulted in failure. We then updated the LOGARCHMETH1 parameter to valid path and then tried rollforward again and this time its SUCCESS.  Finally we have activated and connected to database and made valid changes to bufferpool size based on database_memory and restarted the DB.


Readers anybody has any doubts or any other errors that you faced please post your comments here and we shall discuss on them and update here.

Catalog a DB2 database

Aim : Need to access a remote database as local database on the server that i currently reside on. For this we need to catalog the database

Works on : Machine having db2 server or client sotware installed on it

Its a two step process First catalog node then catalog database.

Catalog Node   - This puts an entry into node directory.
Catalog Database - This puts an entry into local database directory.

Syntax

db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT>

use can use any name for <nodename>, this is just for indetification purpose.
use the ip address or hostname of the target machine for <remote> parameter.
use port number on which the db2 server is listening to <PORT> for allowing incoming connections.

To get the ip address of machine, use command - ifconfig -a
To get the hostname or machine name, use command - hostname
To get the port number to which db2 server is listening to, use command - db2 get dbm cfg | grep SVCE 
SVCENAME is the service name of db2, which we need to match this services files in /etc/services to get the respective port number for the service name(SVCENAME) 


this is example of matching SVCENAME to respective portnumber in /etc/services file

db2inst1@test-machine:~$ db2 get dbm cfg | grep SVCE
 TCP/IP Service name                          (SVCENAME) = db2c_db2inst1
 SSL service name                         (SSL_SVCENAME) = 
db2inst1@test-machine:~$ 
db2inst1@test-machine:~$ cat /etc/services | grep db2c_db2inst1
db2c_db2inst1 50001/tcp

db2 catalog database <dbname> as <aliasname> at node <nodename>

use database name for <dbname> you can also put an alias name for that database using <aliasname> if you dont want any such just ignore it.
user node name that is defined in the first command <nodename>

Always make sure to check the connectivity to the database, that how its shown below.

db2inst1@test-machine:~$ db2 connect to test1 user murali using murali

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = MURALI
 Local database alias   = TEST1

Installation of DB2 V9.7 on UBUNTU 12.04 - PART 2

PART 2

Now drag and drop the downloaded copy of IBM DB2 Data server trail copy from your Windows machine to Ubuntu. Follow below instructions.

Always remember we are doing root installation, so remember to use sudo command / else login into root account and setup the db2

1) To extract zipped files to a directory use command                                                
tar -xvf v9.7_linuxx64_server.tar.gz       -
2) change to extracted directory use, list the files and you can now see 7 script files and 2 directories. Now we need to validate if all the prerequisites are met to continue installing DB2 in the machine.

use this command sh db2prereqcheck it will show you errors/warnings like below.

WARNING:
   The 32-bit library file libpam.so is not found on the system
ERROR:
The required library file libstdc++.so.5 is not found on the system.
ERROR:
The required library file libaio.so.1 is not found on the system.

To fix this errors, we need to download and install below packages. Use below commands

1) sudo apt-get install libpam0g:i386 
2) sudo apt-get install libstdc++5
3) sudo apt-get install libaio-dev


libpam.so.0 (32-bit) is required for DB2 database servers to run 32-bit non-SQL routines, use the command, libaio.so.1 is required for DB2 database servers using asynchronous I/O.
libstdc++.so.5 is required for DB2 database servers and clients.

After install all 3 packages above run the prereqcheck again and see if we are good. Following all the instructions above shall give you no errors and warnings during prereqcheck

Now run sh db2setup to start the actual installation, DB2 Launch pad will open.

From the DB2 Setup launchpad, you can view installation prerequisites and the release notes, or you can proceed directly to the installation. 

Click Install a Product and the Install a Product window displays the products available for installation.

If you have no existing DB2 database products installed on your computer, launch the installation by clicking Install New. Proceed through the installation following the DB2 Setup wizard prompts.

If you have at least one existing DB2 database product installed on your computer, you can:

1) Click Install New to create a new DB2 copy.

2) Click Work with Existing to update an existing DB2 copy, to add function to an existing DB2 copy, upgrade an existing DB2 Version 9.5 and Version 9.7 copy, or to install an add-on product.

After the installation completes, login into the instance user and enter db2 to enter into interactive mode. Now your are good to fly into db2 world....

db2inst1@test-machine:~$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 9.7.6

.....................................................................

.....................................................................

For more detailed help, refer to the Online Reference Manual.

db2 =>