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/RMMExarvBVkIn 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-05.16.15.868260
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-10.07.19.390698
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.
#Db2#Featured-area-2