SAP Sybase IQ: How many connections are in use? SOLVED

Very simple question. Very simple answer.

select 
    @@max_connections as 'max_connections', 
    count(*) as 'active_connections', 
    (1 - (@@max_connections - count(*)) / convert(numeric, @@max_connections)) * 100 as 'percent_active' 
  from sp_iqconnection();

Output:

 max_connections active_connections percent_active
 --------------- ------------------ ---------------------
             350                 68               19.4286

@@max_ connections: For the network server, the maximum number of active clients (not database connections, as each client can support multiple connections). For Adaptive Server Enterprise, the maximum number of connections to the server. — Sybase IQ > Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Variables

Share Button

HOWTO: stty: tcgetattr: Not a typewriter Shell scripting SOLVED

If you connect to a remote system or run a script through a cron like scheduler, you may encounter an error message from the stty or some such program:

stty: tcgetattr: Not a typewriter

The error is raised because your script is being run in a non-interactive mode and the stty program is expecting to have access to a terminal (ptty / tty). If your script isn’t explicitly calling stty, check any scripts that you’re sourcing and you will find code similar to the following:

set -o vi
stty erase ^H

So, how do you work around this? Easily, simply check if the script is running in interactive mode.

if [[ $- = *i* ]]; then
    set -o vi
    stty erase ^H
fi

The shell special variable $- will list the shell modes that are active.

echo $-
ism
Share Button

HOWTO: SAP Sybase IQ / SQL Anywhere : searching date, datetime, time fields

If you’re new to IQ (or SQL Anywhere) you may run into an oddity that if you connect with ISQL the following code works but doesn’t work in DBISQL:

SELECT col1_date
FROM mytable
WHERE col1_date >= '06/01/2014'

This is because any connection to IQ using the native connection (SQL Anywhere connection) that is used by dbisql and the like expects string to datetime format of “YYYY-MM-DD HH:NN:SS.SSS”. This is not changeable by setting an option. The string needs to be converted using a style (datetime string style # 103) prior to being used as a SARG.

SELECT col1_date
FROM mytable
WHERE col1_date >= convert(datetime, '06/01/2014', 103)

Even those of us that have been around for eons tend to forget ‘little’ gotchas like this.

From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X41864.htm :

Table 2-3: Date format conversions using the style parameter
Without century (yy) With century (yyyy) Standard Output
Key “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
0 or 100 Default mon dd yyyy hh:mm AM (or PM)
1 101 USA mm/dd/yy
2 2 SQL standard yy.mm.dd
3 103 English/French dd/mm/yy
4 104 German dd.mm.yy
5 105 dd-mm-yy
6 106 dd mon yy
7 107 mon dd, yy
8 108 HH:mm:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mm:ss AM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
13 113 yy/dd/mm
14 114 mm/yy/dd
14 114 hh:mi:ss:mmmAM(or PM)
15 115 dd/yy/mm
16 or 116 mon dd yyyy HH:mm:ss
17 117 hh:mmAM
18 118 HH:mm
19 hh:mm:ss:zzzAM
20 hh:mm:ss:zzz
21 yy/mm/dd HH:mm:ss
22 yy/mm/dd HH:mm AM (or PM)
23 yyyy-mm-ddTHH:mm:ss

The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.

Share Button

HOWTO: Find the real and effective users in AIX & Linux when you’re sudo / su’d to another

Retrieving the user that you logged in as while running sudo or su’d into another user can be painful if you don’t have access to root. Here’s a short script that will retrieve the original user that was your session logged in as.

#!/bin/ksh93

OS_NAME=$( uname -s )

if [[ $OS_NAME == "AIX" ]]; then
    typeset var TTY
    REAL_USER=$( TTY=$(tty | sed 's:/dev/::' ) ; ps -t "$TTY" -o ruser=,etime= |sort -r -k2,2 | awk '{ print $1 } ' |head -1 )
elif [[ $OS_NAME = "Linux" ]]; then
    REAL_USER=$( ps T --sort start_time --no-heading -o ruser |head -1 )
else
    echo "ERROR: Requires Linux or AIX"
    exit 1
fi

EFFECTIVE_USER=$( whoami )

echo "I am \"$EFFECTIVE_USER\" but really \"$REAL_USER\""
ssh mybox
..
> sudo su - sybase

AIX Output:

 ./realme.ksh
I am "sybase" but really "jason"

Linux Output:

 ./realme.ksh
I am "sybase" but really "jason"
Share Button

HOWTO: Determine what process is listening on a port (AIX Unix specific)

I needed an easy way to determine which process was listening on a port. For AIX, you need to get the socket id from “netstat -Ana” and use the rmsock “rmsock socket_id tcpcb” to get the PID and command. It would be easy to expand this out to list command line and owner for each PID.

--------------------------------------------------------------------------------------
| Process              | PID             | Protocol | Listening On                   |
--------------------------------------------------------------------------------------
| WEBAPL               |         4915396 |      UDP |                127.0.0.1.32807 |
| WEBAPL               |         4915396 |      UDP |                127.0.0.1.32808 |
| WEBAPL               |        12058770 |      UDP |                127.0.0.1.51714 |
| WEBAPL               |        12058770 |      UDP |                127.0.0.1.51715 |
| backupserver         |        19791994 |      TCP |              192.168.1.4.50021 |
--------------------------------------------------------------------------------------
#!/bin/ksh93

OS_NAME=$( uname -s )

if [[ $OS_NAME == "AIX" ]] ; then
    echo "--------------------------------------------------------------------------------------"
    printf "| %-20s | %-15s | Protocol | %-30s |\n" "Process" "PID" "Listening On";
    echo "--------------------------------------------------------------------------------------"

    netstat -Ana | awk '
    /[0-9\*].[0-9].+LISTEN/ {
        SOCKET=$1;
        IPPORT=$5;
        "rmsock " SOCKET " tcpcb" | getline SOCKOUT;
        split(SOCKOUT, sockarray, " ");
        gsub(/[\.\(\)]/, "", sockarray[10]);
        LISTENERS[ sprintf("| %-20s | %15d | %8s | %30s |", sockarray[10], sockarray[9], "TCP", IPPORT) ] = 1;
    }
    /udp.*.[0-9]/ {
        SOCKET=$1;
        IPPORT=$5;
        "rmsock " SOCKET " inpcb" | getline SOCKOUT;
        split(SOCKOUT, sockarray, " ");
        gsub(/[\.\(\)]/, "", sockarray[10]);
        LISTENERS[ sprintf("| %-20s | %15d | %8s | %30s |", sockarray[10], sockarray[9], "UDP", IPPORT) ] = 1;
    }
    END {
        for (var in LISTENERS)
            print var

    }' | sort | uniq

    echo "--------------------------------------------------------------------------------------"
else
    echo "ERROR: Requires AIX"
    exit 1
fi
Share Button