1

I need to know how many connection from an application is connected to my database. for that i should run an stored procedure. the output is like:

  CMD_EXEC: 1
  IFCA_RET: 0
  IFCA_RES: 0
  XS_BYTES: 0
  IFCA_GRES: 0
  GXS_BYTES: 0
  RETURN_CODE: 0
  MSG: NULL

  ROWNUM      TEXT
  1           DSNL200I  -DSNC DISPLAY LOCATION REPORT FOLLOWS-
  2           LOCATION                                       PRDID    T ATT CONNS
  3           ::170.0.0.236                                  SQL09079 S        277
  4           ::170.0.0.243                                  SQL10057 S          0
  5           ::170.0.0.249                                  JCC04160 S          0
  6           ::170.0.0.252                                  JCC03660 S          0
  7           ::170.0.0.204                                  JCC04160 S          0
  8           ::170.0.0.20                                   SQL10057 S          1
  9           ::170.0.0.21                                   JCC03660 S          0
  10          ::170.0.1.21                                   JCC04080 S       1440
  11          ::170.0.1.231                                  JCC03660 S          1
  12          ::170.0.7.2                                    JCC03640 S          0
  13          ::170.0.8.241                                  JCC03640 S          0
  14          ::170.0.8.142                                  JCC03690 S          0
  15          ::192.168.7.2                                  SQL09079 S          0
  16          ::192.168.1.4                                  JCC03650 S          5
  17          ::192.168.1.5                                  JCC03650 S          6
  18          ::192.168.1.0                                  JCC03690 S          0
  19          ::192.168.4.0                                  JCC03610 S          7
  20          ::192.168.4.0                                  JCC03610 S          2
  21          ::192.168.4.0                                  JCC04080 S        504
  22          ::192.168.1.7                                  SQL10055 S          1
  23          DISPLAY LOCATION REPORT COMPLETE

   "ADMIN_COMMAND_DB21" RETURN_STATUS: 0 

the column CONNS shows the count of connections. i need to make an array with this column and find its max. for example something like this:

 #/bin/bash

 #number of logs to offload
 #number of ADBAT
 #number of QUEDBAT
 #number of DSCDBAT
 #nmber of connections

 DB2PATH=/home/db2inst1/sqllib/bin/db2

 $DB2PATH connect to mydb >> /dev/null
 conn_arr=$($DB2PATH "CALL SYSPROC.ADMIN_COMMAND_DB21 ('-dis 
 location',17,LOC,NULL,?,?,?,?,?,?,?,?)"  )
 echo "$conn_arr" | ????
 $DB2PATH connect reset >> /dev/null

Every time this script the results and the row numbers change. What should i use?

  • It strikes me as something you'd want to do in your database query, not in a shell script. – Kusalananda Apr 6 at 7:20
  • @Kusalananda actually there is a table in database that with a simple query you can get the result but that is for LUW(linux). this one is for mainframe(zos) and must be handled with shell script. – BlackCrystal Apr 6 at 7:27
1

To obtain the maximum number of connections and save the number in the variable mx, try:

$ mx=$(awk '/COMPLETE/{exit} f{if($5+0>x)x=$5} /CONNS/{f=1} END{print x}' <<<"$conn_arr")
$ echo $mx
1440

How it works

By default, awk reads through a file one line at a time.

  • /COMPLETE/{exit}

    If the current line matches the regex COMPLETE, then stop reading any more lines and exit to the END command group.

  • f{if($5+0>x)x=$5}

    If the variable f is true (nonzero), then if the number in column 5 is greater than the variable x, set x to the value in column 5.

  • /CONNS/{f=1}

    If the current line contains the string (regex) CONNS, then set variable f to one. This signals the start of the table.

  • END{print x}

    After we have finished reading all the lines, print the value of x.

  • <<<"$conn_arr"

    Tell awk to read its input from the bash variable conn_arr. This construct is called a here string.

  • where should i add the path of my file or the variable that i redirected my SP output to? – BlackCrystal Apr 6 at 6:49
  • 1
    @BlackCrystal In my code, I called that variable conn_arr. Isn't that the same name that you used? – John1024 Apr 6 at 7:07
  • 1
    yes. didn't notice they are the same. WOW. your solution is so fast. in my main script i have a loop that do this for lots of databases and i got result in less than one second.thanks! – BlackCrystal Apr 6 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.