What is Db2Batch in Db2

-

The Db2batch is known as benchmark tool.

It will show the Estimated time of the queries which you have written in the file.

Ex: I have a file called table.sql which is having the below statements.

Table.sql

========================================================================
create table abc.data(id int,name varchar(100));

insert into abc.data values(1,'sql'),(2,'oracle'),(3,'sqlserver'),(4,'mysql');

update abc.data set name = 'mongodb' where id = 1;

insert into abc.data values(10,'aaaaaaaaaaaaaavvcnekneknekdwlcnrflrbfl3rffo3jrr,dnfvdmcdjcbdkpoepmcd.ckdnmcwkeniwnekedoeml');

select * from abc.data;

 

========================================================================

 

the command to execute the db2batch is 

 

db2batch -d sample -f table.sql

 

here sample is my database_name & f is the filename 

 

The below results will show the query & the execution time.

 

* SQL Statement Number 1:

 

create table abc.data(id int,name varchar(100));

 

* Elapsed Time is:       0.512157 seconds

 

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

 

* SQL Statement Number 2:

 

insert into abc.data values(1,'sql'),(2,'oracle'),(3,'sqlserver'),(4,'mysql');

 

* Elapsed Time is:       0.001170 seconds

 

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

 

* SQL Statement Number 3:

 

update abc.data set name = 'mongodb' where id = 1;

 

* Elapsed Time is:       0.001236 seconds

 

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

 

* SQL Statement Number 4:

 

insert into abc.data values(10,'aaaaaaaaaaaaaavvcnekneknekdwlcnrflrbfl3rffo3jrr,dnfvdmcdjcbdkpoepmcd.ckdnmcwkeniwnekedoeml');

 

* Elapsed Time is:       0.001182 seconds

 

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

 

* SQL Statement Number 5:

 

select * from abc.data;

 

ID          NAME

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

          1 mongodb

          2 oracle

          3 sqlserver

          4 mysql

         10 aaaaaaaaaaaaaavvcnekneknekdwlcnrflrbfl3rffo3jrr,dnfvdmcdjcbdkpoepmcd.ckdnmcwkeniwnekedoeml

 

* 5 row(s) fetched, 5 row(s) output.

 

* Elapsed Time is:       0.000373 seconds

 

* Summary Table:

 

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output

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

Statement           1           1       0.512157       0.512157       0.512157        0.512157       0.512157              0             0

Statement           2           1       0.001170       0.001170       0.001170        0.001170       0.001170              0             0

Statement           3           1       0.001236       0.001236       0.001236        0.001236       0.001236              0             0

Statement           4           1       0.001182       0.001182       0.001182        0.001182       0.001182              0             0

Statement           5           1       0.000373       0.000373       0.000373        0.000373       0.000373              5             5

 

* Total Entries:              5

* Total Time:                 0.516118 seconds

* Minimum Time:               0.000373 seconds

* Maximum Time:               0.512157 seconds

* Arithmetic Mean Time:       0.103224 seconds

* Geometric Mean Time:        0.003183 second

 s

Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

Phases of a load operation

-
A complete load operation consists of five distinct phases; they are as follows: Analyze phase Load phase Build phase Delete phase Index copy phase Analyze phase:  This phase of the Load process is only utilized when a column-organized table is being loaded and the column compression dictionaries need to be built. This happens during a LOAD REPLACE operation, a LOAD REPLACE RESETDICTIONARY operation, a LOAD REPLACE RESETDICTIONARYONLY operation, or a LOAD INSERT operation (if the column-organized table is empty). For column-organized tables, this phase is followed by the load, build, and delete phases. The following diagram showcases the steps involved in the analyze phase. The analyze phase is invoked if you are loading data for the first time into a column-organized BLU acceleration table using LOAD INSERT, or running a LOAD REPLACE with RESETDICTIONARY or RESETDICTIONARYONLY clause. Load phase:  During this phase, data is loaded into the table, and index keys and table stat...
Read more