This is the last quiz for 2013 and before I take a break ("vacation"). When looking at the English language version of the DB2 10.5 Information Center, what do these four (4) pages have in common?
Where condition in SQL warehousing transform operator:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.dwe.sqw.doc/designing/data_flow/ropwhere.html
SEND procedure - Send E-Mail to an SMTP server:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055177.html
UTL_SMTP module:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055180.html
The SAMPLE database:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.samptop.doc/doc/r0001094.html
Any guesses? It is about the samples and the current season...
Enjoy the holidays and all the best for 2014!
Henrik's thoughts on life in IT, data and information management, cloud computing, cognitive computing, covering IBM Db2, IBM Cloud, Watson, Amazon Web Services, Microsoft Azure and more.
Thursday, December 12, 2013
Wednesday, December 4, 2013
DB2: Nothing to hide...
Today, while teaching a DB2 class, I was asked whether it is possible to hide all columns of a table. The rationale behind the question was that then all application developers would be forced to specify the columns during INSERT. Well, my first reaction was that at least one column needs to be visible. How does it look like?
Testing is faster than looking into the manual for IMPLICITLY HIDDEN columns, so here is a quick test:
db2 => create table hideme(id int implicitly hidden, s varchar(60) implicitly hidden)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20422N The statement failed because the table "HLOESER.HIDEME" would only
contain hidden columns. SQLSTATE=428GU
db2 => create table hideme(id int, s varchar(60) implicitly hidden)
DB20000I The SQL command completed successfully.
db2 => insert into hideme values(1)
DB20000I The SQL command completed successfully.
db2 => insert into hideme(id,s) values(2,'Hey')
DB20000I The SQL command completed successfully.
db2 => select * from hideme
ID
-----------
1
2
2 record(s) selected.
db2 => select id,s from hideme
ID S
----------- ------------------------------------------------------------
1 -
2 Hey
2 record(s) selected.
At least one column must be visible as assumed.
Testing is faster than looking into the manual for IMPLICITLY HIDDEN columns, so here is a quick test:
db2 => create table hideme(id int implicitly hidden, s varchar(60) implicitly hidden)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20422N The statement failed because the table "HLOESER.HIDEME" would only
contain hidden columns. SQLSTATE=428GU
db2 => create table hideme(id int, s varchar(60) implicitly hidden)
DB20000I The SQL command completed successfully.
db2 => insert into hideme values(1)
DB20000I The SQL command completed successfully.
db2 => insert into hideme(id,s) values(2,'Hey')
DB20000I The SQL command completed successfully.
db2 => select * from hideme
ID
-----------
1
2
2 record(s) selected.
db2 => select id,s from hideme
ID S
----------- ------------------------------------------------------------
1 -
2 Hey
2 record(s) selected.
At least one column must be visible as assumed.
Tuesday, November 26, 2013
MySQL-style LIMIT and OFFSET in DB2
An "ancient" but not yet well-known feature in DB2 is the support of MySQL/PostgreSQL-style LIMIT and OFFSET in SELECT statements and searched UPDATE/DELETE. Unfortunately, it is not really documented and I am working on getting some more documentation added (think about the "mostly harmless" as in the Hitchhiker's Guide to the Galaxy).
To demonstrate what is possible, I created a small table full of good stuff:
db2 => create table sweets(id int, desc varchar(60), quant int)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(1,'dark chocolate',4)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(2,'marzipan bar',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(3,'almond cookies',10)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(4,'granola bar',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(5,'nut chocolate',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(6,'chocolate-covered peanuts',1)
DB20000I The SQL command completed successfully.
db2 => select * from sweets limit 5
SQL0104N An unexpected token "limit" was found following "". Expected tokens
may include: "FETCH FIRST ROWS ONLY". SQLSTATE=42601
By default, DB2 does not know about LIMIT and OFFSET either. But setting the DB2_COMPATIBILITY_VECTOR to MYS (as in MySQL) or "04000" enables the syntactic alternative to the FETCH FIRST n ROWS ONLY. After restarting DB2 the SELECT works:
db2 => select * from sweets limit 5
ID DESC QUANT
----------- --------------------------------------------------- -----------
1 dark chocolate 4
2 marzipan bar 1
3 almond cookies 10
4 granola bar 1
5 nut chocolate 1
5 record(s) selected.
The following two examples show the use of the OFFSET, i.e., where to start in the result set. I combined it with ordering on the quantity.
db2 => select quant,desc from sweets order by num limit 3 offset 2
QUANT DESC
----------- ------------------------------------------------------------
1 nut chocolate
1 chocolate-covered peanuts
4 dark chocolate
3 record(s) selected.
db2 => select quant,desc from sweets order by num limit 2,3
QUANT DESC
----------- ------------------------------------------------------------
1 nut chocolate
1 chocolate-covered peanuts
4 dark chocolate
3 record(s) selected.
Note that in the second example a special abbreviated syntax is used. The first number is the offset where to start, followed by the number of rows to return.
As said, there is not much in the DB2 Information Center (yet). Serge has some more background in his blog about different ways of paging through result sets.
To demonstrate what is possible, I created a small table full of good stuff:
db2 => create table sweets(id int, desc varchar(60), quant int)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(1,'dark chocolate',4)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(2,'marzipan bar',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(3,'almond cookies',10)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(4,'granola bar',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(5,'nut chocolate',1)
DB20000I The SQL command completed successfully.
db2 => insert into sweets values(6,'chocolate-covered peanuts',1)
DB20000I The SQL command completed successfully.
db2 => select * from sweets limit 5
SQL0104N An unexpected token "limit" was found following "". Expected tokens
may include: "FETCH FIRST
By default, DB2 does not know about LIMIT and OFFSET either. But setting the DB2_COMPATIBILITY_VECTOR to MYS (as in MySQL) or "04000" enables the syntactic alternative to the FETCH FIRST n ROWS ONLY. After restarting DB2 the SELECT works:
db2 => select * from sweets limit 5
ID DESC QUANT
----------- --------------------------------------------------- -----------
1 dark chocolate 4
2 marzipan bar 1
3 almond cookies 10
4 granola bar 1
5 nut chocolate 1
5 record(s) selected.
The following two examples show the use of the OFFSET, i.e., where to start in the result set. I combined it with ordering on the quantity.
db2 => select quant,desc from sweets order by num limit 3 offset 2
QUANT DESC
----------- ------------------------------------------------------------
1 nut chocolate
1 chocolate-covered peanuts
4 dark chocolate
3 record(s) selected.
db2 => select quant,desc from sweets order by num limit 2,3
QUANT DESC
----------- ------------------------------------------------------------
1 nut chocolate
1 chocolate-covered peanuts
4 dark chocolate
3 record(s) selected.
Note that in the second example a special abbreviated syntax is used. The first number is the offset where to start, followed by the number of rows to return.
As said, there is not much in the DB2 Information Center (yet). Serge has some more background in his blog about different ways of paging through result sets.
Labels:
competition,
DB2,
Information Center,
IT,
migration,
mysql,
sql,
version 10,
version 10.5
Monday, November 11, 2013
DB2 10.5: Overview of product editions, licensing and features
On the IBM developerWorks website is a new article discussing the different editions of DB2 10.5. It is titled "DB2 editions: Which distributed edition of DB2 10.5 is right for you?". It gives an overview of what editions are available, some of the top notch features, and what licensing options are available. Another new article, "Compare the distributed DB2 10.5 database servers" has a similar intent, but focusses on features by edition and is organized in a table format. The third installment, "Licensing distributed DB2 10.5 servers in a HA environment", does exactly that. :)
Thursday, November 7, 2013
TRANSFER OWNERSHIP: Pass objects on to others...
There are cases when ownership of database objects needs to be reorganized: People leaving the company, new rules from supervisory authorities (financial industry, ...), changes to applications, and much more. How do you change ownership in DB2? The answer is a statement introduced in DB2 9.1. The statement of question is TRANSFER OWNERSHIP.
The owner of an object or the security administrator (SECADM) can execute the statement and pass on the object to another user. SECADMs cannot transfer the object to themselves. There are over 20 different object types that can be handled this way. Even if you don't plan to change object ownerships it is a good idea to read the documentation for the TRANSFER OWNERSHIP statement as it nicely show what kind of objects are managed by DB2 and because for many of those their catalog tables are mentioned.
Why do I talk about such an "old" statement when I could talk about BLU Acceleration or the currently ongoing IOD Conference with all the announcements? Companies are planning DB2 version upgrades to exploit new features and to comply with new rules by certain deadlines.
The owner of an object or the security administrator (SECADM) can execute the statement and pass on the object to another user. SECADMs cannot transfer the object to themselves. There are over 20 different object types that can be handled this way. Even if you don't plan to change object ownerships it is a good idea to read the documentation for the TRANSFER OWNERSHIP statement as it nicely show what kind of objects are managed by DB2 and because for many of those their catalog tables are mentioned.
Why do I talk about such an "old" statement when I could talk about BLU Acceleration or the currently ongoing IOD Conference with all the announcements? Companies are planning DB2 version upgrades to exploit new features and to comply with new rules by certain deadlines.
Tuesday, November 5, 2013
BLU(e)! Cloud! In-Memory! FREE!!!
IBM has announced a so-called "IBM BLU Acceleration for Cloud". Right now it is a technology preview and FREE to use. Thanks to the combination of the BLU Acceleration technology and Cognos it is powerful. Simplicity is guaranteed through the presence of many tools and the "load and go" approach.
Right now you can sign up for free to test the cloud-based offering with your data.
After signing up you will receive an email with instructions and a link. Then you need to bring your own lawyer to analyze several agreements. ;) I accepted them and was ready to go. What is nice are several "tours" that are offered. The tours give an overview of the different buttons and screen areas within the dashboard, the system panel, etc.
After launching the web console different task and tools are provided for "data scientists" and "business analysts" as well as for warehouse developers and database administrators. You can hook up your Excel or use SQL to work with your own data or use a sample database.
More on this in a later blog, now I need to work with my data on the BLU cloud...
Right now you can sign up for free to test the cloud-based offering with your data.
After signing up you will receive an email with instructions and a link. Then you need to bring your own lawyer to analyze several agreements. ;) I accepted them and was ready to go. What is nice are several "tours" that are offered. The tours give an overview of the different buttons and screen areas within the dashboard, the system panel, etc.
After launching the web console different task and tools are provided for "data scientists" and "business analysts" as well as for warehouse developers and database administrators. You can hook up your Excel or use SQL to work with your own data or use a sample database.
More on this in a later blog, now I need to work with my data on the BLU cloud...
Monday, October 21, 2013
Heavy hitter: SYSIBM.SYSTABLES
I tried out the GET_DEPENDENCY procedure on one of the central catalog tables. SYSIBM.SYSTABLES is the catalog table behind SYSCAT.TABLES (and some more). As you can see from the output below, a total of 23 views are dependent on it.
db2 => call dbms_utility.get_dependency('TABLE','SYSIBM','SYSTABLES')
Return Status = 0
DEPENDENCIES ON SYSIBM.SYSTABLES
------------------------------------------------------------------
*TABLE SYSIBM.SYSTABLES()
* VIEW SYSCAT .AUDITUSE()
* VIEW SYSCAT .INDEXES()
* VIEW SYSCAT .NICKNAMES()
* VIEW SYSCAT .PERIODS()
* VIEW SYSCAT .TABLES()
* VIEW SYSIBM .CHECK_CONSTRAINTS()
* VIEW SYSIBM .COLUMNS()
* VIEW SYSIBM .SQLCOLPRIVILEGES()
* VIEW SYSIBM .SQLCOLUMNS()
* VIEW SYSIBM .SQLFOREIGNKEYS()
* VIEW SYSIBM .SQLPRIMARYKEYS()
* VIEW SYSIBM .SQLSPECIALCOLUMNS()
* VIEW SYSIBM .SQLSTATISTICS()
* VIEW SYSIBM .SQLTABLEPRIVILEGES()
* VIEW SYSIBM .SQLTABLES()
* VIEW SYSIBM .TABLES()
* VIEW SYSIBM .TABLE_CONSTRAINTS()
* VIEW SYSIBM .VIEWS()
* VIEW SYSIBMADM.OBJECTOWNERS()
* VIEW SYSIBMADM.PRIVILEGES()
* VIEW SYSSTAT .COLDIST()
* VIEW SYSSTAT .COLUMNS()
* VIEW SYSSTAT .TABLES()
* PACKAGE SYSIBMADM.DBMS_ALERT()
* PACKAGE SYSIBMADM.DBMS_JOB()
* PACKAGE SYSIBMADM.DBMS_UTILITY()
* PACKAGE SYSIBMADM.UTL_DIR()
Do you know of another system table with more dependencies?
db2 => call dbms_utility.get_dependency('TABLE','SYSIBM','SYSTABLES')
Return Status = 0
DEPENDENCIES ON SYSIBM.SYSTABLES
------------------------------------------------------------------
*TABLE SYSIBM.SYSTABLES()
* VIEW SYSCAT .AUDITUSE()
* VIEW SYSCAT .INDEXES()
* VIEW SYSCAT .NICKNAMES()
* VIEW SYSCAT .PERIODS()
* VIEW SYSCAT .TABLES()
* VIEW SYSIBM .CHECK_CONSTRAINTS()
* VIEW SYSIBM .COLUMNS()
* VIEW SYSIBM .SQLCOLPRIVILEGES()
* VIEW SYSIBM .SQLCOLUMNS()
* VIEW SYSIBM .SQLFOREIGNKEYS()
* VIEW SYSIBM .SQLPRIMARYKEYS()
* VIEW SYSIBM .SQLSPECIALCOLUMNS()
* VIEW SYSIBM .SQLSTATISTICS()
* VIEW SYSIBM .SQLTABLEPRIVILEGES()
* VIEW SYSIBM .SQLTABLES()
* VIEW SYSIBM .TABLES()
* VIEW SYSIBM .TABLE_CONSTRAINTS()
* VIEW SYSIBM .VIEWS()
* VIEW SYSIBMADM.OBJECTOWNERS()
* VIEW SYSIBMADM.PRIVILEGES()
* VIEW SYSSTAT .COLDIST()
* VIEW SYSSTAT .COLUMNS()
* VIEW SYSSTAT .TABLES()
* PACKAGE SYSIBMADM.DBMS_ALERT()
* PACKAGE SYSIBMADM.DBMS_JOB()
* PACKAGE SYSIBMADM.DBMS_UTILITY()
* PACKAGE SYSIBMADM.UTL_DIR()
Do you know of another system table with more dependencies?
Friday, October 18, 2013
Friday DB2 Quiz: Not much to report...
It is Friday. How about a quick quiz? What did I do to produce the following output in DB2? Don't comment on the (in)activities you see... :)
Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: HL
Generated: 10/18/2013 09:43:38
Interval monitored: 10
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 0
ACT_COMPLETED_TOTAL 24 246
APP_RQSTS_COMPLETED_TOTAL 0 3
TOTAL_CPU_TIME = 25439
TOTAL_CPU_TIME per request = 8479
Row processing
ROWS_READ/ROWS_RETURNED = 0 (40/246)
ROWS_MODIFIED = 0
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 2 260/10659
For activities 2 221/10512
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 4665
CLIENT_IDLE_WAIT_TIME per second = 466
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 260
I/O wait time
POOL_READ_TIME 0 0
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 80 210
DIRECT_WRITE_TIME 0 0
LOG_DISK_WAIT_TIME 0 0
LOCK_WAIT_TIME 0 0
AGENT_WAIT_TIME 0 0
Network and FCM
TCPIP_SEND_WAIT_TIME 0 0
TCPIP_RECV_WAIT_TIME 0 0
IPC_SEND_WAIT_TIME 0 0
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 0
FCM_RECV_WAIT_TIME 0 0
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 0 0
RECLAIM_WAIT_TIME 0 0
SMP_RECLAIM_WAIT_TIME 0 0
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 10399
Section execution
TOTAL_SECTION_PROC_TIME 2 220
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 1 109
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 0
TOTAL_ROLLBACK_PROC_TIME 0 0
Utilities
TOTAL_RUNSTATS_PROC_TIME 0 0
TOTAL_REORGS_PROC_TIME 0 0
TOTAL_LOAD_PROC_TIME 0 0
Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios
Type Ratio Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data 100 29/0
Index 100 28/0
XDA 0 0/0
COL 0 0/0
Temp data 0 0/0
Temp index 0 0/0
Temp XDA 0 0/0
Temp COL 0 0/0
GBP Data 0 (0 - 0)/0
GBP Index 0 (0 - 0)/0
GBP XDA 0 (0 - 0)/0
GBP COL 0 (0 - 0)/0
LBP Data 100 (29 - 0)/(29 + 0)
LBP Index 100 (28 - 0)/(28 + 0)
LBP XDA 0 (0 - 0)/(0 + 0)
LBP COL 0 (0 - 0)/(0 + 0)
I/O
--------------------------------------------------------------------------------
Buffer pool writes
POOL_DATA_WRITES = 0
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 802
DIRECT_READ_REQS = 32
DIRECT_WRITES = 0
DIRECT_WRITE_REQS = 0
Log I/O
LOG_DISK_WAITS_TOTAL = 0
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 0 0
LOCK_WAITS 0 0
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
Routines
--------------------------------------------------------------------------------
Per activity Total
------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS 0 237
TOTAL_ROUTINE_TIME 41 10294
TOTAL_ROUTINE_TIME per invocation = 43
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 4
SORT_OVERFLOWS = 0
POST_THRESHOLD_SORTS = 0
POST_SHRTHRESHOLD_SORTS = 0
Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send = 0 (0/0)
TCPIP_RECV_VOLUME per receive = 0 (0/0)
Communications with local clients
IPC_SEND_VOLUME per send = 94 (189/2)
IPC_RECV_VOLUME per receive = 160 (482/3)
Fast communications manager
FCM_SEND_VOLUME per send = 0 (0/0)
FCM_RECV_VOLUME per receive = 0 (0/0)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 2
PKG_CACHE_INSERTS = 16
PKG_CACHE_LOOKUPS = 17
Catalog cache
CAT_CACHE_INSERTS = 3
CAT_CACHE_LOOKUPS = 9
Transaction processing
TOTAL_APP_COMMITS = 0
INT_COMMITS = 0
TOTAL_APP_ROLLBACKS = 0
INT_ROLLBACKS = 0
Log buffer
NUM_LOG_BUFFER_FULL = 0
Activities aborted/rejected
ACT_ABORTED_TOTAL = 0
ACT_REJECTED_TOTAL = 0
Workload management controls
WLM_QUEUE_ASSIGNMENTS_TOTAL = 0
WLM_QUEUE_TIME_TOTAL = 0
DB2 utility operations
--------------------------------------------------------------------------------
TOTAL_RUNSTATS = 0
TOTAL_REORGS = 0
TOTAL_LOADS = 0
================================================================================
Part 2 - Application performance drill down
Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
per request WAIT_TIME % COMMITS ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
8479 2 0 40
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
804 8479 2 0 40
Application performance by service class
--------------------------------------------------------------------------------
SERVICE_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
CLASS_ID per request WAIT_TIME % COMMITS ROWS_MODIFIED
-------- ------------------- ----------- ------------- -------------
4 0 0 0 0
11 0 0 0 0
12 0 0 0 0
13 8479 2 0 40
Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
NAME per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0 0 0 0
SYSDEFAULTUSE 8479 2 0 40
================================================================================
Part 3 - Member level information
- I/O wait time is
(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).
TOTAL_CPU_TIME TOTAL_ RQSTS_COMPLETED_ I/O
MEMBER per request WAIT_TIME % TOTAL wait time
------ ---------------------- ----------- ---------------- -----------------
0 8479 2 3 261
241 record(s) selected.
Return Status = 0
Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: HL
Generated: 10/18/2013 09:43:38
Interval monitored: 10
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 0
ACT_COMPLETED_TOTAL 24 246
APP_RQSTS_COMPLETED_TOTAL 0 3
TOTAL_CPU_TIME = 25439
TOTAL_CPU_TIME per request = 8479
Row processing
ROWS_READ/ROWS_RETURNED = 0 (40/246)
ROWS_MODIFIED = 0
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 2 260/10659
For activities 2 221/10512
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 4665
CLIENT_IDLE_WAIT_TIME per second = 466
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 260
I/O wait time
POOL_READ_TIME 0 0
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 80 210
DIRECT_WRITE_TIME 0 0
LOG_DISK_WAIT_TIME 0 0
LOCK_WAIT_TIME 0 0
AGENT_WAIT_TIME 0 0
Network and FCM
TCPIP_SEND_WAIT_TIME 0 0
TCPIP_RECV_WAIT_TIME 0 0
IPC_SEND_WAIT_TIME 0 0
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 0
FCM_RECV_WAIT_TIME 0 0
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 0 0
RECLAIM_WAIT_TIME 0 0
SMP_RECLAIM_WAIT_TIME 0 0
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 10399
Section execution
TOTAL_SECTION_PROC_TIME 2 220
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 1 109
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 0
TOTAL_ROLLBACK_PROC_TIME 0 0
Utilities
TOTAL_RUNSTATS_PROC_TIME 0 0
TOTAL_REORGS_PROC_TIME 0 0
TOTAL_LOAD_PROC_TIME 0 0
Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios
Type Ratio Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data 100 29/0
Index 100 28/0
XDA 0 0/0
COL 0 0/0
Temp data 0 0/0
Temp index 0 0/0
Temp XDA 0 0/0
Temp COL 0 0/0
GBP Data 0 (0 - 0)/0
GBP Index 0 (0 - 0)/0
GBP XDA 0 (0 - 0)/0
GBP COL 0 (0 - 0)/0
LBP Data 100 (29 - 0)/(29 + 0)
LBP Index 100 (28 - 0)/(28 + 0)
LBP XDA 0 (0 - 0)/(0 + 0)
LBP COL 0 (0 - 0)/(0 + 0)
I/O
--------------------------------------------------------------------------------
Buffer pool writes
POOL_DATA_WRITES = 0
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 802
DIRECT_READ_REQS = 32
DIRECT_WRITES = 0
DIRECT_WRITE_REQS = 0
Log I/O
LOG_DISK_WAITS_TOTAL = 0
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 0 0
LOCK_WAITS 0 0
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
Routines
--------------------------------------------------------------------------------
Per activity Total
------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS 0 237
TOTAL_ROUTINE_TIME 41 10294
TOTAL_ROUTINE_TIME per invocation = 43
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 4
SORT_OVERFLOWS = 0
POST_THRESHOLD_SORTS = 0
POST_SHRTHRESHOLD_SORTS = 0
Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send = 0 (0/0)
TCPIP_RECV_VOLUME per receive = 0 (0/0)
Communications with local clients
IPC_SEND_VOLUME per send = 94 (189/2)
IPC_RECV_VOLUME per receive = 160 (482/3)
Fast communications manager
FCM_SEND_VOLUME per send = 0 (0/0)
FCM_RECV_VOLUME per receive = 0 (0/0)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 2
PKG_CACHE_INSERTS = 16
PKG_CACHE_LOOKUPS = 17
Catalog cache
CAT_CACHE_INSERTS = 3
CAT_CACHE_LOOKUPS = 9
Transaction processing
TOTAL_APP_COMMITS = 0
INT_COMMITS = 0
TOTAL_APP_ROLLBACKS = 0
INT_ROLLBACKS = 0
Log buffer
NUM_LOG_BUFFER_FULL = 0
Activities aborted/rejected
ACT_ABORTED_TOTAL = 0
ACT_REJECTED_TOTAL = 0
Workload management controls
WLM_QUEUE_ASSIGNMENTS_TOTAL = 0
WLM_QUEUE_TIME_TOTAL = 0
DB2 utility operations
--------------------------------------------------------------------------------
TOTAL_RUNSTATS = 0
TOTAL_REORGS = 0
TOTAL_LOADS = 0
================================================================================
Part 2 - Application performance drill down
Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
per request WAIT_TIME % COMMITS ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
8479 2 0 40
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
804 8479 2 0 40
Application performance by service class
--------------------------------------------------------------------------------
SERVICE_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
CLASS_ID per request WAIT_TIME % COMMITS ROWS_MODIFIED
-------- ------------------- ----------- ------------- -------------
4 0 0 0 0
11 0 0 0 0
12 0 0 0 0
13 8479 2 0 40
Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
NAME per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0 0 0 0
SYSDEFAULTUSE 8479 2 0 40
================================================================================
Part 3 - Member level information
- I/O wait time is
(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).
TOTAL_CPU_TIME TOTAL_ RQSTS_COMPLETED_ I/O
MEMBER per request WAIT_TIME % TOTAL wait time
------ ---------------------- ----------- ---------------- -----------------
0 8479 2 3 261
241 record(s) selected.
Return Status = 0
Subscribe to:
Posts (Atom)