Timestamp conversion From Text File to Oracle - Datastage

How to convert below Timestamp from Sequential File to Oracle Format using Transformer stage.

Input Fomrat ; 11/1/2016 4:26:52 PM

Conversion :

StringToTimestamp(Tx.DateTime,"%(m,s)-%(d,s)-%yyyy %(H,s):%(n,s):%(s,s) %aa")

Output Format :  2016-11-01 16:26:52




Source:
http://www.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.ds.parjob.adref.doc/topics/r_deeadvrf_time_Formats.html

How to substract timezones in Oracle

select to_char(systimestamp at time zone 'GMT','HH24') -  TO_CHAR(systimestamp,'HH24')  from dual


Substract Hours from Timestamp:

select (to_date('2016/07/01 03:10:04 PM', 'yyyy/MM/dd HH:MI:SS AM') -  interval '5' hour ) from dual

How to Stop/Start All Services in IBM InfoSphere Information Server on Windows

+++  Stop DataStage services +++
Start > Control Panel
Double click on the DataStage icon to open it
Click Stop All Services
Stop the ASB Agent
Start > All Programs > IBM Information Server > Stop the agent
Stop the WebSphere Server
Start > All Programs > IBM WebSphere > Application Server v6 > Profiles > default > Stop the server
Or From Command Line:
cd C:\IBM\WebSphere\AppServer\profiles\InfoSphere\bin
stopServer.bat Server_name



+++   Starting the services   +++
Start the WebSphere Server
Start > All Programs > IBM WebSphere > Application Server v6 > Profiles > default > Start the server

Or From Command Line:
cd C:\IBM\WebSphere\AppServer\profiles\InfoSphere\bin
startServer.bat Server_name

Start the ASB Agent
Start > All Programs > IBM Information Server > Start the agent
Start DataStage services
Start > Control Panel
Double click on the DataStage icon to open it
Click Start All Services

How to get the Job Number or RT_LOG number of a DataStage Job

•  cd to $DSHOME (/IBM/InformationServer/Server/DSEngine) on the server and source the dsenv file with:
• sh
• $ bin/uvsh
• 3. At the UV prompt, log on to the project (the project name is case sensitive):
• > LOGTO projectname
• 4. To find out what the job description number:
• > LIST DS_JOBS jobname
•  This will give the Job Number

Error Selecting from logfile RT_LOGxxx DataStage

DataStage Director Error Message When try to view the log.

Error selecting from log file RT_LOGnn
Command was: SSELECT RT_LOGnn WITH @ID LIKE '1NON' COUNT.SU
Error was: Internal data error. File /RT_LOGnn/':
Computed blink of 0xnnnn does not match expected blink of 0xnnnn! Detected within group starting at address 0xnnnnnn!

Cause:

The error message received indicates that the log file for the job is corrupted.

Solution:

The Log file must be recreated.

We can try 3 things here.

1) Create a Copy of the Job and delete the old job and compile the new job

0r

2) Manully delete and re-create the RT_LOGxx , for this follow below steps.

1. Log on to DS Administrator and go to the Projects tab. Select the project in question and click on the "Command" button.
2. Delete the existing damaged log file with the following command, where xxx is the description number as seen in the error:
   Ex : DELETE.FILE DATA RT_LOG52
3. Create a new log file with the following command, where xxx is the description number as seen in the error:
   CREATE.FILE DATA RT_LOG52 30

Or 

# This will Clear the logfile of the Job

3)   CLEAR.FILE RT_LOG52 ( This will do the trick )

How to Save DataStage Job log from Director to a File

1. 
   
   When DataStage Director is opened it will open in the default view and displays the status of all jobs in the folder currently selected in the repository tree.
   
   
   You must change to the Job Log View.
   Job Log View - Displays the log file for a job chosen from the Job Status view or the Job Schedule view. To switch to this view, choose View > Log, or click the Log button on the toolbar.
   
   
   Remove any filter.
   Select View > Filter Entries
   Under the Limit Window - Select All entries
   Make sure all Types of Messages are selected
   Print out the Job Log to a flat file. The content of the printout depends on the view you are using and the options you choose in the Print dialog box. Print the current view:
   Do one of the following to display the Print dialog box:
   Choose Project > Print... .
   Click the Print button on the toolbar.
   Choose the range of items to print in the Range area: select
   All entries prints all entries in the current view.
   Choose what to print by selecting in the Print what area:
   Full details prints detailed information for each item.
   Select the Print to file check box.
   Click OK. The Print to file dialog box appears. Enter the name of a text file to use. The default is DSDirect.txt in the client directory.
   
   
   Source : http://www-01.ibm.com/support/docview.wss?uid=swg21574038

Replace a String on a First Line in Unix

sed -e '1s/FromString/ToString/' 20160804.txt > 20160804_new.txt

How to convert fixed width file to delimeted file in UNIX

Ex: awk -v OFS=, '{ print substr($0, 1, 1), substr($0, 2, 10), substr($0, 12, 4), substr($0, 16, 2), substr($0, 18, 2) }' FIX_COMMA.txt > CSV.txt

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.

How to remove extra delimiters using DataStage

Input Sample Data:

Order_Number
----------------
C67330672,
C97652762,C67330672,
C67330672,C67330672,C67330672,
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445

To Eliminate extra delimiters [commas ","] in above first 3 rows used below process.

Defined Transformer Stage Variables:

svDCOUNT =  Dcount(Order_Number,",")
     svFIELD =  Field(Order_Number,",", svDCOUNT)
  svISNULL =  If Trim(svFIELD)='' Then 0 Else 1
svLENGTH =  Len(Order_Number)-1


Then in Derivation :

If svISNULL=0 Then Order_Number[1, svLENGTH] Else Order_Number


Output:
----------------------

Order_Number
----------------
C67330672
C97652762,C67330672
C67330672,C67330672,C67330672
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445

Difference between Oracle connector and Oracle Enterprise Stage

-Oracle Enterprise stage runs in sequential mode when used as source but connector can be run in parallel using different parallel read options. 

-Also connector is said to be faster than Enterprise stage. 

-Connection can be saved and can be reused. 

-Error/rejects can be captured with an error code and error text based on certain conditions in connector when used in target.

Assigning a Particular Node Or Resource Pool to a Stage

Specify node map or node pool or resource pool constraints. The configuration file allows you to set up pools of related nodes or resources. The Advanced tab allows you to limit execution of a stage to a particular node or resource pool. You can also use a map to specify a group of nodes that execution will be limited to just in this stage. Supply details as follows:

Node pool and resource constraints. Specify constraints in the grid. Select Node pool or Resource pool from the Constraint drop-down list. Select a Type for a resource pool and, finally, select the name of the pool you are limiting execution to. You can select multiple node or resource pools. This is only enabled if you have defined multiple pools in the configuration file.

Node map constraints. Select the option box and type in the nodes to which execution will be limited in the text box. You can also browse through the available nodes to add to the text box. Using this feature conceptually sets up an additional node pool which does not appear in the configuration file.

The lists of available nodes, available node pools, and available resource pools are derived from the configuration file.

Source: IBM