Sunday, December 22, 2024

Oracle Meta Data Queries

This post explains the queries related to Oracle Metadata tables which is useful for finding the database and tables or user information.

1. Query to find particular table in a database.

SELECT *
FROM sys.all_tables
WHERE table_name LIKE 'STG_D%
The above query will get the all tables which is starting with STG_D. This table contains num of rows for each table etc.
2. Query to find particular column in a table database.
SELECT *
FROM sys.all_tab_columns
WHERE column_name LIKE 'NAME%'
The above query will get the all columns where column name starts with NAME. This table contains information like what is the data type of the column, precision, nullable or not, default value enabled or not, what is the low value of that column and what is the highest value of the column value etc
3. Query to find all view in a database
SELECT *
FROM sys.all_views
WHERE view_name LIKE '%NAME%'
The above query will get the all views in a database. It contains text columns where we can see entire view definition.
4. Query to find all data modifications on a table
SELECT *
FROM sys.all_tab_modifications
WHERE table_name LIKE '%TABLE1%'

The above query will show how many inserts, deletes, updates and trucates happened on that table. It shows the partitions that has been truncated.

5. Query to find all synonyms for a table

SELECT *
FROM sys.all_synonyms
WHERE table_name LIKE '%TABLE1%'

The above query will show all synonyms of TABLE1. Synonyms are using when we have table name is quite lengthy or want to read the table from different schema or restrict access to read only.

6. Query to find trigger on a table


SELECT *
FROM sys.all_triggers
WHERE table_name LIKE '%TABLE1%'


The above query will show if there is any trigger on the table TABLE1.


7. Query to find procedures, functions and packages in a database.

SELECT *
FROM sys.all_procedures
WHERE object_type = 'FUNCTION'

The above query will fecth all the functions in the database. For procedure we can use 'PROCEDURE' filter for packages we can package 'PACKAGE'.
8. To find the locked objects by users we can use the below command to identify users and take action accordingly.
SELECT doj.object_name
,vlo.oracle_username
,vlo.os_user_name
,vlo.process
,vlo.locked_mode
,vs.STATUS
,vs.machine
,vs.port
,vs.terminal
,vs.program
,vs.type
,vs.module
FROM dba_objects doj
JOIN v$locked_object vlo ON doj.object_id = vlo.object_id 
JOIN v$session vs ON vs.sid = vlo.session_id
The above query will fecth the objects that are locked.


Friday, November 8, 2024

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


Uploading: 313980 of 313980 bytes uploaded.



./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



Thursday, October 24, 2024

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]

2) Query to Create a table from 1 to 9 contain each number filled with one cell and total sum should be same.
That means the sum should be equal for each row (horizontally when we sum row) and
each column (when we sum all column values)
to achieve this we need to create a function that will return a table
for a row that sums to 15 by combining col1 ,col2 and col3 in row1
Then list of the 3*3 table.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: M.R M
-- Create date: 22-Dec-2024
-- Description: Create a possible matches
-- =============================================
CREATE FUNCTION dbo.SudokuTable (
-- Add the parameters for the function here
@minval INT
,@maxval INT
)
RETURNS @SudokuTable TABLE (
-- Add the column definitions for the TABLE variable here
FirstColumn INT
,SecondColumn INT
,ThirdColumn INT
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @rc1 INT
,@rc2 INT
,@rc3 INT
,@row1 INT
,@inc INT
,@sumval INT

SET @sumval = 15
SET @inc = 1
SET @rc1 = 1

WHILE @rc1 <= @maxval
BEGIN
SET @rc2 = @inc
SET @rc3 = @inc

WHILE (@rc2 <= @maxval)
BEGIN
SET @rc3 = @inc

IF @rc1 = @rc2
SET @rc2 = @rc2 + @inc

IF @rc1 = @rc3
SET @rc3 = @rc3 + @inc

WHILE (@rc3 <= @maxval)
BEGIN
SET @row1 = sum(@rc1 + @rc2 + @rc3)

IF (@row1 = @sumval)
BEGIN
INSERT INTO @SudokuTable
VALUES (
@rc1
,@rc2
,@rc3
)
END

SET @rc3 = @rc3 + @inc

IF (@rc3 = @rc2)
SET @rc3 = @rc3 + @inc

IF (@rc1 = @rc3)
SET @rc3 = @rc3 + @inc
END

SET @rc2 = @rc2 + @inc
SET @rc3 = @rc1 + @inc
END

SET @rc1 = @rc1 + @inc
END

RETURN
END
GO
The above will generates table have all possibilites to create
15 for each row.
Now we got all list, from that we need to make 3 rows .

SET NOCOUNT ON
DECLARE @SudokuTable AS TABLE (
ID INT identity
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
CREATE TABLE #temp1 (
ID INT identity(1, 1)
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
CREATE TABLE #temp2 (
ID INT identity(1, 1)
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
DECLARE @Finaltable AS TABLE (List VARCHAR(100))
DECLARE @minvalue INT
,@inc INT = 1
,@maxvalue INT
,@r1c1 INT
,@r1c2 INT
,@r1c3 INT
,@r2c1 INT
,@r2c2 INT
,@r2c3 INT
,@r3c1 INT
,@r3c2 INT
,@r3c3 INT
DECLARE @secondminval INT
,@secondmaxval INT
,@thirdminval INT
,@thirdmaxval INT
,@setval INT = 1
INSERT INTO @SudokuTable
SELECT *
FROM [dbo].[SudokuTable](1, 9)
SELECT @maxvalue = max(ID)
,@minvalue = min(ID)
FROM @SudokuTable
WHILE (@minvalue <= @maxvalue)
BEGIN
SELECT @r1c1 = FirstColumn
,@r1c2 = SecondColumn
,@r1c3 = Thirdcolumn
FROM @SudokuTable
WHERE ID = @minvalue

INSERT INTO #temp1
SELECT FirstColumn
,SecondColumn
,Thirdcolumn
FROM @SudokuTable
WHERE FirstColumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)
AND SecondColumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)
AND Thirdcolumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)

SELECT @secondmaxval = max(ID)
,@secondminval = min(ID)
FROM #temp1

WHILE (@secondminval <= @secondmaxval)
BEGIN
SELECT @r2c1 = FirstColumn
,@r2c2 = SecondColumn
,@r2c3 = Thirdcolumn
FROM #temp1
WHERE ID = @secondminval

INSERT INTO #temp2
SELECT FirstColumn
,SecondColumn
,Thirdcolumn
FROM #temp1
WHERE FirstColumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)
AND SecondColumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)
AND Thirdcolumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)

SELECT @thirdmaxval = max(ID)
,@thirdminval = min(ID)
FROM #temp2

WHILE (@thirdminval <= @thirdmaxval)
BEGIN
SELECT @r3c1 = FirstColumn
,@r3c2 = SecondColumn
,@r3c3 = Thirdcolumn
FROM #temp2
WHERE ID = @thirdminval

IF (
sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c2 + @r2c2 + @r3c2)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c3 + @r3c3)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c1 + @r2c2 + @r3c3)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c2 + @r3c1)
)
BEGIN
INSERT INTO @Finaltable
VALUES (
CONCAT (
'##Set '
,@setval
,' for Sudoko##'
)
)

INSERT INTO @Finaltable
VALUES (
CONCAT (
@r1c1
,' '
,@r1c2
,' '
,@r1c3
)
)
,(
CONCAT (
@r2c1
,' '
,@r2c2
,' '
,@r2c3
)
)
,(
CONCAT (
@r3c1
,' '
,@r3c2
,' '
,@r3c3
)
)

SET @setval = @setval + @inc
END

SET @thirdminval = @thirdminval + @inc
END

TRUNCATE TABLE #temp2

SET @secondminval = @secondminval + @inc
END

TRUNCATE TABLE #temp1

SET @minvalue = @minvalue + @inc
END SELECT *
FROM @Finaltable

Thursday, October 17, 2024

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.