Showing posts with label Scripts. Show all posts
Showing posts with label Scripts. Show all posts

Monday, 3 March 2014

DataStage Job Codes. (dsjob)

DataStage Job Codes.
DSJS.RUNNING 0  This is the only status that means the job is actually running
DSJS.RUNOK 1  Job finished a normal run with no warnings
DSJS.RUNWARN 2  Job finished a normal run with warnings
DSJS.RUNFAILED 3  Job finished a normal run with a fatal error
DSJS.QUEUED 4  Job queued waiting for resource allocation
DSJS.VALOK 11  Job finished a validation run with no warnings
DSJS.VALWARN 12  Job finished a validation run with warnings
DSJS.VALFAILED 13  Job failed a validation run
DSJS.RESET 21  Job finished a reset run
DSJS.CRASHED 96  Job has crashed
DSJS.STOPPED 97  Job was stopped by operator intervention (can't tell run type)
DSJS.NOTRUNNABLE 98  Job has not been compiled
DSJS.NOTRUNNING 99  Any other status



Friday, 14 February 2014

Unix Command to Restart(STOP and START) DataStage Server

Unix Command to Restart(STOP and START) DataStage Server

1. Log in to the engine tier computer as the IBM InfoSphere DataStage administrator (usually dsadm).
2. Change to the engine directory and set the environment.
3. cd $DSHOME
. ./dsenv
$DSHOME is the name of the engine directory. The default directory is
/opt/IBM/InformationServer/Server/DSEngine.
4. Stop the engine.
./bin/uv –admin -stop
The command displays a message when the engine stops.
5. Restart the engine.
./bin/uv –admin -start

Wednesday, 22 January 2014

Performance Points

Performance Points:
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
3) Increase Sort performance where possible. Careful job design can improve the performance of sort operations, both in standalone Sort stages and in on-link sorts specified in the Inputs page Partitioning tab of otherstage types.
4) Remove Unneeded Columns. Remove unneeded columns as early as possible within the job flow. Every additional unused column requires additional buffer memory, which can impact performance and make each row transfer from one stage to the next more expensive. If possible, when reading from databases, use a select list to read just the columns required, rather than the entire table.
5) Avoid reading from sequential files using the Same partitioning method. Unless you have specified more than one source file, this will result in the entire file being read into a single partition, making the entiredownstream flow run sequentially unless you explicitly repartition
The environment variable APT_STRING_PADCHAR can be used to change the default pad character from an ASCII NULL (0×0) to another character; for example, an ASCII space (Ox20) .
6)It is important to note that the individual SQL statements required by a Sparse Lookup are an expensive operation from a performance perspective. In most cases, it is faster to use a DataStage Join stage between the input and DB2 reference data than it is to perform a Sparse Lookup.
7) For scenarios where the number of input rows is significantly smaller (1:100 or more) than the number of reference rows in a DB2 or Oracle table, a Sparse Lookup may be appropriate. CPU-intensive applications, which typically perform multiple CPUdemanding operations on each record, benefit from the greatest possible parallelism, up to the capacity supported by your system.
8)Parallel jobs with large memory requirements can benefit from parallelism if they act on data that has been partitioned and if the required memory is also divided among partitions.
9)Applications that are disk- or I/O-intensive, such as those that extract data from and load data into RDBMSs, benefit from configurations in which the number of logical nodes equals the number of disk spindles being accessed. For example, if a table is fragmented 16 ways inside a database or if a data set is spread across 16 disk drives, set up a node pool consisting of 16 processing nodes.
10)For some jobs, especially those that are disk-intensive, you must sometimes configure your system to prevent the RDBMS from having either to redistribute load data or to re-partition the data from an extract operation.
11) The speed of communication among stages should be optimized by your configuration. For example, jobs whose stages exchange large amounts of data should be assigned to nodes where stages communicate by either shared memory (in an SMP environment) or a high-speed link (in an MPP environment). The relative placement of jobs whose stages share small amounts of data is less important.
12)Turn off Runtime Column propagation wherever it’s not required.
13) If you are using a copy or a filter stage either immediately after or immediately before a transformer stage, you are reducing the efficiency by using more stages because a transformer does the job of both copy stage as well as a filter stage
14)If you have a single input and single output when you are using a copy stage then set the force option to tru so that DataStage should not try to optimize the job by removing the Copy operation.

Script to check datastage job is multi instance.

The following script can be used to check if the given datastage job is multi instance or not.
Arguments to the Scripts:
Arg1:Datastage Project Name
Arg2:JobName
#!/bin/ksh
ScriptName=$(\basename $0)
if [[ $# != 2 ]];then
printf “Error: $ScriptName – Required parameter(s) was not supplied!\n\n”
printf “Usage: $ScriptName  <Project> <JobName>\n\n”
printf “       <Project> : is the unique identifier for the datastage project, required\n”
printf “       <JobName> : is the Datastage JobName, required\n”
exit 1
fi
Project=$1
JobName=$2
echo “step ………: checking job whether it is multiinstance or not”
DSHOME=`cat /.dshome`
tmpFile=/tmp/CheckJobType.tmp
cd $DSHOME
bin/uvsh <<! > $tmpFile
LOGTO $Project
SELECT  ‘JobType:’||EVAL “TRANS(‘DS_JOBOBJECTS’,'J\’:@RECORD<5>:’\ROOT’,59,’X')”  FROM DS_JOBS WHERE NAME = ‘$JobName’;
!
if [[ $? != 0 ]]; then
echo “CheckJobType – unable to determine jobtype for job $JobNameh in $Project datastage project!”
exit 1
fi
JobType=$(\grep “^JobType:” $tmpFile |\cut -f 2 -d :)
if [[ -z $JobType ]]; then
echo “CheckJobType – Job $JobName not found in $Project datastage project. JobType cannot be determined!”
\rm -f $tmpFile
exit 1
fi
if [[ $JobType -eq 0 ]]; then
echo “*****$JobName is not a multi instance job*****”
else
echo “*****$JobName is  multi instance job*****”
fi
\rm -f $tmpFile
exit 0

Sample script to connect to oracle database

Here is the sample script to connect to oracle database and get value.
sqllog=/tmp/sqllog.dat
spoolout=/tmp/spool.out
sqlplus -s /nolog <<EOF > $sqllog
set term off
set head off
set linesize 300
set feedback off
set trimspool on
set serveroutput on
whenever oserror exit
whenever sqlerror exit sql.sqlcode
connect OraUser/OraPassword@OraName
spool $spoolout
select * from dual;
spool off
exit
EOF
rc=$?
if [[ $rc != 0 ]];then
echo “unable to connect to oracle database”
fi

Saturday, 2 November 2013

Orchadmin in Datastage- Dataset Deletion, Copy, Extraction

Orchadmin in Datastage

Orchadmin


DataStage stores data in persistent internal (specific to DataStage) format in the form of Data sets. Orchestrate Data set aid in the parallel processing of data and are much faster performance wise. They help in achieving end-to-end parallelism by writing data in partitioned form and maintaining the sort order. Orchestrate Data set consists of one or more data files stored on multiple processing nodes. A parallel data set is represented on disk by:



• A single descriptor file - defines the record schema of the data set and the location of all data files in the set. It does not contain the actual data.
• Data files (which contain the actual data) located on one or more processing nodes.


Orchadmin Utility

This is an Orchestrate Administrator Utility. It can perform operations on Data sets which cannot be performed by normal UNIX file commands. The basic syntax is:


orchadmin [command] [options] [descriptor_files]


Commands

The various commands that are available with orchadmin are dump, delete, truncate, copy and describe.


Dump Command


This command can be used to write records from a given data set onto standard output or can be redirected to a sequential file. The syntax is:
Syntax-orchadmin dump [options] descriptor_file
If no option is specified, all the records will be returned to the standard output.

ex
1)orchadmin dump test.ds

2)orchadmin dump test.ds>temp.txt

In the second example temp.txt file will contain data present in test.ds


Delete Command

rm deletes only descriptor file and the actual data is not deleted as it is present in the data files which reside on the processing nodes.Toremove the persistent data from the data sets the conventional approach is the use of Data set management in data stage.

Orchadmin utility simplifies the whole process by providing the delete command. The syntax is:


Syntax-orchadmin delete [-option] ds_1 ... ds_N

ex-orchadmin delete test.ds


Describe Command

This command outputs a report about the datasets specified. The syntax is:
orchadmin describe [-options] descriptor_file
ex- orchadmin describe test.ds

Copy Command

This command can be used to create an identical dataset with the same column definition and number of records. Orchadmin copy command can be used to take backups of existing datasets.

Syntax-orchadmin copy source-ds target-ds

ex-orchadmin copy temp.ds temp_target.ds


Note:
1)If one uses the UNIX cp command then only the descriptor file is copied, and these descriptor files point to the same data files residing in the processing nodes.
2)Type orchadmin on command prompt to get help information about this command

dsjob -run Command / How to Run Datastage Job from Unix Command Line

How to Run Datastage Job from Unix Command Line:

Most Data warehousing project requires that your job runs in batches at specified time slots. In such cases the Datastage jobs are usually scheduled by using an external scheduling tool like ESP Scheduler, Control M, Autosys, etc… 

This is made possible by writing scripts that will run your jobs through the command line.  The command line is very powerful interface to Datastage which lets us do more than just run the normal job. The guides present in the Datastage documentation will be very helpful in exploring the various things that can be done through the command line. 

However this gives the basics you will need to need to carry out your execution

In UNIX, the Datastage home directory location will always be specified in the “.dshome” file which will be present in the root directory.  Before you can run your Datastage commands you will have to run the following commands


§ cd  `cat /.dshome`

This will change the location to the home directory. By default this will be /opt/IBM/InformationServer/Server/DSEngine

§                     . ./dsenv > /dev/null 2>&1


This will run the dsenv file which contains all the environment variables. Without doing this, your UNIX commands won’t run on the command prompt.

After you have done this then you can use any Datastage command for interacting with the server. The main command 

To run a job:

Using the dsjob command you can start,stop,reset or run the job in  validation mode.

dsjob  –run –mode VALIDATE/RESET/RESTART  project_name  job_name

This command will actually run the job in validation mode. Similarly you can use RESET or RESTART instead of VALIDATE depending on what type of run you want. 

If you want a normal run then you will not need to specify the –mode keyword as shown below

dsjob –run project_name  job_name | job_name.invocationid

Running with the invocationid would mean that the job would be run with that specific invocation id

Now if you have parameters to set or paratemeterset values to set then this can also be as set as shown below

dsjob –run –param variable_name=”VALUE” –param psParameterSet=”vsValueSet” project_name  job_name

To stop a job:

Stopping a job is fairly simple. You might not actually require it but still its worth to take a look. It acts the same way as you would stop a running job the Datastage director.

dsjob –stop  project_name  job_name|job_name.invocationid

To list projects, jobs, stages in jobs, links in jobs, parameters in jobs and invocations of jobs. 

dsjob command can very easily give you all the above based on the different keywords. It will be useful for you if you want to get a report of what’s being used in what project and things like that

The various commands are shown below

‘dsjob –lprojects’  
will give you a list of all the projects on the server

‘dsjob –ljobs  project_name’ 
will give you a list of jobs in a particular project

‘dsjobs –lstages  project_name job_name’  

will give you a list of all the stages used in your job. Replacing –lstage with –links will give you a list of all the links in your job. Using –lparams will give you a list of all the parameters used in your job. Using –linvocations will give you a list of all the invocations of your multiple instance job.

To generate reports of a job

You can get the basic information of a job buy using the  ‘jobinfo’ option as shown below

dsjob -jobinfo  project_name job_name

Running this command will give you a short report of your job which includes The current status of the job, the name of any controlling job for the job, the date and time when the job started , the wave number of the last or current run (internal InfoSphere Datastage reference number) and the user status

You can get a more detailed report using the below command

dsjob -report  project  job_name BASIC|DETAIL|XML

BASIC means that your report will contain very basic information like start/end time of the job , time elapsed and the current status of the job. DETAIL as the name indicates will give you a very detailed report on the job down to the stages and link level. XML would give you an XML report which is also a detailed report in an XML format.

To access logs:

You can use the below command to get the list of latest 5 fatal errors  from the log of the job that was just run

dsjob -logsum –type FATAL –max 5 project_name job_name

You can get different types of information based on the keyword you specify for –type. Full list of allowable types are available in the help guide for reference.
There are a number of other options also available to get different log information. You can explore this in more detail in the developer guide. With the Datastage commands you can administer jobs, run jobs, maintain jobs, handle errors, prepare meaningful job logs and even prepare reports.  The possibilities are endless. If you like to code then you won’t mind spending your time exploring the command line options available.

Basic Practical Perl Tutorial



Hope this helps