Tip 1 : Ignore the SQ SQL Override conditionally
It is possible by defining a mapping parameter for the WHERE clause of the SQL Override. When you need all records from the source, define this parameter as 1=1 in theparameter file and in case you need only selected data, set the parameter accordingly.
Tip 2 : Overcome size limit for a SQL Override in a PowerCenter mapping
The SQL editor for SQL query overrides has a limit of maximum of 32,767 characters.
To overcome this we can do following
To source a SQL of more than 32,767 characters do the following:
1. Create a database view using the SQL query override.
2. Create a Source Definition based on this database view.
3. Use this new Source Definition as the source in the mapping
Tip 3. :Export an entire Informatica folder to a xml file
We can do this in 8.1.1,
1) In designer Select Tools -> Queries and create a newquery. Set the Parameter Name "Folder" equal to the Folder you want to export and then run the query.
2) In the Query Results window, choose Edit -> Select All Then select Tools -> Export to XML File andenter a file name and location. Full Folder willbe exported to an XML file.
We can also use the query tool in Repository Manager, to geteverything in the folder (mappings, sessions, workflows, etc.)
Tip 4 : Validate all mappings in a folder
We can validate all mappings in a folder in following way:
1. Go to the Repository manager client
2. Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:
folder = folder-name
object type = mapping
Use the following Pmrep Execute query command to get persistent output file:
executequery -q[-u ] [-a (append)]
We can write the result to a persistent output file.If the query is successful, it returns the total number of qualifying records.We can use newly created persistent output file as a input file in the following pmrep validate command :
Pmrep validate-i}[-p [-u ] [-a (append)][-b (verbose)]
Tip 5 : If you are getting following error
CMN_1022 [
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed[Microsoft][ODBC Excel Driver]Optional feature not implemented
Database driver error...
Function Name : SetConnectOption
Database driver error...
Function Name : AutoCommitOff]
Solution :to make an entry of excel ODBC in powermart.ini file in informatica folder
If you need some more tips please mail us at support@itnirvanas.com.My Profile
It is possible by defining a mapping parameter for the WHERE clause of the SQL Override. When you need all records from the source, define this parameter as 1=1 in theparameter file and in case you need only selected data, set the parameter accordingly.
Tip 2 : Overcome size limit for a SQL Override in a PowerCenter mapping
The SQL editor for SQL query overrides has a limit of maximum of 32,767 characters.
To overcome this we can do following
To source a SQL of more than 32,767 characters do the following:
1. Create a database view using the SQL query override.
2. Create a Source Definition based on this database view.
3. Use this new Source Definition as the source in the mapping
Tip 3. :Export an entire Informatica folder to a xml file
We can do this in 8.1.1,
1) In designer Select Tools -> Queries and create a newquery. Set the Parameter Name "Folder" equal to the Folder you want to export and then run the query.
2) In the Query Results window, choose Edit -> Select All Then select Tools -> Export to XML File andenter a file name and location. Full Folder willbe exported to an XML file.
We can also use the query tool in Repository Manager, to geteverything in the folder (mappings, sessions, workflows, etc.)
Tip 4 : Validate all mappings in a folder
We can validate all mappings in a folder in following way:
1. Go to the Repository manager client
2. Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:
folder = folder-name
object type = mapping
Use the following Pmrep Execute query command to get persistent output file:
executequery -q
We can write the result to a persistent output file.If the query is successful, it returns the total number of qualifying records.We can use newly created persistent output file as a input file in the following pmrep validate command :
Pmrep validate-i
Tip 5 : If you are getting following error
CMN_1022 [
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed[Microsoft][ODBC Excel Driver]Optional feature not implemented
Database driver error...
Function Name : SetConnectOption
Database driver error...
Function Name : AutoCommitOff]
Solution :to make an entry of excel ODBC in powermart.ini file in informatica folder
If you need some more tips please mail us at support@itnirvanas.com.My Profile
