Wednesday, February 16, 2022

Checking for database connections between partitions

datbase connections between partititons

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.