I'm working on a migration project where the environment involves migration of DB2 database data on to Oracle database. To perform this migration, I started learning DB2 basics. A few hiccups, a few here and there, a few big sighs... I have collected a few useful commands and info from DB2 world which we will discuss in this post with it's Oracle counterpart. I've also planned to update this post as and when possible to keep me as well as the readers updated on DB2. Let's get into DB2 world...
1. Main system process
In Oracle world, one would check whether any instance is running on Linux or Unix server by issuing the following command.
The equivalent command to check whether a DB2 instance is running or not would be given by this below command
There is also a db2 command "db2ilist" which can be used to find the instances available on the server.
1. Main system process
In Oracle world, one would check whether any instance is running on Linux or Unix server by issuing the following command.
[oracle@exdevdbadm01 ~]$ ps -ef|grep pmon oracle 5440 1 0 Feb07 ? 00:11:57 ora_pmon_MV43DRAC1 oracle 6493 1 0 Feb07 ? 00:20:55 ora_pmon_NBDEV3601 oracle 282503 279581 0 08:52 pts/4 00:00:00 grep pmon oracle 387464 1 0 Feb28 ? 00:03:49 ora_pmon_pl360cdb1 [oracle@exdevdbadm01 ~]$
The equivalent command to check whether a DB2 instance is running or not would be given by this below command
$ ps -ef|grep db2sysc oraclei1 14155954 60424412 0 08:54:41 pts/3 0:00 grep db2sysc oraclei1 17891578 31326286 0 Feb 08 - 21:00 db2sysc 0 devptei1 39518344 19333232 39 Jan 31 - 582:44 db2sysc 0 $where db2sysc - DB2 system controller is a main system controller EDU; it handles critical DB2 server events
There is also a db2 command "db2ilist" which can be used to find the instances available on the server.
$ db2ilist oraclei1 devptei1 $
2. List databases
Until Oracle version 11g or Oracle version 12c where parameter enable_pluggable_database is set to FALSE, we would have an instance mapped to a database which are meant as one unless there specifically is a change of database name from the instance name.
While coming to the Oracle database version 12c container configuration, we may have one or more pluggable databases under one database or instance which is the exact model when it comes to DB2. DB2 has a single instance which can contain multiple databases under the instance. Let's see how to get the list of databases under a particular instance in both Oracle and DB2.
[oracle@adm01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 20 09:07:35 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLSAND1 READ WRITE NO
4 PLBASE READ WRITE NO
5 PLBASED1 READ WRITE NO
SQL>
In Oracle, one must connect to the database and issue the "show pdbs" command to list the databases under the container.$ export DB2INSTANCE=oraclei1 $ db2 list database directory System Database Directory Number of entries in the directory = 2 Database 1 entry: Database alias = ORACNV1 Database name = ORACNV1 Local database directory = /database/plans360 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = Database 2 entry: Database alias = DEVPTE2 Database name = DEVPTE2 Node name = PTENODE Database release level = 10.00 Comment = Directory entry type = Remote Catalog database partition number = -1 Alternate server hostname = Alternate server port number =In the above DB2 example, one can see there are 2 databases listed for the current instance connected. The Database 1 entry is a local database which can be identified using the "Local database directory"
Database 2 entry is a database which is cataloged to the current instance, meaning the remote database can be accesses using the current instance. I'll discuss what is cataloging later in this post.
3. Connecting to database
In DB2, one can connect to a database using the below command.
$ db2 connect to oracnv1 Database Connection Information Database server = DB2/AIX64 10.5.7 SQL authorization ID = ORACLEI1 Local database alias = ORACNV1 $So when connected, all the db2 queries issued are against the connected database unless the connectivity is changed again.
Or, one may also connect to the db2 prompt and then issue all the commands under db2 prompt as below.
$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.7
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to oracnv1
Database Connection Information
Database server = DB2/AIX64 10.5.7
SQL authorization ID = ORACLEI1
Local database alias = ORACNV1
db2 => quit
DB20000I The QUIT command completed successfully.
$
4. Instance configurations
All the instance parameters can be queried from the Oracle database using the "show" command or the v$parameter view. In DB2 world this can be done by issuing the below command.
$ db2 get database manager configuration
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x1000
CPU speed (millisec/instruction) (CPUSPEED) = 2.046825e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 32
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =
....
....
<< snipped a few lines >>
....
....
Current effective arch level (CUR_EFF_ARCH_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0
Current effective code level (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0
Keystore type (KEYSTORE_TYPE) = NONE
Keystore location (KEYSTORE_LOCATION) =
$
Now this can further be filtered by using "grep" command to find the particular configuration that we are interested in. I'll show the example of getting the PORT details to connect to the particular instance from remote clients if allowed in the next point.5. Finding the db2 port
This is similar to what Oracle's listener port does. One should know the db2 port on which the instance is listening to make any connection from client outside the server.
To facilitate external connections the Database manager authentication (AUTHENTICATION) configuration parameter should be set to SERVER or SERVER_ENCRYPT.
Take a look at this link to understand more.
$ db2 get dbm cfg | grep SVCE TCP/IP Service name (SVCENAME) = DB2_oraclei1 SSL service name (SSL_SVCENAME) = $ grep DB2_oraclei1 /etc/services DB2_oraclei1 60058/tcp DB2_oraclei1_1 60059/tcp DB2_oraclei1_2 60060/tcp DB2_oraclei1_3 60061/tcp DB2_oraclei1_4 60062/tcp DB2_oraclei1_END 60063/tcp $We first get the service name of the instance from the database manager configuration and then get the PORT number used by the service using the services file under /etc.
This PORT number is required if you tend to connect to the DB2 database using SQL Developer as a client to handle data in a graphical environment.
6. Catalog a DB2 database
Catalog is a term used in DB2 world to add the database details to the current instance so that the database can be accessed from the instance. This is similar to adding the tnsnames configuration to the tnsnames.ora file under Oracle home. To catalog a DB2 database, follow the procedure below.
First add a NODE to the server. This might be of two types - Adding a local node or adding a remote node.
The commands will be as below.
For Local node:
$ db2 CATALOG LOCAL NODE <node name> INSTANCE <instance name>For remote node:
$ db2 catalog tcpip node <node name> remote <server name> server <port> $ ## For eg: $ db2 catalog tcpip node TEST remote alpha.dev.com server 50001
Here I'll be attempting to add a local node and catalog a database to the node.
$ db2 CATALOG LOCAL NODE PTENODE INSTANCE devptei1 SQL1018N The node name "PTENODE" specified in the CATALOG NODE command already exists. $ db2 list node directory Node Directory Number of entries in the directory = 1 Node 1 entry: Node name = PTENODE Comment = Directory entry type = LOCAL Protocol = LOCAL Instance name = devptei1We can see the node is already added which can be confirmed by "list node directory" command. Proceeding further to catalog the database to the node.
$ db2 CATALOG DATABASE DEVPTE1 AT NODE PTENODE DB20000I The CATALOG DATABASE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. $ db2 list database directory System Database Directory Number of entries in the directory = 3 Database 1 entry: Database alias = DEVPTE1 Database name = DEVPTE1 Node name = PTENODE Database release level = 10.00 Comment = Directory entry type = Remote Catalog database partition number = -1 Alternate server hostname = Alternate server port number = Database 2 entry: Database alias = DEVPTE2 Database name = DEVPTE2 Node name = PTENODE Database release level = 10.00 Comment = Directory entry type = Remote Catalog database partition number = -1 Alternate server hostname = Alternate server port number = Database 3 entry: Database alias = ORACNV1 Database name = ORACNV1 Local database directory = /database/plans360 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = $ db2 connect to devpte1 Database Connection Information Database server = DB2/AIX64 10.5.7 SQL authorization ID = ORACLEI1 Local database alias = DEVPTE1 $Now the database is cataloged to the instance and can be accessed without any issues.
Many more on this link
Happy DB2ing!!!
No comments:
Post a Comment