Find the Missing Values from Expected Values - Oracle/SQL

SELECT STATE
FROM
(
SELECT 'KS' AS STATE FROM DUAL UNION
SELECT 'WA' FROM DUAL UNION
SELECT 'OH' FROM DUAL UNION
SELECT 'MI' FROM DUAL UNION
SELECT 'IN' FROM DUAL UNION
SELECT 'AZ' FROM DUAL UNION
SELECT 'CO' FROM DUAL UNION
SELECT 'MO' FROM DUAL UNION
SELECT 'IA' FROM DUAL UNION
SELECT 'IL' FROM DUAL UNION
SELECT 'ND' FROM DUAL UNION
SELECT 'TX' FROM DUAL UNION
SELECT 'WI' FROM DUAL UNION
SELECT 'MN' FROM DUAL UNION
SELECT 'OR' FROM DUAL
) A
where STATE not in ( select STATE from SCHEMA.TABLE )

or You can Create table with Above rows and select the State from the that table.

Extract a String from a file name in unix

echo "BW-CDR-20160712000000-2-005056896A77-210486.csv" | cut -f 1 -d '.' | awk -F '-' '{print $6}'

Result ; 210486

echo "MCLD2BUL.T60718IL02.rdy" | awk -F '.' '{print $2}' | cut -c 7-8

Result ;  IL

Setup ODBC Connection for DataStage to Access on Windows Server

To Access any database from DataStage you will need a ODBC connection.


To Create a ODBC Connection in Windows Server 2008 R2 , follow the below steps.

For 32 Bit OS :

• 
  
• Navigate to Folder - C:\Windows\System32
• Double Click odbcad32.exe
• ODBC Administrator window opens
• Click System DSN Tab
• Click Add
• Then Follow Steps , when done click Finish

For 64 bit OS:

• Navigate to Folder - C:\Windows\SysWOW64
• Double Click odbcad32.exe ( Yes , its odbcad32.exe - its not a typo )
• ODBC Administrator window opens
• Click System DSN Tab
• Click Add
• Then Follow Steps , when done click Finish

How to map DataStage user Credentials to Operating system Credentials

If The DataStage User is not mapped to the Operating system user where the engine tier components are installed , DataStage user will have lot of problems accessing Remote Servers or Company Network Drives.

Example Situaltion : When i was trying to copy some files from the Network Drive using the script that is running from DataStage Parallel Job , it failed with an below error message.

Error Message :  \\network\drive : Logon failure: unknown user name or bad password. 

But when i run the same script from command prompt it ran fine.

Then i figured user credentials are not mapped correctly or not mapped at all. I followed instruction from the below IBM links and it worked fine.

https://www.ibm.com/support/knowledgecenter/SSPT3X_2.1.2/com.ibm.swg.im.iis.found.moz.wc.admin.doc/topics/wsisinst_config_user_mappings.html

1. Log in to the IBM InfoSphere Information Server Web console by using Administrator credentials.
2. On the Administration tab, expand the Domain Management section and click Engine Credentials.
3. Select the InfoSphere Information Server engine for which you want to map user credentials.
4. Click Open User Credentials.
5. Click Browse to search for suite users.
6. Optional: Specify additional search criteria, and click Filter to display a list of users.
7. From the search results, select the suite users that you want to map to the engine tier operating system local credentials and click OK.
8. On the Map User Credentials pane, select one or more users to map to the credentials. If you want to map some suite users to one user and map other suite users to a different user, select one subset of users and continue.
9. In the Assign User Credentials pane, specify the local operating system user credentials. The user name and password that you provide must be a valid user name and password for the operating system where the engine tier components are installed. If you want to preserve credential mappings that users have already configured, select the Apply Only to Users without Credentials check box.
10. Click Apply.