ORA-01652 Informatica
Kept getting this error when trying to query the REP_SESS_TBL_LOG view in the PowerCenter repository:
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP " in the
The stats were run (even though they were run the day before) and that didn't help.
Ran this command it fixed the issue:
pmrep>connect -r Production -d Domain_TSA_Prod -n Administrator -x xxxxxxx
Connected to repository Production in Domain_TSA_Prod as user Administrator
connect completed successfully.
pmrep>updatestatistics
updatestatistics completed successfully.
pmrep>exit
Not quite sure what it does as the PowerCenter documentation says very little on this..
===
Updates statistics for repository tables and indexes.
The command returns “updatestatistics completed successfully” or returns “updatestatistics failed” message.
The UpdateStatistics command uses the following syntax:
updatestatistics
===
Informatica on Windows
If you have a choice, please choose Linux for your PowerCenter platform. Friends don't let friends put their PowerCenter servers on Windows.
A few reasons:
1. There are many times when you must drop to a shell to take care of something with a shell script.
2. Dealing with text files in Linux is a lot easier since there are many many commands/programs for dealing with this vs. Windows Batch script. (if you can call it a scripting language...)
3. It's Windows, enough said..
p.s. Perl on Windows? Really?
File sources with multiple character delimiters
I had a file with multiple delimiters in it. ie. |~| but after running the mapping, it wasn't seeing the ~.
Here was the answer:
To use the multiple character delimiters together as a delimiter for the flat file, do the following
1. Select the Integration Service in Admin console
2. Under the Properties tab, click Edit in the Custom Properties section.
3.Under Name enter the name : MulticharFFDelimiter
4. Under Value, enter the value : Yes
5. Restart the Integration Service.
Select first rows from Teradata
To select the first 100 rows from a Teradata table:
Select top 100 * from table;
Using Parameters for FTP Connections
5. Copy the template text for the connection attributes you want to override. For example, to override the “Remote File Name” and “Is Staged” attributes, copy the following lines: $FTPConnection<VariableName>= $Param_FTPConnection<VariableName>_Remote_Filename= $Param_FTPConnection<VariableName>_Is_Staged= 6. Paste the text into the parameter file. Replace <VariableName> with the connection name, and supply the parameter values. For example: [MyFolder.WF:wf_MyWorkflow.ST:s_MySession] $FTPConnectionMyFTPConn=FTP_Conn1 $Param_FTPConnectionMyFTPConn_Remote_Filename=ftp_src.txt $Param_FTPConnectionMyFTPConn_Is_Staged=YES
Configure a PowerCenter workflow to capture the exit code of a command and use it in a Decision task
Let's say you have a command task that executes a shell script. You want to check the error code of that command task and email a success or failure task upon completion. For some reason, you can't check that actual return code, you have to substring it out of the ErrorMsg back from the Command Task. Lame Informatica.
Also, beware, KB 20394 on Informatica's Support Site is INCORRECT. There are a few things wrong with it. ie. there is no period after the ]. Also, you have tocheck for the 3rd ] as well. Here is the correct code:
Success email link:
IIF(
IIF(Instr($Command.ErrorMsg, 'with exit code [') > 0 AND Instr($Command.ErrorMsg, ']',1,3, 0) > 0,
TO_INTEGER(SUBSTR($Command.ErrorMsg,Instr($Command.ErrorMsg, 'with exit code [') + LENGTH('with exit code ['),Instr($Command.ErrorMsg, ']',1,3) - (Instr($Command.ErrorMsg, 'with exit code [') + LENGTH('with exit code ['))))
,0) > 0, FALSE,TRUE)
Failure email link:
IIF(
IIF(Instr($Command.ErrorMsg, 'with exit code [') > 0 AND Instr($Command.ErrorMsg, ']',1,3, 0) > 0,
TO_INTEGER(SUBSTR($Command.ErrorMsg,Instr($Command.ErrorMsg, 'with exit code [') + LENGTH('with exit code ['),Instr($Command.ErrorMsg, ']',1,3) - (Instr($Command.ErrorMsg, 'with exit code [') + LENGTH('with exit code ['))))
,0) > 0, TRUE,FALSE)
Sending email as someone else..
Sometimes when working with Informatica, you have to write a shell script or two. (or three..) In those scripts, you might need to send an email on failure,etc..
To do this with the standard Linux client, you would use the following syntax:
echo "body of email" | mail -s "subject" to_email@somedomain.com -- -f the_from_email@somedomain.com
The -f is the "from" email address that will appear on the recipients email they receive.
Workflow Parameters – $Param
This one always gets me. Trying to add an email address as a parameter in the Failed section of an session. Kept trying $emailaddress. Then it hit me (for the 6th time in 10 years of doing this) that you need to add $Param_ for any workflow parameter. This KB article explains it a bit more: Workflow Parameters
Informatica HA – Selecting a CFS
Upgrading to Grid/HA requires a clustered file system. Here are some costs. (my client ended up choosing the IBM solution and it's seems to be working fine)
Red Hat’s Global File System (GFS):
- GFS is supported with Informatica GRID only
- Cost for 4 Servers: $3,340 (without tax)
- Estimated Professional Services for installation: $4,000 (without tax)
IBM’s General Parallel File System (GPFS)
- GPFS is supported with Informatica GRID and GRID-HA
- Cost for 4 Servers: $17,864 (without tax).
- Professional services for implementation. Est. 1 day
Symantec’s Veritas Clustered File System (VxFS)
- VxFS is supported with Informatica GRID and GRID-HA (preferred by Informatica)
- Cost for 4 Servers: $23,000 (without tax)
- Estimated Professional Services for installation: $4,000 (without tax)
Also, once we had it live in Dev, we had awful performance with mappings that had large caches to disk. Increasing the CFS cache fixed this although 2G is huge...
maxFilesToCache=4000
pagepool=2G
maxMBpS=800
-Tom
PostgreSQL backing up
A little off topic but I was looking at different databases and how they can be backed up. Came across this nugget about PostgreSQL:
At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log describes every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint.
I've never really used PostgreSQL but that is pretty cool.