Delete Duplicate Rows in DB2 Database

Many times and Many places, this question is being asked :-) that How to delete the duplicate row from a table in different DBs. Here, we will see How to do this in DB2 DB.

DataStage Scenario - Design4 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem4

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Sort and Transformer stage to achieve the output.

Removing last or more character of string

Want to remove last character from a string in Linux, We can do this with help of SED commands.

Use this

a) Remove last char
sed 's/.$//' input
or
sed 's/.\{1\}$//' input

i.e. -
echo 123456 | sed 's/.$//'
12345

echo 123456 | sed  's/.\{1\}$//'
12345

14 design tips for better performance in Datastage

1) Avoid unnecessary type conversions: set the OSH_PRINT_SCHEMAS environment variable to verify that run time schemas match the job design column definitions. If you are using stage variables on a Transformer stage, ensure that their data types match the expected result types.

2) Use Transformer stages sparingly and wisely. Transformer stages can slow down your job. Do not have multiple stages where the functionality could be incorporated into a single stage, and use other stage types to perform simple transformation operations

Delete All PuTTY Sessions Together

When you are swapping an old computer with a new computer, you may end-up transferring all PuTTY sessions to new computer. Once you’ve transferred all PuTTY sessions, execute “putty -cleanup” from the command line as shown below.

datastage4you QAnT - April2013 - 2

Click here ---> datastage4you QAnT - April2013 - 1

#14. DataStage Server – starting the server
a) DB2
su - db2inst1 -c "db2start“
b) WAS Server
/opt/IBM/WebSphere/AppServer/bin/startServer.sh server1 -user wasadmin -password PASSWORD
c) NODE
/opt/IBM/InformationServer/ASBNode/bin/NodeAgents.sh start
d) Engine
/opt/IBM/InformationServer/Server/DSEngine/bin/uv -admin -start

Sort stage to remove duplicate

1)what is the advantage of using sort stage over remove duplicate stage in removing duplicates.
2) Is there any way in which we can specify which record to retain(like retaining the last record or retaining the first) when we remove duplicate using transform stage, similarly in sort stage also.

Ans : 

1)The advantage of using sort stage over remove duplicate stage is that sort stage allows us to capture the duplicate records whereas remove duplicate stage does not.

2) Using a sort stage we can only retain the first record.
Normally we go for retaining last when we sort a particular field in ascending order and try to get the last rec. The same can be done using sort stage by sorting in descending order to retain the first record.

Removing Excel hyperlinks using a macro

Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.

1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:

DataStage Error: Job xxx is being accessed by another user

DataStage Error:  Job xxx is being accessed by another user  

Take below steps to get rid of locks in datastage --

• In web console check active sessions and if job is listed disconnect session.
  
  To view all active sessions:
  
  In the Web console, click the Administration tab.
  In the Navigation pane, select Session Management > Active Sessions
  

Clear the Job log in MetaData Repository

Sometime having too much logs stored in the metadata repository can be a reason behind a jobs slow performance.

Periodic log clearance would not only improve the performance but would keep the metadata database healthy. 

Managing profiles on WebSphere Application Server V7

Listing WebSphere Application Server profiles

To list all profiles on a server:

• On Windows: was_install_dir\bin\manageprofiles.bat –listProfiles
• On UNIX/Linux:was_install_dir/bin/manageprofiles.sh –listProfiles

DataStage huge logs removal resolution

Problem – While running the Jobs in DataStage, sometimes the log becomes so huge that the jobs hangs down. Sometimes when the Job is aborted, even then the Warnings keep on generating for a long time. And this increases the log messages for that job, subsequently increasing the table saving information in the Metadata database.

This increases the size of the table and the database tremendously.