HOWTO: IBM DB2, ODBC, and Linux (*nix) configuration

The DB2 ODBC driver works like anything else IBM. Nonstandard to the rest of the universe. How to set up ODBC:

Unix admin needs to do:
Retrieve the config file locations:

$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/jason/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/etc/odbcinst.ini : list of the available drivers and default configuration for each driver
/etc/odbc.ini : list of the instances/databases for *all* users aka “System DSNs”
${HOME}/.odbc.ini : list of the instances/databases specific to *you* aka “User DSNs”

Add/update the following lines in the /etc/odbcinst.ini file (or wherever the file is as reported by odbcinst -j). Make sure there is no whitespace at the beginnings of the lines.

[DB2]
Description = DB2 Driver
Driver = /opt/ibm/db2/db2_v9.7/lib64/libdb2o.so
fileusage=1
dontdlclose=1

The driver line should point to the full path of libdb2o.so (any *nix but aix) or libdb2o.a (aix).

Add/update the following lines in the ${HOME}/.odbc.ini file:

[MYDB2DB]
Driver=DB2

You need to catalog the database (assuming you already cataloged the node [db2host in my case] – see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001944.html):

 # catalog database database_name as database_alias at node node_name
catalog database MYDB2DB as MYDB2DB at node db2host

Test connectivity to the remote database:

db2 connect to MYDB2DB user testuser using test_password

Ensure we’re using the unixODBC isql:

$ /usr/bin/isql --version
unixODBC 2.2.14

Validate using unixodbc “isql”:

$ /usr/bin/isql -v MYDB2DB testuser test_password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

The DSN for the entry is simply “dbi:ODBC:MYDB2DB” if you’re using Perl. Later versions of the db2 client allow you to specify the connect options in the DSN like so:

my $odbc_connect_str = "dbi:ODBC:"
               . "DRIVER=/opt/ibm/db2_v10.5/lib64/libdb2o.so;
               . "Protocol=tcpip;"
               . "Hostname=mydbhost;"
               . "Port=67033;"
               . "DATABASE=MYDB2DB";
Share Button

HowTo: Set up iSCSI with Multipath (MPIO) in Windows 10 SOLVED

I searched high and low and wasn’t able to find a definitive answer on whether Microsoft Windows 10 supports multipath (MPIO) over iSCSI. I found many many blog posts, articles, and press releases for Windows servers but nothing for Windows 10.

The good news is that Windows 10 supports it out of the box. You don’t have to install anything extra. Of course, the see the benefits of multipath, you will need two or more network cards. You can either bond the network cards together or set up individual routes to the iSCSI network addresses. I’ll assume that you did that already. 🙂

This is only for new connections to your iSCSI targets.  You can not retrofit multipath on to existing connections.  You will need to offline the volumes, remove the targets from the favorites and then reboot.

As an administrator on your Windows 10 box:

  • Launch the iSCSI initiator (Windows key and type iscsi initiator)

  • Add both ip/ports to the iSCSI host in the Discovery -> Portal Groups tab

  • Refresh the iSCSI targets in the Targets tab.  If they aren’t shown, add them manually
  • Select the iSCSI target, click connect, and select “Add this connection to the list of favorites…” (so it will automatically connect the next time you start windows)  and “Enable MPIO”.  Click Okay

  • Select the iSCSI target, click properties.  In the Portal Groups tab you should see both ip/port paths

  • If you needed to offline the volumes, you will need to now online them.  They should retain any drive letter assignments
Share Button

OpenVPN & Network Manager: selecting a random VPN target each time you start the Virtual Private Network (UNIX/Linux) SOLVED

I sometimes perform some IT work for a nonprofit organization. They use OpenVPN for their network but since they reside in different locations, they have multiple OpenVPN servers set up rather than just one point of entry. The problem I’ve noticed is that at times one or another will be slower. While I don’t have a mechanism to identify which is faster, I can roll the dice and have my vpn start script pick a random server instead of me having to randomly pick one myself.

#!/bin/bash

# If the network card is unavailable, we're not going to bring up the vpn
REQUIRED_CONNECTION_NAME="enp0s8"

# VPN_LIST is just a simple array
declare -a VPN_LIST

# BASH arrays start with index 0
i=0

# read the vpn list into an array
while read TMP_VPN; do
    VPN_LIST[$i]="$TMP_VPN"
    ((i++))
done < vpns.txt 

# if the vpns.txt is NOT empty
if (( i >= 0 )); then
    # Choose a random VPN index from the TMP_VPN array
    if (( i > 0 )); then
        ((i - 1))
        ((RANDOM_VPN = $RANDOM % $i))
    else
        RANDOM_VPN=$i
    fi

    # We set the VPN_CONNECTION_NAME to the VPN we chose
    VPN_CONNECTION_NAME=${VPN_LIST[$RANDOM_VPN]}

    DEFAULT_CONNECTION=$( nmcli con show --active |grep "${REQUIRED_CONNECTION_NAME}" )
    VPN_CONNECTION=$( nmcli con show --active | grep "${VPN_CONNECTION_NAME}" )

    # Make sure that the vpn connection isn't already up
    if [[ "${DEFAULT_CONNECTION}" != "${VPN_CONNECTION}" ]]; then
        echo -n "Connecting to ${VPN_CONNECTION_NAME} ... "

        # The credentials are stored in my Gnome keyring so I run the nmcli command as jason
        su - jason -c "nmcli con up id \"${VPN_CONNECTION_NAME}\""

        RC=$?

        if (( RC == 0 )); then
            echo "SUCCESS"
        else
            echo "FAILED"
        fi
    else
        echo "configuration mismatch"
        RC=1
    fi
fi

exit $RC

The file vpns.txt is simply a text file with the names of the VPNs as they are listed in OpenVPN (see /etc/NetworkManager/system-connections for the list of defined VPNs). One VPN per line.

vpn-east.example.org
vpn-west.example.org
vpn-europe.example.org
vpn-tokyo.example.org
Share Button

Loel Phelps: How To Create Sticky (Post-it) Notes With OneNote 2013 (YouTube)

Video by Loel Phelps 🙂

Share Button

HOWTO: Korn Shell / BASH: How to determine if a string is numeric or not

Occasionally we run across something that should be simple. Checking whether a string is a number or not for example.

special_CHAR ‘(‘ pattern ‘)’

The special_CHAR is a prefix that changes the number of characters expected:

‘*’ for zero or more matches
‘+’ at least one match
‘@’ for exactly one match
‘?’ for zero or one matches
‘!’ for negation

The following will match one or more digits:

+([[:digit:]])

See POSIX Character Classes regarding the use of [:digit:]

Below is an example function to determine if a string is numeric written in Korn Shell 93 but will also work in BASH.

The criteria we’re using is based on the US standard of numbers:
1
12.345
and so on

Obviously we could expand this out to handle commas or other separators without too much difficulty.

#!/bin/ksh93

function is_numeric {
    typeset TMP_STR="$1"
    typeset -i TMP_IS_NUMERIC

    if [[ "$TMP_STR" == +([[:digit:]])?(.*([[:digit:]])) ]]; then
        echo "'$TMP_STR' is numeric"
        TMP_IS_NUMERIC=1
    else
        echo "'$TMP_STR' is not numeric"
        TMP_IS_NUMERIC=0
    fi
}

for TMP_STRING in "TEST_VAR" "22" "TRUE1" "TRUE3TRUE" "12.345" "7.8.9.0"; do
    is_numeric "$TMP_STRING"
done

OUTPUT:

$ ./test_regex.ksh
'TEST_VAR' is not numeric
'22' is numeric
'TRUE1' is not numeric
'TRUE3TRUE' is not numeric
'12.345' is numeric
'7.8.9.0' is not numeric

See Finnbarr P. Murphy’s blog for more examples of using regular expressions in Korn Shell 93

Share Button

IQ Error: The multiplex server ‘iq_node_3’ is not included in the multiplex – SOLVED

When you run SAP’s IQ Multiplex cluster for a while you start finding little gotchas that will just drive you to drink. If you don’t drink, you will wish you do. (Just like with any other cluster system)

In my latest foray into the murky waters of IQ Multiplex (v16), if one of the nodes if offline for a while, the coodinator node will mark the node as excluded so the cluster carries on. Not really a big deal until you try to bring up the problem node:

I. 02/09 10:31:45. Database server stopped at Tue Feb 09 2016 10:31
DBSPAWN ERROR:  -82
Unable to start specified database: autostarting database failed
Exception Thrown from stcxtlib/st_database.cxx:10050, Err# 21, tid 2 origtid 2
   O/S Err#: 0, ErrID: 5120 (st_databaseException); SQLCode: -1013113, SQLState: 'QNA49', Severity: 14
[22016]: The multiplex server 'iq_node_3' is not included in the multiplex.
-- (stcxtlib/st_database.cxx 10050)

Error: The multiplex server 'iq_node_3' is not included in the multiplex. The multiplex server 'iq_node_3' is not included in the multiplex.
Server failed to start

2016-02-09-10:31:46 Start of IQ instance iq_mpx_cluster1 failed

Log into the coordinator node, in my case iq_node_1, and run

select server_name, status, mpx_mode, inc_state from sp_iqmpxinfo();
server_name        status   mpx_mode         inc_state
---------------------------------------------------------------
iq_node_1          included coordinator      N/A
iq_node_2          included writer           active
iq_node_3          excluded unknown           timed_out

As you can see, the iq_node_3 node is excluded due to the connection to it from the coordinator timed out. What to do? Simple, first we re-include the node (on the coordinator):

alter multiplex server iq_node_3 status included;

Next we need to resync iq_node_3:

Resync IQ secondary node

The problem node should start up just fine now.

Share Button

Tweaking Microsoft Surface 3

I use a Microsoft Surface 3 Pro among other things at work. It’s growing on me. Here is a short video on how to tweak it

Video by Seon Ong

Share Button

Take effective meeting minutes using OneNote 2013

I’ve been getting into Microsoft OneNote for taking notes, getting organized and so on. Little things like taking notes in a meeting can save your backside (CYA). If there is a white board involved, make sure you use Microsoft’s Office Lens or similar to take a picture of the white board and embed it in your notes

Video by Business Productivity

Share Button

Microsoft OneNote for Pros – Smart Note-Taking Tips

Learn how you can use hot keys, insert and use Audio/Video, integrate Excel, and check changes/versions to a doc through the history tab to be more productive in OneNote. Work Smart brings tips/tricks from Microsoft IT that improve digital productivity and help create an all-digital culture within Microsoft.

Getting into OneNote now…..

Share Button

I was using OneNote like a neanderthal. Now I know better :)

David Smith shows how to use Microsoft’s OneNote so that it is actually useful

Share Button