Extract Transform & Load

5/19/20

SQL SERVER QUERY HISTORY

SELECT dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.last_execution_time > '5/19/2020 11:00'
AND dest.text LIKE '%WS_FILE%';

4/9/20

Datastage DSJOB -JOBINFO statuses

cd C:/IBM/InformationServer/Server/DSEngine/bin

./dsjob -jobinfo $project $jobname

1 "Finished"

2 "Finished (see log)"

11 "Validated OK"

12 "Validated (see log)"

21 "Has been reset"

99 "Compiled"

NOT Runnable Job Status (reset job)

0 "Running"

3 "Aborted"

13 "Failed validation"

96 "Crashed"

97 "Stopped"

98 "Not Compiled"

4/24/19

Auto Generate Seconds Counter in time portion - sql server

select Service,
Subscription_Number,
Order_Status,
Service_Type,
Data_Plan,
Carrier,
MDN,
Usage,
DATEADD(SECOND,ROWNUM,period_end_date) as sequence from
(
select Service,
Subscription_Number,
Order_Status,
Service_Type,
Data_Plan,
Carrier,
MDN,
Usage,
ROW_NUMBER() OVER(ORDER BY Usage ASC) as ROWNUM,
period_end_date
from
(
SELECT [Service]
,substring([Subscription_Number],8,7) Subscription_Number
,[Order_Status]
,[Service_Type]
,[Data_Plan]
,[Carrier]
,[MDN]
,round(ISNULL([Usage],0),2) Usage
,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) as period_end_date
FROM [dbo].[test_Usage]
where upper(Service) <> 'TOTAL'
--order by Subscription_Number desc
)A
)B

8/22/18

How to extract comma separated values from a String or Column or Field in Oracle

Ex: Address > 123 Main St,New York , NY , 00123

SELECT REGEXP_SUBSTR (Address, '[^,]+', 1, 1) AS Street
, REGEXP_SUBSTR (Address, '[^,]+', 1, 2) AS City
, REGEXP_SUBSTR (Address, '[^,]+', 1, 3) AS State
, REGEXP_SUBSTR (Address, '[^,]+', 1, 4) AS Zip
FROM table_x

8/21/18

APT_Communicator::connectTo: connect() failed due to Unix error = 10,061 (Unknown error) on node HOST_NAME using ConnectionInfo object 'TCP, connection Host: HOST_NAME (IP_ADDRESS), TCP port number: 0', RETRYING connect()

Problem(Abstract)

Errors related to this issue:

unable to connect to port 10000 on conductor; socket = 0, Unknown error

APT_Communicator::connectTo: connect() failed due to Unix error = 10,061 (Unknown error) on node HOST_NAME using ConnectionInfo object 'TCP, connection Host: HOST_NAME (IP_ADDRESS), TCP port number: 0', RETRYING connect()

Resolving the problem

Hosts should have at least the following 2 entries:

# localhost name resolution is handled within DNS itself.
127.0.0.1 localhost
::1 localhost

You should be able to ping localhost via V4 and V6 IP topologies:
ping -4 localhost
ping -6 localhost

Set $APT_USE_IPV4=1 as the default in all projects or set this as Windows default environment.

Steps to set $APT_USE_IPV4 DataStage Administrator client:

Select Projects tab
Select Project
Click Properties
Click Environment button
Click Parallel Category
Locate APT_USE_IPV4 and set to TRUE

8/16/18

Last Day of current month and Previous month Oracle sql

SELECT LAST_DAY(SYSDATE) last_day_current_month,
LAST_DAY(add_months(SYSDATE,-1)) last_day_prev_month
FROM DUAL;

6/15/18

HOW TO TAKE BACKUP OF DB2 OFFLINE

+++++++++++++++ TO TAKE BACKUP OF DB2 ++++++++++++++++++++++++
1) STOP DATASTAGE FROM CONTROL PANNEL
2) STOP ASB AGENT FROM SERVICES
3) cd C:\IBM\WebSphere\AppServer\profiles\InfoSphere\bin
stopServer.bat server1

after the successfull stop message then run the below backup commands.

DB2 CONNECT TO XMETA
DB2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
DB2 CONNECT RESET
DB2 DEACTIVATE DATABASE XMETA
DB2 BACKUP DATABASE XMETA TO "D:\Backups\DB2_Backup" EXCLUDE LOGS WITHOUT PROMPTING
DB2 CONNECT TO XMETA
DB2 UNQUIESCE DATABASE
DB2 CONNECT RESET

1)
cd C:\IBM\WebSphere\AppServer\profiles\InfoSphere\bin
startServer.bat server1
2) START ASB AGENT FROM SERVICES
3) START DATASTAGE FROM CONTROL PANNEL

++++++++++++++++++++++++++++++ RESTORE DB +++++++++++++++++++++++++
1) STOP DATASTAGE FROM CONTROL PANNEL
2) STOP ASB AGENT FROM SERVICES
3) cd C:\IBM\WebSphere\AppServer\profiles\InfoSphere\bin
stopServer.bat server1

after the successfull stop message then run the below backup commands.

DB2 CONNECT TO XMETA
DB2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
DB2 CONNECT RESET
DB2 RESTORE DATABASE XMETA FROM "C:\Downloads\DB2_Backup" TAKEN AT 20161102134130 WITHOUT PROMPTING
DB2 CONNECT TO XMETA
DB2 UNQUIESCE DATABASE
DB2 CONNECT RESET

3/22/18

Log everything to log file from unix shell script

Add the below lines at the begining of the script:

logfile=/folder/folder1/logfile.txt
exec > $logfile 2>&1

1/25/18

ODBC Stage Error

Select Statement:
select column1
,column2
,[column3 #]
,column4
from table

Error Message :
Error Message : SDE_ext: ODBC Info: SQLSTATE = 42S22: Native Error Code = 207: Msg = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'column3 ,[column4'.

Solution:
the '#' symbol needs to be converted to internal format of datastage.

So, changing the select statement to below will work.

Modified Select Statement:
select column1
,column2
,[column3 __035__]
,column4
from table

4/19/17

DataStage Server Jobs - Date , TimeStamp Conversion

OConv(Iconv(YourField,"D/YMD"),"D-YMD[4,2,2]") : " 00:00:00"
OConv(Iconv(YourField,"D-YMD"),"D-YMD[4,2,2]") : " 00:00:00"
OConv(Iconv(YourField,"D/YMD"),"D-YM[2,2]") : " 00:00:00"

+++++++

OConv(Iconv(StageVar1,"D/MDY"),"D-YMD[4,2,2]"):" ":Oconv(StageVar2,"MT"):":":"00"

input > 9/30/2017 8:45
Output> 2017-09-30 08:45:00

4/13/17

Date , Timestamp Conversions - SQL SERVER

select getdate()-90
Output : 2017-01-13 11:41:09.303

SELECT CONVERT(VARCHAR(19),getdate(),20) as X
Output : 2017-04-13 11:41:49

1/24/17

How to create a duplicate table in SQL Server

Login to the connection
Right click on the Database Name
Select Tasks
Select Export Data
Select Data Source Server Name and Database Name
Select Destination Server ( if you dont see your server name , add it manually) and Database Name
Then Select Copy Data from one or more tables or views
Then Select The Source Table you want to copy the data from Note : Change the Table Name if you are copying it to same Database
Then Select Next , check Run Immediately
Click Finish

Now the table will be created and Data will be copied.

12/9/16

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

12/8/16

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

9/30/16

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

9/14/16

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

8/30/16

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.

Log on to DS Administrator and go to the Projects tab. Select the project in question and click on the "Command" button.
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
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

# This will Clear the logfile of the Job

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

8/23/16

How to Save DataStage Job log from Director to a File

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

8/5/16

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

7/28/16

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.

7/19/16

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

7/13/16

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

7/5/16

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

Log in to the IBM InfoSphere Information Server Web console by using Administrator credentials.
On the Administration tab, expand the Domain Management section and click Engine Credentials.
Select the InfoSphere Information Server engine for which you want to map user credentials.
Click Open User Credentials.
Click Browse to search for suite users.
Optional: Specify additional search criteria, and click Filter to display a list of users.
From the search results, select the suite users that you want to map to the engine tier operating system local credentials and click OK.
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.
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.
Click Apply.

3/18/16

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

2/14/16

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.

1/10/16

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

10/2/13

ORA-01502 Oracle

Error Message :

The OCI function OCIStmtExecute returned status 1. Error code: 1502, Error message: ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state.

You will get above error messages in Datastage job or toad/sql developer when you are trying insert duplicate records into a table.

Solution :

1) check the index status in table by going to Index tab.
2) If it is in "UNUSABLE" state then you need to rebuild the index using below alter statement :

ALTER INDEX INDEX_NAME REBUILD;

Then run your job or insert sql.

9/20/13

Debug in Datastage 8.7

Step 1 : Right click on the link where you want to create a break point

Step 2 : Then click on Toggle Breakpoint

Step 4 : Then From the Menu bar click "Debug" then click "Go".

Then Job Run window and Debug window will appear, then provide required parameter values and click ok on Jon Run window.

It will show like below when Debug is running

Next it will show up the first row values like below.We can change number of rows you want to see by clicking "Edit Break Point" from the "Menu > Debug " or by right clicking on the "Break Point" that we created in Step 1.

7/1/13

How to search only for numbers in a column in Oracle : rtrim

with test as
(select '1gnec16z15j129947' vin from dual union all
SELECT '99999999999999999' vin FROM DUAL UNION ALL
SELECT '00000000000000000' vin FROM DUAL UNION ALL
SELECT '11111111111111111' vin FROM DUAL UNION ALL
select 'abcdefghijklmnop' vin from dual union all
select 'jtmbk31v076017323' vin from dual
)
SELECT * FROM TEST WHERE not rtrim(vin, '0123456789') is null

OUTPUT :

below is the output of the above query which is eliminating the numbers:

1gnec16z15j129947
abcdefghijklmnop
jtmbk31v076017323

*************************************************
with test as
(select '1gnec16z15j129947' vin from dual union all
SELECT '99999999999999999' vin FROM DUAL UNION ALL
SELECT '00000000000000000' vin FROM DUAL UNION ALL
SELECT '11111111111111111' vin FROM DUAL UNION ALL
select 'abcdefghijklmnop' vin from dual union all
select 'jtmbk31v076017323' vin from dual
)
SELECT * FROM TEST WHERE rtrim(vin, '0123456789') is null

OUTPUT :

below is the output of the above query which is eliminating rows other than numbers.

99999999999999999
00000000000000000
11111111111111111

Nav