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

Nav