Thursday, December 12, 2013

The DB2 10.5 Year End Quiz

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!


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.

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.

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.

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

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?

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