Creating Sample ETL using Informatica 8.6- Part -2

1. start the repository manager to create folders by clicking Start-->All Programs--> Informatica 8.6 -->Client --> Repository manager

2 Click on Repository menu Add Repository





3. Double click on the newly added node to connect to repository







Note: select the domain name first before giving the password, otherwise the connect button wont enable

4. Click on Folder -->Create Folder menu to create the folder.













5. Exit the Repository Manager

Creating Sample ETL using Informatica 8.6- Part -5

1.Start Informatica Workflow designer
2.Connect to repository.
3.Expand the folders, Under workflows the newly created workflow will appear.
4.Click Tools--> WorkFlow Designer
5.Drag and Drop the WorkFlow into WorkFlow Designer and following screen appears









6.Right Click on the Start image in the workflow designer.

7.Click on Start Task, this will opens Informatica PowerCenter WorkFlow monitor.

8.Connect to Repository , Expand till the workflows folder reached.

9.On successful run succedded message will appear like below

Creating Sample ETL using Informatica 8.6- Part -4

Step 4: Generating WorkFlow
1. start Workflow manager by using Start--> Programs-->Informatica Powercenter Designer 8.6--> Workflow Manager

2. click on Connections--> relational menu to create source and target connections













3. Click on New button to define the source connection properties.

4. Give the database user name and password for the source connection and save the source connection.












5. Similarly define the connection for Target.

6. Go back to Powercenter Designer window

7. Click on Mappings --> Generate WorkFlow menu










8. Click Next and select source and target connection from the drop down box










9. Click Next, Click on Configure button to define correct connections









10. Click on Properties tab to give whether to load is append or truncate and load.

Creating Sample ETL using Informatica 8.6- Part -3

Step : Creating WorkFlow Mappings
Pre-requiste: Create ODBC so that tables can be reverse engineerd.

1. Start the Informatica Desinger using Start-->Programs-->Informatica Power designer 8.6-->Client--> Power Center Designer

2.Connec to the repository by giving the repository user name and password.

3.Click Tools--> Source Analyzer

4.Click on the Sources --> import from Database menu

5. Give the datbase user name and password, click Connect









6. Select the table and press OK to import the table into source Analyzer pane.

7. Click on Tools--> Targer Designer menu

8. click on Targets--> Import from Database menu

9. Connect to database after giving target user naem and passowrd and press Connect button

10. Select table and press OK button which will import the table into target analyzer pane.

11. Select the mapping Designer by selecting Tools-->Mapping Designer

12. Drag the Source table from Source folder into Mapping Designer.It will prompt you to enter teh mapping name. Give a valid mapping name.

13. Drag the Target table from Target folder into Mapping Designer.










14. Map the columns from Source Qualifier to the target by clicking on teh column Source qualifier and dragging the selected column to the target column.

Now the mappings been done, We have to genereate Workflow as next Step.

Creating Sample ETL using Informatica 8.6- Part -I

Step: Informatica Adminstration Tasks for creating Services

1.Install the Informatica 8.6

2.After installing , Start the Informatica Services











3.After this the following services will be started










4.Start the informatica Power ceneter Administration console
Start--> All Programs--> Informatica Power Center 8.6-->Services --> Power center Administration console.

Following Screen appears










5. Login using user name and password .
Note this is given during installation time.
Default user name : Administartor
Password : Administrator

6. Click on Admin console menu on the welcome screen










7. Create a oracle database user to hold the repository.

8. Create Repository Service by clicking












9. Fill the necessary details in following screen to create teh repository service

Service Name: Name of the repository service to be created
Connect String: service name pointing to the db
User name: user name created in step7
Password : Password created in Step 7

10 Stop the repository service and restart again so that Integration service can be created.

11. Create the Integration Service by clicking Create-->Integration Service menu.

Repository user name: User who have been already created in informatica security manager.
Password : password of the informatica user








12. Assign the privileges the repository user for the newly created repository by clicking configure Security icon on the right corner.




13. Click on the Roles Tab and select the check box against the created repository.





14. Similarly click on teh Privleges tab and select the check box against the repository name so that all privleges is granted for the user.

goto next step

how to store extremely large files>

its very common for most of us to find a place to back up our data. Gone are the days when we used to back up data using USB, External HDD etc. Now many companies are offering free and paid service to storing up the data online. While goin through following link found that this is quite informative.
http://news.cnet.com/8301-27076_3-20000133-248.html?tag=rtcol;pop

Solution for ORA-01157

Recently while dropping the database some of my other datafiles gone missing. Because of this when trying to open the database got an error "ORA-01157 - Cannot find lock for the file D:\oracle\Oradata\Oradesigner\Designer" and the database is not at all opening

Solution
1. Login to database as / as sysdba
sqlplus > connect / as sysdba
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Feb 8 14:54:38 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
sqlplus > startup mount;
ORACLE instance started.
Total System Global Area 236404368 bytes
Fixed Size 724624 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
sqlplus > alter database datafile 'D:\oracle\Oradata\Oradesigner\Designer' offline drop;
Database altered.
sqlplus> alter database open;
Database opened

Changing the Default Gateway using Command prompt

The command is:
to change IP and default gateway:
netsh int ip set address "local area connection" static 192.168.0.101 255.255.255.0 192.168.0.254 1

to change DNS:
netsh int ip set dns "local area connection" static 192.168.0.254 primary

This is assuming 3 things.
1) The network adapter you're trying to change the IP for is "local area connection". It could also be "local area connection 2" or "wireless network connection". Look in your control panel for the correct name
2) The IP you want to set is 192.168.0.101, change this to whatever IP to want to use.
3) The default gateway and dns are the same IP. If you are using some kind of router they usually are. Change this to match your network config found with the command ipconfig /all

You will need to run both commands to change the IP.

Creating - Oracle DB Links

1. Log into the db from which the query will be executed.
2.create public database link using
ex Create Database link db1_link using db1_machine1
3. Database link is created

Common Problems
===============
1. When global_names parameter set to TRUE then default domain name 'REGRESS.RDBMS.DEV.US.ORACLE.COM' is appended at the end of the dblink name and will cause ORA-02805 error.


Changing global names
================
Alter system set Global_names =false;

Date Queries in MS Access

Writting query in MS Access for Date column differes from other SQL formats. For eg Writing the sql in ORACLE you will follow this patterrn

Select First_Name, DOB from emp where dob='01/jul/2008'

whereas in Access it has to be written like this
Select First_Name, DOB from emp where dob=#01/jul/2008#

Access understands the literals enclosed within # as date.

Java - XPath for parsing XML

XPATH
(XML Path Language) is a language for selecting nodes from an XML document. In addition, XPath may be used to compute values (strings, numbers, or boolean values) from the content of an XML document. The current version of the language is XPath 2.0, but because version 1.0 is still the more widely-used version, this article describes XPath 1.0.

The XPath language is based on a tree representation of the XML document, and provides the ability to navigate around the tree, selecting nodes by a variety of criteria. In popular use (though not in the official specification), an XPath expression is often referred to simply as an XPath.

Originally motivated by a desire to provide a common syntax and behavior model between XPointer and XSLT, XPath has rapidly been adopted by developers as a small query language, and subsets are used in other W3C specifications such as XML Schema and XForms.


Using XPath sample

1. Create the DOM XML document
String fileName = "D:/sample.xml"
SAXBuilder builder = new SAXBuilder();
Document document = builder.build(new File(fileName));

2. Create XPATH query string like "/parent/child/grandchild'[@attribute = value]'"
for eg
String XpathQueryContexts = "/root/module[@display='" + moduleKey +
"']/subjectArea[@name='" + subjAreaKey +
"']/tab[@targettable='" + tabKey + "']/" +
"filter-contexts";

root is the parent node
subjectArea is the child node for root
tab is the child node for subjectArea
targettable is a property defined in the tab node

3. Get the nodes as a ArrayList for the given query condition at step 2
List lstContexts = XPath.selectNodes(jDomDocument, XpathQueryContexts);


More information available in
http://en.wikipedia.org/wiki/XPath

Starting up 10g EM console

1. Start the command prompt

2. Set the ORACLE SID to the instance on which the EM console needs to be started using the command like
Set ORACLE_SID=ocp

3. execute the command
emctl start dbcconsole

Above command results in following status message
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://PURUSHOTHAMAN:5504/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The OracleDBConsoleocp service is starting....
The OracleDBConsoleocp service was started successfully.


4. using IE / firefox open the page http://PURUSHOTHAMAN:5504/em

Changing column data type with data

Recently my friend came with a stragne requirement. He has data in one of the columns which is date value but the table is wrongly created as varchar2 column. He wants to change the datatype from varchar2 to date.

Here it goes

First created a table like this

create table a(id number, dt1 varchar2(20));

Inserted some values into the table using following queries

insert into a values (4,'04/jul/2007')
insert into a values (1,'01/jul/2007')
insert into a values (2,'02/jul/2007')


Now create another column in the same table like this
alter table a add dt2 date


Now update the newly created column with the existing value using the query

update a set dt2=to_date(dt1)


Drop the varchar2 column from the table using
alter table a drop column dt1

Rename the newly created column
alter table a rename column dt2 to dt1

Manual Creation of 10g EM

Create the Database Control Repository and setup the OC4J Application Server
1. Now start the Oracle EM dbconsole Build Script
($ORACLE_HOME/bin/emca for Linux and $ORACLE_HOME\Bin\emca.bat for Windows).

2. Create the repository
$ emca -repos create
$ emca -config dbcontrol db
STARTED EMCA at Fri May 14 10:43:22 MEST 2004
Enter the following information about the databaseto be configured.
Listener port number: 1521
Database SID: AKI1
Service name: AKI1.WORLD
Email address for notification: martin.zahn@akadia.com
Email gateway for notification: mailhost
Password for dbsnmp: xxxxxxx
Password for sysman: xxxxxxx
Password for sys: xxxxxxx



After Creation the following message appears

AM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://akira:5500/em <<<<<<<<<<<>

Using browser the em can be connected at the URL http://akira:5500/em