make an array from a dynamic column in the middle of text

manpreet Best Answer 3 years ago

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?

manpreet 3 years ago

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.