Change Target Databases

[cognostechie]

I want to use two DWs, one that will be populated today and the second one will be populated tomorrow. Both DWs will be used simultaneously on different days after getting populated. So what I need is to have the same catalog populate DB1 today and DB2 tomorrow.

As I understand  we can use RUNDSJOB via command line to  start the process to execute the jobstream.

Ex: RUNDSJOB "OLEDB" "Database" "Userid/Password" "SalesFactJobstream"

However , it doesn't seem to work, maybe I am missing something here? Incorrect syntax?

Would the 'Database' specified here be the Database that would be populated with the ETL process?
If so, I can just have two scripts with two different Databases names?

Is there any other process for my requirement?

or can somebody pass on the correct syntax ?

Thanks a lot !

[MFGF]

Hi,

The rundsjob command needs to locate the database containing the catalog in order to retrieve the jobstream specification, so this is what the "Database" option you are thinking of in the command line is for. It might be worth investigating the use of a Database Alias file using the -A option? You could have two different Database Alias files which you use on alternate days. You could probably create one using the CATEXP command, then copy it and edit it to create the second?

Just a thought,

MF.

[cognostechie]

Thanks so much MFGF ! That explanation of which 'Database' it is really helped a lot !

I was able to do this and as you suggested, now I have two scripts that will be scheduled to run on alternate days.

This is what I have now:

FirstScript.bat
-----------------

cd\
cd "Program Files\cognos\c83\bin"
rundsjob -AC:\DataManager\Usenorthwind.txt OLEDB "User Id=sa;Password=dddxxx;Database=DMCatalog;Data Source=ComputerName\SQLEXPRESS2008" Orderstream

Usenorthwind.txt
----------------------

<DEFAULT> 'NorthwindDM' OLEDB 'User Id=sa;Password=dddxxx;Database=NorthwindDM;Data Source=ComputerName\SQLEXPRESS2008;Provider=sqloledb (SQL Server 2000 OLEDB);Integrated Security=FALSE;COGNOSSQL=FALSE'
<DEFAULT> 'Northwind' OLEDB 'User Id=sa;Password=dddxxx;Database=Northwind;Data Source=ComputerName\SQLEXPRESS2008;Provider=sqloledb (SQL Server 2000 OLEDB);Integrated Security=FALSE;COGNOSSQL=FALSE'

SecondScript.bat
---------------------

cd\
cd "Program Files\cognos\c83\bin"
rundsjob -AC:\DataManager\Usesouthwind.txt OLEDB "User Id=sa;Password=dddxxx;Database=DMCatalog;Data Source=ComputerName\SQLEXPRESS2008" Orderstream

Usesouthwind.txt
----------------------

<DEFAULT> 'NorthwindDM' OLEDB 'User Id=sa;Password=dddxxx;Database=SouthwindDM;Data Source=ComputerName\SQLEXPRESS2008;Provider=sqloledb (SQL Server 2000 OLEDB);Integrated Security=FALSE;COGNOSSQL=FALSE'
<DEFAULT> 'Northwind' OLEDB 'User Id=sa;Password=dddxxx;Database=Northwind;Data Source=ComputerName\SQLEXPRESS2008;Provider=sqloledb (SQL Server 2000 OLEDB);Integrated Security=FALSE;COGNOSSQL=FALSE'


The 'NorthwindDM' and 'SouthwindDM' are the Data marts that will be populated with the ETL process. It still needs
both the alias files to have the same alias names which is 'NorthwindDM' right after the <DEFAULT> because the
Data Source name in Data Manager is 'NorthwindDM'. The Data Source is configured to point to NorthwindDM. So the script via the alias file points that Data Source to use another DB (SouthwindDM).  The alias files also need to have the information
for the source DB from which it reads the data (Northwind).

Took me quite a while to get this working so sharing the exact commands. It might help somebody.

[MFGF]

Marvellous! I'm really pleased you got a working solution. :) Thanks for sharing, too - it could save others in a similar situation a lot of time and effort.

Cheers!

MF.