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?
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.
Monday, October 21, 2013
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
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.
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.
Labels:
administration,
applications,
DB2,
fun,
Information Center,
IT,
Oracle,
PL/SQL,
sql,
version 10.5
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!
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.
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.
Labels:
administration,
applications,
DB2,
fun,
Information Center,
IT,
Oracle,
PL/SQL,
sql
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.
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.
Subscribe to:
Posts (Atom)