Db2 Warehouse (On Premises and Cloud)



Monitoring External Table data movement jobs in Db2

By SANTOSH HEGDE posted Thu November 21, 2019 11:24 PM

External Tables is a SQL based data movement interface which allows loading and unloading data to and from tables using SQL statements. Due to this SQL nature of this data movement interface, users cannot use tradition utility monitoring techniques to monitor load and unload jobs which use external table.

You can understand more about External Table usage from this video https://youtu.be/RMMExarvBVk

In this article I explain how database administrators can identify activities corresponding to external table operations and monitor these activities to understand progress of the load and unload jobs.

Let us consider a simple external table load job to better explain how to monitor external table data movement jobs.

Let us consider the below simple external table load job.

insert into tvc1 select * from external '/home/santhegd/tvc1.txt'

To understand the external table jobs that are running at any point of time, we first need to understand how external tables work. External Tables leverage db2fmp process and hence understanding the information given out by db2pd on FMP processes helps us understand the external table jobs running at any given point in time.

The command “db2pd -fmp “ will give out an output which looks

FMP Process:
Address                                FmpPid Bit Flags               ActiveThrd PooledThrd ForcedThrd Active IPCList                                FmpUser
0x0000000203BCF9C0 10254   64  0x00640002 1                 0                     0                    Yes      0x0000000201FBF4A0 SANTHEGD

Active Threads:
Address                                FmpPid EduPid ThreadId
0x0000000203BCFD60 10254   18          140196235233024

Let us understand this output better. Here the Flags field tells us if this is an external table or not. A Flag value of 0x00640002 indicates this is an external table. At this point , it is not important how we derived to that conclusion, but users can always assume the Flags value for External Tables will be 0x00640002. Once we know the FMP entry corresponding to the external table, we can look at the active threads under it. In this case EduPid 18 is executing an external table job.

We can find more details about this job by finding the application handle of the application executing this job. The command db2pd -apinfo AppHdl -db <dbname> lists the applications. In this list of applications, find the application having Coordinator EDU ID same as the EduPid found in the previous step.

Application :
Address : 0x0000000202CF1300
AppHandl [nod-index] : 7 [000-00007]
TranHdl : 3
Application PID : 6729
Application Node Name : hotellnx115
IP Address: n/a
Connection Start Time : 2019-11-20-
Client User ID : santhegd
System Auth ID : SANTHEGD
Coordinator EDU ID : 18
Coordinator Member : 0
Registered Agents : 1

Once we get the application handle of the application running the external table job, we can use the monitoring functions to monitor the activity being performed by that application.

db2 "select st_text::varchar(80) as job, rows_inserted, local_start_time fromom table(mon_get_activity(7,-2))"

JOB                                                                                                                  ROWS_INSERTED LOCAL_START_TIME
-------------------------------------------------------------------------------- -------------------- --------------------------
insert into tvc1 select * from external '/home/santhegd/tvc1.txt' 214871                   2019-11-21-

In the above statement we pass 7 to mon_get_activity because that is the application handle(AppHandl) corresponding to the Coordinator EDU ID 18.

JOB is the External table statement that is loading the data.
ROWS_INSERTED is the number of records inserted by this external table command at the point the above query was run.
LOCAL_START_TIME is the time the statement started executing. It this value is NULL, that means the statement has entered the system but queued and not started execution yet.

mon_get_activity exposes many other metrics which may be useful to the user about the external table activity. This example just explains how an administrator can monitor the progress of the load job.