2 Years ago, my Dell Studio laptop was shipped with Vista. Honestly, I was never terribly upset by Vista's performance except for its software incompatibilities for many major enterprise applications, at least w.r.t what I use. However, I tried to manage to install some of the applications like OBIEE, Teradata and Informatica (7.1 and 8.6), but with much effort involved with some tweaks and tricks. Luckily, Oracle had released Vista and Windows 7 compatible Oracle 10g Database which I used as Database platform for many applications.

For sometime, I even used XP in VM and used it for some R&D stuff - trying out different Oracle versions, and many others applications, but it was mainly used for very quick, R&D, referencing works but not for using it long hours. Reason being, VM was terribly slow on my Intel Centrino 4GB RAM laptop with Vista as Host OS!!

I had preferred XP as standalone OS that in VM, but XP can't (an older version) be installed easily with dual booting besides to Vista (which is a newer windows version). At one point I even wanted to wipe-off my Vista and install XP on laptop, but I wanted to continue as my warranty was still there, and there are bunch of softwares in Vista that me and my wife use and also I found it difficult to take backup of 320 Gig data.

Now, I couldn't hold it any longer. I finally ventured to install XP beside to Vista with dual booting. Steps you can follow...

I had only 1 partition (C:\ drive) with 300G and that was Vista's home. Dell shipped it that way. Of that, I wanted to allocate 50G specifically for XP.

1. So, I cleared some free space in C:\ and defragmented with free space optmization so all the available freespace on C: comes to tail end of it.
For this, I tried to use Windows Disk Defragmentor but that didn't help. One important reason being that system files can't be moved by Windows Defragmentor. So, use a third party defragmenting sofware - I used PerfectDisk - it works great. You need to disable Hibernation and Paging and do boot enabled defragmentation in PerfectDisk so that all the system files also would get moved.

2. Use the Windows Disk Management (under Computer Mangement) and shrink the C: drive and specify the size how much you want to shrink. In my case I wanted to shrink it from 300G to 250G. Doing so will leave 50G space as raw non-partitioned file system.

3. You need to create a new logical drive (D: in my case) using the 50G free space.

Install Windows XP with using a bootable CD (I used my XP SP3 CD). Here a challenge blows out. During the installation, while file copying in progress and even before accepting License Agreement, machine goes into blue screen. Problem here is, the recent HDD are SATA enabled, and Windows XP does not usually come with SATA drivers and XP does not recognize your HDD and hence the blue screen.

There are difference ways doing this-
1. Use a bootable floppy having SATA drivers and at the start of XP installation press F6 so it picks the drivers from Floppy disk. This F6Floppy option does not work for me as mine is laptop and floppy diskette is out of discussion. There seem a workaround using USB drive for Floppy drive, however I didn't try that.

2. Slipstreaming the SATA drivers using nLite. You can burn a bootable XP CD by integrating the SATA drivers that downloaded from vendor website. This link will help how to proceed with this step further.
Unfortunately, I have a problem with my CD/DVD writer and couldn't burn a new disk. So, I finally ventured with 3 option as described below. In nutshell, here I install XP first with compatible drivers and update the drivers to SATA and then re-enable HDD for SATA.

3. Go to BIOS setup and change HDD type from SATA to IDE.

4. Proceed with XP installation - there will not be any blue screen as IDE drivers are known to typical XP software bundle.

Here, there would be 2 issues you need to fix:

a)
- Note that when you install XP, already existing Vista's bootloader gets lost which means your machine behaves as if there is only one operating system to boot and directly boots up from XP, without even showing up dual boot menu at startup.
- So, let it first boot using XP, then use software like EasyBCD to recover the lost Vista boot record. With EasyBCD, you need to search for boot record available in C: drive (Vista home in my case) and add that to existing boot loader. You can get more details here. Doing this enabled Dual boot for Vista and XP.

b)
Another issue is re-enabling SATA. If I don't do this, Vista would not work - it blue screens out :). So, go to device manager and update the HDD drivers using the SATA drivers you have or downloaded. There are some cool instructions here on how to do this.

5. At this point your XP know how to support both SATA and IDE. So, restart the machine with SATA enabled back this time in the BIOS setup.

6. You should now be able to boot from XP as well as Vista

If anyone wants to give whole thing a try, and if you think I can be of any help, let me know...

Karteek

This post has been in my drafts for quite a long time. I noticed it today and it was almost ready to get published. So, with very minor changes, I am posting it today. Couple of months ago, I had setup 10g OEM in my laptop to try and explore it, and below explained were some challenges I had experienced. Hope they help...

(1)
Symptoms
- Oracle Enterprise Manager (null) - in the browser title
- Enterprise Manager is not able to connect to database instance - error message

OEM uses SYSMAN account to monitor a database.

Changing SYSMAN user's password is not a straight forward approach. We can straight away go ahead and change its password, but OEM can't recognize the new password. When OEM is deployed, at the time of dbconsole creation, SYSMAN's password gets captured in OEM's property file in encrypted format, and we have to fix that file as well when we decide to change SYSMAN password.

A googling of this issue helped with many sources...one I followed one is below

http://forums.oracle.com/forums/thread.jspa?threadID=368185

In the discussion happened in above link, it was suggested to change
$ORACLE_HOME/host_sid/sysman/config/emoms.properties file with new password information without encryption. However, after we restart the dbconsole, "emoms.properties" file gets recreated automatically with encryption enabled back.

(2)
dbconsole: java.lang.exception: no such metric

By now, with the above fix described in (1), OEM was able to connect to the database, but the metric information was still not available. Reason being was similar to SYSMAN issue - OEM was unable to connect to Database using DBSNMP user. So, just make sure you provide the correct login details for DBSNMP (unlock it, if locked and setup with new password in "Monitoring Configuration"). Below link from Don Burleson helps...

http://www.dba-oracle.com/sf_error_dbconsole_java_lang_exception_no_such_metric.htm

- Karteek

Those who want to try on your personal machines, here are the steps.

Note that this procedure is just for your lab or personal usage, and certainly not recommended in most of live scenarios.

Here is the download link for the document…

https://cid-12735760bc8e6854.skydrive.live.com/self.aspx/Shared%20Resources/PowerCenter%208.6%20Installtion%20and%20Configuration.pdf

Karteek

At this moment, I am still only interested in Teradata’s external loading capabilities like FastLoad, Tpump, MultiLoad etc… and on how to use them in Informatica. Following are the steps to load data into a table using FastLoad external tool.

• Create a Loader connection in Workflow Manager (Connections > Loader > New)

• TDPID (Teradata Database ID) is important – you can get this from your Windows hosts file.The CLI software looks for this TDPID in the /etc/hosts file and will
  automatically strip cop1 from the name and use the TDPE as the system
  identifier (or whatever other name you use). My hosts name contains looks as below…

# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

127.0.0.1       localhost  localtdcop1
::1             localhost

So, after stripping cop1 from “localtdcop1”, now the TDPID is “localtd”.

• I chose Truncate target table option so FastLoad truncates the target table before it loads any data.
• I want to load the data into a named flat file first and then into actual target table through FastLoad. So, I have enabled “Is Staged” option. Else Integration Service would use a named pipe means you can not view the data, like you can view with “Is Staged”option, after the load is completed.
• In the target properties, select “File Writer” as Writer type instead of “Relation Writer”.
• Select Connection type as “Loader” and chose the above create Loader connection

I will give a try with other features and utilities and try to post them here…

Karteek

You know I am still living with Vista :) Somehow I am managing it, though at times it becomes hard to work with it. Vista is cool, but other software's compatible with Vista…just a bit hard to find, and even if you find it, it would be hard to make it work with Vista.

I wanted to explore Teradata and so recently downloaded the latest Teradata 13 from www.teradata.com/downloadcenter/. Great news Teradata 13 officially supports Vista, and even Windows 7 – really cool!

It all worked for me…I could able able to query the tables using SQL Assistant well. But I experienced some issue with Teradata ODBC – driver was not getting loaded. When I use a DSN created using Teradata ODBC driver, it was throwing some error like “Specified driver could not be loaded due to system error 126”

Below error screen shot was when I used DataDirect driver. I had the same issue even with Teradata ODBC driver (I think Teradata either does not have a native driver or it recommends its ODBC driver – I could not see a Teradata native driver one like Oracle’s)

It appears it could not able to locate the actual driver file. But it is not – I verified the registry and it actually was pointing to a correct file.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Teradata  =>  C:\Program Files\Teradata\Client\13.0\ODBC Driver for Teradata\Lib\tdata32.dll

After doing some research and googling, I realized that it actually not the driver but some of its supporting files causing this issue. There are few .dll files at the below location which perhaps were not getting loaded along with driver file.

C:\Program Files\Teradata\Client\13.0\Shared ICU Libraries for Teradata\lib

icudttd36l.dll
icuintd36.dll
icuiotd36.dll
iculetd36.dll
iculxtd36.dll
icuuctd36.dll

I added this location to the system path and it finally worked. In your case, if it does not work just by adding it to the path, copy the .dll files to windows\system32.

Hope it saves sometime when you encounter the similar issue :)

Update (04/25/2010):

It seems Teradata has its own CLI (Call-Level Interface) for clients to be able to connect database server via network. Following methods are supported by Teradata in the Network.

• NET Data Provider for Teradata
• Java Database Connectivity (JDBC)
• OLE DB Provider for Teradata
• Open Database Connectivity (ODBC)
• Teradata CLIv2 for Network Attached Systems (also available for Channel Attached Systems)

Teradata documentation says that the client applications can use the following APIs to communicate with Teradata Database:

• NET Data Provider
• Java Database Connectivity (JDBC)
• Open database Connectivity (ODBC)

Notice that there is no Teradata CLI to use it with a client.

Update (04/26/2010):

Few of the Teradata client tools like BTEQ and FastLoad are also experiencing the similar driver locating problem. Add the location “C:\Program Files\Teradata\Client\13.0\CLIv2” to your problem.

Problem Symptom: When you open FastLoad an error dialog box is thrown saying that “This application has failed to start because wincli32.dll was not found” and suggests to reinstall the software to fix the same. Please do not reinstall, I tried and it did not work. Like I said, just update your user/system path.

Karteek

It is same as on Vista! I did this yesterday on my friend's machine shipped with Windows 7.

http://karteekblog.blogspot.com/2009/12/powercenter-860-on-vista.html

Karteek

Yesterday I gave Developer certification successfully. Overall it was a good experience.

I was recently going through my.informatica.com site to find some interesting tool set called “Debugging Tools” from Informatica. There is a tool called “MetaQuery 2.0”, a java window application, that connects to PowerCenter repository database and fetches some key properties of various repository related objects. As my-informatica is for customers & partners, I can not give the site contents directly or MetaQuery tool for downloading here (it may be classified).  However, I am just pasting the description provided there for this tool below.
Metaquery 2.0
“Metaquery is an Informatica tool to query against PowerCenter repository for some of the key properties in session, workflow, and mapping objects. This tool is helpful to gather information regarding properties set in any of these objects (like session or workflow) and get the output report exported into a readable excel sheet or text file. The latest version supports repositories in DB2 and Sybase in addition to Oracle repositories. More properties in session and workflow are queries upon as well.”
I think this certainly is a useful tool to have for repository analysis and debugging. This tool is not exposing the queries it is using to fetch the data (in any readable format), but you are allowed only to choose the options on GUI (for example, sessions with target table enabled option) and submit. I usually write some queries on repository with all sort of reverse engineering, but as the underlying architecture/database design is no where documented it is difficult at times to get the requirement done properly and accurately.
Some screen shots of tool:



When I saw this tool first time on my-informatica site, I felt happy that it would reveal some facts about how some repository tables are related and logic behind using them. But it was not the case as the tool revealed only usage options from GUI, and all the underlying queries are still hidden in java .class files.  I had to use an alternative approach for revealing the queries. I used JD Java Decompiler and decompiled the class file (.\XML\SQLQueriesOra.class) and that is it – all the queries that the tool used for 46 features are available. I have compiled all of them at once place in a spread sheet (link provided below). Same query details can also be retrieved from java command line console, where in java echos the query each time you submit the request from front end. Hope you enjoy it!
A sample query used for getting the “The name of the source table used in the mapping” is below:
SELECT A.SUBJ_NAME,C.MAPPING_NAME,D.INSTANCE_NAME SOURCE_NAME_IN_MAPPING FROM OPB_SUBJECT A,OPB_MAPPING C,OPB_WIDGET_INST D WHERE A.SUBJ_ID=C.SUBJECT_ID AND C.MAPPING_ID=D.MAPPING_ID AND D.WIDGET_TYPE=1 AND D.WIDGET_ID IN (SELECT SRC_ID FROM OPB_SRC )
Welcome you all to add some of your experiences on your repository usage.
Repository Queries for PowerCenter 8x (from MetaQuery).xls
Karteek

I still have to figure out the internals of Informatica BULK target load type. How exactly it works, how it is different from its Normal load, and more importantly how its implementation is different from database to database. Today I have encountered with some issue with bulk load type enabled and ROLLBACKing the transaction (in Informatica) using Transaction Control transformation.
My idea is to issue ROLLBACK after inserting few records into the target. So, I have picked SCOTT.EMP table (in Oracle) as source with ORDER BY EMPNO.
As I wanted to issue ROLLBACK in the half i.e. after loading about 7 records out of 14, I created a Transaction Control Transformation with “IIF(EMPNO = 7782, TC_ROLLBACK_BEFORE)”
This failed with below ORA- error.

Rollback statistics:
WRT_8162 ===================================================
WRT_8330 Rolled back [6] inserted, [0] deleted, [0] updated rows for the target [EMP]

CMN_1022 [
ORA-24795: Illegal ROLLBACK attempt made

Database driver error...
Function Name : Commit
Database driver error...
Function Name : Rollback]

karteek@orcl10gr2>insert into /*+ APPEND */ emp select * from scott.emp;
14 rows created.
karteek@orcl10gr2>rollback;
Rollback complete.
karteek@orcl10gr2>

• APPEND hint makes the INSERT statement to write the data directly into the data blocks above the high water mark, and more over this supports only INSERT with SELECT clause, but not with VALUES clause where in we pass values for each record.
• Chances are there that Informatica is using an approach similar to BULK COLLECT INTO in Oracle. But, this might not be exactly the same approach, because BULK COLLECT is possible only in PL/SQL.

SQL>desc tnum;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B NUMBER

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID
SQL> select * from tnum;

no rows selected

SQL> $sqlldr karteek control=num.ctl direct=true
Password:
SQL*Loader: Release 10.2.0.3.0 - Production on Mon Mar 8 11:03:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 3.

SQL> select * from tnum;

A B
---------- ----------
1 3
2 4
3 7

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Users\Karteek\test>

I just now installed and configured it, and also did a simple "Hello World” kind of test run which went fine.
Quite a simple installtion it was (especially on XP).

There are 2 differences between installation on Windows XP (32bit) and Vista:

1. appropriate .Net runtime Vista (it needs 1.1) and
2. picking up the right setup files

• Informatica 8.6\Server\Server\Windows\Disk1\InstData\VM\install.exe  - for installing server
• Informatica 8.6\Client\Client\Disk1\InstData\VM\install.exe  - for installing client

In the last post I described the approach for installing Informatica Powercenter 7.1 on WIndows Vista . There was something I did on configuring Windows services which I would like to mention here.

When Informtica is installed on Windows with repository on Oracle database, it will show the following services (as display names) in “Services” window.

1. OracleServiceORCL10G (there are other Oracle services as well like, but I am not considering them for the current scenario, as this service alone is just enough to describe what I want to say)
2. Informatica Repository Server (PmRepServer)
3. Informatica (Powermart)

Previously I used to keep Oracle in “Automatic” startup option so that Oracle database is available as my machine boots up (means I do not have to manually bring it up). I had wished the same effect for both Informatica services as well, so that I did not have to start them every time I reboot my machine. Problem was that there is a dependency among these 3 services. Rep service can not start unless Oracle database is available (since Rep service requires rep database), and Informatica service can not start without Rep service in place running. Due to these dependencies among services and I did not set up any dependency rule between them, they all used to start their services at one time (after system reboots), but only Oracle used to succeed as Oracle does not have dependency on others (at least not on other 2 Informatica services).

I always overlooked the “Dependencies” tab that is there when you open properties of a service (Right click on service and choose Properties). But the last time, while I was installing Infa 7.1 on my Vista laptop I gave a close look at, and realized that that was something useful for me. You can very easily define the dependencies between services so that they start or stop in order.

The following steps need to be performed in order to create a dependency (Consider taking backup of current registry settings)

1. Run 'regedit' to open your registry.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and locate the service that you need to set a dependency for. (here you need to search for service name, not display name i.e. locate PmRepServer not “Informatica Repository Server”)
3. Open the 'DependOnService' key on the right side. If the selected service does not have a 'DependOnService' key, then create one by right-clicking and selecting New > Multi-String Value.
4. In the value field, enter the names of all services that the current service will depend on. Each service name must be entered properly (again, it should be service name, not disaplay name) and on a separate line.
5. Click OK

Perform the step 2 and 3 for both “Informatica Repository Server” (PmRepServer) with “OracleServiceORCL10G” as DependOnService, and “Informatica” (Powermart) as DependOnService on “Informatica Repository Server”.

This setup makes sure that the service start happens only in “OracleServiceORCL10G > Informatica Repository Server > Informatica” order, and the service stop in reverse order.

There is a new Startup Type option introduced in Windows Vista called “Automatic (Delayed Start)”. I feel this is another good feature. This option delays the Automatic starting up of services for some time (may be few seconds) until the machine boot-up is completely done (you can avoid/reduce the painful slow/no responses on restarts). I enabled this option on “OracleServiceORCL10G” service. Anyway, other 2 services are now dependent on Oracle service, so enabling delayed startup type explicitly would have no additional effect, so they are just “Automatic” startup type.

Hope it helps!

Karteek

I was just going through Powercenter Monitor's help guide and noticed a page describing about the different possible statuses that a workflow or a task can be in at a given point in time. I found a status  called "Unknown Status" as one possbile status, and per guide here is when it can happen.


Unknown Status

ERROR Wed Oct 7 00:55:09 2009 1409415520 LM_36317 Error in starting execution of workflow [id = 2651] [w_sales_fact].
ERROR Wed Oct 7 00:55:09 2009 1409415520 LM_36363 Workflow [w_sales_fact]: Cannot rename workflow log file [/opt/app/Informatica/pc/server/infa_shared/WorkflowLogs/w_orders_LOAD.log.bin] to [/opt/app/Informatica/pc/server/infa_shared/WorkflowLogs/w_orders_LOAD.log.1.bin].
INFO Wed Oct 7 00:55:01 2009 1409415520 LM_36814 Workflow [w_sales_fact]: Got scheduled request to start workflow.
INFO Wed Oct 7 00:55:01 2009 1398925664 LM_36814 Workflow [w_orders]: Got scheduled request to start workflow.
INFO Wed Oct 7 00:55:00 2009 183009705024 LM_36813 Workflow [w_sales_fact], run instance name [] is scheduled to run now, schedule last saved by user [karteek].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27029 Use persisted repository value [0] for user-defined workflow/worklet variable:[$$Load_Failed_ctr].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27029 Use persisted repository value [0] for user-defined workflow/worklet variable:[$$Is_LoadFailed].
INFO Wed Oct 7 00:55:00 2009 1388435808 VAR_27027 Use default value [] for user-defined workflow/worklet variable:[$$Delay].
INFO Wed Oct 7 00:55:00 2009 183009705024 LM_36813 Workflow [w_orders], run instance name [] is scheduled to run now, schedule last saved by user [karteek].

select subject_area, task_name workflow_name, attr_value cur_log_name, task_name || '.log' new_log_name 
from infav8.rep_task_attr a, infav8.rep_all_tasks b
where attr_name = 'Workflow Log File Name'
and a.task_id = b.task_id
and a.version_number = b.version_number
and task_name || '.log' <> attr_value  -- change pattern in this line
order by 1, 2

• When a PowerCenter repository have versioning enabled and a dependent activity is still checked out when scheduling the workflow - solve this by checking in all the dependent objects (from Informatica KB - ID: 100943)
• There is a known issue (CR 182478) that has been fixed in PowerCenter 8.6, causing workflow to be in "Unknown Status". To resolve the issue, upgrade to PowerCenter 8.6. However, as a workaround in versions earlier than PowerCenter 8.6, set Save workflow log for these runs to zero (0) in workflow properties (ID: 106278).

This is little off from my usual topics. Paul Krugman, an Op-Ed columnist in NYTimes, recently expressed his opinions on mischievous Chinese currency in the world market, and about its implications on the current economy in rest of the world. A nice post to read…(the same post is published in today’s The Hindu daily, in case if you prefer hard copy :) ).

http://www.nytimes.com/2009/10/23/opinion/23krugman.html?_r=1

- Karteek

I am trying something on SAS. It was an alien thing until recently, but should look forward to see how it turns out to be soon. My very intial impression on the software was not so good - didn't really captivate me. However I still have to go a lot deep into it to scratch its surface. Perhaps I will soon have a post on my experiences with it.

So far the best thing I noticed in SAS is its Help - perhaps one of the best manuals I happened to surf through.

Catch you again...

Karteek

It's known thing that Oracle and Linux are nicely coupled that the scope of performance issues in Oracle is not just limited to Oracle sometimes... I stumbledupon one cool post by Riyaj (http://orainternals.wordpress.com/) in which he had explained how he troubleshooted the CPU usage issue with an optimal memory configuration.

Recent OS architectures are designed to use all available memory. Therefore, paging daemons doesn’t wake up until free memory falls below a certain threshold. It’s possible for the free memory to drop near zero and then climb up quickly as the paging/swapping daemon starts to work harder and harder. This explains why free memory went down to 78MB and rose to 4.7GB 10 minutes later.


What is using my memory though? /proc/meminfo is useful in understanding that, and it shows that the pagetable size is 5GB. How interesting!

Original post can be found here.
 
Problem was identified with paging which used BIG pagetable(due to smaller unit page file size, 4KB, and larger memory ~10GB) meaning so many pages (millions - ~ 2.6m) that machine most of the resources in managing the pages itself so nothing left for other any critical processes. He increased the page file size to 2MB and thereby decreasing the pagetable size and main bottleneck was resolved. It's a good read.
 
- Karteek

In the recent past I have been noticing an SMTP ORA- error while a stored procedure tries to send out an email. Error is below.

PRE-SESS> TM_6159 Error executing stored procedure...
TM_6159 [4294965496] [
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 272
ORA-06512: at "SYS.UTL_SMTP", line 248
ORA-06512: at "SYS.UTL_SMTP", line 259
ORA-06512: at "YK.EMAIL_TEST", line 39

$ telnet hydapp01 25
Trying 124.2.78.2...
Connected to hydapp01 (124.2.78.2).
Escape character is '^]'.
220 hydapp01 ESMTP Sendmail 8.13.7+Sun/8.13.7; Thu, 17 Sep 2009 06:48:13 -0400 (EDT)
helo domain
250 hydapp01 Hello hydapp02 [124.2.78.3], pleased to meet you
mail from:yk@hyd.com
250 2.1.0 yk@hyd.com... Sender ok
rcpt to:karteek.yelampally1@hyd.com
250 2.1.5 karteek.yelampally1@hyd.com... Recipient ok
data
354 Enter mail, end with "." on a line by itself
Subject: This is SMPT test using telnet
This should be rock successful.
.
250 2.0.0 n8HAmDQu006085 Message accepted for delivery
quit
221 2.0.0 hydapp01 closing connection
Connection closed by foreign host.
$

NULL is perhaps one of  the most dangerous concept but easily ignored by many. Because its definition it self is not clear or rather not a standard one. Different softwares and applications define NULL differently and so their functionality. I have not got much exposure to numeruos technologies, but so far with my experience Oracle handles NULL very eligantly. Oracle defines NULL as something not defined, so you don't know its value. And more interestingly, both 'NULL = NULL' and 'NULL != NULL' are not true, because when you don't know the value of NULL it can not be compared. By now one more point is clear that NULL and '' (a zero length string) are not same in Oracle - since '' is a knwon value.

Handling NULL is not at all difficult, at least from my experience. All that we need to know is how your environment handles NULL - understanding its definition and little bit testing. Common mistake is many people ignore the very basic truth that NULL in their environment could work differently from thier past experiences with NULL, and face surprises that are usually difficult to debug. It may work differently not just from application to application but even from version to version.

Today, I was caught in surprise when NULL behaved differently from Informatica 7.1 to 8.6. See the below image.

There are many session instances that run cocurrently. All of them are same but they just run  for different connections (1 session = 1 source = 1 connection) for  which connection names are parameterized. Since number of sources is not static, I have to run this  workflow until I finish running a session for every source. If  I have 10 concurrent sessions and 47 sources then it would  require 5 workflow runs ( 10 + 10 + 10 + 10 + 7). So in that last run I need to run only 7 sessions but not 10, and this is  taken care by the link condictions I've specified in between "Start" task and Session instances. In Informatica 8.6 I defined a link as one below...

IS NOTNULL($$DBConnectionSource1)

$cat w_source_test.prm
[Test_Folder.WF:w_source_test]

$$DBConnection1=IDPV1
$$DBConnection2=IDPV2
$$DBConnection3=IDPV3
$$DBConnection4=IDSV1
$$DBConnection5=IDTV1
$$DBConnection6=IMPV1
$$DBConnection7=IMPV2
$

Link [Start --> s_m_source_test_7]: condition is TRUE for the expression [NOT ISNULL($$DBConnection7)] 
Link [Start --> s_m_source_test_8]: condition is FALSE for the expression [NOT ISNULL($$DBConnection8)] 

$$DBConnectionSource1 != '' 

Link [Start --> s_m_infa_connection_test_8]: condition is TRUE for the expression [NOT ISNULL($$DBConnection8)].

Link [Start --> s_m_infa_connection_test_9]: condition is FALSE for the expression [$$DBConnection9 != ''].

DBConnection8 was not defined in parameterfile but that was still evaluated to true treating its value as not a NULL, but DBConnection9 link evaluated correctly (to FALSE since value is not set) against zero length string.
 
So, it's just a point of note, but the bottomline is that we simply just can not ignore NULL as it is nothing set with or undefined, because you never know how that unknown thing is handled by your app. 
 

Update:

Although this post has much talked about Informatica handling NULL, there are some interesting posts of Tom Kyte (of asktom.oracle.com) on handling of NULL in Oracle.

http://tkyte.blogspot.com/2006/01/something-about-nothing.html
http://tkyte.blogspot.com/2006/01/mull-about-null.html

- Karteek

Last week, we were trying to bring an improvement in one of our ETL processes. Process is such that Hyperion cube’s load needs to started just after some ETL loads’ completion. Both Informatica and Hyperion are hosted on different servers, and both are Linux. So, there has to be some dependency established between ETL process on Informatica server and Cube load process so that Cube load gets kicked-off immediately after ETL finishes.

If you feel you do not have much time to conver the full details below, you directly skip to here as main purpose of this post is explain the difficultly I faced in using ssh from Informatica.

We could think of an approach where in trigger file (zero byte file) from ETL to notify Cubes’ load job. But this is very loosely coupled – ETL should either send the trigger on to Hyperion server and Cube load process that already started on Hyperion server should be waiting for the trigger to kick-off cubes load. There are some serious drawbacks in this approach -

Both ETL and Cubes’ load jobs should be scheduled on their servers – how does already started Cube load process know about ETL job failed and that current is ignored – fairly acceptable scenario, I believe. As a work around, Cube load job on Hyperion server can for defined time period, and it still doesn’t find trigger file then stop cubes load – some sort of workable, but still not good when schedule of ETL changed or ETL took longer period. (Trigger file released on to a shared file system instead of sftping is just nothing but a trigger file dependent)

A good possible workaround I could think of is an Enterprise Job Scheduler that reduces if not fully eliminates the process dependency barriers among the different servers. In addition to Informatica and Hyperion servers if there is another third server that controls Jobs on rest of the servers this issue is solved – Job scheduler to start the Cube load process only after ETL finishes.

But not every company uses a Job Server/Scheduler, unless the IT is relatively complex and value for money is not identified. My client is not using the Job scheduler, at least that I know of in their well matured BI department. Just because of some rare (at for them) benefits that Job Scheduler can bring, simply ignoring the classic scheduler that Informatica gives is also not good idea. So be with it.

At this point of time, only option I can think of is remote invocation of Cube load process on Hyperion server from Informatica server as part of its ETL itself – no it’s tightly coupled – process of Hyperion server doesn’t even start unless ETL finishes.

So, point is clear by now – I need to start the Cubes’ load process from Informatica, precisely through a command task calling a remote shell script. A code snippet is something like this…

ssh –q –l hyp01 hypsrvr ‘/opt/app/Hyperion/cube_load.ksh’

I noticed a weird behaviour of this process when it was invoked from Informatica 7.1. Even though cube_load.ksh process on Hyperion server is finished, process on Informatica 7.1 server still shows up in the process list (ps -f) forever, and so does in Informatica monitor. The very same command when executed directly on the server (from putty) worked fine. Even a simple ssh command for “date” gave the same result when ran from Informatica. For the first time I tried to use strace on the PID of ssh process, though not successfully – but that was showing only “select(16, [0 13], [], NULL, NULL” – still not helpful.

ssh –q –l hyp01 hypsrvr ‘date’

I then tried in our other upgraded environment Informatica 8.6 and that worked. I’m suspecting 7.1 has this bug fixed in later releases. I’m not confident enough to say that Informatica 7.1 has this bug, as Linux machine environment settings also a possible reason. So, I deeply appreciate sharing your similar experiences. This is our Informatica Linux server version.

With my boss help I tested rsh too instead ssh – we knew rsh was not the way to go due to the risks involved, we just gave a try and that worked in both 7.1 and 8.6 – only ssh had the problem.

$ uname -svm
Linux #1 SMP Wed Jul 12 23:36:53 EDT 2006 i686 

Something off the topic I learned from him was how to setup .rhosts for rsh. I knew already how to setup the similar for ssh (generating the public keys on client machine and them to host machine’s authorization keys file), but rsh too has some similarity in the setup. There are no encrypted keys generation required, but just adding the client user name in the remote machine’s HOME/.rhosts file and that works.

As I requested, please do share your experiences with Informatica and ssh together.

Karteek

There is some interesting behaviour that I noticed recently in Informatica's repository with respect to session's run time information. If you are not much comfortable with Informatica repository that I now I am going to explain about, here is some quick notes.

Informatica entire application lies on a data repository that consists of several tables (mostly start with OPB*). Perhaps in order to make an ease access to repository information, Informatica also provided several views based on the OPB tables. For many of the common questions you may want the answer REP views should help, bu there is always an exception and OPB tables are there. When you write queries on OPB tables take due care that you do not ruin database with bad joins - for this Informatica manual gives the columns that generally good candidates for joins when using REP views, but generally the same information applies to joining tables as well since a view is just a named query. Some useful views are... REP_WORKFLOWS - Workflow master list REP_WFLOW_RUN - Worklow run time details REP_ALL_TASKS - Master task list REP_TASK_INST_RUN - Task level run time details REP_SESS_LOG - Session's Success/Failed records etc...

With those details, let me explain the what the prob

em is in 7.1. Workflow_Run_ID uniquely identifies each run of a workflow - means when a workflows starts (starting part of the workflow or full workflow or a schedule run) it would have a new workflow_run_id.

For suspended workflow (enabled "Suspend on Failure"), common way we recover it is by resuming it after the fix is provided (in 8.6 option name is "Recover"), and resuming it obviously not a new run so doesn't carry a new worklflow_run_id after resumed. It also means that there would not be a new entry into REP_WFLOW_RUN (or OPB_WFLOW_RUN table) when a workflow is resumed.

Moving a little back, a workflow goes to suspended state when it's meant to fail by the configuration of underlying tasks - means when underlying task fails that makes it's parent (or workflow in a simple setup) to fail and so workflow suspends. So, when such suspended workflow is resumed that actually restarts the failed tasks in the run. I noticed, in 7.1, even though resuming does not create a new workflow_run_id, tasks that restarts when its parent workflow is resumed would create new entries in REP_TASK_INST_RUN. That might be for good reason, but I see that of not much help rather misleading as there is no column that distinguishes task restarts (start_time of task can give that info, but that's not a good candidate).

Moreover, Informatica 7.1 behaves badly in maintaining information in those records - if a task is failed, say 3 times, when resumed multiple times, and finally succeeded in 4th attempt, then all the 4 records of that task run would get updated with last run information (except start_time that I identified) - end_time, error_code etc...and I would say this is completely misleading. Similar behaviour can be noticed in other tables/views that stores the task level run time information like REP_SESS_LOG (OPB_SESS_LOG).

If you run this below query on 7.1 repository you would notice some records being fetched (provided there was atleast one attempt of resuming a suspended workflow)

select workflow_run_id, instance_id, count(*)
from infav71.rep_task_inst_run
group by workflow_run_id, instance_id
having count(*) > 1

select * from infa71.OPB_TASK_INST_RUN     where INSTANCE_ID = 18738 and WORKFLOW_RUN_ID = 7113806;

SUBJECT_ID    WORKFLOW_ID    WORKFLOW_RUN_ID    WORKLET_RUN_ID    CHILD_RUN_ID    INSTANCE_ID    INSTANCE_NAME    TASK_NAME    TASK_ID    TASK_TYPE    START_TIME    END_TIME    RUN_ERR_CODE    RUN_ERR_MSG    RUN_STATUS_CODE    RUN_MODE    VERSION_NUMBER    SERVER_ID    SERVER_NAME
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 2:00    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 1:47    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 1:14    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:52    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:17    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR
48    13707    7113806    7113823    0    18738    s_m_test    s_m_test    20461    68    8/24/2009 0:02    8/24/2009 2:00    0        1    1    1    1    TEST_SRVR

If you look at the above result only start_time is differing, and values of older runs are updated to last run values (error_code, end_time etc... though they have different values from run to run)

Looks like it's fixed in 8.6 - I did not test in version between 7.1 and 8.6 though. In 8.6 however number of resuming of a suspended workflow may be, a failed task instance would have only one run per workflow_run_id. This is much better, rather misleading with more information. if you run the first group by query in your 8.6 repository, you should not be getting any records at all.

select workflow_run_id, instance_id, count(*)
from infav88.rep_task_inst_run
group by workflow_run_id, instance_id
having count(*) > 1
0 row(s) retrieved

Hope that helps.

I appreciate your comments... Thanks!

Karteek