The first part of this post can be found in this link
7. DB2 database version
7. DB2 database version
The database version can be found on Oracle database by the v$version view. In DB2 this can be found using a few methods as given below.
$ db2 connect to oracnv1
Database Connection Information
Database server = DB2/AIX64 10.5.7
SQL authorization ID = ORACLEI1
Local database alias = ORACNV1
$ db2 "select VERSIONNUMBER, VERSION_TIMESTAMP, VERSIONBUILDLEVEL from SYSIBM.SYSVERSIONS"
VERSIONNUMBER VERSION_TIMESTAMP VERSIONBUILDLEVEL
------------- -------------------------- ------------------------------
7020400 2004-12-02-20.08.48.824019 -
8020000 2005-02-07-10.32.54.570402 -
9050300 2009-02-09-11.30.01.831943 -
9070900 2014-10-29-17.32.22.790717 s140512
10050400 2014-10-29-17.34.55.809574 s140813
5 record(s) selected.
$ db2level
DB21085I This instance or install (instance name, where applicable:
"oraclei1") uses "64" bits and DB2 code release "SQL10057" with level
identifier "0608010E".
Informational tokens are "DB2 v10.5.0.7", "s151221", "IP23949", and Fix Pack
"7".
Product is installed at "/opt/IBM/db2/V10.5".
$
SYSIBM.SYSVERSIONS shows the history of the database versions along with its timestamp on when it has been upgraded.8. DDL for objects
In Oracle one can use dbms_metadata package to get the DDL information for the objects such as Tables, Indexes, etc., Showing an example of getting the DDL of a table in Oracle.
SQL> set heading off echo off pages 999 long 90000
SQL> select DBMS_METADATA.GET_DDL('TABLE','AB_DOMAIN','PLANS360') FROM DUAL;
CREATE TABLE "PLANS360"."AB_DOMAIN"
( "DOMAIN_COID" CHAR(26 CHAR) NOT NULL ENABLE,
"SEQUENCE_NO" NUMBER(18,0) NOT NULL ENABLE,
"PRIMARY_PLATFORM" CHAR(2 CHAR) NOT NULL ENABLE,
"DATA_" RAW(1024) DEFAULT '2727' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PLANS360_DATA"
SQL>
This is very easy to get in the DB2 database using db2look utility. Usage is given as an example below. If one likes to list all the tables under a particular schema, he/she can issue the following command to get the list of tables.$ db2 list tables for schema dba
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
AB_COARSEOBJECT DBA T 2004-12-02-20.12.51.662416
AB_CODEMAP DBA T 2004-12-02-20.12.55.041893
AB_CONFIG DBA T 2004-12-02-20.12.51.779508
AB_DOMAIN DBA T 2004-12-02-20.12.54.818882
....
....
<< snipped few lines >>
....
....
AB_PARALLELMESSAGE DBA T 2005-04-11-12.17.27.384100
AB_POSTDATELOGDET DBA T 2004-12-02-20.12.51.917250
$
$ db2look -d oracnv1 -e -z dba -t AB_DOMAIN # -o BA_ADDRESS.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: ORACLEI1
-- Specified SCHEMA is: DBA
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue Mar 21 09:15:50 EDT 2017
-- Database Name: ORACNV1
-- Database Manager Version: DB2/AIX64 Version 10.5.7
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO ORACNV1;
------------------------------------------------
-- DDL Statements for Table "DBA "."AB_DOMAIN"
------------------------------------------------
CREATE TABLE "DBA "."AB_DOMAIN" (
"DOMAIN_COID" CHAR(26) NOT NULL ,
"SEQUENCE_NO" DECIMAL(18,0) NOT NULL ,
"PRIMARY_PLATFORM" CHAR(2) NOT NULL ,
"DATA_" VARCHAR(1024) FOR BIT DATA NOT NULL WITH DEFAULT )
IN "USERSPACE1"
ORGANIZE BY ROW;
-- DDL Statements for Indexes on Table "DBA "."AB_DOMAIN"
SET NLS_STRING_UNITS = 'SYSTEM';
CREATE UNIQUE INDEX "DBA "."DOMAINIX" ON "DBA "."AB_DOMAIN"
("DOMAIN_COID" ASC)
COMPRESS NO
INCLUDE NULL KEYS DISALLOW REVERSE SCANS;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
$
Here-d stands for database name
-e stands for extract DDL statements
-z stands for schema name
-t stands for table name; up to 30 table names can be provided
-o stands for output file name which I have commented in this example. If -o is added to the db2look command, the output will be redirected to output file name mentioned and will not be displayed on screen.
If -t is not used, DDL for all the objects under schema DBA will be extracted.
For more on this tool, check on this link.
9. Finding object sizes
The sizes for Oracle objects can be found using the dba_segments views. The DB2 counter part would be SYSIBMADM.ADMINTABINFO administrative view. One can also get the information by joining syscat.tables and syscat.tablespaces views. Examples given for both..
$ db2 "SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
> SUBSTR(TABNAME,1,30) TABNAME,
> (SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE)) AS TOTAL_SIZE_IN_KB
> --,(SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE))/1024 AS TOTAL_SIZE_IN_MB
> --,(SUM(DATA_OBJECT_P_SIZE)+SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE)) / (1024*1024) AS TOTAL_SIZE_IN_GB
> FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA ='DBA' group by tabname,tabschema"
TABSCHEMA TABNAME TOTAL_SIZE_IN_KB
------------------ ------------------------------ --------------------
DBA AB_COARSEOBJECT 1704
DBA AB_CODEMAP 1540
....
....
<< snipped a few lines >>
....
....
DBA AB_CONFIG 24
DBA AB_DOMAIN 20
DBA AB_ENVIRONMENT 44
$
$ db2 "select sum(a.fpages*PAGESIZE/1024) as size_of_schemainKB
> from syscat.tables a, syscat.tablespaces b
> where a.TBSPACEID=b.TBSPACEID and a.tabschema='DBA' group by a.tabschema"
SIZE_OF_SCHEMAINKB
--------------------
147680
1 record(s) selected.
$
10. Details on DB2 backup image
When there is a DB2 image is provided, we can use db2ckbkp tool to verify the backup image and get the details of the image.
$ db2ckbkp -h CBQC.0.db2v10q1.DBPART000.20170227162646.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- CBQC
Server Database Alias -- CBQC
Client Database Alias -- CBQC
Timestamp -- 20170227162646
Database Partition Number -- 0
Instance -- db2v10q1
<< snipped a few lines >>
Platform -- 0x14 (AIX-64)
Encrypt Info Flags -- 0x0
The proper image file name would be:
CBQC.0.db2v10q1.DBPART000.20170227162646.001
[1] Buffers processed: ######################################################################################################
##############################################################################################################################
##############################################################################################################################
##############################################################################################################################
#######################################################################################################
Image Verification Complete - successful.
$
You get all the information such as database name, instance that the DB belongs to, etc., Once the image is verified, we can restore the image if required.11. Restoring DB2 backup image
The restore of the image can be done as below.
$ db2 restore database CBQC from /dbbackup/oracnv1 taken at 20170227162646 into oracnv1 SQL2528W Warning! Restoring to an existing database that is the same as the backup image database, but the alias name "ORACNV1" of the existing database does not match the alias "CBQC" of backup image, and the database name "ORACNV1" of the existing database does not match the database name "CBQC" of the backup image. The target database will be overwritten by the backup version. Do you want to continue ? (y/n) y DB20000I The RESTORE DATABASE command completed successfully. $The database name CBQC is obtained from the db2ckbkp command.
The path /dbbackup/oracnv1 is where I have stored my backup image.
I have multiple backups store taken at different time and hence the usage of "taken at" clause.
oracnv1 is an already existing database. If the database doesn't exist, create one and restore.
I'll try to update the post as much as possible..
Happy DB2ing...!!!
