Hello all.. it’s been a very long time since i had come up with a post on my blog. Finally decided to push myself to get one simple post to have it kick started. So giving a simple post which i believe helps more and keeps me going with sharing more and more.

This post of mine shall cover an activity of administration on top of Informatica installation and more in specific to Power Center and Power Exchange. A recent activity in my assignment where we have had a recent Upgrade applied on the existing version of Informatica from 9.1.0 HF6 to 9.6.1 HF4. And so i had some really good experience and would like to bring them all to every one of you with a  hope it helps few of us. I would cover a series of posts to cover all my experience and knowledge on this.

To start with, installing/configuring Plugins on a new installation or on a Upgraded version of your Informatica. When you install Informatica you either have some Native/in-built plugins or you will be forced to install some of them as external plugins which are again provided by Informatica.

For instance i had to install new version of Power Exchange for SAP and Essbase connectors to my Power Center Upgraded version of 9.6.1.

Step -1:

Install the version of Power Exchange setup that can be downloaded with help of Informatica’s GCS team. This shall be a regular installation setup which shall place the respective .xml for the Power Exchange plugin on your installation folder path of Informatica server – <Installation-folder>/9.6.1/server/bin

Step-2:

Logon to your Admin console and on your Repository settings, change the Operating mode from ‘Normal’ to ‘Exclusive’. Allows no user to access the Repository while your admin activity is carried out.

Step -3:

Registering Plugin through Admin console

This is pretty much a straight forward method, where you login to the Administrator of your Informatica with the right access.

Then on the Repository settings, at the right most pane on the ‘Plugin’ tab, would show up an empty page if you have had no other plugin installed. Click on the Plugin icon/symbol on the right top of the pane as shown below –

registerplugin_mahaveer_blog

Then comes the pop up to allow you browse your plugin which should be on your Server/bin/ path as said earlier. Select and continue on with the right authorized user to complete your registration of the Plugin.

registerplugin_mahaveer_blog2

Register Plugin through Command line

This again would require bring your Repository to ‘Exclusive’ mode and then logging on to your Server machine.

To start with open your pmrep executable available at the server/bin, and connect using the administrative account and this shall have a syntax as below –

pmrep connect -r REP_name -d DOM_name -n Administrator -x admin_password

This shall successful connect your repository as Administrator.

Find .xml in the following directory: $INFA_HOME\server\bin\Plugin which would come up after your earlier Power Exchange installation. And now you can execute the RegisterPlugin command with the reference to your plugin.xml file,

pmrep registerplugin -i <$INFA_HOME\server\bin\Plugin\plugin_file.xml –e –N.

where ‘-e’ will be used in case where you want your registering has to update on top of an older version of the same plugin (you could see the same as checkbox when you did through admin console).

And ‘-N’ is required when you either install it on an already existing Repository and has just been upgraded, or if the Power Center already comprises the Plugin and only needs a registering or if you are registering an already existing plugin just to get the additional feature from a new version/upgrade of the plugin.

registerplugin_mahaveer_blog3

This shall successfully register your already installed plugin successfully and let you avail the feature of the respective Power Exchange connectivity.

Hope this helps, happy learning and sharing all.. 🙂

 

Advertisements

Hello All,

Hope you all had a very great Christmas and awaiting to kick off a new year soon. Before we kick off, a happy new year to all of you in advance..!!

Just when i was waking up to welcome a sunny Saturday, i had a thought mugging around me and telling me to do this. Yes to write a post on what i had been badly attacked by, it was indeed not something of a physical attack 🙂

So without further dawdling let me explain how hard i had to fight against the Target Update Override which helps me customize my Update strategy used in Informatica allowing us to Update any target table despite not having the right Primary Key combination for the Update. I have done this before and have done it with a blunder every time which taught me something right but time and again i have done the same repetitively. I just then had thought to help other fellow developers avoid this and so i am writing this blog post to help you out with the Target Update Override usage.

Probably when you go for an Update over your target, you would use the Update Strategy which would link to your target ports, the Primary key columns on which Informatica would perform an update on the rest of the columns you will be linking with the target. So there would also be a scenario where you will wish to update few fields with a conditional check over the other fields (non primary key columns).

There are two ways of doing this,

Method 1 –

Make target structure change on your Informatica side to make the Ports/columns on which you will be performing a check to update the row. This means you will have to change these check fields to have a Primary key property enabled on the Informatica end and then use the Update strategy transformation as a usual way.

blog_primary_key_infa_2

Method 2-

To script down a Update sql override in the target. It’s easier to be done with a usual Update query syntax to be written on your target object in the mapping and there is a strict syntax to be followed which also means a very proper spacing to be used between the Operators and the sql literals. By default, the PowerCenter Integration Service updates target tables based on key values. You can override the default UPDATE statement for each target in a target definition. And as discussed we would like to Update on the basis of the Non key columns and this can be done something like below,

Sytax:

UPDATE SALES_TBL SET EMP_NAME = :TU.EMP_NAME DATE_SHIPPED = :TU.DATE_SHIPPED TOTAL_SALES = :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, use :TU to specify ports.

blog_primary_key_infa I made a mistake..!!

I am just adding this part to save your time. I had done a very silly mistake while composing my Update override query. For a very long time my session was failing and i was unable to figure out what went wrong with the session failing stating i had my Update query wrong.

Revisiting it almost like 100 times changing all that i could and unable to get what went wrong 😦

Finally a big brick hit my head and i just copied my Update sql to the notepad and found that i had no space between WHERE clause and my operand, and i was not able to figure this out as i had it going to a next line character, just as is in below picture.. which i would think was just like a usual word wrap..!!

blog_primary_key_infa_3

Hope this helps you and saves your time. Thanks for reading the post and share as it’s fun to learn and share 🙂

Hello Friends, Time for some learning. This time i have taken up the Informatica Variables and Parameters which i am very much fond of 🙂

This time i have taken up something that i came across in one of the development my team was involved in, and there was a need to pass a value from one task/session to another as we had a check with respect to the value we get from one session to be used going forward in the process.

blog_banner_session_Variable_assignment

For instance, my requirement is to check on the count of records from session_1 and then to count on records in session_2 but with the same counter (increment from where the Session_1 count ended) ending up with the total count of records. Something like, i had 100 records in session_1 and then i have to count from there as 100+1 and so on for the session_2. Looks simple, but quite a nail biting and challenging experience while we try getting it in Informatica!!

And then we came across this approach/feature available in the Informatica ‘Pre-Session/Post-Session Variable Assignment’. Which was all about the assigning of Variable values between the Sessions and it’s Parent Worklet/Workflow and vice-versa. Interesting right? If not clear lets go with a step by step illustration for a better understanding on this.

1. All we need to do is define a Counter variable/Parameter $$v1_Count for the session_1, and then our session runs to populate $$v1_Count as a usual Mapping Variable/Parameter does.

2. Next is, to assign this value to the common Variable (that acts as an intermediate between two of our sessions) which will be the Parent Worklet/Workflow Variable defined as any other Variable in the Edit Workflow/Worklet Options. In my case i have Workflow variable $$wf_v_Count defined.

3. Now in the session_1, Components tab -> lets edit the “Post session Variable assignment” option (it can be on failure or on success),

(i) Post-session on success variable assignment
(ii) Post-session on failure variable assignment

and i define it as – “Parent_Workflow/Worklet_Variable = Mapping_Variable/Parameter”

i.e., $$wf_v_Count = $$v1_Count

So here we have the $$v1_Count value open for any task,session or Worklet under the same parent can take up and use it for processing.

4. In our case, for the next session_2, i have to go for Components tab -> “Pre Session Variable Assignment” to be defined to assign the value from the $$wf_v_Count to the respective session’s Mapping Variable/Parameter $$v2_Count.

So we define it as “Mapping_Variable/Parameter = Parent_Workflow/Worklet_Variable”

i.e., $$v2_Count = $$wf_v_Count

The same can be used all over the Parent Workflow/Worklet, in any decision tasks or on link conditions. Thus we have now passed on the value from one session to the other and can be processed thereafter. Hope this was an interesting and helpful piece to you.

Do post your comments on any corrections or queries with respect to this. Share this if it can help someone.

Hello all.. This post is a result of all my excitement after getting the Informatica installed on my PC after multiple times of install and uninstall attempts and wish to share with you all and make sure you get it done as I did. I am to explain the whole sequence of steps that we are to follow in getting the Informatica installed successfully on the Windows7. Here I have installed the Informatica 8.6.0 version.

Important Note: Make sure you set all things before you begin the installation. For this refer to my previous post – http://bit.ly/QggXmF

If you are done with the above, let’s start the installation right away, Click and run the install.exe (good to run as an administartor).

This shall start the Informatica Installation wizard as is seen in the below screen shot,

And now we are to give the License Key, this shall be mostly available in your Informatica Software folder location – D:\Softwares\Informatica PowerCenter 8.6.0 for Windows 32Bit\. Select the key file available ‘Oracle_All_OS_Prod.key’ and give a next.

Select the Folder location where you wish to install the Informatica, I cose the drive ‘D:’

Next comes the HTTPS Configuration. I prefer having it default and proceed with the installation

If there is a block on by your Firewall, simply give an Allow with the both options checked to allow the Informatica application. And thus the Installation shall begin.

Go for the ‘Create a New Domain’

Then comes the Database settings, I had a SQL Server as my Database and all the settings were given as per the installed Database. Don not make any change on the Custom String option, and ensyre the right Database setting by having a ‘Test Connection’.

Then comes the Domain settings, let these remain the default. All we need to do is to set a password here for the admin user.

Just have a check on the Advance Configuration and do not make any change and proceed with the installation.

And here we are done with the Domain configured, Finally the step to create user with Password set.

This will complete the whole installation and we are perfectly done with a successful installation of the Informatica server, DO also copy the information that appears on the final screen of installation wizard for future use and reference. One final check after the install on the Informatica services running:

Right click on MyComputer -> Manage (Computer Management), and under Services and Applications click on Services, look for the ‘Informatica Services’ and ‘Informatica Orchestration Server’, both the  services should be up with the status being Started.

Hope this helps you all. Share as this might help some one 🙂

Walking on the MOON!!

July 18, 2012

Might be wondering if i am to write on some space journey!! Let me end all your excitement with the picture below.

Yeah this is how the moon feel has been brought live in the name of road re laying. Early morning i started my day with freshness of a day starting after a drizzle all through the night. I was saying to myself – ‘Great day and in chennai a wonder that happens very rare.. sun was dull and it was a feel good’. And then came the OMG moment!!

As i walked along the road near to the Bus Termainal at my residence locality (Korattur) had a great picture in front. The road being re layed. My first word seeing this – what the HELL…!! (i can sound even bad). This was horrible, i don’t understand where were these guys laying the road when we had almost 6 months of dry summer. Why is that always these activities start as it starts drizzling climate?

And this follows a month of roads being un attended due to down pour of rain and by the time climate gets friendly the roads have already back to the so called Moon surface (I mean the craters.. nope the pits and cracks..). This really creates a great amount of discomfort for the office and school goers and all the mobility of traffic stops and slows down the spped of life.

No point in complaining, just lets make sure that we send this message and make every one to think on this – not only the moment we use the road. Spread the message and get a person to think about this.

This was interesting, today when i was at office my senior just showed me
this funny feature that google has added as one of its creative crisps.

Just like to share it with all of you. Its interesting and really good to
give a try. Just get to the google search page and type this text “Let it
snow” 🙂 hope you enjoy the aftermath.

Just it gives an awesome search page with the snow coming down and
gradually our monitors freezing down, this is really cool. This desrves
appreciation and definitely something different than the usual google
doodles that google has been doing day out and day in.

Thanks for reading, share it with others 🙂

Hello all, its been months since i have written a blog. Its never late than never. So let me start it right away with a post on SQL. This is my first post on SQL and hope you all find it useful.

I am today giving a post on getting the size of the tables in your database. Its very simple and we have a system defined procedure that makes your job easier. The syntax to get this is,

exec sp_spaceused ‘TABLE NAME’

You shall get the metdata information on your table as you query the above.

Lets try to get the size information on all the tables of your whole DB. Wondering how? This is even simpler, as usual to make the job easy we have got a time saving procedure to do it for us. Lets make a query to get the size of all tables,

EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

The question mark replaces the table names of our DB. This query returns mutiple tables of the metadata like size of every table in our database. To get a view of the metadata as a single table the following set of query can be run together,

/* CREATE A TABLE TO STORE THE INFORMATION */

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

/*INSERTING THE INFORMATION RETRIEVED TO THE TABLE*/

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

/*VIEW THE INFORMATION FROM THE TABLE & DROPPING THE TABLE*/

SELECT * FROM #t

DROP TABLE #t /*OPTIONAL TO DROP*/

Hope this post was useful to you all. Thanks and feel free to comment and share your ideas.

%d bloggers like this: