Data Stage Job Status Values

    The Data Stage Job Status values can be found in
$DSHOME/include/dsapi.h file

  These are the values defined in dsapi.h file for JOB STATUS:

1. #define DSJS_RUNNING        0      /* Job running */
   
2. #define DSJS_RUNOK          1      /* Job finished a normal run with no warnings */
   
3. #define DSJS_RUNWARN        2      /* Job finished a normal run with warnings */
   
4. #define DSJS_RUNFAILED      3      /* Job finished a normal run with a fatal error */
   
5. #define DSJS_VALOK          11       /* Job finished a validation run with no warnings */
   
6. #define DSJS_VALWARN        12      /* Job finished a validation run with warnings */
   
7. #define DSJS_VALFAILED      13      /* Job failed a validation run */
   
8. #define DSJS_RESET          21       /* Job finished a reset run */
   
9. #define DSJS_CRASHED        96      /* Job was stopped by some indeterminate action */
   
10. #define DSJS_STOPPED        97      /* Job was stopped by operator intervention (can't tell run type) */
    
11. #define DSJS_NOTRUNNABLE    98      /* Job has not been compiled */
    
12. #define DSJS_NOTRUNNING     99      /* Any other status */
    

JobStatus

DataStage Version 8 on Information Server

Enterprise PACKs

SAP BW Pack o BAPI: (Staging Business API) loads from any source to BW. o OpenHub: extract data from BW.

SAP R/3 Pack o ABAP: (Advanced Business Application Processing) auto generate ABAP, Extraction Object Builder, SQL Builder, Load and execute ABAP from DataStage, CPI-C Data Transfer, FTP Data Transfer, ABAP syntax check, background execution of ABAP. o IDoc: create source system, IDoc listener for extract, receive IDocs, send IDocs. o BAPI: BAPI explorer, import export Tables Parameters Activation, call and commit BAPI.

Siebel Pack o EIM: (data integration manager) interface tables o Business Component: access business views via Siebel Java Data Bean o Direct Access: use a metadata browser to select data to extract o Hierarchy: for extracts from Siebel to SAP BW.

Oracle Applications Pack o Oracle flex fields: extract using enhanced processing techniques. o Oracle reference data structures: simplified access using the Hierarchy Access component. o Metadata browser and importer

DataStage Pack for PeopleSoft Enterprise o Import business metadata via a metadata browser. o Extract data from PeopleSoft tables and trees.

JD Edwards Pack o Standard ODBC calls o Pre-joined database tables via business views Database Connectivity The common connection objects functionality means the very wide range of DataStage database connections are now available across Information Server products. Latest supported databases for version 8:

DB2 8.1, 8.2 and 9.1

Oracle 9i, 10i, 10gR2 not Oracle 8

SQL Server 2005 plus stored procedures.

Teradata v2r5.1, v2r6.0, v2r6.1 (DB server) / 8.1 (TTU) plus Teradata Parallel Transport (TPT) and stored procedures and macro support, reject links for bulk loads, restart capability for parallel bulk loads.

Sybase ASE 15, Sybase IQ 11.5, 12.5, 12.7

Informix 10 (IDS)

SAS 612, 8.1, 9.1 and 9.1.3

IBM WS MQ 6.1, WS MB 5.1

Netezza v3.1

ODBC 3.5 standard and level 3 compliant

UniData 6 and UniVerse ?

Red Brick Source

New Stages in DataStage Version 8

New Stages in Datastage Version 8 A new stage from the IBM software family, new stages from new partners and the convergence of QualityStage functions into Datastage. Apart from the SCD stage these all come at an additional cost.

WebSphere Federation and Classic Federation

Netezza Enterprise Stage

SFTP Enterprise Stage

iWay Enterprise Stage

Slowly Changing Dimension: for type 1 and type 2 SCDs.

Six QualityStage stages New Functions in Existing Stages

Complex Flat File Stage: Multi Format File (MFF) in addition to existing cobol file support.

Surrogate Key Generator: now maintains the key source via integrated state file or DBMS sequence.

Lookup Stage: range lookups by defining checking high and low range fields on the input or reference data table. Updatable in memory lookups.

Transformer Stage: new surrogate key functions Initialize() and GetNextKey().

Enterprise FTP Stage: now choose between ftp and sftp transfer. Source :

When to choose Server or Parallel Data stage job

1. The choice of server or parallel depends upon time to implement, functionality and cost.
2. When we have lots of functionality to implement for lower volume and hardware is less and ease of implementation we can go for Server jobs.
3. Parallel jobs are costly due to high scale of hardware , difficult to implement, extreme processing capabilities for absurd volumes with vast array of operators for high-performance manipulation.
4. When the data volume is less it is better to go for Server job as parallel jobs can have a longer start up time.
5. When data volume is high, it is better to choose parallel job than server job. Parallel job will be a lot faster than server job even if it runs on single node. The obvious incentive for going parallel is data volume. Parallel jobs can remove bottlenecks and run across multiple nodes in a cluster for almost unlimited scalability. At this point parallel jobs become the faster and easier option. A parallel sort stage is lot faster than server stage. A Transformer stage in parallel job with the same transformations in server job is faster. Even on one node with a compiled transformer stage, the parallel version was three times faster. On 1 node configuration that does not have a lot of parallel processing also we can still get big performance improvements from an Enterprise Edition job. The improvements will be multiplied 10 or more than that if we work on 2CPU machines and two nodes in most stages.
6. Parallel jobs take advantage of both pipeline parallelism and partitioning parallelism.
7. We can improve the performance of server job by enabling inter process row buffering. This helps stages to exchange data as soon as it is available in the link. IPC stage also helps passive stage to read data from another as soon as data is available. In other words, stages do not have to wait for the entire set of records to be read first and then transferred to the next stage. Link partitioner and link collector stages can be used to achieve a certain degree of partitioning parallelism.
8. Look up with sequential file is possible in parallel jobs and not possible in server jobs.

Datastage DsAdmin Questions related to Unix

1. List the files in current directory sorted by size ? - ls -l | grep ^- | sort -nr
2. List the hidden files in current directory ? - ls -a1 | grep "^\."
3. Delete blank lines in a file ? - cat sample.txt | grep -v ‘^$’ > new_sample.txt
4. Search for a sample string in particular files ? - grep .Debug. *.confHere grep uses the string .Debug. to search in all files with extension..conf. under current directory.
5. Display the last newly appending lines of a file during appendingdata to the same file by some processes ? - tail .f Debug.logHere tail shows the newly appended data into Debug.log by some processes/user.
6. Display the Disk Usage of file sizes under each directory in currentDirectory ? - du -k * | sort .nr (or) du .k . | sort -nr
7. Change to a directory, which is having very long name ? - cd CDMA_3X_GEN*Here original directory name is . .CDMA_3X_GENERATION_DATA..
8. Display the all files recursively with path under current directory ? - find . -depth -print
9. Set the Display automatically for the current new user ? - export DISPLAY=`eval ‘who am i | cut -d"(" -f2 | cut -d")" -f1′`Here in above command, see single quote, double quote, grave ascent is used. Observe carefully.
10. Display the processes, which are running under yourusername ? - ps .aef | grep MaheshvjHere, Maheshvj is the username.
11. List some Hot Keys for bash shell ? - Ctrl+l . Clears the Screen. Ctrl+r . Does a search in previously given commands in shell. Ctrl+u - Clears the typing before the hotkey. Ctrl+a . Places cursor at the beginning of the command at shell. Ctrl+e . Places cursor at the end of the command at shell. Ctrl+d . Kills the shell. Ctrl+z . Places the currently running process into background.
12. Display the files in the directory by file size ? - ls .ltr | sort .nr .k 5
13. How to save man pages to a file ? - man | col .b > Example : man top | col .b > top_help.txt
14. How to know the date & time for . when script is executed ? - Add the following script line in shell script.eval echo "Script is executed at `date`" >> timeinfo.infHere, .timeinfo.inf. contains date & time details ie., when script is executed and history related to execution.
15. How do you find out drive statistics ? - iostat -E
16. Display disk usage in Kilobytes ? - du -k
17. Display top ten largest files/directories ? - du -sk * | sort -nr | head
18. How much space is used for users in kilobytes ? - quot -af
19. How to create null file ? - cat /dev/null > filename1
20. Access common commands quicker ? - ps -ef | grep -i $@
21. Display the page size of memory ? - pagesize -a
22. Display Ethernet Address arp table ? - arp -a
23. Display the no.of active established connections to localhost ? - netstat -a | grep EST
24. Display the state of interfaces used for TCP/IP traffice ? - netstat -i
25. Display the parent/child tree of a process ? - ptree Example: ptree 1267
26. Show the working directory of a process ? - pwdx Example: pwdx 1267
27. Display the processes current open files ? - pfiles Example: pfiles 1267
28. Display the inter-process communication facility status ? - ipcs
29. Display the top most process utilizing most CPU ? - top .b 1
30. Alternative for top command ? - prstat -a

Datastage DsAdmin Questions

1. Have you created User groups and Users? $ group -c ourgroup $ group -m groupname userid $ group -o groupname userid $ group -O groupname userid # adduser --ingroup grname userid
2. How to kill the process? $ ps $kill -9/-1 PID
3. How to Unlock DataStage Jobs if the job is used by the another user or Hanged out?

   su - dsadm {enter your password for dsadm, else you can use root} cd `cat /.dshome` . ./dsenv bin/uvsh list.readu {find the row that shows your lock, look over to the USERNO column and get that number} UNLOCK USER nnnnn ALL QUIT

   • CD To $DSHOME/../Projects/ProjectName
   • Type the Following and press Enger. INSERT INTO VOC (F0, F1, F2, F3, F4, F5) VALUES ('UNLOCK','V', 'list_readu','E','BV','unlock')
     1. cd ${INSTALLATION_PATH}/DSEngine/bin
     2. ./dsenv
     3. ./uv -admin -stop
4. How to Add Unlock command to Vocabulary file(VOC)? CD To $DSHOME/../Projects/ProjectName INSERT INTO VOC (F0, F1, F2, F3, F4, F5) VALUES ('UNLOCK','V', 'list_readu','E','BV','unlock')
5. What is the command to Restart the DataStage Server? What is UV over there
6. Tell me some problems you may encounter when the DataStage running on UNIX platforms? 1. Running out of file units, 2. Running out of memory(Heap error), 3. open connection, 4. ODBC connection problems.
7. What is Heap Error? Anything to do with "heap" is about allocation of memory resources. Either install more memory in your server(s), or reduce overall demand for memory, perhaps by running less no. of jobs simultaneously, or by running on a configuration with less no. of processing nodes.
8. What happens when you shut down the Datastage server without closing all client connections? There may be an open connection from a Datastage client, Without shutting down the client processes, sockets are released on most platforms after the timeout period of about 6 - 10 minutes, depending on system tuning.
9. Which command is used to know whether the datastage server is running or not? ps -ef | grep dsrpc (this process should not be running when DataStage has stopped successfully) OR #netstat | grep dsrpcd
10. How do you restart the failure job in sequencer using Administrator or Director? Sequence(Add check points on failure)

Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr
    

Useful Guidelines in Designing Ascential Data Stage Server Jobs

1. Logically create the folders / subfolders on the file server so that the files can be placed and accessed from the relevant folders/subfolders.
   
2. Group the Jobs logically into various categories / Subcategories.
   
3. Comment the Jobs by using Annotation stage which tells the users, the functionality implemented in the job.
   
4. Give descriptions in the properties of the stages used so that others can identify the functionality implemented in it.
   
5. Name passive stages with the Table/File Names they access in it. 6. Name active stages to match their function.
   
6. Name links to express the direction and type of data flowing through them.
   
7. Use job parameters where ever it is required. This makes the process easy while moving into production.
   
8. While using ODBC stages remember to remove the derivations in the columns.
   
9. Use ODBC stage to access relational tables.
   
10. Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process.
    
11. Use the in-built functions present in Data Stage rather than creating a new routine for implementing the same logic as of in-built function.
    

Easy way to remove the column derivation

1. Open the transformer stage
2. Copy the columns from source(ODBC ) to target(Sequential File)
3. Delete the columns from source(ODBC)
4. Copy all the columns from target to the source.
5. Close the transformer.
6. Now we find that all the derivations are cleared from ODBC stage.

While using Reference Lookups

1. Compare the number of input rows with the number of rows in the reference table. If the reference table is smaller than the number of input rows, pre-load the reference table into a hash file and then reference the hash file.
   
2. Consider moving reference lookups to a join within the input stage. All columns used to join the tables should be indexed to maximize performance.
   
3. If the number of rows in a hashed file is small, consider Pre-loading the file into memory by checking the Pre-load file to memory checkbox in the Hash File stage.
   
4. Remove unused columns from transforms. This does not apply to columns in sequential files or output to hash files.
   
5. While mapping the input records with the Hash Look ups, remember that the fields getting mapped should be of same data type and of same length.
   
6. While loading the hash files, trim the data.

Duke Consulting Tips & Tricks for Datastage

Kim Duke has been providing wonderful tips and tricks for Ascential Datastage.You can find and gain lot of information on extracing etl job statistics ,routines,basic shell scripting and much more at Duke Consulting Tips & Tricks You can download all these documents and improve your job performance and capture job statistics. The starting point for performance tuning is to get and track row counts. Rows per second is the leading indicator of problems. If rows start slowing down over time then why. EtlStats.zip conatins jobs and documentation to get row counts. We run this job at the end of each job sequence. The results are stored in Oracle. The DDL to create the tables is included. Also included is DSaveAsBmp.bat which will create bmp images for each job. Makes for great documentation.

• Get row counts on all jobs in a sequence, all jobs or just one job.
• Installing EtlStats.
• Sample reports from EtlStats. Sample records for ETL_QA tables.
• Jobs and routines to extract information about jobs.
• Documentation for routines
• Routines for Generate create table scripts from DS_METADATA.,Display metadata mismatches,Put a standard long description on a job. ,Get last warning message from log file,Get shell based on OS (DOS or SH),Compare first row of sequential file with column names to lengths.
• Backup all DataStage projects on a server.
• Korn Shell, Perl, Universe and Vi help files.
• Xml Best Practices
• Tech Tips From Ascential now IBM

Datastage Basic Commands to release jobs,Shutdown and retart Datastage client cmmands and basic Unix commands

1. What is the difference between root and non root install? 2. Have you created User groups and Users? $ group -c ourgroup $ group -m groupname userid $ group -o groupname userid $ group -O groupname userid # adduser --ingroup grname userid 3. How to kill the process? $ ps $kill -9/-1 PID 4. How to Unlock DataStage Jobs if the job is used by the another user or Hanged out? su - dsadm {enter your password for dsadm, else you can use root} cd `cat /.dshome` . ./dsenv bin/uvsh list.readu {find the row that shows your lock, look over to the USERNO column and get that number} UNLOCK USER nnnnn ALL QUIT 1. CD To $DSHOME/../Projects/ProjectName 2. Type the Following and press Enger. INSERT INTO VOC (F0, F1, F2, F3, F4, F5) VALUES ('UNLOCK','V', 'list_readu','E','BV','unlock') a. cd ${INSTALLATION_PATH}/DSEngine/bin b. ./dsenv c. ./uv –admin –stop 5. How to Add Unlock command to Vocabulary file(VOC)? CD To $DSHOME/../Projects/ProjectName INSERT INTO VOC (F0, F1, F2, F3, F4, F5) VALUES ('UNLOCK','V', 'list_readu','E','BV','unlock') 6. What is the command to Restart the DataStage Server? What is UV over there 7. Tell me some problems you may encounter when the DataStage running on UNIX platforms? 1. Running out of file units, 2. Running out of memory(Heap error), 3. open connection, 4. ODBC connection problems. 8. What is Heap Error? Anything to do with "heap" is about allocation of memory resources. Either install more memory in your server(s), or reduce overall demand for memory, perhaps by running less no. of jobs simultaneously, or by running on a configuration with less no. of processing nodes. 9. What happens when you shut down the Datastage server without closing all client connections? There may be an open connection from a Datastage client, Without shutting down the client processes, sockets are released on most platforms after the timeout period of about 6 - 10 minutes, depending on system tuning. 10. Which command is used to know whether the datastage server is running or not? ps –ef | grep dsrpc (this process should not be running when DataStage has stopped successfully) OR #netstat | grep dsrpcd 11. How do you restart the failure job in sequencer using Administrator or Director? Sequence(Add check points on failure) 1. What does the pkgadd command do? 2. How do you create a solaris package? 3. How do you view shared memory statistics? 4. How do you get system diagnostics information? 5. What is OBP and how do you access it? 6. What is LOM and how do you access it? 7. What is VTS? 8. What is an alternative to the “top” command on Solaris? 9. What is /etc/system for? 10. What does ndd do? 11. What does init 5 do? 12. What does init 0 do? 13. How do you boot from CD-ROM? 14. What is jumpstart? 15. How do you boot from a Network with jumpstart? 16. What is JASS? 17. What is the difference between NFS version 2 and NFS version 3? 18. What is RPC? Why do I need it? 19. Are kernel parameters tunable during runtime? 20. What does fmthard do? 21. Job Scheduling; mainly crontab, at, batch command 22. Backup stetegy; incremental, full system back up; diff between tar & ufsdump 23. diff between hard link & softlink 24. How to list only the directories inside a directory (Ans. ls -l|grep "^d") 25. RAID levels; pros & cons of diffrent levels; what is RAID 1+0 26. How to recover a system whose root password has lost? 27. What is a daemon? 28. How to put a job in background & bring it to foreground? 29. What is default permissions for others in a file? 30. Questions on shell initialization scripts? 31. Questions on restricted shell 32. What is diff betwn grep & find? 33. What is egrep? 34. Questions on shell programming 35. What is a pipe? 36. Questions on Solaris patch management like pkgadd etc 37. Questions on file system creation; actually what happens when we create a file system? 38. Questions on RBAC? what is a role accound & what is a profile? 39.From command line how will you add a user account? the full command will all arguments. 40.Fs it advisable to put a swap partion in RAID1 (mirroring?) pros & cons? 41. List the files in current directory sorted by size ? - ls -l | grep ^- | sort -nr 42. List the hidden files in current directory ? - ls -a1 | grep "^\." 43. Delete blank lines in a file ? - cat sample.txt | grep -v ‘^$’ > new_sample.txt 44. Search for a sample string in particular files ? - grep .Debug. *.confHere grep uses the string .Debug. to search in all files with extension..conf. under current directory. 45. Display the last newly appending lines of a file during appendingdata to the same file by some processes ? - tail .f Debug.logHere tail shows the newly appended data into Debug.log by some processes/user. 46. Display the Disk Usage of file sizes under each directory in currentDirectory ? - du -k * | sort .nr (or) du .k . | sort -nr 47. Change to a directory, which is having very long name ? - cd CDMA_3X_GEN*Here original directory name is . .CDMA_3X_GENERATION_DATA.. 48. Display the all files recursively with path under current directory ? - find . -depth -print 49. Set the Display automatically for the current new user ? - export DISPLAY=`eval ‘who am i | cut -d"(" -f2 | cut -d")" -f1′`Here in above command, see single quote, double quote, grave ascent is used. Observe carefully. 50. Display the processes, which are running under yourusername ? - ps .aef | grep MaheshvjHere, Maheshvj is the username. 51. List some Hot Keys for bash shell ? - Ctrl+l . Clears the Screen. Ctrl+r . Does a search in previously given commands in shell. Ctrl+u - Clears the typing before the hotkey. Ctrl+a . Places cursor at the beginning of the command at shell. Ctrl+e . Places cursor at the end of the command at shell. Ctrl+d . Kills the shell. Ctrl+z . Places the currently running process into background. 52. Display the files in the directory by file size ? - ls .ltr | sort .nr .k 5 53. How to save man pages to a file ? - man | col .b > Example : man top | col .b > top_help.txt 54. How to know the date & time for . when script is executed ? - Add the following script line in shell script.eval echo "Script is executed at `date`" >> timeinfo.infHere, .timeinfo.inf. contains date & time details ie., when script is executed and history related to execution. 55. How do you find out drive statistics ? - iostat -E 56. Display disk usage in Kilobytes ? - du -k 57. Display top ten largest files/directories ? - du -sk * | sort -nr | head 58. How much space is used for users in kilobytes ? - quot -af 59. How to create null file ? - cat /dev/null > filename1 60. Access common commands quicker ? - ps -ef | grep -i $@ 61. Display the page size of memory ? - pagesize -a 62. Display Ethernet Address arp table ? - arp -a 63. Display the no.of active established connections to localhost ? - netstat -a | grep EST 64. Display the state of interfaces used for TCP/IP traffice ? - netstat -i 65. Display the parent/child tree of a process ? - ptree Example: ptree 1267 66. Show the working directory of a process ? - pwdx Example: pwdx 110 67. Display the processes current open files ? - pfiles Example: pfiles 1267 68. Display the inter-process communication facility status ? - ipcs 69. Display the top most process utilizing most CPU ? - top .b 1 70. Alternative for top command ? - prstat -a

Using Data Stage Job Parameters,Using Data Stage After/before job routines,Data Stage Job Control Functions

To Define job parameters ,Specify before and after routines ,Use job control routines ,Create a job that controls other jobs. Job Parameters can be used and included in:

Passive stage file and table names

Passive stage directory paths

Account names for hashed files

Transformer stage derivations

Transformer stage constraints

Example for Defining Job Parameters:

Click Edit--->Job Properties ---->Select parameters tab ---->Type parameters

Example for using Parameters in Passive Stages:

Parameter Need pound (#) signs Using Job Parameters in Transformer stages: Parameters are Inserted from Operand menu Example for Running Jobs with Parameters: Enter values for parameters Before and After Routines Can be called:

• Run before or after a job
• Run before or after a transformer stage
• Built-in Before/After routines can used to call ExecDos , ExecShell ,ExecTCL
• Can define custom routines

Examplle for Using Before and After Routines in a job :

In this example dos command is called an its value is mentioned in Input value using job parameters. Job Control Functions can be used:

• Use to control jobs and obtain project and job information
• Can be executed In Job control tab of Job Properties window
• Can be executed Within DS routines
• Can be executed Within derivations
• DSAttachJob
• DSSetParam
• DSRunJob
• DSWaitForJob
• DSGetProjectInfo
• DSGetJobInfo
• DSLogInfo

Exampe for Creating a Controlling Job or calling a jb within a job:

Data Stage Enterprise Edition Server Routines

Routine to Read no of records in a file: ---------------------------------------- Parameters to be passed are Arg1(path),Arg2(file name) Code: ---- vParamFile = Arg1 : "/" : Arg2 vCountVal = 0 OpenSeq vParamFile To FileVar Else Call DSLogWarn("Cannot open ":vParamFile , "Cannot Open ParamFile") End Loop ReadSeq Dummy From FileVar Else Exit ;* at end-of-file vCountVal = vCountVal + 1 Repeat CloseSeq FileVar Ans=vCountVal Return (vCountVal) To send mail: ----------------- Four parameters are to be passed for this routine: Message,Subject,Sendto(Mail id),From (lan id) command = "echo ":Message:" | mail -s ":Subject:" ":SendTo:",":From Call DSExecute("UNIX",command, output, returncode) Ans = returncode To rename the files with timestamp and move files from one directory to another: ----------------------------------------------------------------- $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'mv /path1/path2/filename.txt /newpath/newpath1/filename_`date +"%Y%m%d%H%M%S"`.txt ', Output, SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error", "JobControl") Abort End Else ErrorCode = 0 To connect to db2 database from routine: --------------------------------------- $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'. /export/home/db2inst8/sqllib/db2profile', Output, SystemReturnCode) Call DSExecute("UNIX",'db2 "connect to db2 DSNNAME user USERNAME using PASSWORD"', Output, SystemReturnCode) If SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error", "JobControl") Abort End Else ErrorCode = 0 To get record count from a table: --------------------------------------- PgmName = "CountfromTable" * Set default to empty string Ans = "" T_NAME = Oconv(TableName,"ABC") If Len(Trim(T_NAME)) = 0 Then Message = "No Table name supplied... Abort" Call DSLogFatal(Message,PgmName) ErrorCode = @TRUE Goto TheEnd End Continue: * Format SQL to select count(*) from table Ans = "SELECT COUNT(*) FROM schemaname.":Trim(T_NAME) Return = Ans To find a file in a path1 and moving the file to path2 : ------------------------------------------------------------ $INCLUDE DSINCLUDE JOBCONTROL.H Call DSExecute("UNIX",'find /path/path1/':Arg1, Output, SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogInfo("No Files found for Rename","JobControl") End Else Call DSExecute("UNIX",'mv /path/path1/':Arg1:' /path/path2/':Arg1, Output,SystemReturnCode) if SystemReturnCode <> 0 Then Call DSLogFatal("Unix Command Error","Output is " : Output, "JobControl") Abort End End ErrorCode = 0 Ans = 0

Ascential Data Stage FAQ's

Q. What is the architecture of Data Stage A. Client Server Architecture Q. What are the components of Client and explain them? A.Designer, Director, Manager, Administrator Q.What is the difference between ODBC Stage and OCI Stage A. In ODBC Stage user can connect to any data base and through OCI Stage user can connect to Oracle Database only. If user uses ODBC Stage than DSN name should be create in Data Source. Q.What is a Passive and Active Stage? Give Example A. If the data changes from in put to out put than it is an active stage else it is a passive stage. Transformer Stage is an Active stage while Hash Stage is a passive stage. Q.How to find the length of a String? A. By using Len function user can find out the length of the sting. Q.What is the format for if-Else Block A. If Column1 > 100 Then "A" Else "B" Q.How do u concatenates two values. A. The concatenation operator is “:” Q.Where you define the job parameter. A. The job parameter is defined in the job->Edit->job Properties. Than click the Job Parameter Menu. Q.How to use the parameter in a job? A. To use the defined job parameters, you must specify them when you edit a stage. When you edit any of the fields for which you wish to use a parameter, enter #Param#, where Param is the name of the job parameter. Q.How to convert the string into uppercase. A. To convert the string into uppercase we need to use UPCASE function E.g. String a=My Value; UPCASE (a) =MYVALUE; Q.But how to convert lower case? A. DOWNCASE (a) =my value; Q.How to get a substring from a string. A. String a=My Value; If user wants first three character than the function is Substrings (a, 1, 3); Q.How do u schedules a job? Q.How to create a sequence in Data stage job? A. By using the KeyMgtGetNextValue.This is present in the category SDK/KeyMgt Q.How many number of input link will be used in transformer stage? A. Transformer stages in server jobs can have one primary input link, but there can be any number of reference inputs. Q.What is the use of Transformer Stage? A. Transformer stages do not extract data or write data to a target database. They are used to handle extracted data, perform any conversions required, and pass data to another Transformer stage or a stage that writes data to a target data table. Q.Where user can write custom routine? A. The user can write custom routine in Manager. Q.Have ever writes any routine? If yes explain. Q.What is the difference between Stage Variable and System Variable? A.DataStage provides a set of variables containing useful system information that you can access from a transform or routine. System variables are read-only. All System Variable starts with @.Some of the System variables are @DATE the internal date when the program started. See the Date function. @DAY The day of the month extracted from the value in @DATE. @FALSE The compiler replaces the value with 0. @INROWNUM Input row counter. For use in constrains and derivations in Transformer stages. @OUTROWNUM Output row counter (per link). For use in derivations in Transformer stages. @LOGNAME The user login name. @MONTH The current extracted from the value in @DATE. @NULL the null value. @NULL.STR The internal representation of the null value, Char (128). @NUMPARTITIONS In a parallel Transformer stage output derivation gives the total number of partitions for the stage. @PARTITIONNUM In a parallel Transformer stage output derivation gives the partition number for the particular instance. @PATH The pathname of the current Data Stage project. @SCHEMA The schema name of the current Data Stage project. @SM A sub value mark (a delimiter used in UniVerse files), Char(252). @SYSTEM.RETURN.CODE Status codes returned by system processes or commands. @TIME The internal time when the program started. See the Time function. @TM A text mark (a delimiter used in UniVerse files), Char(251). @TRUE The compiler replaces the value with 1. @USERNO The user number. @VM A value mark (a delimiter used in UniVerse files), Char(253). @WHO The name of the current DataStage project directory. @YEAR The current year extracted from @DATE. But Stage variables created by the user. It executes first while running.It is defined inside the transformer stage. In this user will see the stage varaible window and he can defines there.

Data Stage FAQ's

1 What is the flow of loading data into fact & dimensional tables? Fact table - Table with Collection of Foreign Keys corresponding to the Primary Keys in Dimensional table. Consists of fields with numeric values. Dimension table - Table with Unique Primary Key... 2 What is the default cache size? How do you change the cache size if needed? Default cache size is 256 MB. We can incraese it by going into Datastage Administrator and selecting the Tunable Tab and specify the cache size over there. 3 What is Modulus and Splitting in Dynamic Hashed File? In a Hashed File, the size of the file keeps changing randomly. If the size of the file increases it is called as "Modulus". If the size of the file decreases it is called as "... 4 What does a Config File in parallel extender consist of? Config file consists of the following. a) Number of Processes or Nodes. b) Actual Disk Storage Location. 5 What are types of Hashed File? Hashed File is classified broadly into 2 types. a) Static - Sub divided into 17 types based on Primary Key Pattern. b) Dynamic - sub divided into 2 types i) Gen... 6 What are the difficulties faced in using DataStage ? or what are the constraints in using DataStage ? 7 What are Stage Variables, Derivations and Constants? Stage Variable - An intermediate processing variable that retains value during read and doesnt pass the value into target column. Derivation - Expression that specifies value to be passed o... 8 Types of vies in Datastage Director? There are 3 types of views in Datastage Director a) Job View - Dates of Jobs Compiled. b) Log View - Status of Job last run c) Status View - Warning Messages, Event Messages, Program G... 9 Types of Parallel Processing? Parallel Processing is broadly classified into 2 types. a) SMP - Symmetrical Multi Processing. b) MPP - Massive Parallel Processing. 10 Orchestrate Vs Datastage Parallel Extender? Orchestrate itself is an ETL tool with extensive parallel processing capabilities and running on UNIX platform. Datastage used Orchestrate with Datastage XE (Beta version of 6.0) to incorporate the p... 11 Importance of Surrogate Key in Data warehousing? Surrogate Key is a Primary Key for a Dimension table. Most importance of using it is it is independent of underlying database. i.e Surrogate Key is not affected by the changes going on with a databas... 12 How to run a Shell Script within the scope of a Data stage job? By using "ExcecSH" command at Before/After job properties. 13 How to handle Date convertions in Datastage? Convert a mm/dd/yyyy format to yyyy-dd-mm? We use a) "Iconv" function - Internal Convertion. b) "Oconv" function - External Convertion. Function to convert mm/dd/yyyy format to yyyy-dd-mm is Oconv(Iconv... 14 How do you execute datastage job from command line prompt? Using "dsjob" command as follows. dsjob -run -jobstatus projectname jobname 15 Functionality of Link Partitioner and Link Collector? Link Partitioner : It actually splits data into various partitions or data flows using various partition methods . Link Collector : It collects the data coming from partitions, merges ... 16 Dimensional modelling is again sub divided into 2 types. a)Star Schema - Simple & Much Faster. Denormalized form. b)Snowflake Schema - Complex with more Granularity. More normalized form. 17 Differentiate Primary Key and Partition Key? Primary Key is a combination of unique and not null. It can be a collection of key values called as composite primary key. Partition Key is a just a part of Primary Key. There are several methods of ... 18 Differentiate Database data and Data warehouse data? Data in a Database is a) Detailed or Transactional b) Both Readable and Writable. c) Current. 19 Containers : Usage and Types? Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers. a) Local Container: Job Specific b) Shared Container: Used in any job wit... 20 Compare and Contrast ODBC and Plug-In stages? ODBC : a) Poor Performance. b) Can be used for Variety of Databases. c) Can handle Stored Procedures. Plug-In: a) Good Performance. b) Database specific.(Only one database) <... 21 Dimension Modelling types along with their significance Data Modelling is Broadly classified into 2 types. a) E-R Diagrams (Entity - Relatioships). b) Dimensional Modelling.

Data Stage FAQ's -1

11 What are types of Hashed File? Hashed File is classified broadly into 2 types. a) Static - Sub divided into 17 types based on Primary Key Pattern. b) Dynamic - sub divided into 2 types i) Gen... 12 Containers : Usage and Types? Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers. a) Local Container: Job Specific b) Shared Container: Used in any job wit... 13 Compare and Contrast ODBC and Plug-In stages? ODBC : a) Poor Performance. b) Can be used for Variety of Databases. c) Can handle Stored Procedures. Plug-In: a) Good Performance. b) Database specific.(Only one database) <... 14 How to run a Shell Script within the scope of a Data stage job? By using "ExcecSH" command at Before/After job properties. 15 How to handle Date convertions in Datastage? Convert a mm/dd/yyyy format to yyyy-dd-mm? We use a) "Iconv" function - Internal Convertion. b) "Oconv" function - External Convertion. Function to convert mm/dd/yyyy format to yyyy-dd-mm is Oconv(Iconv... 16 Types of Parallel Processing? Parallel Processing is broadly classified into 2 types. a) SMP - Symmetrical Multi Processing. b) MPP - Massive Parallel Processing. 17 What does a Config File in parallel extender consist of? Config file consists of the following. a) Number of Processes or Nodes. b) Actual Disk Storage Location. 18 Functionality of Link Partitioner and Link Collector? Link Partitioner : It actually splits data into various partitions or data flows using various partition methods . Link Collector : It collects the data coming from partitions, merges ... 19 How did u connect to DB2 in your last project? Using DB2 ODBC drivers. 20 What are OConv () and Iconv () functions and where are they used? IConv() - Converts a string to an internal storage formatOConv() - Converts an expression to an output format. 21 What are Routines and where/how are they written and have you written any routines before? Routines are stored in the Routines branch of the DataStage Repository, where you can create, view or edit. The following are different types of routines: 1) Transform functions&nb... 22) Dimension Modelling types along with their significance Data Modelling is Broadly classified into 2 types. a) E-R Diagrams (Entity - Relatioships). b) Dimensional Modelling. 23) Dimensional modelling is again sub divided into 2 types. a)Star Schema - Simple & Much Faster. Denormalized form. b)Snowflake Schema - Complex with more Granularity. More normalized form. 24) Importance of Surrogate Key in Data warehousing? Surrogate Key is a Primary Key for a Dimension table. Most importance of using it is it is independent of underlying database. i.e Surrogate Key is not affected by the changes going on with a databas... 25) Differentiate Database data and Data warehouse data? Data in a Database is a) Detailed or Transactional b) Both Readable and Writable. c) Current. 26) What is the flow of loading data into fact & dimensional tables? Fact table - Table with Collection of Foreign Keys corresponding to the Primary Keys in Dimensional table. Consists of fields with numeric values. Dimension table - Table with Unique Primary Key... 27) Orchestrate Vs Datastage Parallel Extender? Orchestrate itself is an ETL tool with extensive parallel processing capabilities and running on UNIX platform. Datastage used Orchestrate with Datastage XE (Beta version of 6.0) to incorporate the p... 28) Differentiate Primary Key and Partition Key? Primary Key is a combination of unique and not null. It can be a collection of key values called as composite primary key. Partition Key is a just a part of Primary Key. There are several methods of ... 29) How do you execute datastage job from command line prompt? Using "dsjob" command as follows. dsjob -run -jobstatus projectname jobname 30) What are Stage Variables, Derivations and Constants? Stage Variable - An intermediate processing variable that retains value during read and doesnt pass the value into target column. Derivation - Expression that specifies value to be passed o... 31) What is the default cache size? How do you change the cache size if needed? Default cache size is 256 MB. We can incraese it by going into Datastage Administrator and selecting the Tunable Tab and specify the cache size over there.