why we use stored procedure transformation?
- 4 Answers
- 7538 Views
- IBM, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
The Stored Procedure Transformation is used to call the
stored procedure.
First, we have to create a Stored procedure in Oracle or SQL
Server.It have 2 types.
one is Connected Stored Procedure and another one is
Unconnected Stored Procedure.
| Is This Answer Correct ? | 5 Yes | 0 No |
Answer / vas.chiky
for complex results, which we cannot obtain in SQL or from
informatica transformation, we go for procedures.
Also doing the calculation in database level doesnt makes
work load high for our informatica server.
process will be given to database.
| Is This Answer Correct ? | 3 Yes | 0 No |
Answer / abhinaw prakash
Valid Stored Procedure Use cases
Complex Calculations
If we have a complex calculation that needs to be done for
each record repeatedly we may use stored procedure
transformation. Complex statistical calculations where a
certain measure is being calculated for each of the record
is a good example where one may want to use this.
Performance
There are cases where you may want to use this to improve
your performance. Let’s say one calculation requires
accessing or looking up multiple records or values from
several different tables. If you were to perform this
calculation in Informatica, you will have to perform
multiple lookup operations in many tables. Depending upon
the types of your lookup, this may involve caching the
entire data or at least making multiple network level calls
to transport these data over the network layer. Instead, if
we use a stored procedure transformation, we can perform the
entire calculation within the database, thereby, eliminating
any need to transport data over the network.
Security
Although rare but sometimes client may have a proprietary
algorithm implemented in database. Client does not want to
let others know about the actual algorithm in use, however,
wishes that we take advantage of this algorithm. If the
algorithm is implemented in database stored procedure and
proper access restrictions are placed, then we will not be
able to read the SP to see the algorithm, but still can call
this from Informatica to take the advantage of the
algorithm. Example, proprietary trading algorithms in banks
and brokerage farms.
Reusability
Reusability is a reason why you may want to use a stored
procedure transformation. Suppose you have already written a
complex code in database programming language, tested the
code exhaustively and you are pleased with the performance
of this piece of code. Now that your Organization is
implementing Informatica, you may not wish to rewrite this
entire piece of code in Informatica again. You can reuse
your existing code through stored procedure transformation.
| Is This Answer Correct ? | 3 Yes | 0 No |
where does the default group roew in a router is stored what is the default size of memory in router
What are the scheduling options to run a sesion?
How to use procedural logic inside infromatica?
What is hash partition?
What is a data modeling?
Can you use flat files in Mapplets.
write s sql query following table some duplicate present i want unique one column duplicate another column display? name id a 1 a 1 b 2 b 2 c 3 i want the required output like unique duplicate name id name id a 1 a 1 b 2 b 2 c 3
There are 2 files, Master and User. We need to compare 2 files and prepare a output log file which lists out missing Rolename for each UserName between Master and User file. Please find the sample data- MASTER.csv ---------- Org|Tmp_UsrID|ShortMark|Rolename ---|---------|----------|------------ AUS|0_ABC_PW |ABC PW |ABC Admin PW AUS|0_ABC_PW |ABC PW |MT Deny all GBR|0_EDT_SEC|CR Edit |Editor GBR|0_EDT_SEC|CR Edit |SEC MT103 GBR|0_EDT_SEC|CR Edit |AB User USER.csv -------- Org|UserName|ShortMark|Rolename ---|--------|---------|------------ AUS|charls |ABC PW |ABC Admin PW AUS|amudha |ABC PW |MT Deny all GBR|sandya |CR Edit |Editor GBR|sandya |CR Edit |SEC MT103 GBR|sandya |CR Edit |AB User GBR|sarkar |CR Edit |Editor GBR|sarkar |CR Edit |SEC MT103 Required Output file: --------------------- Org|Tmp_UsrID|UserName|Rolename |Code ---|---------|--------|------------|-------- AUS|0_ABC_PW |charls |ABC Admin PW|MATCH AUS|0_ABC_PW |charls |MT Deny all |MISSING AUS|0_ABC_PW |amudha |ABC Admin PW|MISSING AUS|0_ABC_PW |amudha |MT Deny all |MATCH GBR|0_EDT_SEC|sandya |Editor |MATCH GBR|0_EDT_SEC|sandya |SEC MT103 |MATCH GBR|0_EDT_SEC|sandya |AB User |MATCH GBR|0_EDT_SEC|sarkar |Editor |MATCH GBR|0_EDT_SEC|sarkar |SEC MT103 |MATCH GBR|0_EDT_SEC|sarkar |AB User |MISSING Both the files are mapped through Organization, Shor_mark. So, based on each Organization, Short_Mark, for each UserName from User.csv, we need to find the Matching and Missing Rolename. I am able to bring Matching records in the output. But really I don't find any concept or logic to achieve "MISSING" records which are present in Master and not in User.csv for each UserName. Please help out guys. Let me know if you need any more information. Note:- In User.csv file, there are n number of Organization, under which n number Shortmark comes which has n number of UserName.
Please let me know how to make encryption and decryption with example?
What are active and passive transformations?
wt is the difference between truncate and delete in which situation u use delete and truncate in real time..
CANNOT USE PARAMETER FILE! Hi all, I am trying to use parameter file for my workflow. This could help me to filter records where CITY = 'Portland' Following is what I have done: **in Designer - create new parameter : $$PARA_FIL, Parameter, String, IsExprVar=TRUE, Initial value = [empty] - Source Qualifier/ Properties/Source Filter: CUSTOMERS.CITY='$$PARA_FIL' **Create Parameter file: C:\Informatica\PowerCenter8.6.0 \server\infa_shared\BWParam\DynamicParamTest.txt $$PARA_FIL='Portland' **Configure workflow to use the parameter file: Edit Workflow/Properties/Parameter Filename: C:\Informatica\PowerCenter8.6.0 \server\infa_shared\BWParam\DynamicParamTest.txt I also configured directory of parameter file for session task. However, I just got this in the session log: [SQ_CUSTOMERS] SQL Query [SELECT CUSTOMERS.CUSTOMER_ID FROM CUSTOMERS WHERE CUSTOMERS.CITY='$$PARA_FIL'] No record has been loaded to target. It seems that the parameter file has not been read. I cannot understand the reason why. Could any of you kindly suggest me anything? Thanks
Teradata 
