Checking for database connections between partitions

In Db2 for i a "database" is the equivalent of a partition in IBM i "speak". Many of us work with multiple partitions, and there are programs that fetch and send data between them. The two most common ways you can send and receive data between partitions is using a DDM file and the SQL three part name.

Over my three decades of programming I have used both many times, although in the past few years I have used the SQL three part name more often. To go from just fetching a few records from a file, to copying whole files. But how can I tell if a particular job is using these kind of connections to fetch or send data?

Fortunately there is a SQL table function, ACTIVE_DB_CONNECTIONS, that allows me to see those connections.

While playing with this table function I did find that you will need to use a profile that has *JOBCTL authority, and I have the same profile name and password on both partitions.

ACTIVE_DB_CONNECTIONS required one parameter to be passed to it, the job name:

QSYS2.ACTIVE_DB_CONNECTIONS('999999/user/job_name')

If I want to look at my current job I can replace the job name with an asterisk:

QSYS2.ACTIVE_DB_CONNECTIONS('*')

Let me get started with the first example, a DDM file. First I am going to need one:

CRTDDMF FILE(MYLIB/DDMFILE) RMTFILE(MYLIB1/TESTFILE) RMTLOCNAME(RMTSYS)

If I run this simple example using ACTIVE_DB_CONNECTIONS...

01 SELECT * 02 FROM TABLE(QSYS2.ACTIVE_DB_CONNECTIONS('474383/SIMON/SIMON_1'))

... I get no results as the connection has not been used.

I created an extremely simple RPG program to use the DDM file:

01 **free 02 dcl-f DDMFILE ; 03 read DDMFILE ; 04 *inlr = *on ;

After executing the program I then use the following statement:

01 SELECT CONNECTION_USAGE , 02 REMOTE_HOST_NAME AS "Rmt host", 03 REMOTE_JOB_NAME AS "Rmt job", 04 REMOTE_USER AS "Rmt usr", 05 CONNECTION_TYPE AS "Con typ", 06 SCOPE 07 FROM TABLE(QSYS2.ACTIVE_DB_CONNECTIONS('474383/SIMON/SIMON_1'))

I am only interested a few of the columns ACTIVE_DB_CONNECTIONS returns:

1. CONNECTION_USAGE  Is this partition the application requester or server?
2. REMOTE_HOST_NAME  Name of the remote partition
3. REMOTE_JOB_NAME  The job name of the connection on the remote partition
4. REMOTE_USER  The user name that is used to establish the remote connection
5. CONNECTION_TYPE  There are four types of connection types. In these examples I am only concerned with two:
   • SNA:  SNA connection
   • TCP/IP:  TCP/IP connection
6. SCOPE  The scope of the connection

As I had used the DDM file, the connection was established and a job started on the remote partition:

CONNECTION_USAGE Rmt host Rmt job Rmt usr Con typ SCOPE ------------------ -------- ---------------- ------- ------- ---------------- APPLICATION SERVER RMTSYS 515092/QUSER/DEV SIMON SNA ACTIVATION GROUP

When that job ends so does the connection to the remote partition.

So what does a SQL three part name show? I ran the following statement in ACS's Run SQL Scripts:

SELECT * FROM RMTSYS.MYLIB1.TESTFILE ;

That established the connection, and started a job on the remote partition.

I use the following SQL statement to see the connection:

01 SELECT CONNECTION_USAGE , 02 REMOTE_HOST_NAME AS "Rmt host", 03 REMOTE_JOB_NAME AS "Rmt job", 04 REMOTE_USER AS "Rmt usr", 05 CONNECTION_TYPE AS "Con typ", 06 THREE_PART_NAMING AS "3 pt", 07 SCOPE 08 FROM TABLE(QSYS2.ACTIVE_DB_CONNECTIONS('*')) ;

I added an extra column to my results:

1. THREE_PART_NAMING  Was the connection started by a SQL statement that contained a three part name?

My results are as follows:

CONNECTION_USAGE Rmt host Rmt job Rmt usr Con typ 3 pt SCOPE ------------------ -------- --------------------- ------- ------- ---- ---------------- APPLICATION SERVER RMTSYS 484927/QUSER/QRWTSRVR SIMON TCP/IP YES ACTIVATION GROUP

If I had established both kinds of connections within the same job they would be shown in the same set of results:

CONNECTION_USAGE Rmt host Rmt job Rmt usr Con typ 3 pt SCOPE ------------------ -------- --------------------- ------- ------- ---- ---------------- APPLICATION SERVER RMTSYS 515116/QUSER/DEV SIMON SNA NO ACTIVATION GROUP APPLICATION SERVER RMTSYS 484927/QUSER/QRWTSRVR SIMON TCP/IP YES ACTIVATION GROUP

 

You can learn more about the ACTIVE_DB_CONNECTIONS SQL table function from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.