1)How can handle the after job subroutine in the transformer stage?
ANS:In Transformer Stage click on properities tab in tool bar(Left corner tab). It displays stage properities page like all the other job properities page. There you can specify your before after stage subroutins.
2)What is DataStage parallel Extender ?
ANS1:its a new version of datastage called PX it process data using partion/collection methods (algorithms) it is very fast compare to older (server) version..partioning can be defined via Config file which stores no of nodes and resource pool information. there are various new stages added to PX version for example dataset, fileset, row generator, look up stage...and many morecheers,
ANS2:Parallel extender is that the parallel processing of data extraction and transformation application . there are two types of parallel processing 1) pipeline parallelism 2) partition parallelism
Thursday, August 21, 2008
Thursday, July 31, 2008
Rendering Oracle HTML DB Reports as PDFs Using Formatting Objects Processor
Process Overview :
The process of rendering Oracle HTML DB report output to PDF requires a few different pieces to be in place:
Oracle HTML DB 1.6 - This version of HTML DB is required to generate report output in an XML format that can be understood by FOP.
Java Platform - an environment in which a JavaServer Page (JSP) can perform an XML/XSL transformations.
JavaScript - on the page producing the Oracle HTML DB report, a certain amount of JavaScript is required to pass the report output over to the JSP.
Extensible Stylesheet Language Transformation (XSLT) - an XSLT document is required by the JSP to transform the report in XML format to PDF.
Process Example
To illustrate this concept, we will add an "Output to PDF" link to the template of an HTML DB region that contains a report. When users click on this link, a new browser window will pop up containing the PDF version of the same report output. Although we will be using a simple query on the HR.EMPLOYEES table, the formatting code provided with this Technical Note is not query-specific and should work with any queries that you create. The first few steps of this example are performed outside the Oracle HTML DB environment to put the various supporting pieces in place.
Step 1: Add FOP Libraries to the Java Environment
In order to render the received XML report output into a specific format (PDF in this case), your Java environment needs access to the FOP libraries. In this example we will use Oracle Application Server Containers for J2EE (OC4J) 10g for that environment, so the libraries will be added to $ORACLE_HOME/oc4j/j2ee/home/config/application.xml.
Download and unzip the FOP libraries from http://xml.apache.org/fop/
Place the unzipped fop-0.20.5 directory in $ORACLE_HOME/oc4j/j2ee
Include the following lines in $ORACLE_HOME/oc4j/j2ee/home/config/application.xml:
Step 2: Add the Rendering Components Specific to Oracle HTML DB to the Java Environment
Two files specific to this Oracle HTML DB PDF rendering process are required: htmldb_fop_render.jsp and htmldb_example.xslt. Simply put, htmldb_fop_render.jsp receives the XML report output that is POSTed by the JavaScript soon to be added below and performs an XML/XSL transformation using htmldb_example.xslt and the FOP libraries. Although these two files can be placed elsewhere, putting them both in $ORACLE_HOME/oc4j/j2ee/home/default-web-app is easiest for now.
Step 3a: Create an Oracle HTML DB Report
Create a report on scalar data types that will be used for this example. As mentioned previously, the formatting code added above isn't report specific, so any report of scalar datatypes will do.
Log into your Oracle HTML DB workspace.
Go to the Application Builder home by clicking the name of an application to which you wish to add this example report.
Click Create Page.
Select Page with Component and click Next.
Select Report and click Next.
Select SQL Report and click Next.
Finish the report wizard as desired while providing a query to be used. The simple query below would suffice for these purposes:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees
Please note that if your HTML DB application schema does not have access to the HR schema, simply use another query for this example.
Step 3b: Add a Page-Level Item and Branch to Oracle HTML DB Report
To illustrate the ability to reference page-level items from the XSLT used in this transformation, add an item and a branch to return users to the current page.
From the Page Definition view of the newly created page, click the Create icon in the Items section.
Select Text and click Next.
Select Text Field (always submits page when Enter pressed) and click Next.
Name the item "MY_REPORT_PARAMETER" and click Next.
Provide a Label value of "My Report Parameter" and click Next.
Accept the remaining defaults in the Create Page Item wizard and return to the Page Definition view.
Click the Create icon in the Branches section.
Accept all the defaults of the Create Branch wizard while providing &APP_PAGE_ID. for the target page.
Click Create Branch.
Lastly, include MY_REPORT_PARAMETER in your report region by changing its query to:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees
where salary > :MY_REPORT_PARAMETER or :MY_REPORT_PARAMETER IS null
Step 4: Add PDF Link to Report Screen
From the Page Definition view of your report page, click the name of the region template displayed in the Templates section of the Shared Components column.
Add the link definition below somewhere to the Template field of this screen. Placing the link immediately before the #CLOSE# substitution string tends to render nicely.
Output to PDF Document
Click Apply Changes.
Step 5: Modify and Upload the JavaScript File
Download this JavaScript file and change the path shown in this line:
var g_Render_URL = 'http://:/htmldb_fop_render.jsp';
to match the location of the JSP you added in Step 2, above. For example:
var g_Render_URL = 'http://myhostname.com:8888/htmldb_fop_render.jsp';
Go back into Oracle HTML DB and click the Application Builder tab at the top of the screen.
Click the Shared Components icon.
Click the Static Files link in the Files section.
Click Create.
Click Browse to navigate to your modified htmldb_pdf.js file.
Click Upload.
Step 6: Reference the JavaScript Functions From Your Report Page
Return to the Page Definition view of the page containing your report region.
Call the uploaded JavaScript from the header of this page by adding the following line to the "HTML Header" field:
Click Apply Changes.
Step 7: Run the Report Page and Click the "Output to PDF Document" Link
Summary
This Technical Note has offered a simple example of using Oracle HTML DB 1.6 and a Java environment to render HTML DB report output as a PDF document. The process illustrated can be summarized as follows:
HTML DB generates HTML report output in an HTML DB application page.
End users click a link on the page.
The link calls JavaScript that tells the HTML DB engine to send the report output to a JSP in a specific XML format.
The JSP performs an XML/XSL transformation using the provided XSLT and the FOP libraries.
FOP renders the document as PDF.
Using this concept, Oracle HTML DB developers can now use FOP to control all aspects of generated report output down to the layout within specific formats such as PDF, PCL, PS, SVG, Print, AWT, MIF and TXT.
Additional Resources & Information
The example shown above is a simple one in which HTML DB report output is sent to a specific PDF format. The layout of the rendered PDF document is controlled almost entirely by the htmldb_example.xslt stylesheet. Developers wishing to customize this resulting output can do so by making changes to that htmldb_example.xslt file. To learn more about transformation using such XSLT stylesheets, see the following resources:
The process of rendering Oracle HTML DB report output to PDF requires a few different pieces to be in place:
Oracle HTML DB 1.6 - This version of HTML DB is required to generate report output in an XML format that can be understood by FOP.
Java Platform - an environment in which a JavaServer Page (JSP) can perform an XML/XSL transformations.
JavaScript - on the page producing the Oracle HTML DB report, a certain amount of JavaScript is required to pass the report output over to the JSP.
Extensible Stylesheet Language Transformation (XSLT) - an XSLT document is required by the JSP to transform the report in XML format to PDF.
Process Example
To illustrate this concept, we will add an "Output to PDF" link to the template of an HTML DB region that contains a report. When users click on this link, a new browser window will pop up containing the PDF version of the same report output. Although we will be using a simple query on the HR.EMPLOYEES table, the formatting code provided with this Technical Note is not query-specific and should work with any queries that you create. The first few steps of this example are performed outside the Oracle HTML DB environment to put the various supporting pieces in place.
Step 1: Add FOP Libraries to the Java Environment
In order to render the received XML report output into a specific format (PDF in this case), your Java environment needs access to the FOP libraries. In this example we will use Oracle Application Server Containers for J2EE (OC4J) 10g for that environment, so the libraries will be added to $ORACLE_HOME/oc4j/j2ee/home/config/application.xml.
Download and unzip the FOP libraries from http://xml.apache.org/fop/
Place the unzipped fop-0.20.5 directory in $ORACLE_HOME/oc4j/j2ee
Include the following lines in $ORACLE_HOME/oc4j/j2ee/home/config/application.xml:
Step 2: Add the Rendering Components Specific to Oracle HTML DB to the Java Environment
Two files specific to this Oracle HTML DB PDF rendering process are required: htmldb_fop_render.jsp and htmldb_example.xslt. Simply put, htmldb_fop_render.jsp receives the XML report output that is POSTed by the JavaScript soon to be added below and performs an XML/XSL transformation using htmldb_example.xslt and the FOP libraries. Although these two files can be placed elsewhere, putting them both in $ORACLE_HOME/oc4j/j2ee/home/default-web-app is easiest for now.
Step 3a: Create an Oracle HTML DB Report
Create a report on scalar data types that will be used for this example. As mentioned previously, the formatting code added above isn't report specific, so any report of scalar datatypes will do.
Log into your Oracle HTML DB workspace.
Go to the Application Builder home by clicking the name of an application to which you wish to add this example report.
Click Create Page.
Select Page with Component and click Next.
Select Report and click Next.
Select SQL Report and click Next.
Finish the report wizard as desired while providing a query to be used. The simple query below would suffice for these purposes:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees
Please note that if your HTML DB application schema does not have access to the HR schema, simply use another query for this example.
Step 3b: Add a Page-Level Item and Branch to Oracle HTML DB Report
To illustrate the ability to reference page-level items from the XSLT used in this transformation, add an item and a branch to return users to the current page.
From the Page Definition view of the newly created page, click the Create icon in the Items section.
Select Text and click Next.
Select Text Field (always submits page when Enter pressed) and click Next.
Name the item "MY_REPORT_PARAMETER" and click Next.
Provide a Label value of "My Report Parameter" and click Next.
Accept the remaining defaults in the Create Page Item wizard and return to the Page Definition view.
Click the Create icon in the Branches section.
Accept all the defaults of the Create Branch wizard while providing &APP_PAGE_ID. for the target page.
Click Create Branch.
Lastly, include MY_REPORT_PARAMETER in your report region by changing its query to:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees
where salary > :MY_REPORT_PARAMETER or :MY_REPORT_PARAMETER IS null
Step 4: Add PDF Link to Report Screen
From the Page Definition view of your report page, click the name of the region template displayed in the Templates section of the Shared Components column.
Add the link definition below somewhere to the Template field of this screen. Placing the link immediately before the #CLOSE# substitution string tends to render nicely.
Output to PDF Document
Click Apply Changes.
Step 5: Modify and Upload the JavaScript File
Download this JavaScript file and change the path shown in this line:
var g_Render_URL = 'http://
to match the location of the JSP you added in Step 2, above. For example:
var g_Render_URL = 'http://myhostname.com:8888/htmldb_fop_render.jsp';
Go back into Oracle HTML DB and click the Application Builder tab at the top of the screen.
Click the Shared Components icon.
Click the Static Files link in the Files section.
Click Create.
Click Browse to navigate to your modified htmldb_pdf.js file.
Click Upload.
Step 6: Reference the JavaScript Functions From Your Report Page
Return to the Page Definition view of the page containing your report region.
Call the uploaded JavaScript from the header of this page by adding the following line to the "HTML Header" field:
Click Apply Changes.
Step 7: Run the Report Page and Click the "Output to PDF Document" Link
Summary
This Technical Note has offered a simple example of using Oracle HTML DB 1.6 and a Java environment to render HTML DB report output as a PDF document. The process illustrated can be summarized as follows:
HTML DB generates HTML report output in an HTML DB application page.
End users click a link on the page.
The link calls JavaScript that tells the HTML DB engine to send the report output to a JSP in a specific XML format.
The JSP performs an XML/XSL transformation using the provided XSLT and the FOP libraries.
FOP renders the document as PDF.
Using this concept, Oracle HTML DB developers can now use FOP to control all aspects of generated report output down to the layout within specific formats such as PDF, PCL, PS, SVG, Print, AWT, MIF and TXT.
Additional Resources & Information
The example shown above is a simple one in which HTML DB report output is sent to a specific PDF format. The layout of the rendered PDF document is controlled almost entirely by the htmldb_example.xslt stylesheet. Developers wishing to customize this resulting output can do so by making changes to that htmldb_example.xslt file. To learn more about transformation using such XSLT stylesheets, see the following resources:
Sunday, June 29, 2008
Datastage certification Dump
QUESTION
1
Which three stages support the dynamic (runtime) definition of the physical column
metadata? (Choose three.)
A. the Sequential stage
B. the Column Export stage
C. the CFF stage
D. the DRS stage
E. the Column Import stage
Answer: A,B,E
QUESTION
2
A Varchar(10) field named SourceColumn is mapped to a Char(25) field named
TargetColumn in a Transformer stage. The APT_STRING_PADCHAR environment
variable is set in Administrator to its default value. Which technique describes how to
write the derivation so that values in SourceColumn are padded with spaces in
TargetColumn?
A. Include APT_STRING_PADCHAR in your job as a job parameter. Specify the C/C++
end of string character (0x0) as its value.
B. Map SourceColumn to TargetColumn. The Transformer stage will automatically pad
with spaces.
C. Include APT_STRING_PADCHAR in your job as a job parameter. Specify a space as
its value.
D. Concatenate a string of 25 spaces to SourceColumn in the derivation for
TargetColumn.
Answer: C
QUESTION
3
Which three privileges must the user possess when running a parallel job? (Choose
three.)
A. read access to APT_ORCHHOME
B. execute permissions on local copies of programs and scripts
C. read/write permissions to the UNIX/etc directory
D. read/write permissions to APT_ORCHHOME
E. read/write access to disk and scratch disk resources
Answer: A,B,E
QUESTION
4
In a Teradata environment, which stage invokes Teradata supplied utilities?
A. Teradata API
B. DRS Teradata
C. Teradata Enterprise
D. Teradata Multiload
Answer: D
QUESTION
5
When importing a COBOL file definition, which two are required? (Choose two.)
A. The file you are importing is accessible from your client workstation.
B. The file you are importing contains level 01 items.
C. The column definitions are in a COBOL copybook file and not, for example, in a
COBOL source file.
D. The file does not contain any OCCURS DEPENDING ON clauses.
Answer: A,B
QUESTION
6
Which two tasks will create DataStage projects? (Choose two.)
A. Export and import a DataStage project from DataStage Manager.
B. Add new projects from DataStage Administrator.
C. Install the DataStage engine.
D. Copy a project in DataStage Administrator.
Answer: B,C
QUESTION
7
Which three defaults are set in DataStage Administrator? (Choose three.)
A. default prompting options, such as Autosave job before compile
B. default SMTP mail server name
C. project level default for Runtime Column Propagation
D. project level defaults for environment variables
E. project level default for Auto-purge of job log entries
Answer: C,D,E
QUESTION
8
Which three are keyless partitioning methods? (Choose three.)
A. Entire
B. Modulus
C. Round Robin
D. Random
E. Hash
Answer: A,C,D
QUESTION
9
Which two must be specified to manage Runtime Column Propagation? (Choose two.)
A. enabled in DataStage Administrator
B. attached to a table definition in DataStage Manager
C. enabled at the stage level
D. enabled with environmental parameters set at runtime
Answer: A,C
QUESTION
10
Which three are valid ways within a Job Sequence to pass parameters to Activity stages?
(Choose three.)
A. ExecCommand Activity stage
B. UserVariables Activity stage
C. Sequencer Activity stage
D. Routine Activity stage
E. Nested Condition Activity stage
Answer: A,B,D
QUESTION
11
A client requires that a database table be done using two jobs. The first job writes to a
dataset. The second job reads the dataset and loads the table. The two jobs are connected
in a Job Sequence. What are three benefits of this approach? (Choose three.)
A. The time it takes to load the table is reduced.
B. The database table can be reloaded after a failure without re-reading the source data.
C. The dataset can be used by other jobs even if the database load fails.
D. The dataset can be read if the database is not available.
E. The data in the dataset can be archived and shared with other external applications.
Answer: B,C,D
QUESTION
12
You are reading customer data using a Sequential File stage and transforming it using the
Transformer stage. The Transformer is used to cleanse the data by trimming spaces from
character fields in the input. The cleansed data is to be written to a target DB2 table.
Which partitioning method would yield optimal performance without violating the
business requirements?
A. Hash on the customer ID field
B. Round Robin
C. Random
D. Entire
Answer: B
QUESTION
13
Which three are valid trigger expressions in a stage in a Job Sequence? (Choose three.)
A. Equality(Conditional)
B. Unconditional
C. ReturnValue(Conditional)
D. Difference(Conditional)
E. Custom(Conditional)
Answer: B,C,E
QUESTION
14
An Aggregator stage using a Hash technique processes a very large number of rows
during month end processing. The job occasionally aborts during these large runs with an
obscure memory error. When the job is rerun, processing the data in smaller amounts
corrects the problem. Which change would correct the problem?
A. Set the Combinability option on the Stage Advanced tab to Combinable allowing the
Aggregator to use the memory associated with other operators.
B. Change the partitioning keys to produce more data partitions.
C. Add a Sort stage prior to the Aggregator and change to a sort technique on the Stage
Properties tab of the Aggregator stage.
D. Set the environment variable APT_AGG_MAXMEMORY to a larger value.
Answer: C
QUESTION
15
Which three actions are performed using stage variables in a parallel Transformer stage?
(Choose three.)
A. A function can be executed once per record.
B. A function can be executed once per run.
C. Identify the first row of an input group.
D. Identify the last row of an input group.
E. Lookup up a value from a reference dataset.
Answer: A,B,C
1
Which three stages support the dynamic (runtime) definition of the physical column
metadata? (Choose three.)
A. the Sequential stage
B. the Column Export stage
C. the CFF stage
D. the DRS stage
E. the Column Import stage
Answer: A,B,E
QUESTION
2
A Varchar(10) field named SourceColumn is mapped to a Char(25) field named
TargetColumn in a Transformer stage. The APT_STRING_PADCHAR environment
variable is set in Administrator to its default value. Which technique describes how to
write the derivation so that values in SourceColumn are padded with spaces in
TargetColumn?
A. Include APT_STRING_PADCHAR in your job as a job parameter. Specify the C/C++
end of string character (0x0) as its value.
B. Map SourceColumn to TargetColumn. The Transformer stage will automatically pad
with spaces.
C. Include APT_STRING_PADCHAR in your job as a job parameter. Specify a space as
its value.
D. Concatenate a string of 25 spaces to SourceColumn in the derivation for
TargetColumn.
Answer: C
QUESTION
3
Which three privileges must the user possess when running a parallel job? (Choose
three.)
A. read access to APT_ORCHHOME
B. execute permissions on local copies of programs and scripts
C. read/write permissions to the UNIX/etc directory
D. read/write permissions to APT_ORCHHOME
E. read/write access to disk and scratch disk resources
Answer: A,B,E
QUESTION
4
In a Teradata environment, which stage invokes Teradata supplied utilities?
A. Teradata API
B. DRS Teradata
C. Teradata Enterprise
D. Teradata Multiload
Answer: D
QUESTION
5
When importing a COBOL file definition, which two are required? (Choose two.)
A. The file you are importing is accessible from your client workstation.
B. The file you are importing contains level 01 items.
C. The column definitions are in a COBOL copybook file and not, for example, in a
COBOL source file.
D. The file does not contain any OCCURS DEPENDING ON clauses.
Answer: A,B
QUESTION
6
Which two tasks will create DataStage projects? (Choose two.)
A. Export and import a DataStage project from DataStage Manager.
B. Add new projects from DataStage Administrator.
C. Install the DataStage engine.
D. Copy a project in DataStage Administrator.
Answer: B,C
QUESTION
7
Which three defaults are set in DataStage Administrator? (Choose three.)
A. default prompting options, such as Autosave job before compile
B. default SMTP mail server name
C. project level default for Runtime Column Propagation
D. project level defaults for environment variables
E. project level default for Auto-purge of job log entries
Answer: C,D,E
QUESTION
8
Which three are keyless partitioning methods? (Choose three.)
A. Entire
B. Modulus
C. Round Robin
D. Random
E. Hash
Answer: A,C,D
QUESTION
9
Which two must be specified to manage Runtime Column Propagation? (Choose two.)
A. enabled in DataStage Administrator
B. attached to a table definition in DataStage Manager
C. enabled at the stage level
D. enabled with environmental parameters set at runtime
Answer: A,C
QUESTION
10
Which three are valid ways within a Job Sequence to pass parameters to Activity stages?
(Choose three.)
A. ExecCommand Activity stage
B. UserVariables Activity stage
C. Sequencer Activity stage
D. Routine Activity stage
E. Nested Condition Activity stage
Answer: A,B,D
QUESTION
11
A client requires that a database table be done using two jobs. The first job writes to a
dataset. The second job reads the dataset and loads the table. The two jobs are connected
in a Job Sequence. What are three benefits of this approach? (Choose three.)
A. The time it takes to load the table is reduced.
B. The database table can be reloaded after a failure without re-reading the source data.
C. The dataset can be used by other jobs even if the database load fails.
D. The dataset can be read if the database is not available.
E. The data in the dataset can be archived and shared with other external applications.
Answer: B,C,D
QUESTION
12
You are reading customer data using a Sequential File stage and transforming it using the
Transformer stage. The Transformer is used to cleanse the data by trimming spaces from
character fields in the input. The cleansed data is to be written to a target DB2 table.
Which partitioning method would yield optimal performance without violating the
business requirements?
A. Hash on the customer ID field
B. Round Robin
C. Random
D. Entire
Answer: B
QUESTION
13
Which three are valid trigger expressions in a stage in a Job Sequence? (Choose three.)
A. Equality(Conditional)
B. Unconditional
C. ReturnValue(Conditional)
D. Difference(Conditional)
E. Custom(Conditional)
Answer: B,C,E
QUESTION
14
An Aggregator stage using a Hash technique processes a very large number of rows
during month end processing. The job occasionally aborts during these large runs with an
obscure memory error. When the job is rerun, processing the data in smaller amounts
corrects the problem. Which change would correct the problem?
A. Set the Combinability option on the Stage Advanced tab to Combinable allowing the
Aggregator to use the memory associated with other operators.
B. Change the partitioning keys to produce more data partitions.
C. Add a Sort stage prior to the Aggregator and change to a sort technique on the Stage
Properties tab of the Aggregator stage.
D. Set the environment variable APT_AGG_MAXMEMORY to a larger value.
Answer: C
QUESTION
15
Which three actions are performed using stage variables in a parallel Transformer stage?
(Choose three.)
A. A function can be executed once per record.
B. A function can be executed once per run.
C. Identify the first row of an input group.
D. Identify the last row of an input group.
E. Lookup up a value from a reference dataset.
Answer: A,B,C
Datstage solutions, knowledge base, FAQ and best practices
Datstage solutions, knowledge base, FAQ and best practices
1.What are the ways to execute datastage jobs?
A job can be run using a few different methods:
* from Datastage Director (menu Job -> Run now...)
* from command line using a dsjob command
* Datastage routine can run a job (DsRunJob command)
* by a job sequencer
2.How to invoke a Datastage shell command?
Datastage shell commands can be invoked from :
* Datastage administrator (projects tab -> Command)
* Telnet client connected to the datastage server
3.How to stop a job when its status is running?
To stop a running job go to DataStage Director and click the stop button (or Job -> Stop from menu). If it doesn't help go to Job -> Cleanup Resources, select a process with holds a lock and click Logout
If it still doesn't help go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).
4. How to release a lock held by jobs?
Go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).
5. How to run and schedule a job from command line?
To run a job from command line use a dsjob command
Command Syntax: dsjob [-file
6. User privileges for the default DataStage roles?
The role privileges are:
* DataStage Developer - user with full access to all areas of a DataStage project
* DataStage Operator - has privileges to run and manage deployed DataStage jobs
* -none- - no permission to log on to DataStage
7. What is a command to analyze hashed file?
There are two ways to analyze a hashed file. Both should be invoked from the datastage command shell. These are:
* FILE.STAT command
* ANALYZE.FILE command
8. Is it possible to run two versions of datastage on the same pc?
Yes, even though different versions of Datastage use different system dll libraries.
To dynamically switch between Datastage versions install and run DataStage Multi-Client Manager. That application can unregister and register system libraries used by Datastage.
9. How to send notifications from Datastage as a text message (sms) to a cell phone
There is a few possible methods of sending sms messages from Datastage. However, there is no easy way to do this directly from Datastage and all methods described below will require some effort.
The easiest way of doing that from the Datastage standpoint is to configure an SMTP (email) server as a mobile phone gateway. In that case, a Notification Activity can be used to send message with a job log and any desired details. DSSendMail Before-job or After-job subroutine can be also used to send sms messages.
If configured properly, the recipients email address will have the following format: 600123456@oursmsgateway.com
If there is no possibility of configuring a mail server to send text messages, you can to work it around by using an external application run directly from the operational system. There is a whole bunch of unix scripts and applications to send sms messages.
In that solution, you will need to create a batch script which will take care of sending messages and invoke it from Datastage using ExecDOS or ExecSh subroutines passing the required parameters (like phone number and message body).
Please keep in mind that all these solutions may require a contact to the local cellphone provider first and, depending on the country, it may not be free of charge and in some cases the provider may not support the capability at all.
10.Error in Link collector - Stage does not support in-process active-to-active inputs or outputs
To get rid of the error just go to the Job Properties -> Performance and select Enable row buffer.
Then select Inter process which will let the link collector run correctly.
Buffer size set to 128Kb should be fine, however it's a good idea to increase the timeout.
11.What is the DataStage equivalent to like option in ORACLE
The following statement in Oracle:
select * from ARTICLES where article_name like '%WHT080%';
Can be written in DataStage (for example as the constraint expression):
incol.empname matches '...WHT080...'
12.what is the difference between logging text and final text message in terminator stage
Every stage has a 'Logging Text' area on their General tab which logs an informational message when the stage is triggered or started.
* Informational - is a green line, DSLogInfo() type message.
* The Final Warning Text - the red fatal, the message which is included in the sequence abort message .
13.Error in STPstage - SOURCE Procedures must have an output link
The error appears in Stored Procedure (STP) stage when there are no stages going out of that stage.
To get rid of it go to 'stage properties' -> 'Procedure type' and select Transform
14. How to invoke an Oracle PLSQL stored procedure from a server job
To run a pl/sql procedure from Datastage a Stored Procedure (STP) stage can be used.
However it needs a flow of at least one record to run.
It can be designed in the following way:
* source odbc stage which fetches one record from the database and maps it to one column - for example: select sysdate from dual
* A transformer which passes that record through. If required, add pl/sql procedure parameters as columns on the right-hand side of tranformer's mapping
* Put Stored Procedure (STP) stage as a destination. Fill in connection parameters, type in the procedure name and select Transform as procedure type. In the input tab select 'execute procedure for each row' (it will be run once).
15. Is it possible to run a server job in parallel?
Yes, even server jobs can be run in parallel.
To do that go to 'Job properties' -> General and check the Allow Multiple Instance button.
The job can now be run simultaneously from one or many sequence jobs. When it happens datastage will create new entries in Director and new job will be named with automatically generated suffix (for example second instance of a job named JOB_0100 will be named JOB_0100.JOB_0100_2). It can be deleted at any time and will be automatically recreated by datastage on the next run.
16.Datastage routine to open a text file with error catching
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END
17. Datastage routine which reads the first line from a text file
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END
READSEQ FILE1.RECORD FROM H.FILE1 ELSE
Call DSLogWarn("******************** File is empty", "JobControl")
END
firstline = Trim(FILE1.RECORD[1,32]," ","A") ******* will read the first 32 chars
Call DSLogInfo("******************** Record read: " : firstline, "JobControl")
CLOSESEQ H.FILE1
18.How to test a datastage routine or transform?
To test a datastage routine or transform go to the Datastage Manager.
Navigate to Routines, select a routine you want to test and open it. First compile it and then click 'Test...' which will open a new window. Enter test parameters in the left-hand side column and click run all to see the results.
Datastage will remember all the test arguments during future tests.
19.When hashed files should be used? What are the benefits or using them?
Hashed files are the best way to store data for lookups. They're very fast when looking up the key-value pairs.
Hashed files are especially useful if they store information with data dictionaries (customer details, countries, exchange rates). Stored this way it can be spread across the project and accessed from different jobs.
20.How to construct a container and deconstruct it or switch between local and shared?
To construct a container go to Datastage designer, select the stages that would be included in the container and from the main menu select Edit -> Construct Container and choose between local and shared.
Local will be only visible in the current job, and share can be re-used. Shared containers can be viewed and edited in Datastage Manager under 'Routines' menu.
Local Datastage containers can be converted at any time to shared containers in datastage designer by right clicking on the container and selecting 'Convert to Shared'. In the same way it can be converted back to local.
21.Corresponding datastage data types to ORACLE types?
Most of the datastage variable types map very well to oracle types. The biggest problem is to map correctly oracle NUMBER(x,y) format.
The best way to do that in Datastage is to convert oracle NUMBER format to Datastage Decimal type and to fill in Length and Scale column accordingly.
There are no problems with string mappings: oracle Varchar2 maps to datastage Varchar, and oracle char to datastage char.
22.How to adjust commit interval when loading data to the database?
In earlier versions of datastage the commit interval could be set up in:
General -> Transaction size (in version 7.x it's obsolete)
Starting from Datastage 7.x it can be set up in properties of ODBC or ORACLE stage in Transaction handling -> Rows per transaction.
If set to 0 the commit will be issued at the end of a successfull transaction.
23.What is the use of INROWNUM and OUTROWNUM datastage variables?
@INROWNUM and @OUTROWNUM are internal datastage variables which do the following:
* @INROWNUM counts incoming rows to a transformer in a datastage job
* @OUTROWNUM counts oucoming rows from a transformer in a datastage job
These variables can be used to generate sequences, primary keys, id's, numbering rows and also for debugging and error tracing.
They play similiar role as sequences in Oracle.
24.Datastage trim function cuts out more characters than expected
By deafult datastage trim function will work this way:
Trim(" a b c d ") will return "a b c d" while in many other programming/scripting languages "a b c d" result would be expected.
That is beacuse by default an R parameter is assumed which is R - Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
To get the "a b c d" as a result use the trim function in the following way: Trim(" a b c d "," ","B")
25. Database update actions in ORACLE stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Available actions:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
26. Use and examples of ICONV and OCONV functions?
ICONV and OCONV functions are quite often used to handle data in Datastage.
ICONV converts a string to an internal storage format and OCONV converts an expression to an output format.
Syntax:
Iconv (string, conversion code)
Oconv(expression, conversion )
Some useful iconv and oconv examples:
Iconv("10/14/06", "D2/") = 14167
Oconv(14167, "D-E") = "14-10-2006"
Oconv(14167, "D DMY[,A,]") = "14 OCTOBER 2006"
Oconv(12003005, "MD2$,") = "$120,030.05"
That expression formats a number and rounds it to 2 decimal places:
Oconv(L01.TURNOVER_VALUE*100,"MD2")
Iconv and oconv can be combined in one expression to reformat date format easily:
Oconv(Iconv("10/14/06", "D2/"),"D-E") = "14-10-2006"
27.ERROR 81021 Calling subroutine DSR_RECORD ACTION=2
Error message:
DataStage Repository Interface:
Error calling subroutine: DSR_RECORD (Action=2);
check DataStage is set up correctly in project
Development (Internal Error (81021))
Datastage system help gives the following error desription:
SYS.HELP. 081021
MESSAGE.. dsrpc: Error writing to Pipe.
The problem appears when a job sequence is used and it contains many stages (usually more than 10) and very often when a network connection is slow.
Basically the cause of a problem is a failure between DataStage client and the server communication.
The solution to the issue is:
# Do not log in to Datastage Designer using 'Omit' option on a login screen. Type in explicitly username and password and a job should compile successfully.
# execute the DS.REINDEX ALL command from the Datastage shell - if the above does not help
28. How to check Datastage internal error descriptions
# To check the description of a number go to the datastage shell (from administrator or telnet to the server machine) and invoke the following command: SELECT * FROM SYS.MESSAGE WHERE @ID='081021'; - where in that case the number 081021 is an error number
The command will produce a brief error description which probably will not be helpful in resolving an issue but can be a good starting point for further analysis.
29.Error timeout waiting for mutex
The error message usually looks like follows:
... ds_ipcgetnext() - timeout waiting for mutex
There may be several reasons for the error and thus solutions to get rid of it.
The error usually appears when using Link Collector, Link Partitioner and Interprocess (IPC) stages. It may also appear when doing a lookup with the use of a hash file or if a job is very complex, with the use of many transformers.
There are a few things to consider to work around the problem:
- increase the buffer size (up to to 1024K) and the Timeout value in the Job properties (on the Performance tab).
- ensure that the key columns in active stages or hashed files are composed of allowed characters – get rid of nulls and try to avoid language specific chars which may cause the problem.
- try to simplify the job as much as possible (especially if it’s very complex). Consider splitting it into two or three smaller jobs, review fetches and lookups and try to optimize them (especially have a look at the SQL statements).
30. ERROR 30107 Subroutine failed to complete successfully
Error message:
Error calling subroutine:
DSR_RECORD (Action=2); or *DataStage*DSR_SELECT (Action=7);
check DataStage is set up correctly in project Development
(Subroutine failed to complete successfully(30107))
Datastage system help gives the following error desription:
SYS.HELP. 930107
MESSAGE.. DataStage/SQL: Illegal placement of parameter markers
The problem appears when a project is moved from one project to another (for example when deploying a project from a development environment to production).
The solution to the issue is:
# Rebuild the repository index by executing the DS.REINDEX ALL command from the Datastage shell
31.Datastage Designer hangs when editing job activity properties
The appears when running Datastage Designer under Windows XP after installing patches or the Service Pack 2 for Windows.
After opening a job sequence and navigating to the job activity properties window the application freezes and the only way to close it is from the Windows Task Manager.
The solution of the problem is very simple. Just Download and install the “XP SP2 patch” for the Datastage client.
It can be found on the IBM client support site (need to log in):
https://www.ascential.com/eservice/public/welcome.do
Go to the software updates section and select an appropriate patch from the Recommended DataStage patches section.
Sometimes users face problems when trying to log in (for example when the license doesn’t cover the IBM Active Support), then it may be necessary to contact the IBM support which can be reached at WDISupport@us.ibm.com
1.What are the ways to execute datastage jobs?
A job can be run using a few different methods:
* from Datastage Director (menu Job -> Run now...)
* from command line using a dsjob command
* Datastage routine can run a job (DsRunJob command)
* by a job sequencer
2.How to invoke a Datastage shell command?
Datastage shell commands can be invoked from :
* Datastage administrator (projects tab -> Command)
* Telnet client connected to the datastage server
3.How to stop a job when its status is running?
To stop a running job go to DataStage Director and click the stop button (or Job -> Stop from menu). If it doesn't help go to Job -> Cleanup Resources, select a process with holds a lock and click Logout
If it still doesn't help go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).
4. How to release a lock held by jobs?
Go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).
5. How to run and schedule a job from command line?
To run a job from command line use a dsjob command
Command Syntax: dsjob [-file
6. User privileges for the default DataStage roles?
The role privileges are:
* DataStage Developer - user with full access to all areas of a DataStage project
* DataStage Operator - has privileges to run and manage deployed DataStage jobs
* -none- - no permission to log on to DataStage
7. What is a command to analyze hashed file?
There are two ways to analyze a hashed file. Both should be invoked from the datastage command shell. These are:
* FILE.STAT command
* ANALYZE.FILE command
8. Is it possible to run two versions of datastage on the same pc?
Yes, even though different versions of Datastage use different system dll libraries.
To dynamically switch between Datastage versions install and run DataStage Multi-Client Manager. That application can unregister and register system libraries used by Datastage.
9. How to send notifications from Datastage as a text message (sms) to a cell phone
There is a few possible methods of sending sms messages from Datastage. However, there is no easy way to do this directly from Datastage and all methods described below will require some effort.
The easiest way of doing that from the Datastage standpoint is to configure an SMTP (email) server as a mobile phone gateway. In that case, a Notification Activity can be used to send message with a job log and any desired details. DSSendMail Before-job or After-job subroutine can be also used to send sms messages.
If configured properly, the recipients email address will have the following format: 600123456@oursmsgateway.com
If there is no possibility of configuring a mail server to send text messages, you can to work it around by using an external application run directly from the operational system. There is a whole bunch of unix scripts and applications to send sms messages.
In that solution, you will need to create a batch script which will take care of sending messages and invoke it from Datastage using ExecDOS or ExecSh subroutines passing the required parameters (like phone number and message body).
Please keep in mind that all these solutions may require a contact to the local cellphone provider first and, depending on the country, it may not be free of charge and in some cases the provider may not support the capability at all.
10.Error in Link collector - Stage does not support in-process active-to-active inputs or outputs
To get rid of the error just go to the Job Properties -> Performance and select Enable row buffer.
Then select Inter process which will let the link collector run correctly.
Buffer size set to 128Kb should be fine, however it's a good idea to increase the timeout.
11.What is the DataStage equivalent to like option in ORACLE
The following statement in Oracle:
select * from ARTICLES where article_name like '%WHT080%';
Can be written in DataStage (for example as the constraint expression):
incol.empname matches '...WHT080...'
12.what is the difference between logging text and final text message in terminator stage
Every stage has a 'Logging Text' area on their General tab which logs an informational message when the stage is triggered or started.
* Informational - is a green line, DSLogInfo() type message.
* The Final Warning Text - the red fatal, the message which is included in the sequence abort message .
13.Error in STPstage - SOURCE Procedures must have an output link
The error appears in Stored Procedure (STP) stage when there are no stages going out of that stage.
To get rid of it go to 'stage properties' -> 'Procedure type' and select Transform
14. How to invoke an Oracle PLSQL stored procedure from a server job
To run a pl/sql procedure from Datastage a Stored Procedure (STP) stage can be used.
However it needs a flow of at least one record to run.
It can be designed in the following way:
* source odbc stage which fetches one record from the database and maps it to one column - for example: select sysdate from dual
* A transformer which passes that record through. If required, add pl/sql procedure parameters as columns on the right-hand side of tranformer's mapping
* Put Stored Procedure (STP) stage as a destination. Fill in connection parameters, type in the procedure name and select Transform as procedure type. In the input tab select 'execute procedure for each row' (it will be run once).
15. Is it possible to run a server job in parallel?
Yes, even server jobs can be run in parallel.
To do that go to 'Job properties' -> General and check the Allow Multiple Instance button.
The job can now be run simultaneously from one or many sequence jobs. When it happens datastage will create new entries in Director and new job will be named with automatically generated suffix (for example second instance of a job named JOB_0100 will be named JOB_0100.JOB_0100_2). It can be deleted at any time and will be automatically recreated by datastage on the next run.
16.Datastage routine to open a text file with error catching
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END
17. Datastage routine which reads the first line from a text file
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END
READSEQ FILE1.RECORD FROM H.FILE1 ELSE
Call DSLogWarn("******************** File is empty", "JobControl")
END
firstline = Trim(FILE1.RECORD[1,32]," ","A") ******* will read the first 32 chars
Call DSLogInfo("******************** Record read: " : firstline, "JobControl")
CLOSESEQ H.FILE1
18.How to test a datastage routine or transform?
To test a datastage routine or transform go to the Datastage Manager.
Navigate to Routines, select a routine you want to test and open it. First compile it and then click 'Test...' which will open a new window. Enter test parameters in the left-hand side column and click run all to see the results.
Datastage will remember all the test arguments during future tests.
19.When hashed files should be used? What are the benefits or using them?
Hashed files are the best way to store data for lookups. They're very fast when looking up the key-value pairs.
Hashed files are especially useful if they store information with data dictionaries (customer details, countries, exchange rates). Stored this way it can be spread across the project and accessed from different jobs.
20.How to construct a container and deconstruct it or switch between local and shared?
To construct a container go to Datastage designer, select the stages that would be included in the container and from the main menu select Edit -> Construct Container and choose between local and shared.
Local will be only visible in the current job, and share can be re-used. Shared containers can be viewed and edited in Datastage Manager under 'Routines' menu.
Local Datastage containers can be converted at any time to shared containers in datastage designer by right clicking on the container and selecting 'Convert to Shared'. In the same way it can be converted back to local.
21.Corresponding datastage data types to ORACLE types?
Most of the datastage variable types map very well to oracle types. The biggest problem is to map correctly oracle NUMBER(x,y) format.
The best way to do that in Datastage is to convert oracle NUMBER format to Datastage Decimal type and to fill in Length and Scale column accordingly.
There are no problems with string mappings: oracle Varchar2 maps to datastage Varchar, and oracle char to datastage char.
22.How to adjust commit interval when loading data to the database?
In earlier versions of datastage the commit interval could be set up in:
General -> Transaction size (in version 7.x it's obsolete)
Starting from Datastage 7.x it can be set up in properties of ODBC or ORACLE stage in Transaction handling -> Rows per transaction.
If set to 0 the commit will be issued at the end of a successfull transaction.
23.What is the use of INROWNUM and OUTROWNUM datastage variables?
@INROWNUM and @OUTROWNUM are internal datastage variables which do the following:
* @INROWNUM counts incoming rows to a transformer in a datastage job
* @OUTROWNUM counts oucoming rows from a transformer in a datastage job
These variables can be used to generate sequences, primary keys, id's, numbering rows and also for debugging and error tracing.
They play similiar role as sequences in Oracle.
24.Datastage trim function cuts out more characters than expected
By deafult datastage trim function will work this way:
Trim(" a b c d ") will return "a b c d" while in many other programming/scripting languages "a b c d" result would be expected.
That is beacuse by default an R parameter is assumed which is R - Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
To get the "a b c d" as a result use the trim function in the following way: Trim(" a b c d "," ","B")
25. Database update actions in ORACLE stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Available actions:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
26. Use and examples of ICONV and OCONV functions?
ICONV and OCONV functions are quite often used to handle data in Datastage.
ICONV converts a string to an internal storage format and OCONV converts an expression to an output format.
Syntax:
Iconv (string, conversion code)
Oconv(expression, conversion )
Some useful iconv and oconv examples:
Iconv("10/14/06", "D2/") = 14167
Oconv(14167, "D-E") = "14-10-2006"
Oconv(14167, "D DMY[,A,]") = "14 OCTOBER 2006"
Oconv(12003005, "MD2$,") = "$120,030.05"
That expression formats a number and rounds it to 2 decimal places:
Oconv(L01.TURNOVER_VALUE*100,"MD2")
Iconv and oconv can be combined in one expression to reformat date format easily:
Oconv(Iconv("10/14/06", "D2/"),"D-E") = "14-10-2006"
27.ERROR 81021 Calling subroutine DSR_RECORD ACTION=2
Error message:
DataStage Repository Interface:
Error calling subroutine: DSR_RECORD (Action=2);
check DataStage is set up correctly in project
Development (Internal Error (81021))
Datastage system help gives the following error desription:
SYS.HELP. 081021
MESSAGE.. dsrpc: Error writing to Pipe.
The problem appears when a job sequence is used and it contains many stages (usually more than 10) and very often when a network connection is slow.
Basically the cause of a problem is a failure between DataStage client and the server communication.
The solution to the issue is:
# Do not log in to Datastage Designer using 'Omit' option on a login screen. Type in explicitly username and password and a job should compile successfully.
# execute the DS.REINDEX ALL command from the Datastage shell - if the above does not help
28. How to check Datastage internal error descriptions
# To check the description of a number go to the datastage shell (from administrator or telnet to the server machine) and invoke the following command: SELECT * FROM SYS.MESSAGE WHERE @ID='081021'; - where in that case the number 081021 is an error number
The command will produce a brief error description which probably will not be helpful in resolving an issue but can be a good starting point for further analysis.
29.Error timeout waiting for mutex
The error message usually looks like follows:
... ds_ipcgetnext() - timeout waiting for mutex
There may be several reasons for the error and thus solutions to get rid of it.
The error usually appears when using Link Collector, Link Partitioner and Interprocess (IPC) stages. It may also appear when doing a lookup with the use of a hash file or if a job is very complex, with the use of many transformers.
There are a few things to consider to work around the problem:
- increase the buffer size (up to to 1024K) and the Timeout value in the Job properties (on the Performance tab).
- ensure that the key columns in active stages or hashed files are composed of allowed characters – get rid of nulls and try to avoid language specific chars which may cause the problem.
- try to simplify the job as much as possible (especially if it’s very complex). Consider splitting it into two or three smaller jobs, review fetches and lookups and try to optimize them (especially have a look at the SQL statements).
30. ERROR 30107 Subroutine failed to complete successfully
Error message:
Error calling subroutine:
DSR_RECORD (Action=2); or *DataStage*DSR_SELECT (Action=7);
check DataStage is set up correctly in project Development
(Subroutine failed to complete successfully(30107))
Datastage system help gives the following error desription:
SYS.HELP. 930107
MESSAGE.. DataStage/SQL: Illegal placement of parameter markers
The problem appears when a project is moved from one project to another (for example when deploying a project from a development environment to production).
The solution to the issue is:
# Rebuild the repository index by executing the DS.REINDEX ALL command from the Datastage shell
31.Datastage Designer hangs when editing job activity properties
The appears when running Datastage Designer under Windows XP after installing patches or the Service Pack 2 for Windows.
After opening a job sequence and navigating to the job activity properties window the application freezes and the only way to close it is from the Windows Task Manager.
The solution of the problem is very simple. Just Download and install the “XP SP2 patch” for the Datastage client.
It can be found on the IBM client support site (need to log in):
https://www.ascential.com/eservice/public/welcome.do
Go to the software updates section and select an appropriate patch from the Recommended DataStage patches section.
Sometimes users face problems when trying to log in (for example when the license doesn’t cover the IBM Active Support), then it may be necessary to contact the IBM support which can be reached at WDISupport@us.ibm.com
Advantages and Disadvantages of using DataStage ETL tool
Major business and technical advantages and disadvantages of using DataStage ETL tool
Business advantages of using DataStage as an ETL tool:
# Significant ROI (return of investment) over hand-coding
# Learning curve - quick development and reduced maintenance with GUI tool
# Development Partnerships - easy integration with top market products interfaced with the datawarehouse, such as SAP, Cognos, Oracle, Teradata, SAS
# Single vendor solution for bulk data transfer and complex transformations (DataStage versus DataStage TX)
# Transparent and wide range of licensing options
Technical advantages of using DataStage tool to implement the ETL processes
# Single interface to integrate heterogeneous applications
# Flexible development environment - it enables developers to work in their desired style, reduces training needs and enhances reuse. ETL developers can follow data integrations quickly through a graphical work-as-you-think solution which comes by default with a wide range of extensible objects and functions
# Team communication and documentation of the jobs is supported by data flows and transformations self-documenting engine in HTML format.
# Ability to join data both at the source, and at the integration server and to apply any business rule from within a single interface without having to write any procedural code.
# Common data infrastructure for data movement and data quality (metadata repository, parallel processing framework, development environment)
# With Datastage Enterprise Edition users can use the parallel processing engine which provides unlimited performance and scalability. It helps get most out of hardware investment and resources.
# The datastage server performs very well on both Windows and unix servers.
Major Datastage weaknesses and disadvantages
# Big architectural differences in the Server and Enterprise edition which results in the fact that migration from server to enterprise edition may require vast time and resources effort.
# There is no automated error handling and recovery mechanism - for example no way to automatically time out zombie jobs or kill locking processes. However, on the operator level, these errors can be easily resolved.
# No Unix Datastage client - the Client software available only under Windows and there are different clients for different datastage versions. The good thing is that they still can be installed on the same windows pc and switched with the Multi-Client Manager program.
# Might be expensive as a solution for a small or mid-sized company.
Business advantages of using DataStage as an ETL tool:
# Significant ROI (return of investment) over hand-coding
# Learning curve - quick development and reduced maintenance with GUI tool
# Development Partnerships - easy integration with top market products interfaced with the datawarehouse, such as SAP, Cognos, Oracle, Teradata, SAS
# Single vendor solution for bulk data transfer and complex transformations (DataStage versus DataStage TX)
# Transparent and wide range of licensing options
Technical advantages of using DataStage tool to implement the ETL processes
# Single interface to integrate heterogeneous applications
# Flexible development environment - it enables developers to work in their desired style, reduces training needs and enhances reuse. ETL developers can follow data integrations quickly through a graphical work-as-you-think solution which comes by default with a wide range of extensible objects and functions
# Team communication and documentation of the jobs is supported by data flows and transformations self-documenting engine in HTML format.
# Ability to join data both at the source, and at the integration server and to apply any business rule from within a single interface without having to write any procedural code.
# Common data infrastructure for data movement and data quality (metadata repository, parallel processing framework, development environment)
# With Datastage Enterprise Edition users can use the parallel processing engine which provides unlimited performance and scalability. It helps get most out of hardware investment and resources.
# The datastage server performs very well on both Windows and unix servers.
Major Datastage weaknesses and disadvantages
# Big architectural differences in the Server and Enterprise edition which results in the fact that migration from server to enterprise edition may require vast time and resources effort.
# There is no automated error handling and recovery mechanism - for example no way to automatically time out zombie jobs or kill locking processes. However, on the operator level, these errors can be easily resolved.
# No Unix Datastage client - the Client software available only under Windows and there are different clients for different datastage versions. The good thing is that they still can be installed on the same windows pc and switched with the Multi-Client Manager program.
# Might be expensive as a solution for a small or mid-sized company.
Data warehouse
Data warehouse
Data Warehouse is a central managed and integrated database containing data from the operational sources in an organization (such as SAP, CRM, ERP system). It may gather manual inputs from users determining criteria and parameters for grouping or classifying records.
That database contains structured data for query analysis and can be accessed by users. The data warehouse can be created or updated at any time, with minimum disruption to operational systems. It is ensured by a strategy implemented in a ETL process.
A source for the data warehouse is a data extract from operational databases. The data is validated, cleansed, transformed and finally aggregated and it becomes ready to be loaded into the data warehouse.
Data warehouse is a dedicated database which contains detailed, stable, non-volatile and consistent data which can be analyzed in the time variant.
Sometimes, where only a portion of detailed data is required, it may be worth considering using a data mart. A data mart is generated from the data warehouse and contains data focused on a given subject and data that is frequently accessed or summarized.
Keeping the data warehouse filled with very detailed and not efficiently selected data may lead to growing the database to a huge size, which may be difficult to manage and unusable. To significantly reduce number of rows in the data warehouse, the data is aggregated which leads to the easier data maintenance and efficiency in browsing and data analysis.
A well designed and maintained Data Warehouse can significantly improve the quality and accessibility of the company data and increase the amount of information delivered to the end users.
# Key Data Warehouse systems and the most widely used database engines for storing and serving data for the enterprise business intelligence and performance management: Teradata
# Oracle
# Microsoft SQL Server
# IBM DB2
# SAS
Data Warehouse is a central managed and integrated database containing data from the operational sources in an organization (such as SAP, CRM, ERP system). It may gather manual inputs from users determining criteria and parameters for grouping or classifying records.
That database contains structured data for query analysis and can be accessed by users. The data warehouse can be created or updated at any time, with minimum disruption to operational systems. It is ensured by a strategy implemented in a ETL process.
A source for the data warehouse is a data extract from operational databases. The data is validated, cleansed, transformed and finally aggregated and it becomes ready to be loaded into the data warehouse.
Data warehouse is a dedicated database which contains detailed, stable, non-volatile and consistent data which can be analyzed in the time variant.
Sometimes, where only a portion of detailed data is required, it may be worth considering using a data mart. A data mart is generated from the data warehouse and contains data focused on a given subject and data that is frequently accessed or summarized.
Keeping the data warehouse filled with very detailed and not efficiently selected data may lead to growing the database to a huge size, which may be difficult to manage and unusable. To significantly reduce number of rows in the data warehouse, the data is aggregated which leads to the easier data maintenance and efficiency in browsing and data analysis.
A well designed and maintained Data Warehouse can significantly improve the quality and accessibility of the company data and increase the amount of information delivered to the end users.
# Key Data Warehouse systems and the most widely used database engines for storing and serving data for the enterprise business intelligence and performance management: Teradata
# Oracle
# Microsoft SQL Server
# IBM DB2
# SAS
Datastage tutorial and training
Datastage tutorial and training
Datastage guide Table of contents:
# Lesson 1. Datastage-modules - the lesson contains an overview of the datastage components and modules with screenshots.
# Lesson 2. Designing jobs - datastage palette - a list of all stages and activities used in Datastage
# Lesson 3. Extracting and loading data - ODBC and ORACLE stages - description and use of the ODBC and ORACLE stages (ORAOCI9) used for data extraction and data load. Covers ODBC input and output links, Oracle update actions and best practices.
# Lesson 4. Extracting and loading data - sequential files - description and use of the sequential files (flat files, text files, CSV files) in datastage.
# Lesson 5. Transforming and filtering data - use of transformers to perform data conversions, mappings, validations and datarefining. Design examples of the most commonly used datastage jobs.
# Lesson 6. Performing lookups in Datastage - how to use hash files and database stages as a lookup source.
# Lesson 7. Implementing ETL process in Datastage - step by step guide on how to implement the ETL process efficiently in Datastage. Contains tips on how to design and run a set of jobs executed on a daily basis.
# Lesson 8. SCD implementation in Datastage - the lesson illustrates how to implement SCD's (slowly changing dimensions) in Datastage, contains job designs, screenshots and sample data. All the Slowly Changing Dimensions types are described in separate articles below:
# SCD Type 1
# SCD Type 2
# SCD Type 3 and 4
# Lesson 9. Datastage jobs real-life solutions - a set of examples of job designs resolving real-life problems implemented in production datawarehouse environments in various companies.
# Lesson 10. Header and trailer file processing - a sample Datastage job which processes a textfile organized in a header and trailer format. This format may be represented for example by mainframe, EDI or EPIC files
Lesson 1:
Datastage modules
The DataStage components:
* Administrator - Administers DataStage projects, manages global settings and interacts with the system. Administrator is used to specify general server defaults, add and delete projects, set up project properties and provides a command interface to the datastage repository.
With Datastage Administrator users can set job monitoring limits, user privileges, job scheduling options and parallel jobs default.
* Manager - it's a main interface to the Datastage Repository, allows its browsing and editing. It displays tables and files layouts, routines, transforms and jobs defined in the project. It is mainly used to store and manage reusable metadata.
* Designer - used to create DataStage jobs which are compiled into executable programs. is a graphical, user-friendly application which applies visual data flow method to develop job flows for extracting, cleansing, transforming, integrating and loading data. It’s a module mainly used by Datastage developers.
* Director - manages running, validating, scheduling and monitoring DataStage jobs. It’s mainly used by operators and testers.
Lesson 3:
Designing jobs - ODBC and ORACLE stages
ODBC stages are used to allow Datastage to connect to any data source that represents the Open Database Connectivity API (ODBC) standard.
ODBC stages are mainly used to extract or load the data. However, ODBC stage may also be very helpful when aggregating data and as a lookup stage (in that case it can play role of aggregator stage or a hash file and can be used instead).
Each ODBC stage can have any number of inputs or outputs.
The input links specify the data which is written to the database (they act as INSERT, UPDATE or DELETE statements in SQL). Input link data can be defined in various ways: using an SQL statement constructed by DataStage, a user-defined SQL query or a stored procedure.
Output links specify the data that are extracted (correspond to the SQL SELECT statement). The data on an output link is passed through ODBC connector and processed by an underlying database.
If a processing target is an Oracle database, it may be worth considering use of ORACLE (ORAOCI9) stage. It has a significantly better performance than ODBC stage and allows setting up more configuration options and parameters native to the Oracle database.
There’s a very useful option to issue an SQL before or after main dataflow operations (Oracle stage properties -> Input -> SQL). For example, when loading a big chunk of data into an oracle table, it may increase performance to drop indexes in a ‘before SQL’ tab and create indexes and analyze table in a ‘after SQL’ tab ('ANALYZE TABLE xxx COMPUTE STATISTICS' SQL statement).
Update actions in Oracle stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Update actions available from the drop-down list:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
Lesson 4:
Designing jobs - sequential (text) files
Sequential File stages are used to interract with text files which may involve extracting data from and write data to a text file. Sequential File stages can read files, create (overwrite) or append data to a text file. It can be processed on any drive that is local or mapped as a shared folder or even on an FTP server (combined with an FTP stage). Each Sequential File stage can have any number of inputs or outputs. However, trying to write to a sequential file simultaneously from two data streams will surely cause an error.
Lesson 5:
Designing jobs - tranforming and filtering data
It's a very common situation and a good practice to design datastage jobs in which data flow goes in the following way:
EXTRACT SOURCE -> DATA VALIDATION, REFINING, CLEANSING -> MAPPING -> DESTINATION
The data refining, validation and mapping part of the process is mainly handled by a transformer stage. Transformer stage doesn't extract or write data to a target database. It handles extracted data, performs conversions, mappings, validations, passes values and controls the data flow.
Transformer stages can have any number of input and output links. Input links can be primary or reference (used for lookups) and there can only be one primary input and any number of reference inputs.
Datastage guide Table of contents:
# Lesson 1. Datastage-modules - the lesson contains an overview of the datastage components and modules with screenshots.
# Lesson 2. Designing jobs - datastage palette - a list of all stages and activities used in Datastage
# Lesson 3. Extracting and loading data - ODBC and ORACLE stages - description and use of the ODBC and ORACLE stages (ORAOCI9) used for data extraction and data load. Covers ODBC input and output links, Oracle update actions and best practices.
# Lesson 4. Extracting and loading data - sequential files - description and use of the sequential files (flat files, text files, CSV files) in datastage.
# Lesson 5. Transforming and filtering data - use of transformers to perform data conversions, mappings, validations and datarefining. Design examples of the most commonly used datastage jobs.
# Lesson 6. Performing lookups in Datastage - how to use hash files and database stages as a lookup source.
# Lesson 7. Implementing ETL process in Datastage - step by step guide on how to implement the ETL process efficiently in Datastage. Contains tips on how to design and run a set of jobs executed on a daily basis.
# Lesson 8. SCD implementation in Datastage - the lesson illustrates how to implement SCD's (slowly changing dimensions) in Datastage, contains job designs, screenshots and sample data. All the Slowly Changing Dimensions types are described in separate articles below:
# SCD Type 1
# SCD Type 2
# SCD Type 3 and 4
# Lesson 9. Datastage jobs real-life solutions - a set of examples of job designs resolving real-life problems implemented in production datawarehouse environments in various companies.
# Lesson 10. Header and trailer file processing - a sample Datastage job which processes a textfile organized in a header and trailer format. This format may be represented for example by mainframe, EDI or EPIC files
Lesson 1:
Datastage modules
The DataStage components:
* Administrator - Administers DataStage projects, manages global settings and interacts with the system. Administrator is used to specify general server defaults, add and delete projects, set up project properties and provides a command interface to the datastage repository.
With Datastage Administrator users can set job monitoring limits, user privileges, job scheduling options and parallel jobs default.
* Manager - it's a main interface to the Datastage Repository, allows its browsing and editing. It displays tables and files layouts, routines, transforms and jobs defined in the project. It is mainly used to store and manage reusable metadata.
* Designer - used to create DataStage jobs which are compiled into executable programs. is a graphical, user-friendly application which applies visual data flow method to develop job flows for extracting, cleansing, transforming, integrating and loading data. It’s a module mainly used by Datastage developers.
* Director - manages running, validating, scheduling and monitoring DataStage jobs. It’s mainly used by operators and testers.
Lesson 3:
Designing jobs - ODBC and ORACLE stages
ODBC stages are used to allow Datastage to connect to any data source that represents the Open Database Connectivity API (ODBC) standard.
ODBC stages are mainly used to extract or load the data. However, ODBC stage may also be very helpful when aggregating data and as a lookup stage (in that case it can play role of aggregator stage or a hash file and can be used instead).
Each ODBC stage can have any number of inputs or outputs.
The input links specify the data which is written to the database (they act as INSERT, UPDATE or DELETE statements in SQL). Input link data can be defined in various ways: using an SQL statement constructed by DataStage, a user-defined SQL query or a stored procedure.
Output links specify the data that are extracted (correspond to the SQL SELECT statement). The data on an output link is passed through ODBC connector and processed by an underlying database.
If a processing target is an Oracle database, it may be worth considering use of ORACLE (ORAOCI9) stage. It has a significantly better performance than ODBC stage and allows setting up more configuration options and parameters native to the Oracle database.
There’s a very useful option to issue an SQL before or after main dataflow operations (Oracle stage properties -> Input -> SQL). For example, when loading a big chunk of data into an oracle table, it may increase performance to drop indexes in a ‘before SQL’ tab and create indexes and analyze table in a ‘after SQL’ tab ('ANALYZE TABLE xxx COMPUTE STATISTICS' SQL statement).
Update actions in Oracle stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Update actions available from the drop-down list:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.
Lesson 4:
Designing jobs - sequential (text) files
Sequential File stages are used to interract with text files which may involve extracting data from and write data to a text file. Sequential File stages can read files, create (overwrite) or append data to a text file. It can be processed on any drive that is local or mapped as a shared folder or even on an FTP server (combined with an FTP stage). Each Sequential File stage can have any number of inputs or outputs. However, trying to write to a sequential file simultaneously from two data streams will surely cause an error.
Lesson 5:
Designing jobs - tranforming and filtering data
It's a very common situation and a good practice to design datastage jobs in which data flow goes in the following way:
EXTRACT SOURCE -> DATA VALIDATION, REFINING, CLEANSING -> MAPPING -> DESTINATION
The data refining, validation and mapping part of the process is mainly handled by a transformer stage. Transformer stage doesn't extract or write data to a target database. It handles extracted data, performs conversions, mappings, validations, passes values and controls the data flow.
Transformer stages can have any number of input and output links. Input links can be primary or reference (used for lookups) and there can only be one primary input and any number of reference inputs.
Subscribe to:
Posts (Atom)