Looking for something? Try here..

Saturday, July 22, 2017

Parallelism on single object

Today as my long time dream come true, I presented my maiden session in an user group meet up, AIOUG Bengaluru Chapter meet up which was a small session but received accolades from the attendees making me feel encouraged to do more such presentations in the future.

I'm attaching the presentation slide as a pdf file which can be downloaded from here.

The content actually speaks about how to efficiently utilize parallel processes when working with single object such as a single data file or a single table. The slides are self explanatory and you might always utilize the comments section to get your doubts clarified.

Happy Meet up!!! :)

Tuesday, March 21, 2017

Basic DB2 commands - Part 2

The first part of this post can be found in this link

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...!!!