Windows Server tips

1. How to read a file using Power Shell

a. How to search for a text within a  file -

Get-Content <file> | Where-Object {$_ -like '*<TEXT*'} 

b. How to search for a text recursively in a drive :

Get-ChildItem -Path e:\*.* -recurse |  Select-String -Pattern "SQL14P3" 

c. How to read last n lines from  a file -

Get-Content <file> | Select-Object -last 100

Determine which program uses or blocks a port

Windows:

The Netstat.exe utility has a switch, that can display the process identifier (ID) that is associated with each connection to identify port conflicts. This information can be used to determine which process (program) listens on a given port.

Using Netstat command:

1. Open a CMD prompt
2. Type in the command: netstat -ano -p tcp
3. You'll get an output similar to this one
4. Look-out for the TCP port in the Local Address list and note the corresponding PID number

If you use Task Manager, you can match the process ID that is listed to a process name (program). This feature enables you to find the specific port that a program currently uses. Because this specific port is in use already by a program, another program is prevented from using that same port.

To match the process ID to a program using Task Manager:

1. Press CTRL+ALT+DELETE, and then click Task Manager.
2. Click the Processes tab.
3. If you do not have a PID column, click View, click Select Columns, and then click to select the PID (Process Identifier) check box.
4. Click the column header that is labeled "PID" to sort the process by their PIDs. You should be able to easily find the process ID and match it to the program that is listed in Task Manager. 

To match the process ID to a program using the command line:

Example to find which process uses TCP port 9443:

C:\>netstat -ano -p tcp |find "9443"

Process with PID 1400 is listening on TCP port 9443. Now we can query task list to find the process.

C:\>tasklist |find "1400"

Here is a normal situation where EveryonePrint Web service is using TCP port 9443.

Linux:

The Netstat.exe command has a switch, that can display the process identifier (PID) that is associated with each connection to identify port conflicts. This information can be used to determine which process (program) listens on a given port.

Using Netstat command:

1. Open a terminal
2. Type in the command: sudo netstat -ano -p tcp
3. You'll get an output similar to this one
4. Look-out for the TCP port in the Local Address list and note the corresponding PID number

To match the process ID to a program:

From the netstat list you already know the Program name, you can have further details about it using the following command: 

ps -ef | grep <PID number>

Finding certificate details with thumbprint or name with Powershell:

Get-ChildItem -path 'Cert:\*CertificateThumbprintWithoutAnySpaces' -Recurse | Format-List 

Get-ChildItem -path 'Cert:\*b558e239f239bb87197eca54748e8c6e8be45aa9' -Recurse | Format-List

Configuring​ an ODBC driver to access Redshift

Environment - RHEL ( Unix OS) , SAP Data Services 4.2 on IPS and using Progress Data Direct Driver manager. 

 Pre-requisites - 

• A Driver Manager (because we are working on Linux)
• A Driver for Redshift
• A working Redshift
• A Security Group in AWS that allows the server to connect to Redshift

If you are missing ANY of these, you will not be successful!
Keep in mind that the Driver Manager (we are using Progress Data Direct) and the Driver are 2 very different things!
The driver for Redshift is what allows you to connect to it.
Whereas, the Driver Manager , well, it manages my drivers! 

In other words, if I was to pick different data sources (say, Redshift, and Impala), it would be the part that chooses the right driver for the right database.
Note that you still need the driver manager on Linux, even if you have a single data source.

Check unixODBC there

[ec2-user@ip-10-0-0-145 ~]$ sudo rpm -q unixODBC
package unixODBC is not installed

If not, install it

[ec2-user@ip-10-0-0-145 ~]$ sudo yum -y install unixODBC

Loaded plugins: amazon-id, rhui-lb

rhui-REGION-client-config-server-7 | 2.9 kB 00:00:00

rhui-REGION-rhel-server-releases | 3.7 kB 00:00:00

rhui-REGION-rhel-server-rh-common | 1.9 kB 00:00:00

(1/4): rhui-REGION-client-config-server-7/x86_64/primary_​db | 4.3 kB 00:00:00

(2/4): rhui-REGION-rhel-server-rh-common/7Server/x86_64/u​pdateinfo | 11 kB 00:00:00

(3/4): rhui-REGION-rhel-server-rh-common/7Server/x86_64/p​rimary | 30 kB 00:00:00

(4/4): rhui-REGION-rhel-server-releases/7Server/x86_64/pr​imary_db | 12 MB 00:00:00

(1/2): rhui-REGION-rhel-server-releases/7Server/x86_64/up​dateinfo | 549 kB 00:00:00

(2/2): rhui-REGION-rhel-server-releases/7Server/x86_64/gr​oup_gz | 133 kB 00:00:00

rhui-REGION-rhel-server-rh-common 131/131

Resolving Dependencies

--> Running transaction check

---> Package unixODBC.x86_64 0:2.3.1-10.el7 will be installed

--> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.3.1-10.el7.x86_64

--> Running transaction check

---> Package libtool-ltdl.x86_64 0:2.4.2-20.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================​==================================================​==================================================​==========================

Package Arch Version Repository Size

==================================================​==================================================​==================================================​==========================

Installing:

unixODBC x86_64 2.3.1-10.el7 rhui-REGION-rhel-server-releases 413 k

Installing for dependencies:

libtool-ltdl x86_64 2.4.2-20.el7 rhui-REGION-rhel-server-releases 49 k

Transaction Summary

==================================================​==================================================​==================================================​==========================

Install 1 Package (+1 Dependent package)

Total download size: 461 k

Installed size: 1.3 M

Downloading packages:

(1/2): libtool-ltdl-2.4.2-20.el7.x86_64.rpm | 49 kB 00:00:00

(2/2): unixODBC-2.3.1-10.el7.x86_64.rpm | 413 kB 00:00:00

--------------------------------------------------​--------------------------------------------------​--------------------------------------------------​--------------------------

Total 1.1 MB/s | 461 kB 00:00:00

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : libtool-ltdl-2.4.2-20.el7.x86_64 1/2

Installing : unixODBC-2.3.1-10.el7.x86_64 2/2

Verifying : libtool-ltdl-2.4.2-20.el7.x86_64 1/2

Verifying : unixODBC-2.3.1-10.el7.x86_64 2/2

Installed:

unixODBC.x86_64 0:2.3.1-10.el7

Dependency Installed:

libtool-ltdl.x86_64 0:2.4.2-20.el7

Dealing with the Redshift Driver

Note that once again, there are multiple choices. Datadirect offers some drivers, for a fee. Here, we are using the drivers provided for free by AWS.

Check if it’s there

[ec2-user@ip-10-0-0-145 ~]$ sudo rpm -q AmazonRedshiftODBC-64bit.x86_64

package AmazonRedshiftODBC-64bit.x86_64 is not installed

If not, install it

Make sure to check this page for the hyperlink to the latest .rpm of the driver. (mine below dates back to May).

[ec2-user@ip-10-0-0-145 ~]$ wget https://s3.amazonaws.com/redshift-downloads/driver​s/AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm--2015-05-15 12:45:31-- https://s3.amazonaws.com/redshift-downloads/driver​s/AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm

Resolving s3.amazonaws.com (s3.amazonaws.com)... 54.231.9.88

Connecting to s3.amazonaws.com (s3.amazonaws.com)|54.231.9.88|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 10546318 (10M) [application/x-rpm]

Saving to: ‘AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm’

100%[=============================================​==================================================​=======================================>] 10,546,318 --.-K/s in 0.1s

2015-05-15 12:45:31 (70.7 MB/s) - ‘AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm’ saved [10546318/10546318]

[ec2-user@ip-10-0-0-145 ~]$ sudo yum -y --nogpgcheck localinstall AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm

Loaded plugins: amazon-id, rhui-lb

Examining AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm: AmazonRedshiftODBC-64bit-1.1.0-1.x86_64

Marking AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package AmazonRedshiftODBC-64bit.x86_64 0:1.1.0-1 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================​==================================================​==================================================​==========================

Package Arch Version Repository Size

==================================================​==================================================​==================================================​==========================

Installing:

AmazonRedshiftODBC-64bit x86_64 1.1.0-1 /AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64 30 M

Transaction Summary

==================================================​==================================================​==================================================​==========================

Install 1 Package

Total size: 30 M

Installed size: 30 M

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : AmazonRedshiftODBC-64bit-1.1.0-1.x86_64 1/1

Verifying : AmazonRedshiftODBC-64bit-1.1.0-1.x86_64 1/1

Installed:

AmazonRedshiftODBC-64bit.x86_64 0:1.1.0-1

Complete!

Create some Environment Variables

The "USER DATA SOURCE" odbc.ini file located at /home/boxiadm/app/bodsx/dataservices/DataDirect/odbc/odbc.ini is the actual file used.

set the environment variables with the environment file. in my case it is .dbenv.csh

For UNIX system to pick up the right locations, we will need to define some environment variables.
But first, let’s see which location is currently defined.
Before changing anything else, you should run:

ODBC configuration files

We will need to update the 3 files, to describe our driver, and our data sources. I advise you to do them in the order described here.

Check the drivers

To check which drivers are configured, I can run this: 

[ec2-user@ip-10-0-0-145 ~]$ odbcinst -q -d 

note - this file is updated by the driver installation. no manual addition required in most of the case.

 

Watch out: In your odbcinst.ini file, if you have one or more spaces at the beginning of the line with the driver name, the odbcinst -q -d  will not pick them up. I does not seem to read the line at all. I wasted quite a bit of time, because I had "indented" the file. So remember that there should be no leading space on the line before the [MyRedshiftDriver].

amazon.redshiftodbc.ini

The default file needs to be modified because we are using unixODBC. The parts I changed are in Red below.

[ec2-user@ip-10-0-0-145 ~]$ cat /shared/odbc/amazon.redshiftodbc.ini

## If you did not install in the default directory, replace '/opt/amazon/redshiftodbc'

## with the correct location.

## - Note that this default DriverManagerEncoding of UTF-32 is for iODBC.

## - unixODBC uses UTF-16 by default.

## - If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value.

## Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC

## - AmazonDM can be used with UTF-8 or UTF-16.

## The DriverUnicodeEncoding setting will cause AmazonDM to run in UTF-8 when set to 2 or UTF-16 when set to 1.

[Driver]

#EG# DriverManagerEncoding=UTF-32

DriverManagerEncoding=UTF-16   --> make sure to update this 

ErrorMessagesPath=/opt/amazon/redshiftodbc/ErrorMe​ssages

LogLevel=0

LogPath=[LogPath]

## - Uncomment the ODBCInstLib corresponding to the Driver Manager being used.

## - Note that the path to your ODBC Driver Manager must be specified in LD_LIBRARY_PATH.

# Generic ODBCInstLib

# iODBC

#EG# ODBCInstLib=libiodbcinst.so

# AmazonDM / unixODBC

#ODBCInstLib=libodbcinst.so

# AIX specific ODBCInstLib

# iODBC

#ODBCInstLib=libiodbcinst.a(libiodbcinst.so.2)

# AmazonDM

#ODBCInstLib=libodbcinst.a(odbcinst.so)

# unixODBC

ODBCInstLib=/usr/lib64/libodbcinst.so   --> make sure to update this ; otherwise , 

We will get this error while testing :

[ec2-user@ip-10-0-0-145 ~]$ isql -v MyRealRedshift

[S1000][unixODBC][Amazon][ODBC] (11560)

Unable to locate SQLGetPrivateProfileString function.

[ISQL]ERROR: Could not SQLConnect

odbc.ini

 [ec2-user@ip-10-0-0-145 ~]$ cat /home/boxiadm/app/bodsx/dataservices/DataDirect/odbc/odbc.ini

ODBC Data Sources]

[AmazonODBC64]

# Driver: The location where the ODBC driver is installed to.

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonre​dshiftodbc64.so

# Required: These values can also be specified in the connection string.

Server=abc

Port=1234

Database=mydb

locale=en-US

 

This is where we will define our data sources. To see what is currently defined:

[ec2-user@ip-10-0-0-145 ~]$ odbcinst -q -s

 

Make sure to have the right database, user name, password details in odbc.ini file. 

[ec2-user@ip-10-0-0-145 ~]$ isql -v MyRealRedshift dummy dummy