I am using 8.6 version and In our sql override there are couple of columns which are more than 4000 i.e string 4000 , we are taking a substruing and sending them as 1000 in sql override but still the data is getting rejected at source level. when i was going through the informatica potral i came across this infor will this help in resolving this issue... RR_4053 : Row error occurred while fetching data from database. This error occurs when no column value is greater than 4000 characters in this case. Cause This occurs when Informatica Integration Service is fetching an array.
Cause: This occurs when Informatica Integration Service is fetching an array.
Solution : Steps
1. In Administration Console , click on Integration Service
2. Expand Custom Properties
3. Add a property called OptimizeODBCRead with value No
4. Bounce Integration Service
5. With this flag the Integration Service uses the single row fetch instead of doing array fetch.
Wednesday, August 29, 2012
Saturday, March 10, 2012
Informatica PowerCenter Migration Checklist 7/28/2003
__ 1. Have all naming standards been used?
__ 2. Have the mappings/workflows been documented? Including:
- descriptions and text within the PowerCenter mappings and workflows;
- restart instructions; and
- special handling instructions.
__ 3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?
__ 4. Have DDL scripts moved to production? Are there additional objects/DDL to move to production for source or lookup tables?
__ 5. Optional, but critical: Have the business users reviewed the test data and approved the results?
__ 6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?
__ 7. Have reasonable error checks been coded for in the error handling routines?
__ 8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.
__ 9. Has the fail parent option been checked?
__ 10. Have all test settings such as “collect performance data” been disabled?
__ 11. Are there sequence generators that need the current values adjusted? What should the current value be?
__ 12. Have any reusable objects been used? Have they been documented?
__ 13. Have post-session emails been set up and are the proper email addresses used?
(Indicate whether a failure is critical or not.)
__ 14. Have relational connections been reviewed and are they correct?
__ 15. Have mapping and session parameters and variables been reviewed, values set?
__ 16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?
__ 17. Have the session failure variables been reviewed?
__ 2. Have the mappings/workflows been documented? Including:
- descriptions and text within the PowerCenter mappings and workflows;
- restart instructions; and
- special handling instructions.
__ 3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?
__ 4. Have DDL scripts moved to production? Are there additional objects/DDL to move to production for source or lookup tables?
__ 5. Optional, but critical: Have the business users reviewed the test data and approved the results?
__ 6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?
__ 7. Have reasonable error checks been coded for in the error handling routines?
__ 8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.
__ 9. Has the fail parent option been checked?
__ 10. Have all test settings such as “collect performance data” been disabled?
__ 11. Are there sequence generators that need the current values adjusted? What should the current value be?
__ 12. Have any reusable objects been used? Have they been documented?
__ 13. Have post-session emails been set up and are the proper email addresses used?
(Indicate whether a failure is critical or not.)
__ 14. Have relational connections been reviewed and are they correct?
__ 15. Have mapping and session parameters and variables been reviewed, values set?
__ 16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?
__ 17. Have the session failure variables been reviewed?
Wednesday, November 9, 2011
SQL Server Date Formats
SQL Server Date Formats
One of the most frequently asked questions in SQL Server forums is how to format a
datetime value or column into a specific date format. Here's a summary of
the different date formats that come standard in SQL Server as part of the CONVERT
function. Following the standard date formats are some extended date
formats that are often asked by SQL Server developers.
It is worth to note that the output of these date formats are of VARCHAR data
types already and not of DATETIME data type. With this in mind,
any date comparisons performed after the datetime value has been formatted are
using the VARCHAR value of the date and time and not its original DATETIME
value.
| Standard Date Formats | |||
| Date Format | Standard | SQL Statement | Sample Output |
| Mon DD YYYY 1
HH:MIAM (or PM) |
Default | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Jan 1 2005 1:29PM 1 |
| MM/DD/YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] | 11/23/98 |
| MM/DD/YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] | 11/23/1998 |
| YY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] | 72.01.01 |
| YYYY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] | 1972.01.01 |
| DD/MM/YY | British/French | SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] | 19/02/72 |
| DD/MM/YYYY | British/French | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | 19/02/1972 |
| DD.MM.YY | German | SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] | 25.12.05 |
| DD.MM.YYYY | German | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] | 25.12.2005 |
| DD-MM-YY | Italian | SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] | 24-01-98 |
| DD-MM-YYYY | Italian | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | 24-01-1998 |
| DD Mon YY 1 | - | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] | 04 Jul 06 1 |
| DD Mon YYYY 1 | - | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | 04 Jul 2006 1 |
| Mon DD, YY 1 | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] | Jan 24, 98 1 |
| Mon DD, YYYY 1 | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Jan 24, 1998 1 |
| HH:MM:SS | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | 03:24:53 |
| Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 | Default +
milliseconds |
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | Apr 28 2006 12:32:29:253PM 1 |
| MM-DD-YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] | 01-01-06 |
| MM-DD-YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | 01-01-2006 |
| YY/MM/DD | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] | 98/11/23 |
| YYYY/MM/DD | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] | 1998/11/23 |
| YYMMDD | ISO | SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] | 980124 |
| YYYYMMDD | ISO | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] | 19980124 |
| DD Mon YYYY HH:MM:SS:MMM(24h) 1 | Europe default + milliseconds | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 28 Apr 2006 00:34:55:190 1 |
| HH:MI:SS:MMM(24H) | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] | 11:34:23:013 |
| YYYY-MM-DD HH:MI:SS(24h) | ODBC Canonical | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | 1972-01-01 13:42:24 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) | ODBC Canonical
(with milliseconds) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 1972-02-19 06:35:24.489 |
| YYYY-MM-DDTHH:MM:SS:MMM | ISO8601 | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | 1998-11-23T11:25:43:250 |
| DD Mon YYYY HH:MI:SS:MMMAM 1 | Kuwaiti | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | 28 Apr 2006 12:39:32:429AM 1 |
| DD/MM/YYYY HH:MI:SS:MMMAM | Kuwaiti | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | 28/04/2006 12:39:32:429AM |
Here are some more date formats that does not come standard in SQL Server as
part of the CONVERT function.
| Extended Date Formats | ||
| Date Format | SQL Statement | Sample Output |
| YY-MM-DD |
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] |
99-01-24 |
| YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] |
1999-01-24 |
| MM/YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] |
08/99 |
| MM/YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] | 12/2005 |
| YY/MM | SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] | 99/08 |
| YYYY/MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] | 2005/12 |
| Month DD, YYYY 1 | SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9)
AS [Month DD, YYYY] |
July 04, 2006 1 |
| Mon YYYY 1 | SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] | Apr 2006 1 |
| Month YYYY 1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS
[Month YYYY] |
February 2006 1 |
| DD Month 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS
[DD Month] |
11 September 1 |
| Month DD 1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS
[Month DD] |
September 11 1 |
| DD Month YY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' '
+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] |
19 February 72 1 |
| DD Month YYYY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' '
+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] |
11 September 2002 1 |
| MM-YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] |
12/92 |
| MM-YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] | 05-2006 |
| YY-MM | SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] |
92/12 |
| YYYY-MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] | 2006-05 |
| MMDDYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] | 122506 |
| MMDDYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] | 12252006 |
| DDMMYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] | 240702 |
| DDMMYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] | 24072002 |
| Mon-YY 1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS
[Mon-YY] |
Sep-02 1 |
| Mon-YYYY 1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS
[Mon-YYYY] |
Sep-2002 1 |
| DD-Mon-YY 1 | SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] | 25-Dec-05 1 |
| DD-Mon-YYYY 1 | SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] | 25-Dec-2005 1 |
1 To make the month name in upper case, simply use
the UPPER string function.
Wednesday, March 10, 2010
EBCDIC file handling
Sourcing the EBCDIC file from mainframes to Informatica can be a harder job! Verifying and managing the Packed decimals and the zoned decimals are as easy as the normal flat file handling. You should be in a position to know various flat file options to have it done!
In informatica to read an EBCDIC file VSAM source file strucure is used. Where we can prepare EBCDIC file from the flat file by the tool called 'Ultraedit'. To prepare from ASCII, first need to convert to EBCDIC format. Then to remove null spaces convert it to hex mode and serch and remove for "0D 25" with null. After that again return it to EBCDIC mode and this file with .TRX extension you can use for testing purpose.
With Regards, Sanket
Using a well known file enditor VEDIT ,the EBCDIC file can directly be edited without translating it and then can be transferred back to a mainframe computer. For transfering to a PC or a UNIX system particularly to a database such as SQL or MS Access, using VEDIT, the EBCDIC file can be translated to ASCII before transfer. The EBCDIC files that do not contain packed-decimal fields are particularly easy to translate to ASCII using the VEDIT.
Informatica Metadata Repository tables
While browsing through the Informatica rep and opb tables, we are usually stuck up as we do not understand what the widget_id and widget_types are for.
Below is the list of widget_types. This data would help us ease the handling of metadata.
Widget Ids and transformation types
widget_type
Type of transformation
1Source
2Target
3Source Qualifier
4Update Strategy
5expression
6Stored Procedures
7Sequence Generator
8External Procedures
9Aggregator
10Filter
11Lookup
12Joiner
14Normalizer
15Router
26Rank
44mapplet
46mapplet input
47mapplet output
55XML source Qualifier
80Sorter
97Custom Transformation
What are widget IDs and why do we require them?
Informatica maintains metedata regarding the mappings and its tranformations, sessions, workflows and their statistics. These details are maintained in a set of tables called OPB tables and REP tables.
The widget refers to the types of transformation details stored in these tables.
Port types in a transformation
As the above section details, widget is a transformation in metadata tables. To get the port type from repository table, below is the SQL snippet to use
select a.widget_id, decode(a.porttype, 1, 'INPUT',
3, 'IN-OUT',
2, 'OUT',
32, 'VARIABLE',
8, 'LOOKUP',
10, 'OUT-LOOKUP',
to_char(a.porttype)) Port_Type
from opb_widget_field a;
If you want to know the mapping name, then match the widget_id against the widget_id of opb_widget_inst and then pull the mapping_id which can be mapped against mapping_id in opb_mappings table. If you want to know the Folder name, then map the subject_id from opb_mappings to that of subj_id in OPB_SUBJECTS table to get the subject_name.
Expressions and SQL overrides in a transformation
OPB_EXPRESSION is the table that stores all the expressions in metadata.
To associate an expression to a field in a transformation, OPB_WIDG_EXPR is the table to be used.
select g.expression
from opb_widget_expr f,
opb_expression g
where f.expr_id = g.expr_id
SQL overrides can be in Source Qualifiers and Lookup transformations.
To get the SQL Override from metadata, check REP_WIDGET_ATTR.ATTR_VALUE column.
Below is the list of widget_types. This data would help us ease the handling of metadata.
Widget Ids and transformation types
widget_type
Type of transformation
1Source
2Target
3Source Qualifier
4Update Strategy
5expression
6Stored Procedures
7Sequence Generator
8External Procedures
9Aggregator
10Filter
11Lookup
12Joiner
14Normalizer
15Router
26Rank
44mapplet
46mapplet input
47mapplet output
55XML source Qualifier
80Sorter
97Custom Transformation
What are widget IDs and why do we require them?
Informatica maintains metedata regarding the mappings and its tranformations, sessions, workflows and their statistics. These details are maintained in a set of tables called OPB tables and REP tables.
The widget refers to the types of transformation details stored in these tables.
Port types in a transformation
As the above section details, widget is a transformation in metadata tables. To get the port type from repository table, below is the SQL snippet to use
select a.widget_id, decode(a.porttype, 1, 'INPUT',
3, 'IN-OUT',
2, 'OUT',
32, 'VARIABLE',
8, 'LOOKUP',
10, 'OUT-LOOKUP',
to_char(a.porttype)) Port_Type
from opb_widget_field a;
If you want to know the mapping name, then match the widget_id against the widget_id of opb_widget_inst and then pull the mapping_id which can be mapped against mapping_id in opb_mappings table. If you want to know the Folder name, then map the subject_id from opb_mappings to that of subj_id in OPB_SUBJECTS table to get the subject_name.
Expressions and SQL overrides in a transformation
OPB_EXPRESSION is the table that stores all the expressions in metadata.
To associate an expression to a field in a transformation, OPB_WIDG_EXPR is the table to be used.
select g.expression
from opb_widget_expr f,
opb_expression g
where f.expr_id = g.expr_id
SQL overrides can be in Source Qualifiers and Lookup transformations.
To get the SQL Override from metadata, check REP_WIDGET_ATTR.ATTR_VALUE column.
Sunday, March 7, 2010
Differences between Advanced External Procedure and External Procedure Transformations
Advanced External Procedure Transformation
Advanced External Procedure transformation is an Active and Connected transformation. It operates in conjunction with procedures, which are created outside of the Designer interface to extend PowerCenter/PowerMart functionality. It is useful in creating external transformation applications, such as sorting and aggregation, which require all input rows to be processed before emitting any output rows.
External Procedure Transformation
External Procedure transformation is an Active and Connected/UnConnected transformations. Sometimes, the standard transformations such as Expression transformation may not provide the functionality that you want. In such cases External procedure is useful to develop complex functions within a dynamic link library (DLL) or UNIX shared library, instead of creating the necessary Expression transformations in a mapping.
Differences between Advanced External Procedure and External Procedure Transformations:
External Procedure returns single value,
whereas Advanced External Procedure returns multiple values.
External Procedure supports COM and Informatica procedures
whereas AEP supports only Informatica Procedures.
Advanced External Procedure transformation is an Active and Connected transformation. It operates in conjunction with procedures, which are created outside of the Designer interface to extend PowerCenter/PowerMart functionality. It is useful in creating external transformation applications, such as sorting and aggregation, which require all input rows to be processed before emitting any output rows.
External Procedure Transformation
External Procedure transformation is an Active and Connected/UnConnected transformations. Sometimes, the standard transformations such as Expression transformation may not provide the functionality that you want. In such cases External procedure is useful to develop complex functions within a dynamic link library (DLL) or UNIX shared library, instead of creating the necessary Expression transformations in a mapping.
Differences between Advanced External Procedure and External Procedure Transformations:
External Procedure returns single value,
whereas Advanced External Procedure returns multiple values.
External Procedure supports COM and Informatica procedures
whereas AEP supports only Informatica Procedures.
Difference between Connected and UnConnected Lookup Transformation
Difference between Connected and UnConnected Lookup Transformation:
Connected lookup receives input values directly from mapping pipeline
whereas UnConnected lookup receives values from: LKP expression from another transformation.
Connected lookup returns multiple columns from the same row
whereas UnConnected lookup has one return port and returns one column from each row.
Connected lookup supports user-defined default values
whereas UnConnected lookup does not support user defined values.
Connected lookup receives input values directly from mapping pipeline
whereas UnConnected lookup receives values from: LKP expression from another transformation.
Connected lookup returns multiple columns from the same row
whereas UnConnected lookup has one return port and returns one column from each row.
Connected lookup supports user-defined default values
whereas UnConnected lookup does not support user defined values.
Informatica 7 vs 8 (architecture level)
The architecture of Power Center 8 has changed alot;
PC8 isservice-oriented for modularity, scalability and flexibility.
The Repository Service and Integration Service (as replacementfor Rep Server and Informatica Server) can be run on different computers in a network (so called nodes), even redundantly.
Management is centralized, that means services can be startedand stopped on nodes via a central web interface.
Client Tools access the repository via that centralized machine,resources are distributed dynamically.
Running all services on one machine is still possible, ofcourse.
PC8 isservice-oriented for modularity, scalability and flexibility.
The Repository Service and Integration Service (as replacementfor Rep Server and Informatica Server) can be run on different computers in a network (so called nodes), even redundantly.
Management is centralized, that means services can be startedand stopped on nodes via a central web interface.
Client Tools access the repository via that centralized machine,resources are distributed dynamically.
Running all services on one machine is still possible, ofcourse.
Differences between informatica 6,7,8 versions (application level)
6 to 7
union transformation
lookup on flat files
we will connect workflow through designer itself
7 to 8
java transformation
sql transformation
HTML transformation
8 version contains integration services
many source files are added like
webservices,
tibco,
webmethods
user defined functions ...
union transformation
lookup on flat files
we will connect workflow through designer itself
7 to 8
java transformation
sql transformation
HTML transformation
8 version contains integration services
many source files are added like
webservices,
tibco,
webmethods
user defined functions ...
Thursday, November 6, 2008
Unable to connect to SQLServer 2005 on repository
Change the authentication of the SQL Server to "SQL Server and Windows" (also known as "mixed mode").
OR
Use a different network library.
Go to SQL Server Configuration Manager > Protocols for MSSQLSERVER > enable TCP/IP
and restart SQL server services
OR
Use a different network library.
Go to SQL Server Configuration Manager > Protocols for MSSQLSERVER > enable TCP/IP
and restart SQL server services
Subscribe to:
Posts (Atom)