Informatica domain Certificate Installation process

Informatica requires SSL certificate from 10.2 to enables secure communication between client and server machines.

1. Download the certificate in pfx (PKCS12) format from your organisation portal if trust chain is issued by org or create trust chain.

Import the certificate 

keytool -importkeystore -srcstoretype JKS -srckeystore privatecertificate.pfx 

-deststoretype PKCS12 -destkeystore infa_keystore.pkcs12

2. convert pfx to pem format

openssl pkcs12-in infa_keystore.pkcs12 -nodes -out infa_keystore.pem

3. convert pkcs12 to jks

keytool -importkeystore -srcstoretype PKCS12 -srckeystore infa_keystore.pkcs12

-deststoretype PKCS12 -destkeystore infa_keystore.jks

4. Import the root certificate into infa_truststore.jks. Below command will create theinfa_truststore.jks. file with root inside it . Trust the certififcate with yes when it is prompted.

keytool -import -alias root -keystore infa_truststore.jks -trustcerts -file "Root.crt"

5. Import the intermediate certificate into infa_truststore.jks. Below command will create theinfa_truststore.jks. file with root inside it . Trust the certififcate with yes when it is prompted.

keytool -import -alias root -keystore infa_truststore.jks -trustcerts -file "Intermediate.crt"

6.Import the public certificate into infa_truststore.jks. Below command will create theinfa_truststore.jks. file with root inside it . Trust the certififcate with yes when it is prompted.

keytool -import -alias root -keystore infa_truststore.jks -trustcerts -file "Cert.crt"

7. convert jks into pkcs12 format

keytool -importkeystore -srcstoretype JKS -srckeystore infa_truststore.jks

-deststoretype PKCS12 -destkeystore infa_truststore.pkcs12

7. convert pkcs12 into pem format

openssl pkcs12 -in infa_truststore.pkcs12 -nodes -out infa_truststore.pem

We have keystore and trust store in jks and pem format. We can use these trust chain for admin console and SSL connections.

to avoid confusion for domain and admin cosole keystore. We will copy the infa keystore to default keystore.

8. Copy infa keystore to default keystore.

cp infa_truststore.jks default.keystore

9. Copy these certificates into informatica default path which is below path.

cp   infa_*  $INFA_HOME/services/shared/

10. Go to ISP config and nodemeta.xml

cd $INFA_HOME/isp/config

cat nodemeta.xml | grep https

11. Shutdown the domain. Verify that no process running before proceed further step

./stopinfa.sh stop  

12. Get the https port and setup the node with the https port using infa setup command.

cd $INFA_HOME/isp/bin

./infasetup.sh updategatewaynode -dn Domain_ISP -hs httpsportnumber -kf keystorefile -kp keystorepassword -nk pathofthekeystore -nkp truststorefilepassword -nt truststore -ntp trusstorepassword

then run the default path also

./infasetup.sh updategatewaynode -hs httpsportnumber -kf defaultkeystorefile  -kp password

13. start the services

./startinfa.sh start

14. To check whether services are running fine

cd $INFA_HOME/logs/node

tail -50f catalina.out

15. Check all init methods called successfully  from cataline.out file.

Verify admin console running fine by using the file node.log

tail -50f node.log

16. Copy these truststore files into client machine and place in Informatica installed directory/shared and connect from client machine..

Informatica ODBC Connectivity Tests

Informatica connections can be configured using ODBC Drivers. This will be store in odbc.ini file.

cd $ODBC_HOME

./ssgodbc.aix64 -d datasource name -u username -p password

[Oracle_SSL]

Driver=/opt/sw/ODBC8.0/lib/DWora28.so

Description=DataDirect 7.1 Oracle Wire Protocol

IpAddress=12.12.1.12.12

PortNumber=1299

ServiceName=abc_service

EncryptionMethod=1

WithHold=1

TrustStore=/opt/sw/security/keystore.pem

Keystore=/opt/sw/wallets/ewallet.p12

KeystorePassword=keknekn

ValidateServerCertificate=1

CryptoProtocolVersion=TLSv1.2

./ssgodbc.aix64 -d datasource name -u username -p password

Sample String

------------------

[DB2_SSL]

Driver=/opt/sw/ODBC8.0/lib/DBdb227.so

Description=DataDirect 7.1 DB2 Wire Protocol

Database=Db2database

EncryptionMethod=1

GrantAuthId=LOCAL

GrantExecute=1

IpAddress=102.12.12.12

IsolationLevel=CURSOR_STABILITY

TcpPort=5029

WithHold=1

TrustStore=/opt/sw/security/keystore.pem

ValidateServerCertificate=1

CryptoProtocolVersion=TLSv1.2

SQL Logical Questions and answers

1) Query to combine two columns but second columns called Name and Price. 

Name max length will be 60. Then after 60 length price column will start. 

SELECT Name
	,Price
	,CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 45)
		,Price
		) AS ConcatedString
FROM [portfolio];


Here name coulmn values current lenght is different however when we combine using price it should show padding.
This can be achieved using the LEFT and Replicate function.




2)If there is multiple fields we need to follow the same

DECLARE @Table AS TABLE (Val VARCHAR(4000))

INSERT INTO @Table
SELECT CONCAT (
		'Name'
		,Replicate(' ', 36)
		,'Price'
		,Replicate(' ', 10)
		,'Price Change'
		)

INSERT INTO @Table
SELECT CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 40)
		,LEFT(CONCAT (
				Price
				,Replicate(' ', 15)
				), 15)
		,[Price Change]
		)
FROM [portfolio]

SELECT *
FROM @Table


The above query information can be achieved by using LPAD and RPAD functions.
unfortunately SQL server do not have LPAD and RPAD functionality. 
It be achieved by creating function by using LPAD and RPAD functions.

2) RPAD and LPAD Function in SQL Server

SQL Server by default do not have RPAD and LPAD functions.

CREATE FUNCTION dbo.LPAD (
	@Column VARCHAR(100)
	,@PadString VARCHAR(10)
	,@length INT
	)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Resultvar VARCHAR(100);

	SELECT @Resultvar = LEFT(CONCAT (
				REPLICATE(@PadString, @length - LEN(@Column))
				,@Column
				), @length);

	RETURN @Resultvar
END



CREATE FUNCTION dbo.RPAD (
	@Column VARCHAR(100)
	,@PadString VARCHAR(10)
	,@length INT
	)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Resultvar VARCHAR(100);

	SELECT @Resultvar = RIGHT(CONCAT (
				@Column
				,REPLICATE(@PadString, @length - LEN(@Column))
				), @length);

	RETURN @Resultvar
END


The query can be re written like the below

SELECT CONCAT (
		dbo.LPAD(Name, ' ', 60)
		,dbo.LPAD(Price, ' ', 60)
		,[Price Change]
		)
FROM [portfolio]

Import CSV Files into SQL Server using SSMS (SQL Server Management Studio)

 SSMS tool is used for writing queries. Apart from this, it is very helpful tool to import the CSV data into SQL Server.

Suppose we have a CSV file like this which is having double quotes and comma separated and it has headers also.

To Load this file into SQL Server, We can import using the below.

Database --> Tasks --> Import Data

It opensup the wizard.

Click next. Select Flat File as Data Source and select the file path,

Format :  Delimited. In the above CSV the delimiter is comma. 

Header Row Delimeter :  It is comma

Header rows to skip :  We have headers in the csv file so it should be zero

Select check box column names in the first row data

Click on columns to preview

If you want to change anything, use the advanced tab 

To preview rows clik on preview. 

Click next and select SQL native client and SQL server instance name. We windows or SQL based on the usage. Select database. 

Click next and sleect target name. We can rename the table also if it new table. Since this table not exist in db ,SQL will create automatically.

If we want to change anything in destination mapping we can use edit mapping. If we are sure it is not exist we can use create destination table or use other options based on usage.

Run immediately to run the package.

Click Finish to start the package.

The package run completed successfully.

We can see verify the table data by running the select in the target database.

Java Program to connect SQL server and fetch results from database using stored procedure

1. Program to fecth the Output from SQL server using the stored procedure without parameter

2. Program to fecth the Output from SQL server using the stored procedure with parameter

package SqlOutput;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://myserver;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

Scanner scanner = new Scanner(System.in);

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

//System.out.print("Enter your age: ");

//int age = scanner.nextInt();

// Display the input back to the user

// System.out.println("Hello, " + name + "! You are " + age + " years old.");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

// System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_StockInfo(?)}");

stmt.setString(1, name);

// Execute the stored procedure

ResultSet rs = stmt.executeQuery();

// Process the result set

while (rs.next()) {

System.out.println("Name: " + rs.getString("Name")+ " Quantity: " + rs.getString("Quantity"));

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

3. Program to fecth the multi results Output from SQL server using the stored procedure with parameters

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://DESKTOP-H8VE8AJ;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

try

(

Scanner scanner = new Scanner(System.in)

)

{

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

//System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_Stock_Details(?)}");

stmt.setString(1, name);

// Execute the stored procedure

Boolean results = stmt.execute();

while (results) {

ResultSet rs = stmt.getResultSet();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 1; i <= columnCount; i++) {

String columnName = rsmd.getColumnName(i);

Object columnValue = rs.getObject(i);

System.out.println(columnName + ": " + columnValue);

}

}

results = stmt.getMoreResults();

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

}

Java Program to Connect SQL Server

 First time I tried to connect SQL Server using Java

'

Faced below issue while trying to connect

This driver is not configured for integrated authentication. We need to download the jdbc libraries and place in this path.

Dowloaded file

Place file in jre path

"connection refused: getsockopt. verify the connection properties. make sure that an instance of sql server is running on the host and accepting tcp/ip connections at the port. make sure that tcp connections to the port are not blocked by a firewall.".

This is due to TCP/IP Disabled. Enabled TCP/IP and restarted services.

Add jdbc drivers to connect SQL server. Download latest libraries from SQL server

PowerShell SQLInvoke-Sqlcmd Scripts

1) PowerShell Command to display date by Connecting SQL Server 

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "DESKTOP-H8VE8AJ"

2) PowerShell Command to execute SQL Satement and save output to a file

Invoke-Sqlcmd -InputFile "C:\Users\OneDrive\Resumes\SQL Server Management Studio\Profit.sql" | Out-File -FilePath "C:\Users\OneDrive\Resumes\SQL Server Management Studio\Profit.rpt"

3) PowerShell command to pass the vairables

$StringArray = "MYVAR1='Machinename'", "MYVAR2='InstanceName'","MYVAR3='ServerName'

Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY(`$(MYVAR1)) AS ComputerName,

SERVERPROPERTY(`$(MYVAR2)) AS InstanceName,

SERVERPROPERTY(`$(MYVAR3)) AS ServerName;" -Variable $StringArray -ServerInstance "DESKTOP-H8VE8AJ"

4) 

Openssl command for certficates and key generation

 1) To connect secure port using SSL we can use the below command

openssl s_client -connect hostname/localhostip:portnumber

ex :  openssl s_client -connect 127.0.0.1:443

2) To show the certificate from website we can use the below command

openssl s_client -showcerts -connect 127.0.0.1:443

3) To Print Certificate we can use OpenSSL

echo | openssl s_client www.google.con -connectwww.google.con:443 2>dev/null | openssl x509 text

4) To covert pkcs12 to pem format we can use OpenSSL

openssl pkcs12 -in "certificate in pkcs12 format" -nodes -out "new_cert.pem"

5) to convert the certificate from crt to pem format

openssl x509 -in "cert.crt" -out "cert.pem"

6) to convert the certificate from cer to pem format

openssl x509 -in "cert.cer" -out "cert.pem"

7) to convert the certificate from pem to der format

openssl x509 -outform der -in "cert.pem" -out "cert.der"

8)  Openssl can used to generate public private key pair. Below uses pkcs8 format

openssl genrsa 4096 | openssl pkcs8 -topk8 -inform PEM -out rsa_key_4096.p8 -nocrypt

openssl rsa -in rsa_key_4096.p8 -pubout rsa_key_4096.pub

9)  Openssl can used to generate public private key pair. Below uses pkcs8 format

DB2 Commands

 1) db2level

This will show the DB2 product version.

2) db2 list node directory

This will show nodeDirectory list that contains hostname, service name, protocol, node name

3) db2 list database directory

This will show database name , database version, authetication type etc details

4)  db2 connect to "databasename" user "üsername" using "Password"

This will connect to database