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

Monday, October 14, 2013

connect_proc and locales: Connecting the dots for combined fun

Last year in a blog article I had written about some fun I had with different locales and the dayname function in DB2. Last week I showed you links to customize the application environment. Why not combine the two for some added fun? Let me show you how to connect the dots. I am going to show you how to adapt the locale setting in DB2 based on user preferences.

First, we need a simple table to store our user preferences:
create table myschema.users (id int, uname varchar(30), locale char(5));

For our testing purposes, two user entries will do:
insert into myschema.users values(1,'HLOESER','de_DE');
insert into myschema.users values(2,'DB2FENC1','fr_FR');

What we will do is to set up our own procedure as connect_proc in DB2. In that procedure we access our small user table, read out the preference for the locale setting based on the value of the SESSION_USER. Then we set CURRENT LOCALE LC_TIME special register which controls the language and behavior for day name, timestamp format, rounding, truncation and some more.

create or replace procedure hl.my_connect()
reads sql data
language sql
BEGIN
   declare loc char(5);
   select locale into loc from myschema.users
        where uname=SESSION_USER;
   set current locale lc_time loc;
END@


After the procedure is created, some testing should be done:
db2 => call my_connect()

  Return Status = 0

What also needs to be done is to allow everyone to execute our procedure:
db2 => grant execute on procedure hl.my_connect to public
DB20000I  The SQL command completed successfully.
  
 The final step is to update the database configuration and tell DB2 to use our procedure on every successful connection.
db2 => update db cfg using connect_proc hloeser.my_connect
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


To see the effect of our procedure in place we need to connect as user "hloeser" and as "db2fenc1".

Testing as "hloeser":
select dayname(current date) from dual

1                                                                                                  
----------------------------------------------------------------------------------------------------
Monday



When we connect as "db2fenc1", the result is different:

values(dayname(current date))

1                                                                                                  
----------------------------------------------------------------------------------------------------
lundi 


It seems, replacing the connect_proc with our procedure worked.  I close with one important advise: Leave one database connection open during testing. If your connect procedure is not working well, you need that connection to reset the database configuration (update db cfg using connect_proc '')...

Update: There was a question whether the connection is needed or why I recommend it: The answer is that connect_proc, once set, can only be updated when connected to the database. Unsetting connect_proc can be done when not connected, but the database needs to be inactive. Thus, having the connection saves time when developing.

Friday, October 11, 2013

IBM Commercial: Back in Mobile Computing

I came across this old commercial about IBM's first mobile computer. The compter is lightweight, works everywhere, and yes, it is mobile. But all this based on 1970er standards. Compare this to today, check your emails on your smartphone and enjoy the weekend...



DB2 V10.5 Fixpack 2 available

The next fixpack for DB2 V10.5 has been released. It can be found on the DB2 fixpack site. The feature changes are documented in the fixpack summary in the Information Center.

Enjoy the weekend!

Tuesday, October 8, 2013

I'm not you - Ways to customize a DB2 application environment

I am not you, she is not him. Users differ, applications differ. There are two interesting concepts in DB2 that help to adapt an application environment and application and that are not widely known. One is the connect_proc database configuration parameter, the other is conditional compilation of SQL. Let me give you some ideas of what it is and links to dig deeper into it.

Introduced in DB2 9.7 FP3, the connect procedure let's use configure a stored procedure that is invoked whenever someone (an application) connects to DB2. Thus, the session context can be tailored to the user or application by setting the locale, the path to resolve functions, optimization levels and more. Even though the procedure is not allowed to modify the database it could invoke another routine with an autonomous transaction. That way some information could be logged ("track", "audit") or another event be triggered. The DB2 Information Center has some examples on how to use connect_proc for session customization. Serge Rielau showed how to implement some logging.

Conditional compilation allows to tailor the compiled SQL to the actual environment. You could just use procedure or function code for little or for big endian - making it execute faster, implement your own routine in different ways depending on the DB2 version - use a more efficient implementation with newer DB2 versions, or plug in a lighter version of code - maybe without debug code. The key is to use SQL_CCFLAGS, flags for conditional SQL compilation. The variable can be set at the database or session level and then referenced within the actual SQL code. Special "_IF"/"_ELSEIF"/"_THEN"/"_END" directives do the trick of selecting the code you want. This is similar to shell scripting and programming languages.
In the DB2 system catalog you can find out which flags were set when a routine or trigger was compiled ("whom to blame..."). Of course the currently set value can be retrieved using the CURRENT SQL_CCFLAGS special registry.

Of course it is possible to combine the two. Develop your own library of connect-related actions, audit, and setup routines, then set SQL_CCFLAGS to implement just the mix you need for a specific application environment.

Monday, October 7, 2013

Best Practices: Troubleshooting DB2 servers

How about PD, FODC, TRC, core, EcuRep, DIAG, caem, lock, mem, log, OPM, CPU, EDU, app, support, and many more terms in a single paper? The newly published Best Practices: Troubleshooting DB2 servers looks into what should be done to handle issues with DB2. It discusses what data to collect, how DB2 is actually configured to dump the right data, how to exchange that information with IBM, and how to look into typical scenarios on your own.

Because of what is covered, the depth and the embedded links to even dig deeper into troubleshooting topics, it is a good read for beginners and experienced DB2 users.

BTW: You can find all DB2 Best Practices here.