[Informatica] Tutorial Part 1

Here is the link if Informatica Tutorial Part 1 for beginners:

http://www.youtube.com/watch?v=ufH_n5exxQw

[Informatica] Configure Power Center Integration Service

Finally we need to configure Integration Service through which we can execute our workflows.
1. In the Administrator tool, click the Domain tab.
2. On the Navigator Actions menu, click New > PowerCenter Integration Service.
The New Integration Service dialog box appears.
3. Enter the Service Name, License, And Primary Node.
4. Now provide the repository information which you just created, its included repository name, username and password.
5. Click Finish.

[Informatica] Configure Power Center Repository Service

After the successful installation of Informatica we need to configure repository service. These steps need to be done from windows operating system. Any browser should be installed for performing these steps.
1. Open up the browser and type in below format http://DomainName:PortNo/administrator/index.html
Sample: http://informatica-test:6007/administrator/index.html
2. Enter the username and password provided during the installation.
3. In the Administrator tool, click the Domain tab.
4. In the Navigator, select the folder where you want to create the PowerCenter Repository Service. Note: If you do not select a folder, you can move the PowerCenter Repository Service into a folder after you create it.
5. In the Domain Actions menu, click New > PowerCenter Repository Service. The Create New Repository Service dialog box appears.
6. Provide the Repository Service Name and all other database parameters.
7. Click the option to create repository contents.
8. Select the license, node and all other information that need to be provided.
9. Select “UTF-8 encoding of Unicode” in Code page combo box.
10. Click Ok.

[Informatica] Server Installation 9.0.1

Here are steps to install informatica server 9.0.1:

1. Verify that your environment meets the minimum system requirements, and complete the pre-installation tasks as completed in steps 3 and 4 of this guide.
2. Log in to the machine with a system user account.
3. Close all other applications.
4. On a shell command line, run the install.sh file located in the root directory. The installer displays the message to verify that the locale environment variables are set.
5. If the environment variables are not set, enter N to exit the installer and set them as required. If the environment variables are set, enter Y to continue.
6. Enter C for console mode installation.
7. Press Enter.
8. Enter 1 to install Informatica 9.0.1. Review the system requirements and pre-installation tasks. Verify that all installation requirements are met before you continue the installation.
9. Press Enter.
10. Enter the path and file name of the Informatica license key.
11. Enter the path for the installation directory or press Enter to use the default directory. The directory names in the path must not contain spaces or the following especial characters: @|* $ # ! % ( ) { } [ ] , ; '
12. Review the installation information, and press Enter to continue. The installer copies the Informatica files to the installation directory.
13. Enter 1 to create a domain. If you create a domain, the node on the current machine becomes a gateway node on the domain. The gateway node contains a Service Manager that manages all domain operations.
14. Select whether to set up a secure connection between the Service Manager and the Administrator tool. Press 1 to Enable HTTPS for Informatica Administrator.
15. Select the database to use for the domain configuration repository. Press 1 to select Oracle.
16. Enter the Database User ID and Password.
17. Select how you want to specify the database connection URL. Press 1 for JDBC URL.
18. Now enter the properties at the prompt.
a. Database Address: Enter HostName:PortNo
b. Database service name: Enter SID
c. Configure JDBC parameters: Select yes, enter the parameters or press enter to accept the default.
19. If the database contains a domain configuration repository for a previous domain, select 2 to overwrite the data or set up another database. Press Continue to overwrite.
20. Enter the following information at the prompt:
a. Domain name: Enter Domain Name
b. Domain host name: Enter Host Name
c. Node name: Press Enter for default Node Name
d. Domain port number: Press Enter for default port name
e. Domain user name: Enter username, this would be used for initial setup of Informatica
f. Domain user password: Enter Password
g. Confirm password: Enter Password again
21. Now it would ask whether to display the default ports for the domain and node components assigned by the installer. Press 1 for No.
22. Installation is now complete.
23. Go to the Informatica installation directory and write the below command to bring up the Informatica service.
infaservice.sh startup

[Informatica] How to import workflow in Informatica PC

Here are the steps to import workflow in Informatica Power Centre:

1. Verify that xml file is present in source folder.
2. Open Informatica PC Repository Manager.
3. Connect to the repository by providing relevant username and password.
4. Create a new folder in repository.
5. Go to that folder and then under Repository menu at the top click import objects.
6. Select the file that is present in informaticarepository folder and click next.
7. In this screen click button add all as you would need all the objects to execute workflow which are currently present in xml file and click next.
8. Select the folder which was created recently and click next.
9. Now click next one more time and click import. It would show you all the objects import status.
10. Now you can verify in the folder that workflow exists.

[T-SQL] Database backup procedure - advanced

I have got this script from one of the blogs:

if exists (select * from sysobjects where id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBackupAllDatabases]
go

Create Procedure spBackupAllDatabases
@Path varchar(128) ,
@Type varchar(4) -- Full / Log
as
/*
Backup file format
_Full_yyyymmdd_hhmmss.bak
_Log_yyyymmdd_hhmmss.bak

exec spBackupAllDatabases 'c:\SQLBackups\', 'Full'
*/
/*
drop table DatabaseBackup

Create table DatabaseBackup
(
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionPeriodFull datetime not null ,
RetentionPeriodLog datetime not null
)
*/
set nocount on
declare @sql varchar(1000)

-- Get all database names
create table #DBName
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod datetime null
)

insert #DBName
(Name)
select name
from master..sysdatabases

-- Include any new databases in the backup
insert DatabaseBackup
(
Name ,
BackupFlagFull ,
BackupFlagLog ,
RetentionPeriodFull ,
RetentionPeriodLog
)
select #DBName.Name ,
'Y' ,
'N' ,
'7 jan 1900' , -- default one week
'1 jan 1900'
from #DBName
left outer join DatabaseBackup
on DatabaseBackup.Name = #DBName.Name
where DatabaseBackup.Name is null
and lower(#DBName.Name) <> 'tempdb'

-- Remove any non-existant databases
delete DatabaseBackup
where not exists
(
select *
from #DBName
where #DBName.Name = DatabaseBackup.Name
)

delete #DBName

create table #ExistingBackups
(
Name varchar(128) ,
ID int identity (1,1)
)

-- loop through databases
declare @Name varchar(128) ,
@RetentionPeriod datetime ,
@LastBackupToKeep varchar(8) ,
@ID int ,
@MaxID int

insert #DBName
(Name, RetentionPeriod)
select Name, case when @Type = 'Full' then RetentionPeriodFull else RetentionPeriodLog end
from DatabaseBackup
where (@Type = 'Full' and BackupFlagFull = 'Y')
or (@Type = 'Log' and BackupFlagLog = 'Y')

select @MaxID = max(ID) ,
@ID = 0
from #DBName

while @ID < @MaxID begin -- get next database to backup select @ID = min(ID) from #DBName where ID > @ID

select @Name = Name ,
@RetentionPeriod = RetentionPeriod
from #DBName
where ID = @ID

-- Delete old backups
delete #ExistingBackups
select @sql = 'dir /B ' + @Path
select @sql = @sql + @Name + '_' + @Type + '*.*'

insert #ExistingBackups exec master..xp_cmdshell @sql

if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups

select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep

declare @eID int ,
@eMaxID int ,
@eName varchar(128)

-- loop round all the out of date backups
select @eID = 0 ,
@eMaxID = coalesce(max(ID), 0)
from #ExistingBackups

while @eID < @eMaxID begin select @eID = min(ID) from #ExistingBackups where ID > @eID
select @eName = Name from #ExistingBackups where ID = @eID

select @sql = 'del ' + @Path + @eName
exec master..xp_cmdshell @sql
end
delete #ExistingBackups

-- now do the backup
select @sql = @Path + @Name + '_' + @Type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
if @Type = 'Full'
backup database @Name
to disk = @sql
else
backup log @Name
to disk = @sql
end

go

[T-SQL] Database backup script

Today i got a request to provide a database backup script in such a way that every time it generated new name for database. So here is the script:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),114)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
Print @filename
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor