Working with the Command Prompt in informatica

PMCMD:

Use pmcmd commands with operating system scheduling tools like cron, or you can embed pmcmd commands into shell or Perl scripts.

When you run pmcmd in command line mode, you enter connection information such as domain name, Integration Service name, user name and password in each command. For example, to start the workflow “wf_SalesAvg” infolder “SalesEast,” The user, seller3, with the password “jackson” sends the request to start the workflow.

syntax:

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast wf_SalesAvg

Command Line Mode:

1. At the command prompt, switch to the directory where the pmcmd executable is located.

By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2. Enter pmcmd followed by the command name and its required options and arguments:

pmcmd command_name [-option1] argument_1 [-option2] argument_2...

Interactive Mode :

1. At the command prompt, switch to the directory where the pmcmd executable is located. By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2. At the command prompt, type pmcmd.This starts pmcmd in interactive mode and displays the pmcmd> prompt. You do not have to type pmcmd before each command in interactive mode.

3. Enter connection information for the domain and Integration Service.

For example:

connect -sv MyIntService -d MyDomain -u seller3 -p jackson

4. Type a command and its options and arguments in the following format:command_name [-option1] argument_1 [-option2] argument_2...pmcmd runs the command and displays the prompt again.

5. Type exit to end an interactive session.

For example, the following commands invoke the interactive mode, establish a connection to Integration Service“MyIntService,” and start workflows “wf_SalesAvg” and “wf_SalesTotal” in folder “SalesEast”:

pmcmd

pmcmd> connect -sv MyIntService -d MyDomain -u seller3 -p jackson

pmcmd> setfolder SalesEast

pmcmd> startworkflow wf_SalesAvg

pmcmd> startworkflow wf_SalesTotal

Scripting pmcmd Commands :

For example, the following UNIX shell script checks the status of Integration Service “testService,” and if it is running, gets details for session “s_testSessionTask”:

#!/usr/bin/bash

# Sample pmcmd script

# Check if the service is alive

pmcmd pingservice -sv testService -d testDomain

if [ "$?" != 0 ]; then

# handle error

echo "Could not ping service"

exit

fi

# Get service properties

pmcmd getserviceproperties -sv testService -d testDomain

if [ "$?" != 0 ]; then

# handle error

echo "Could not get service properties"

exit

fi

# Get task details for session task "s_testSessionTask" of workflow

# "wf_test_workflow" in folder "testFolder"

pmcmd gettaskdetails -sv testService -d testDomain -u Administrator -p adminPass -folder testFolder -

workflow wf_test_workflow s_testSessionTask

if [ "$?" != 0 ]; then

# handle error

echo "Could not get details for task s_testSessionTask"

exit

fi

Schedule Workflow

Instruct the Integration Service to schedule a workflow. Use this command to reschedule a workflow that has been removed from the schedule.

The ScheduleWorkflow command uses the following syntax in the command line mode:

pmcmd ScheduleWorkflow

<<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]>

<<-user|-u> username|<-uservar|-uv> userEnvVar>

<<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>

[<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv>

userSecuritydomainEnvVar>]

[<-folder|-f> folder]

workflow

Types Of Sources

In Source Analyzer, we define the source definitions that we will use in a mapping. We can either import a source definition or manually create the definition.

We can import or create the following types of source definitions in the Source Analyzer:

• Relational tables, views, and synonyms
• Fixed-width and delimited flat files that do not contain binary data.
• COBOL files
• XML files
• Data models using certain data modeling tools through Metadata Exchange for Data Models

1.Working with Relational Sources

Special Character Handling:

We can import, create, or edit source definitions with table and column names containing special characters, such as the slash (/) character through the Designer.

When we use the Source Analyzer to import a source definition, the Designer retains special characters in table and field names.

However, when we add a source definition with special characters to a mapping, the Designer either retains or replaces the special character. Also, when we generate the default SQL statement in a Source Qualifier transformation for a relational source, the Designer uses quotation marks around some special characters. The Designer handles special characters differently for relational and non-relational sources.

Importing a Relational Source Definition

1. Connect to repository.
2. Right click the folder where you want to import source definition and  click open. The folder which is connected gets bold. We can work in only one folder at a time.
3. In the Source Analyzer, click Sources > Import from Database.
4. Select the ODBC data source used to connect to the source database. If you need to create or modify an ODBC data source, click the Browse button to open the ODBC Administrator. Create the data source, and click OK. Select the new ODBC data source.

5.Enter a database user name and password to connect to the database.

6.Click Connect. Table names will appear.

7.Select the relational object or objects you want to import.

8.Click OK.

9.Click Repository > Save.

Updating a Relational Source Definition

We can update a source definition to add business names or to reflect new column names, data types, or other changes. We can update a source definition in the following ways:

Edit the definition: Manually edit the source definition if we need to configure properties that we cannot import or if we want to make minor changes to the source definition.

Re import the definition: If the source changes are significant, we may need to Re import the source definition. This overwrites or renames the existing source Definition. We can retain existing primary key-foreign key relationships and descriptions in the source definition being replaced.

Editing Relational Source Definitions

1. Select Tools -> Source Analyzer
2. Drag the table you want to edit in workspace.
3. In the Source Analyzer, double-click the title bar of the source definition. Or Right click the table and click edit.
4. In table tab, we can rename, add owner name, business description or edit database type.
5. Click the Columns Tab. Edit column names, data types, and restrictions. Click OK.

2. Working with Flat Files

To use flat files as sources, targets, and lookups in a mapping we must import or create the definitions in the repository. We can import or create flat file source definitions in the Source Analyzer.

We can import fixed-width and delimited flat file definitions that do not contain binary data. When importing the definition, the file must be in a directory local to the client machine. In addition, the Integration Service must be able to access all source files during the session.

Special Character Handling:

When we import a flat file in the Designer, the Flat File Wizard uses the file name as the name of the flat file definition by default. We can import a flat file with any valid file name through the Flat File Wizard. However, the Designer does not recognize some special characters in flat file source and target names.

When we import a flat file, the Flat File Wizard changes invalid characters and spaces into underscores ( _ ). For example, you have the source file "sample prices+items.dat". When we import this flat file in the Designer, the Flat File Wizard names the file definition sample_prices_items by default.

To import a fixed-width flat file definition:

1. Open the Source Analyzer and click Sources > Import from File. The Open Flat File dialog box appears.
2. Browse and Select the file you want to use.
3. Select a code page.
4. Click OK.
5. Edit the following settings:

6) Click Next. Follow the directions in the wizard to manipulate the column breaks in the file preview window. Move existing column breaks by Dragging them. Double-click a column break to delete it.

7) Click next and Enter column information for each column in the file.

8) Click Finish.

9) Click Repository > Save.

To import a delimited flat file definition:

Delimited flat files are always character-oriented and line sequential. The column precision is always measured in characters for string columns and in significant digits for numeric columns. Each row ends with a newline character. We can import a delimited file that does not contain binary data or multi byte character data greater than two bytes per character.

Steps:

1. Repeat Steps 1-5 as in case of fixed width.
2. Click Next.
3. Enter the following settings:

4) Enter column information for each column in the file.

5) Click Finish. 6) Click Repository > Save. 

Editing Flat File Definitions

1) Select Tools -> Source Analyzer

2) Drag the file you want to edit in workspace.

3) In the Source Analyzer, double-click the title bar of the source definition.

• We can edit source or target flat file definitions using the following definition tabs:
•  Table tab: Edit properties such as table name, business name, and flat file properties.
•  Columns tab: Edit column information such as column names, data types, precision, and formats.
• Properties tab: View the default numeric and date time format properties in the Source Analyzer and the Target Designer. You can edit these properties for each source and target instance in a mapping in the Mapping Designer.
•  Metadata Extensions tab: Extend the metadata stored in the repository by associating information with repository objects, such as flat file definitions.

4) Click the Advanced button to edit the flat file properties. A different dialog box appears for fixed-width and delimited files.

5) Do the changes as needed.

6) Click OK.

7) Click Repository > Save.

The way to handle target flat files is also same as described in the above sections. Just make sure that instead of Source Analyzer,Select Tools -> Target Designer. Rest is same.

Introduction to Big Data

The total volume of data stored electronically, as on 2012, 2.7 zettabytes as per Forbes.com

(A zettabyte is 10^21 bytes, or equivalently one thousand Exabyte, one million petabytes, or one billion terabytes).

Statistical Facts on Big Data :

• $300 Billion Potential annual value to US health care.
• $250 billion Potential value to Europe's Public sector administration.
• $600 Billion Potential annual consumer surplus from using personal location data globally.
• 140,000-190,000 - More deep analytical talent positions open for data savvy managers in USA during 2011.

Bulk Loading

Bulk Loading is technique  to load the data in inromatica.

You can enable bulk loading when you load to DB2, Sybase, Oracle, or Microsoft SQL Server.If you enable bulk loading for other database types, the Integration Service reverts to a normal load.The Integration Service invokes the database bulk utility and bypasses the database log. Without writing to the database log, however, the target database cannot perform roll-back you must specify a normal load for data driven sessions. When you specify bulk mode and data driven, the Integration Service reverts to normal load.For Sybase and DB2 targets, the Integration Service ignores the commit interval and commits data when the writer block is full. For Microsoft SQL Server and Oracle targets, the Integration Service commits data at each commit interval.

Note:

Do not define CHECK constraints in the database.

Do not define primary and foreign keys in the database. However, you can define primary and foreign keys for the target definitions in the Designer.

To bulk load into indexed tables, choose non-parallel mode and disable the Enable Parallel Mode option. For more information, see Relational Database Connections.

Note that when you disable parallel mode, you cannot load multiple target instances, partitions, or sessions into the same table.

To bulk load in parallel mode, you must drop indexes and constraints in the target tables before running a bulk load session. After the session completes, you can rebuild them. If you use bulk loading with the session on a regular basis, use pre- and post-session SQL to drop and rebuild indexes and key constraints.

When you use the LONG datatype, verify it is the last column in the table.

Specify the Table Name Prefix for the target when you use Oracle client 9i. If you do not specify the table name prefix, the Integration Service uses the database login as the prefix.

SCHEDULERS

We can schedule a workflow to run continuously, repeat at a given time or interval, or we can manually start a workflow. The Integration Service runs a scheduled workflow as configured.

By default, the workflow runs on demand. We can change the schedule settings by editing the scheduler. If we change schedule settings, the Integration Service reschedules the workflow according to the new settings.

• A scheduler is a repository object that contains a set of schedule settings.
• Scheduler can be non-reusable or reusable.
• The Workflow Manager marks a workflow invalid if we delete the scheduler associated with the workflow.
• If we choose a different Integration Service for the workflow or restart the Integration Service, it reschedules all workflows.
• If we delete a folder, the Integration Service removes workflows from the schedule.
• The Integration Service does not run the workflow if:
• The prior workflow run fails.
• We remove the workflow from the schedule
• The Integration Service is running in safe mode

Creating a Reusable Scheduler

• For each folder, the Workflow Manager lets us create reusable schedulers so we can reuse the same set of scheduling settings for workflows in the folder.
• Use a reusable scheduler so we do not need to configure the same set of scheduling settings in each workflow.
• When we delete a reusable scheduler, all workflows that use the deleted scheduler becomes invalid. To make the workflows valid, we must edit them and replace the missing scheduler.

Steps:

1. Open the folder where we want to create the scheduler.
2. In the Workflow Designer, click Workflows > Schedulers.
3. Click Add to add a new scheduler.
4. In the General tab, enter a name for the scheduler.
5. Configure the scheduler settings in the Scheduler tab.
6. Click Apply and OK.

Configuring Scheduler Settings

Configure the Schedule tab of the scheduler to set run options, schedule options, start options, and end options for the schedule.

There are 3 run options:

1. Run on Demand
2. Run Continuously
3. Run on Server initialization

1. Run on Demand:

Integration Service runs the workflow when we start the workflow manually.

2. Run Continuously:

Integration Service runs the workflow as soon as the service initializes. The Integration Service then starts the next run of the workflow as soon as it finishes the previous run.

3. Run on Server initialization

Integration Service runs the workflow as soon as the service is initialized. The Integration Service then starts the next run of the workflow according to settings in Schedule Options.

Schedule options for Run on Server initialization:

•  Run Once: To run the workflow just once.
•  Run every: Run the workflow at regular intervals, as configured.
•  Customized Repeat: Integration Service runs the workflow on the dates and times specified in the Repeat dialog box.

Start options for Run on Server initialization:

· Start Date

· Start Time

End options for Run on Server initialization:

• End on: IS stops scheduling the workflow in the selected date.
• End After: IS stops scheduling the workflow after the set number of
• workflow runs.
• Forever: IS schedules the workflow as long as the workflow does not fail.

Creating a Non-Reusable Scheduler

1. In the Workflow Designer, open the workflow.
2. Click Workflows > Edit.
3. In the Scheduler tab, choose Non-reusable. Select Reusable if we want to select an existing reusable scheduler for the workflow.
4. Note: If we do not have a reusable scheduler in the folder, we must
5. create one before we choose Reusable.
6. Click the right side of the Scheduler field to edit scheduling settings for the non- reusable scheduler
7. If we select Reusable, choose a reusable scheduler from the Scheduler
8. Browser dialog box.
9. Click Ok.

Points to Ponder :

• To remove a workflow from its schedule, right-click the workflow in the Navigator window and choose Unscheduled Workflow.
• To reschedule a workflow on its original schedule, right-click the workflow in the Navigator window and choose Schedule Workflow.

INDIRECT LOADING FOR FLAT FILES

Suppose, you have 10 flat files of same structure. All the flat files have same number of columns and data type. Now we need to transfer all the 10 files to same target.

Names of files are say EMP1, EMP2 and so on.

Solution1:

1. Import one flat file definition and make the mapping as per need.

2. Now in session give the Source File name and Source File Directory location of one file.

3. Make workflow and run.

4. Now open session after workflow completes. Change the Filename and Directory to give information of second file. Run workflow again.

5. Do the above for all 10 files.

Solution2:

1. Import one flat file definition and make the mapping as per need.

2. Now in session give the Source Directory location of the files.

3. Now in Fieldname use $InputFileName. This is a session parameter.

4. Now make a parameter file and give the value of $InputFileName.

$InputFileName=EMP1.txt

5. Run the workflow

6. Now edit parameter file and give value of second file. Run workflow again.

7. Do same for remaining files.

Solution3:

1. Import one flat file definition and make the mapping as per need.

2. Now make a notepad file that contains the location and name of each 10 flat files.

Sample:

D:\EMP1.txt

E:\EMP2.txt

E:\FILES\DWH\EMP3.txt and so on

3. Now make a session and in Source file name and Source File Directory location fields, give the name and location of above created file.

4. In Source file type field, select Indirect.

5. Click Apply.

6. Validate Session

7. Make Workflow. Save it to repository and run.

Workflow Variables

You can create and use variables in a workflow to reference values and record information. For example, use a Variable in a Decision task to determine whether the previous task ran properly. If it did, you can run the next task.

If not, you can stop the workflow. Use the following types of workflow variables:

•  Predefined workflow variables. The Workflow Manager provides predefined workflow variables for tasks within a workflow.
•  User-defined workflow variables. You create user-defined workflow variables when you create a workflow. Use workflow variables when you configure the following types of tasks:
•  Assignment tasks. Use an Assignment task to assign a value to a user-defined workflow variable. For Example, you can increment a user-defined counter variable by setting the variable to its current value plus 1.
•  Decision tasks. Decision tasks determine how the Integration Service runs a workflow. For example, use the Status variable to run a second session only if the first session completes successfully.
•  Links. Links connect each workflow task. Use workflow variables in links to create branches in the workflow. For example, after a Decision task, you can create one link to follow when the decision condition evaluates to true, and another link to follow when the decision condition evaluates to false.
•  Timer tasks. Timer tasks specify when the Integration Service begins to run the next task in the workflow. Use a user-defined date/time variable to specify the time the Integration Service starts to run the next task.

Use the following keywords to write expressions for user-defined and predefined workflow variables:

• AND
• OR
• NOT
• TRUE
• FALSE
• NULL
• SYSDATE

Predefined Workflow Variables:

Each workflow contains a set of predefined variables that you use to evaluate workflow and task conditions. Use the following types of predefined variables:

• Task-specific variables. The Workflow Manager provides a set of task-specific variables for each task in the workflow. Use task-specific variables in a link condition to control the path the Integration Service takes when running the workflow. The Workflow Manager lists task-specific variables under the task name in the Expression Editor.
• Built-in variables. Use built-in variables in a workflow to return run-time or system information such as folder name, Integration Service Name, system date, or workflow start time. The Workflow Manager lists built-in variables under the Built-in node in the Expression Editor.

Task-Specific Variables	Description	Task Types	Data type
Condition	Evaluation result of decision condition expression. If the task fails, the Workflow Manager keeps the condition set to null. Sample syntax: $Dec_TaskStatus.Condition = <TRUE | FALSE | NULL | any integer>	Decision	Integer
End Time	Date and time the associated task ended. Precision is to the second. Sample syntax: $s_item_summary.EndTime > TO_DATE('11/10/2004 08:13:25')	All tasks	Date/Time
ErrorCode	Last error code for the associated task. If there is no error, the Integration Service sets ErrorCode to 0 when the task completes. Sample syntax: $s_item_summary.ErrorCode = 24013. Note: You might use this variable when a task consistently fails with this final error message.	All tasks	Integer
ErrorMsg	Last error message for the associated task.If there is no error, the Integration Service sets ErrorMsg to an empty string when the task completes. Sample syntax: $s_item_summary.ErrorMsg = 'PETL_24013 Session run completed with failure Variables of type Nstring can have a maximum length of 600 characters. Note: You might use this variable when a task consistently fails with this final error message.	All tasks	Nstring
First Error Code	Error code for the first error message in the session. If there is no error, the Integration Service sets FirstErrorCode to 0 when the session completes. Sample syntax: $s_item_summary.FirstErrorCode = 7086	Session	Integer
FirstErrorMsg	First error message in the session.If there is no error, the Integration Service sets FirstErrorMsg to an empty string when the task completes. Sample syntax: $s_item_summary.FirstErrorMsg = 'TE_7086 Tscrubber: Debug info… Failed to evalWrapUp'Variables of type Nstring can have a maximum length of 600 characters.	Session	Nstring
PrevTaskStatus	Status of the previous task in the workflow that the Integration Service ran. Statuses include: 1.ABORTED 2.FAILED 3.STOPPED 4.SUCCEEDED Use these key words when writing expressions to evaluate the status of the previous task. Sample syntax: $Dec_TaskStatus.PrevTaskStatus = FAILED	All Tasks	Integer
SrcFailedRows	Total number of rows the Integration Service failed to read from the source. Sample syntax: $s_dist_loc.SrcFailedRows = 0	Session	Integer
SrcSuccessRows	Total number of rows successfully read from the sources. Sample syntax: $s_dist_loc.SrcSuccessRows > 2500	Session	Integer
StartTime	Date and time the associated task started. Precision is to the second. Sample syntax: $s_item_summary.StartTime > TO_DATE('11/10/2004 08:13:25')	All Task	Date/Time
Status	Status of the previous task in the workflow. Statuses include: - ABORTED - DISABLED - FAILED - NOTSTARTED - STARTED - STOPPED - SUCCEEDED Use these key words when writing expressions to evaluate the status of the current task. Sample syntax: $s_dist_loc.Status = SUCCEEDED	All Task	Integer
TgtFailedRows	Total number of rows the Integration Service failed to write to the target. Sample syntax: $s_dist_loc.TgtFailedRows = 0	Session	Integer
TgtSuccessRows	Total number of rows successfully written to the target. Sample syntax: $s_dist_loc.TgtSuccessRows > 0	Session	Integer
TotalTransErrors	Total number of transformation errors. Sample syntax: $s_dist_loc.TotalTransErrors = 5	Session	Integer

User-Defined Workflow Variables:

You can create variables within a workflow. When you create a variable in a workflow, it is valid only in that workflow. Use the variable in tasks within that workflow. You can edit and delete user-defined workflow variables.

Use user-defined variables when you need to make a workflow decision based on criteria you specify. For example, you create a workflow to load data to an orders database nightly. You also need to load a subset of this data to headquarters periodically, every tenth time you update the local orders database. Create separate sessions to update the local database and the one at headquarters.

Use a user-defined variable to determine when to run the session that updates the orders database at headquarters.

To configure user-defined workflow variables, complete the following steps:

1. Create a persistent workflow variable, $$WorkflowCount, to represent the number of times the workflow has run.

2. Add a Start task and both sessions to the workflow.

3. Place a Decision task after the session that updates the local orders database.Set up the decision condition to check to see if the number of workflow runs is evenly divisible by 10. Use the modulus (MOD) function to do this.

4. Create an Assignment task to increment the $$WorkflowCount variable by one.

5. Link the Decision task to the session that updates the database at headquarters when the decision condition evaluates to true. Link it to the Assignment task when the decision condition evaluates to false. When you configure workflow variables using conditions, the session that updates the local database runs every time the workflow runs. The session that updates the database at headquarters runs every 10th time the workflow runs.

Creating User-Defined Workflow Variables :

You can create workflow variables for a workflow in the workflow properties.

To create a workflow variable:

1. In the Workflow Designer, create a new workflow or edit an existing one.

2. Select the Variables tab.

3. Click Add.

4. Enter the information in the following table and click OK:

Field	Description
Name	Variable name. The correct format is $$VariableName. Workflow variable names are not case sensitive. Do not use a single dollar sign ($) for a user-defined workflow variable. The single dollar sign is reserved for predefined workflow variables
Data type	Data type of the variable. You can select from the following data types: - Date/Time - Double - Integer - Nstring
Persistent	Whether the variable is persistent. Enable this option if you want the value of the variable retained from one execution of the workflow to the next.
Default Value	Default value of the variable. The Integration Service uses this value for the variable during sessions if you do not set a value for the variable in the parameter file and there is no value stored in the repository. Variables of type Date/Time can have the following formats: - MM/DD/RR - MM/DD/YYYY - MM/DD/RR HH24:MI - MM/DD/YYYY HH24:MI - MM/DD/RR HH24:MI:SS - MM/DD/YYYY HH24:MI:SS - MM/DD/RR HH24:MI:SS.MS - MM/DD/YYYY HH24:MI:SS.MS - MM/DD/RR HH24:MI:SS.US - MM/DD/YYYY HH24:MI:SS.US - MM/DD/RR HH24:MI:SS.NS - MM/DD/YYYY HH24:MI:SS.NS You can use the following separators: dash (-), slash (/), backslash (\), colon (:), period (.), and space. The Integration Service ignores extra spaces. You cannot use one- or three-digit values for year or the “HH12” format for hour. Variables of type Nstring can have a maximum length of 600 characters.
Is Null	Whether the default value of the variable is null. If the default value is null, enable this option.
Description	Description associated with the variable.

5. To validate the default value of the new workflow variable, click the Validate button.

6. Click Apply to save the new workflow variable.

7. Click OK.