Test 415: IBM WebSphere IIS DataStage Enterprise Edition v7.5

Section 1 - Installation and Configuration (5%)

1. Describe how to properly install and configure DataStage EE
1. Describe users and groups
2. Describe the environment (e.g., dsenv, ODBC)
1. Setup database connectivity
3. Describe OS configuration/kernel
4. Describe USS configuration
5. Identify required components for server
1. C++ compiler


2. Identify the tasks required to create and configure a project to be used for EE jobs.
1. Project location
2. Assign DataStage EE roles
3. Environment defaults


3. Given a configuration file, identify its components and its overall intended purpose.
1. Project location
2. Assign DataStage EE roles
3. Environment defaults


4. List the steps necessary to start/stop DataStage EE properly.
1. netstat -a|grep ds

Section 2 - Metadata (5%)

1. Demonstrate knowledge of Orchestrate schema.
1. Distinguish internal data type (Orchestrate schema) vs external data type
2. Describe how to set extended properties for table definition
3. Import metadata using plug-ins vs orchdbutil
4. Explain nullable mapping rules (e.g., source vs target)
5. NLS data types


2. Identify the method of importing metadata.
1. Flat sources (e.g., sequential file, Orchestrate schema, ODBC, etc.)
2. COBOL CopyBook
3. XML


3. Given a scenario, demonstrate knowledge of runtime column propagation.
1. Usage
2. Impact on stage mapping and target databases

Section 3 - Persistent Storage (10%)

1. Given a scenario, explain the process of importing/exporting data to/from framework (e.g., sequential file, external source/target).
1. Explain use of various file stages (e.g., file, CFF, fileset, dataset) and where appropriate to use
2. If USS, define the native file format (e.g., EBCDIC, VSDM)


2. Given a scenario, describe proper use of a sequential file.
1. Read in parallel (e.g., reader per node, multiple files)
2. Handle various formats (e.g., fix vs variable, delimeted vs nondelimited, etc.)
3. Describe how to import and export nullable data
4. Explain how to identify and capture rejected records (e.g., log counts, using reject link, options for rejection)


3. Given a scenario, describe proper usage of CFF (native not plug-in).
1. Explain how to import data from a file that has multiple varying record types (e.g., COBOL CopyBook, EBCDIC to ASCII)


4. Describe proper usage of FileSets and DataSets.
1. Explain differences and similarities of FileSet and DataSet (e.g., header data file segments, internal DS vs external format (FS))
2. Determine which tools can be used to manage FileSets and DataSets (GUI and CLI)


5. Describe use of FTP stage for remote data (e.g., how to parallel, plug-in vs enterprise).
1. Restructure stages (e.g., column import/export)


6. Identify importing/exporting of XML data.
1. XML stage options and usage for XPATH and XLS

Section 4 - Parallel Architecture (10%)

1. Given a scenario, demonstrate proper use of data partitioning and collecting.
1. Partitioning goals (e.g., data distribution, meeting data grouping requirements)
2. Explain the differences and usage of round robin, entire, same, and hash partitioning
3. Explain the differences between auto, ordered, and sort collector
4. Identify partitioning type, parallel/sequential by analyzing a DataStage EE screen shot
5. Explain the differences between partitioning keys and sorting (stage) keys through a scenario where these do not match
6. Demonstrate proper use of partitioning methods based on given business requirements and DataStage EE technical requirements
7. Explain the differences between a funnel state and a collector
8. Describe input of partitioning and re-partitioning in an MPP/cluster environment


2. Given a scenario, demonstrate knowledge of parallel execution.
1. Given a job design and configuration file, provide estimates of the number of processes generated at runtime
2. Explain the purpose and use of resource and node pools
3. Given a source DataSet, describe the degree of parallelism using auto and same partitioning

Section 5 - Databases (15%)

1. Given a scenario, demonstrate proper selection of database stages and database specific stage properties.
1. DB2
1. Based on DB2 connectivity requirements, select appropriate stage (e.g., DB2 API, DB2 Enterprise)
2. List environment variables needed to successfully run a DB2 job
2. Teradata
1. Based on functional requirements, select appropriate Teradata stage (e.g., TD Enterprise, TD MultiLoad (target only), TD API)
2. Requested sessions and total sessions
3. DRS
4. ODBC
5. Informix
6. Sybase
7. Oracle
1. Remote Server
2. Partition table
3. Given a scenario using Oracle, load target and proper use of index mode and $API_ORA_LOAD_OPTIONS


2. Identify source database options.
1. Identify which stages/options read in parallel
1. Explain the degree of parallelism for Oracle Enterprise and DB2 Enterprise
2. Identify the use of "sparse" vs "normal" lookup
3. When to use (e.g., 1:100)
4. How to construct sparse lookup and SQL statements


3. Given a scenario, demonstrate knowledge of target database options.
1. Articulate benefits and limitations of using parallel load method (e.g., constraints, speed, exclusive locks, etc.)
2. Explain the differences between upsert and load methods on target database stages
3. Describe how to control restart of DB target (separate the "L" from ETL)
4. Use OPEN, CLOSE, create temp, Load, SELECT FROM... INSERT INFO, DROP
5. Identify the impact of RCP when target has fewer columns than job flow
6. Separate update and insert records before target upsert


4. Given a scenario, describe how to design EE ETL jobs that will extract data from DB2 (or any DBMS), combine with data from another source and load to another DBMS target.
5. Demonstrate knowledge of working with NLS database sources and targets.

Section 6 - Data Transformation (15%)

1. Given a scenario, demonstrate knowledge of default type conversions, output mappings, and associated warnings.
1. Demonstrate appropriate use of modify for NULL handling, string trim, non-default conversions


2. Given a scenario, demonstrate proper selections of Transformer stage vs other stages.
1. Copy stage/output mapping
2. No BASIC Transformer
3. Filter vs Transformer constraint (e.g., filter vs interpreted)


3. Given a scenario, describe Transformer stage capabilities (including stage variables, link variables, DataStage macros, constraints, system variables, link ordering, @PART NUM, functions).
1. Explain NULL handling within parallel transformer (e.g., reject rule, NULL functions)
2. Demonstrate the use of Transformer stage variables (e.g., to identify key grouping boundaries on incoming data
3. Use a Transformer only to generate a sequence of numbers in parallel


4. Identify processes to add functionality not provided by existing DataStage stages (e.g., wrapper, buildops, user def functions/routines).
5. Given a scenario, demonstrate no closed loop scenario when updating a reference table.

Section 7 - Combining and Sorting Data (10%)

1. Demonstrate knowledge of Join, Lookup, and Merge stages.
1. Explain the differences between Lookup, Join, and Merge stages and demonstrate when to use each
2. Explain link (e.g., input/output/reject) requirements
3. Explain sorting requirements
4. Explain full outer join usage
5. Demonstrate understanding of link ordering with JOIN and MERGE stages
6. Lookup - continue (also outer JOIN) with non-nullable input columns
7. Explain what happens with duplicate input column names on JOIN and MERGE


2. Given a scenario, demonstrate knowledge of SORT stage.
1. Describe the differences between parallel SORT, SORT, and sequential SORT
2. Describe the differences between unique option in osrt, RemDup stage
3. Demonstrate understanding of SORT key column properties (e.g., don't sort, previously sorted)
4. Demonstrate understanding of framework inserted sorts
1. Examine score
2. Set $APT_SORT_INSERTION_CHECK_ONLY
5. Distinguish between SQL ORDER BY vs parallel sort
6. Explain NLS collation sequences


3. Given a scenario, demonstrate understanding of Aggregator stage.
1. Memory impact of sort vs hash
2. Demonstrate use of Aggregator properties
1. Count vs calculation
2. Explicitly create output columns and map
3. Take output of Aggregator and join with detail records


4. Describe the proper usage of change capture/change apply.
1. Separate delta rows (e.g., inserts vs updates)

Section 8 - Automation and Production Deployment (10%)

1. Given a scenario, articulate the change control process.
1. "Dev to QA to prod"
1. Explain the differences between compile and force compile
2. Use of protected projects
3. Import/Export
1. Including compiled Transformer
4. Managing custom components (e.g., buildops, wrappers)
5. USS deployment topics (e.g., remote deployment, how to deploy)


2. Identify the use of the dsjob command line utility.
1. Explain how a DataStage job may be run using a third party scheduler (e.g., autosys, control U, CRON, etc.)
2. Explain how performance run statistics from a job run can be captured
3. Capture log records to a text file (XML format)


3. Explain how to use parameters to simplify the creation and maintenance of job designs.
4. Explain the processes necessary to run multiple copies of the source (job multi-instance)
5. Given a scenario, demonstrate the ability to use job sequencers (e.g., exception hunting, re-startable, dependencies, passing return value from routing, parameter passing and job status)
1. Create different designs using Job Sequencer

Section 9 - Monitoring and Troubleshooting (10%)

1. Given a scenario, demonstrate knowledge of parallel job score.
1. Show how it is captured and interpret its results
2. Identify and interpret its contents
1. Number of processes
2. Number of nodes
3. Number of DataSets
4. Combined operators
5. Parallel/Sequential execution
6. Inserted buffer and sort operators


2. Given a scenario, identify and define environment variables that control EE with regard to added functionality and reporting.
1. Articulate ability to identify runtime metadata vs design metadata
2. APT_PM_SHOW_PIDS, etc.


3. Given a process list, identify conductor, section leader, and player processes.
4. Given a scenario, identify areas that may improve performance (e.g., buffer size, repartitioning, config files, operator combination, etc.).

Section 10 - Job Design (10%)

1. Demonstrate knowledge of shared containers.
1. Reuse using RCP
2. Using usage analysis and multi-job compile to recompile jobs that use a modified shared container


2. Given a scenario, describe how to minimize SORTS and repartitions.
3. Demonstrate knowledge of creating restart points across jobs using intermediate DataSets.
4. Given a scenario, demonstrate proper use of standards.
1. Naming conventions for stages and links
2. Differences between annotation and description annotation