Agile Transformation Leadership, Coaching, and Mentoring
db2top Federation Screen
Today’s topic is db2top’s Federation screen. Federation is a technology that allows you to build a heterogeneous set of database management systems into a single interface without requiring you to move all your data into one database. DB2 for LUW federation lets you query and perform transactions against Oracle, DB2 for z/OS, Microsoft SQL Server, or even another DB2 for LUW system. In today’s post, I will show you how to set up a simple federated system that connects to a DB2 for LUW data source and we will see how to monitor it with the db2top Federated screen.
On developerWorks, there is a good introductory article on IBM’s federated database technology written by Laura Haas and Eileen Lin. For this blog post, I’m just going to create a very simple configuration that does just enough to let us see what the db2top Federation screen can do. We’ll start by creating two DB2 for LUW databases, fedsrvdb and datasrc. Each database is created on a different physical machine. We want fedsrvdb to act as a federated server. This means that it routes SQL to the data source server. Here is how we’ll configure it:
Create fedsrvdb database on server 1.
db2 create db fedsrvdb
Create datasrc database on server 2.
db2 create db datasrc
On server 1, turn on federation in the database manager configuration.
db2 update dbm cfg using federated yes
On server 2, set svcename in database configuration for datasrc so that it can be cataloged from server 1.
db2 update dbm cfg using svcename <port-number>
On server 1, catalog server 2’s instance as a remote node and catalog the datasrc database.
db2 catalog tcpip node server2 remote server2 server <port-number>
db2 catalog db datasrc at node server2
On fedsrvdb database, create DRDA wrapper.
db2 create wrapper drda
On fedsrvdb database, create a server definition using the DRDA wrapper.
db2 "create server srv2 type DB2/UDB version 7 wrapper drda authorization \"<your-userid>\" password \"<your-password>\" options (dbname 'datasrc')"
On fedsrvdb database, create a user mapping using the server definition.
db2 "create user mapping for <your-userid> server srv2 options (remote_authid '<your-authid>', remote_password '<your-password>')"
Now we can connect to fedsrvdb and use passthru SQL to send SQL to datasrc.
db2 set passthru srv2
db2 "select count(*) from syscat.tables"
db2 set passthru reset
With one more step, we can create nicknames for the tables at the datasrc database so that SQL can be issued against them without having to turn on passthru.
db2 "create nickname systab for srv2.\"SYSIBM\".\"SYSTABLES\""
db2 "select count(*) from systab"
Now let’s use db2top to monitor all of this federated activity. You launch db2top on your federated server and you launch the Federated screen by holding down the Shift key and pressing the ‘F’ key. The Federation screen consists only of a table, as shown below:
db2top Federation screen
The table has the following columns (split into multiple screens):
Column Name
Definition
Application Id
This identifier is generated when the application connects to the database at the database manager (appl_id). It is shown in bold text if any kind of work is being done between the federated server and a data source.
Application Name
The name of the application running at the client, as known to the database (appl_name).
Application Status
The current status of the application (appl_status). Possible values are:
Initializing (SQLM_INIT)
Connect Pending (SQLM_CONNECTPEND)
Connected (SQLM_CONNECTED)
UOW Executing (SQLM_UOWEXEC)
UOW Waiting in the application (SQLM_UOWWAIT)
Lock Waiting (SQLM_LOCKWAIT)
Commit Active (SQLM_COMMIT_ACT)
Rollback Active (SQLM_ROLLBACK_ACT)
Recompiling Plan (SQLM_RECOMP)
Compiling SQL Stmt (SQLM_COMP)
Request Interupted (SQLM_INTR)
Disconnect Pending (SQLM_DISCONNECTPEND)
Prepared Transaction (SQLM_TPREP)
Trans. heuristically committed (SQLM_THCOMT)
Trans. heuristically aborted (SQLM_THABRT)
Transaction ended (SQLM_TEND)
Creating Database (SQLM_CREATE_DB)
Restarting Database (SQLM_RESTART)
Restoring Database (SQLM_RESTORE)
Backing Up Database (SQLM_BACKUP)
Loading Database (SQLM_LOAD)
Unloading Database (SQLM_UNLOAD)
I/O Error Waiting (SQLM_IOERROR_WAIT)
Quiescing a Tablespace (SQLM_QUIESCE_TABLESPACE)
Waiting for remote node (SQLM_WAITFOR_REMOTE)
Pending results from remote request (SQLM_REMOTE_RQST)
Decoupled from coordinator (SQLM_DECOUPLED)
Rollback to savepoint (SQLM_ROLLBACK_TO_SAVEPOINT)
Unknown[x] where x is the value of an SQLM constant
Auth Id
The authorization ID of the user who invoked the application that is being monitored (auth_id).
Column Name
Definition
Delta Mode:
Rows Selected/s
Actual Mode:
Rows Selected
Delta Mode:
The number of rows per second that have been selected and returned to the federated server from the data source for this application during the interval (rows_selected).
Actual Mode:
The total number of rows that have been selected and returned to the federated server from the data source for this application since the last snapshot reset or federated server instance start (rows_selected).
Delta Mode:
Select Statements/s
Actual Mode:
Select Statements
Delta Mode:
The number of SQL SELECT statements per second that were executed by the federated server interacting with the data source for this application during the interval (select_sql_stmts).
Actual Mode:
The total number of SQL SELECT statements that were executed by the federated server interacting with the data source for this application since the last snapshot reset or federated server instance start (select_sql_stmts).
Select Time
This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to queries from this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (select_time).
Delta Mode:
Rows Updated/s
Actual Mode:
Rows Updated
Delta Mode:
The number of row updates attempted per second between the federated server and the data source for this applications during the interval(rows_updated).
Actual Mode:
The total number of row updates attempted between the federated server and the data source for this application since the last snapshot reset or federated server instance start (rows_updated).
Delta Mode:
Update Statements/s
Actual Mode:
Update Statements
Delta Mode:
The number of times per second that the federated server has issued an UPDATE statement to this data source on behalf of this application during the interval (update_sql_stmts).
Actual Mode:
The total number of times the federated server has issued an UPDATE statement to this data source on behalf of this application from the start of the federated server instance, or the last reset of the database monitor counters (update_sql_stmts).
Update Time
The aggregate amount of time, in milliseconds, that it has taken this data source to respond to UPDATEs from this application since the start of the federated server instance, or the last reset of the database monitor counters (update_time).
Delta Mode:
Rows Deleted/s
Actual Mode:
Rows Deleted
Delta Mode:
The number of row deletions attempted per second between the federated server and the data source for this application during the interval (rows_deleted).
Actual Mode:
The total number of row deletions attempted between the federated server and the data source for this application since the last snapshot reset or federated server instance start (rows_deleted).
Delta Mode:
Delete Statements/s
Actual Mode:
Delete Statements
Delta Mode:
The number of times per second that the federated server has issued a DELETE statement to this data source on behalf of this application during the interval (delete_sql_stmts).
Actual Mode:
The total number of times the federated server has issued a DELETE statement to this data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (delete_sql_stmts).
Delete Time
The aggregate amount of time, in milliseconds, that it has taken this data source to respond to DELETEs from this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (delete_time).
Column Name
Definition
Delta Mode:
Rows Inserted/s
Actual Mode:
Rows Inserted
Delta Mode:
The number of row insertions attempted per second between the federated server and the data source for this application during the interval (rows_inserted).
Actual Mode:
The total number of row insertions attempted between the federated server and the data source for this application since the last snapshot reset or federated server instance start (rows_inserted).
Delta Mode:
Insert Statements/s
Actual Mode:
Insert Statements
Delta Mode:
The number of times per second that the federated server has issued an INSERT statement to this data source on behalf of this application during the interval (insert_sql_stmts).
Actual Mode:
The total number of times the federated server has issued an INSERT statement to this data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (insert_sql_stmts).
Insert Time
The aggregate amount of time, in milliseconds, that it has taken this data source to respond to INSERTs from this application running on this federated server instance since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (insert_time).
Delta Mode:
Pass Thrus/s
Actual Mode:
Pass Thrus
Delta Mode:
The number of SQL statements per second that the federated server has passed through directly to this data source on behalf of this application during the interval (passthrus).
Actual Mode:
The total number of SQL statements that the federated server has passed through directly to this data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (passthrus).
Passthru Time
The aggregate amount of time, in milliseconds, that it has taken this data source to respond to PASSTHRU statements from this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (passthru_time).
Delta Mode:
SP_Rows Selected/s
Actual Mode:
SP_Rows Selected
Delta Mode:
The number of rows per second sent from the data source to the federated server as a result of stored procedure operations for this application during the interval (sp_rows_selected).
Actual Mode:
The total number of rows sent from the data source to the federated server as a result of stored procedure operations for this application since the start of the federated server instance, or the last reset of the database monitor counters (sp_rows_selected).
Delta Mode:
Stored Procs/s
Actual Mode:
Stored Procs
Delta Mode:
The number of stored procedures per second during the interval that the federated server has called at this data source on behalf of this application (stored_procs).
Actual Mode:
The total number of stored procedures from the start of the federated server instance, or the last reset of the database monitor counters, that the federated server has called at this data source on behalf of this application (stored_procs).
Proc Time
This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to stored procedure statements from this application since the start of the federated server instance or the last reset of the database monitor counters (stored_proc_time).
Column Name
Definition
Delta Mode:
Failed Statements/s
Actual Mode:
Failed Statements
Delta Mode:
The number of SQL statements per second that were attempted by the federated server interacting with the data source for this application during the interval, but failed (failed_sql_stmts).
Actual Mode:
The total number of SQL statements that were attempted by the federated server interacting with the data source for this application since the start of the federated server instance or the last reset of the database monitor counters, but failed (failed_sql_stmts).
Delta Mode:
Commit Statements/s
Actual Mode:
Commit Statements
Delta Mode:
The number of SQL COMMIT statements per second that have been attempted by the federated server interacting with the data source on behalf of this application during the interval (commit_sql_stmts).
Actual Mode:
The total number of SQL COMMIT statements that have been attempted by the federated server interacting with the data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters (commit_sql_stmts).
Delta Mode:
Rollback Statements/s
Actual Mode:
Rollback Statements
Delta Mode:
The number of SQL ROLLBACK statements per second that have been attempted by the federated server interacting with the data source on behalf of this application during the interval (rollback_sql_stmts).
Actual Mode:
The total number of SQL ROLLBACK statements that have been attempted by the federated server interacting with the data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters (rollback_sql_stmts).
Remote Locks
The total number of remote locks that the federated server has called at this data source on behalf of any application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (remote_locks).
RemLock Time
The aggregate amount of time, in milliseconds, that this data source spent in a remote lock from this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (remote_lock_time).
Delta Mode:
Create NickName/s
Actual Mode:
Create NickName
Delta Mode:
The number of times per second the federated server has created a nickname over an object residing on this data source on behalf of this application during the interval (create_nickname).
Actual Mode:
The total number of times the federated server has created a nickname over an object residing on this data source on behalf of this application since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest (create_nickname).
Cr_Nick Time
The aggregate amount of time, in milliseconds, that this data source spent processing CREATE NICKNAME statements from this application since the start of the federated server instance, or the last reset of the database monitor counter, whichever is the latest (create_nickname_time).
Column Name
Definition
DataBase Name
The real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created (db_name).
Datasource Name
The name of the data source whose remote access information is being displayed by the federated server (datasource_name). This element corresponds to the ‘SERVER’ column in SYSCAT.SERVERS.
Client Name
The nname in the database manager configuration file at the client database partition (client_nname). This element only applies to Windows Environments where the NetBIOS LAN environment exists.
The Federation screen is affected by pressing the ‘k’ key to switch between deltas and actuals. It is not affected by pressing the ‘i’ key to switch between showing active objects and showing all objects, pressing ‘X’ to switch an extended mode on and off, or pressing ‘G’ to switch between global and local snapshots.
