Datastage Tips

• Problem 1: How to do Cartesian join è Join every row of a table to every row of other table
• Solution : Use “column generator” stage
• How to :

1) Add both tables in DS job

2) Add dummy column using “Column Generator” stage each for both tables. Make sure dummy column values are same.

3) Join both tables using these dummy columns

• Problem 2: How to add century break for date value
• Solution : Use “Format” string option in Date Type
• How to:

For date column, add format string based on century break value you required.

• Problem 3: How to add DataStage Echo statements for OSH statements
  • Solution: Set “OSH_ECHO” parameter value to “True”
  • How to:
    • From project parameter list, add “OSH_ECHO”
    • Set default value to “True”

• Problem 4:How to count number of jobs in DS project
  • Solution: Use dsjobs “-ljobs” command
  • How to: Go to DS Administrator “projects” tab
    •     Click on command button
    •     Enter following command to execute:
    •     SH -c “dsjob -ljobs | wc -l”

• Problem 5: How to execute Unix command from DS job
  • Solution : Use “External Source” stage in DS job
  • How to:
    • Add “External Source” stage in DS job
    • In source program property, enter Unix command

• Problem 6: How to suppress job warnings in DS job log
  • Solution: Add “rule” in Message Handler
  • How to:
    • From DS Director, from Message Handler, add a rule
    • Select Warning message as example of Message text

• Problem 7: How to use “Otherwise” constraint in Transformer Stage
  • Solution: To use “Otherwise” constraint in Transformer stage, Order of link is important.
  • How to:
    • Typically link with “Otherwise” constraint should be last in Transformer stage link order

• Problem 8: How to set Pad Character in DS Job
  • Solution: Use “APT_STRING_PADCHAR” project parameter
  • How to:
    • From project parameters, add “APT_STRING_PADCHAR” in DS job parameters
    • Set value for this parameter with required character
  • Note:
    • DS Default value is 0x0 ASCII NULL character
    • Override is mainly useful for column with data type CHAR

• Problem 9: How to get a last record
  • Solution: Use “Tail” stage in DS job
  • How to:
    • Add “Tail” stage in DS job
    • Specify “Number of Rows” = 1

• Problem 10:How to get category name from DS job name
  • Solution :Use following statement: Select * from DS_JOBS where NAME = ‘’;
  • How to:
    • Go to DS Administrator “projects” tab
    • Click on command button
    • Enter following command to execute:
    Select * from DS_JOBS where NAME = ‘’;

• Problem 11: How to get DS job detailed information in log
  • Solution: Set “APT_DUMP_SCORE” parameter value to “1”
  • How to:
    • From project parameters, add “APT_DUMP_SCORE” as job parameter
    • Set value to “1”

• Problem 12: How to Reject Sequential file records with strings longer than their declared maximum column length
  • Solution: Set “APT_IMPORT_REJECT_STRING_FIELD_OVERRUNS” to “1”

• Problem 13:What to use instead of “Transformer” Stage
  • Solution:
    • Use “Copy” stage instead of “Transformer” Stage for following:
      • Renaming columns
      • Dropping columns
      • Default type conversions
      • Job design placeholder between stages
    • Use “Modify” stage
      • Non default type conversions
      • Null handling
      • Character string trimming

• Problem 14 : How to copy group permissions from one project to other
  • Solution : From Unix, copy following files from source project to target project dir: .developer.adm, .operator.adm, .prodmgr.adm
  • How to:
    • Login to Unix server using “dsadm” account
    • Go to target project dir “/local/data1/Ascential/DataStage/
    • Copy .adm files from source project to this project