Note: The automatic processing of Rational Team Concert data sources is available in the Rational Insight 1.0.0.1 ETL.

The IBM® Rational® Insight extract, transform, and load (ETL) process for Rational Team Concert is different from other supported point product data extractions provided in the out-of-the-box Data Manager catalog, because it uses a direct database connection to the IBM® Rational® Team Concert™ data warehouse. There are no REST services to provide the data to the ETL, and there are no associated XML metadata configuration files.

To perform an ETL action on multiple Rational Team Concert data sources in Rational Insight, you must use IBM® Cognos® Data Manager procedure component to make system calls. You must complete these tasks to set up your environment:

1. Register the Data Manager database in the Insight Data Manager catalog so that you can get the encrypted password for the user account that connects to the catalog database.
2. For each Rational Team Concert resource that needs to be processed, register its database. Use a specific naming convention for the connection profile name, such as “Rational Team Concert X,” where X is a digit.
3. Remove the definition of the RESOURCEGROUP_ID variable from the RTCE2E job.
4. Manually export the registered database aliases from the catalog database.
5. Generate a batch file that creates alias files to use with the rundsnode.exe executable for each of the Rational Team Concert resources.
6. Generate a batch file and execute commands to process all of the Rational Team Concert resources that were created in the previous step.

This procedure sets up an automatic process to generate the alias files to process all Rational Team Concert resources in an end-to-end scenario. You use only one scheduled job to invoke the RTCE2E node against all Rational Team Concert repositories, in sequence. This process will only work for Rational Team Concert resources that are all deployed on the same DBMS. That is they are all deployed on DB2 or all deployed on Oracle or all deployed on SQL Server.

Register the Data Manager database

To register the Data Manager database in the Insight Data Manager catalog:

1. In Data Manager, select Library > Connections (see Figure 1).
2. Create an entry for the Data Manager catalog database (see Figure 2).

Back to top

Register the Rational Team Concert resources

1. In Data Manager, select Library > Connections.
2. For each additional Rational Team Concert database that you need to process, create a connection entry that uses the naming convention "Rational Team Concert X," where X is a digit (see Figure 3).

Back to top

Remove the RESOURCEGROUP_ID variable definition

The predefined RESOURCEGROUP_ID variable at the RTCE2E job node groups all data from various Rational Team Concert resources as the same data source. Because you want to process more than one Rational Team Concert resource, you must delete the variable at the RTCE2E node.

To uniquely identify the data from each Rational Team Concert resource in the Rational Insight data warehouse, you must first define the RESOURCEGROUP_ID variable outside of the scope of the job; that is, define and set it at runtime from the command line and then find a good, unique value to set it to.

In the following example, the variable is set to the value of the database alias itself:

To set a variable from the command line when invoking the rundsjob executable file, you must pass the -V flag along with the variable name and value that you want to set it to.

-V<variable>=<value>

Back to top

Export the aliases from the catalog

Export the registered database aliases so that you can get the encrypted password for the Data Manager database that you registered in the first step. You do this so that the batch files that are generated in the subsequent steps do not store nor use a clear text password.

1. To export the aliases, use the catexp utility in the Data Manager.
2. After you generate the file and extract all of the information, make sure that you delete the file so that you do not leave behind profile and connection information. Anyone with access to the file, server, knowledge of the catalog, and the jobs it contains might be able to invoke a Data Manager job against a repository.

The following is an example of a command to issue from a command prompt for an IBM® DB2® database:

catexp DB2 DSN=DMCAT;UID=<user name>;PWD=<passwd> DMCatAlias.txt A

where <user name> is the account that you use to connect to the catalog database and <password> is the password for this account. The contents of the export are located in the DMCatAlias.txt file, and the A flag forces the command to export only the database aliases.

3. In the DMCatAlias.txt file, locate the entry that pertains to the Data Manager catalog, and take note of the encrypted password:

// Database Alias specification
// Data Manager - catalog API v8.0
<DEFAULT> 'DMCAT' DB2 'DSN=DMCAT;UID=dweadmin;
PWD=~3EC298FE80F9AD09D22FE05EF394CE4C0B;TRUSTED=FALSE;COGNOSSQL=FALSE'

Back to top

Automatically generate the connection files

In the Insight Data Manager catalog, the default Rational Team Concert builds normally processes the connection with a profile name of "Rational Team Concert". In order to process more than one Rational Team Concert resource, without having to duplicate the builds in the catalog for each resource, you must change the connection information for the "Rational Team Concert" connection dynamically. To accomplish this:

1. Create an alias file for each of the Rational Team Concert resources. The file contains a connection entry for the Rational Insight warehouse, and the Rational Team Concert resource in question.
2. Then, create a job and use a procedure node that will issue system commands to generate and run a batch file.

The UI in Figure 4 contains an example of the variables and their values at the job level, and the list that follows defines the variables.

• DMCATUSER: The account to use to connect to the Data Manager catalog database
• DMCATPASSWD: The encrypted password for this account that you extracted previously in the process
• DMCATDRIVER: The RDBMS driver to use for the connection, IBM DB2, Oracle, or Microsoft® SQL Server®
• SQLSERVINST: If SQL Server is used for the Data Manager catalog database, here is where to specify the instance where it resides
• WINAUTH: If SQL Server is used for the Data Manager catalog database, here is where to specify whether windows authentication should be used (in that case set the value to TRUE) otherwise use SQL Server authentication (in that case set the value to FALSE).
• DMCATCONST: The connection string to the Data Manager catalog database, based on what DMCATDRIVER (driver) is specified.

Here, we examine the cases of the DB2, Oracle, and SQL Server drivers. You can add to the list or make it specific to support only one kind of driver in your environment. The following definitions are for Microsoft® Windows® and Linux®:

CASE Upper($DMCATDRIVER) OF
BEGIN
'DB2': RETURN 'DB2 DSN={$DMCATDB};UID={$DMCATUSR};PWD={$DMCATPASSWD}';
'ORACLE': RETURN CONCAT('ORACLE ','{$DMCATUSR}','/','{$DMCATPASSWD}','@','{$DMCATDB}');
'OLEDB': CASE $WINAUTH OF
       BEGIN
        TRUE:RETURN CONCAT('OLEDB   "Data Source"=','{$SQLSERVINST}',';
"Integrated Security"=TRUE',';Database=','{$DMCATDB}');
        DEFAULT:RETURN CONCAT('OLEDB   "Data Source"=','{$SQLSERVINST}',';
"User Id"=','{$DMCATUSR}',';Password=','{$DMCATPASSWD}',';"Integrated Security"=FALSE',
';Database=','{$DMCATDB}'); 
         END
DEFAULT: RETURN 'NOT SUPPORTED RDBMS';

Note:
For Linux, you must escape the semicolon (;) with a backslash (\) as the ; is a statement terminator in Linux.

CASE Upper($DMCATDRIVER) OF
BEGIN
'DB2': RETURN 'DB2 DSN={$DMCATDB}\;UID={$DMCATUSR}\;PWD={$DMCATPASSWD}';
'ORACLE': RETURN CONCAT('ORACLE ','{$DMCATUSR}','/','{$DMCATPASSWD}','@','{$DMCATDB}');
DEFAULT: RETURN 'NOT SUPPORTED RDBMS';
END

The node in the job is a procedure node that contains system calls (see Figure 5, Figure 6, and Figure 7).

Click to enlarge

System('mkdir ..\ricon');
System('catexp {$DMCATCONSTR} ..\ricon\ListRTC.txt A');
System('echo @echo off> ..\ricon\rtc.bat');
System('echo pushd ..\ricon >> ..\ricon\rtc.bat');
System('echo SETLOCAL ENABLEDELAYEDEXPANSION >> ..\ricon\rtc.bat');
System('echo set /A TOTRTC=1 >> ..\ricon\rtc.bat');
System(concat('echo for /F "tokens=*" %%g in (',char(39),'findstr /I 
/C:"Rational Team Concert" 
ListRTC.txt',char(39),') do (>> ..\ricon\rtc.bat'));
System('echo findstr /I /C:"Rational Data Warehouse" 
ListRTC.txt ^>RTC!TOTRTC!.txt >> ..\ricon\rtc.bat');
System(Concat('echo FOR /F "tokens=2 delims=',char(39), ' eol=/" %%a  in (',char(39),
'echo "%%g"',char(39),') do ( >>  ..\ricon\rtc.bat'));
System('echo set  GSUB=%%g  >>  ..\ricon\rtc.bat');
System('echo set GSUB=!GSUB:%%a=Rational Team Concert! >>  ..\ricon\rtc.bat');
System('echo echo !GSUB! ^>^> RTC!TOTRTC!.txt>> ..\ricon\rtc.bat');
System('echo SET /A TOTRTC=!TOTRTC!+1>> ..\ricon\rtc.bat');
System('echo )>> ..\ricon\rtc.bat');
System('echo )>> ..\ricon\rtc.bat');
System('echo ENDLOCAL>> ..\ricon\rtc.bat');
System('echo popd >> ..\ricon\rtc.bat');
System('call ..\ricon\rtc.bat');
System('del /F /Q ..\ricon\rtc.bat');

System('mkdir $COG_ROOT/ricon');
System('catexp {$DMCATCONSTR} $COG_ROOT/ricon/ListRTC.txt A');
System('echo "#!/bin/ksh"> $COG_ROOT/ricon/rtc.sh');
System('echo "curDir=\`pwd\`" >> $COG_ROOT/ricon/rtc.sh');
System('echo "cd \$COG_ROOT/ricon" >> $COG_ROOT/ricon/rtc.sh');
System('echo "typeset -i TOTRTC=1" >> $COG_ROOT/ricon/rtc.sh');
System('echo "RIDW=\`cat ListRTC.txt|grep 
-e \"Rational Data Warehouse\"\`
">>$COG_ROOT/ricon/rtc.sh');
System('echo "while read line" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "do" >>$COG_ROOT/ricon/rtc.sh ');
System('echo "echo \$line|grep \"Rational Team Concert\">/dev/null" 
>>$COG_ROOT/ricon/rtc.sh  ');
System('echo "if [ \$? -eq 0 ]" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "then" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "echo \$RIDW>RTC\$TOTRTC.txt" >>$COG_ROOT/ricon/rtc.sh');
System(Concat('echo "echo \$line |sed ',char(39),
's/Rational Team Concert [1-9]/Rational Team Concert/g',char(39),' >>RTC\$TOTRTC.txt" 
>>$COG_ROOT/ricon/rtc.sh'));
System('echo "TOTRTC=\$TOTRTC+1" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "fi" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "done <ListRTC.txt" >>$COG_ROOT/ricon/rtc.sh  ');
System('echo "cd \$curDir" >>$COG_ROOT/ricon/rtc.sh');
System('chmod +x $COG_ROOT/ricon/rtc.sh');
System('$COG_ROOT/ricon/rtc.sh');
System('rm -f $COG_ROOT/ricon/rtc.sh');

The output of this execution is a batch or shell script file called rtc.bat/rtc.sh located in the $COg_ROOT/ricon. After it is executed, it is immediately deleted.

The output in the example in Figure 8 displays three text files, one for each of the connection profiles for DB2 databases for Rational Team Concert and a DB2 Rational Insight database:

• Rational Team Concert
• Rational Team Concert 2
• Rational Team Concert 3

The RTC*.txt files contain the following code shown in Listings 5, 6, and 7.

<DEFAULT> 'Rational Data Warehouse' DB2 'DSN=RIDW;UID=db2admin;
  PWD=~850926D39C5C57FA0AAEA66B472FB0757B;COGNOSSQL=FALSE'
<DEFAULT> 'Rational Team Concert' DB2 'DSN=JAZZDB;UID=dbjzadmin;
  PWD=~BA3E90727065956DB28BB8B2C3E09690CB;TRUSTED=FALSE;COGNOSSQL=FALSE'   

<DEFAULT> 'Rational Data Warehouse' DB2 'DSN=RIDW;UID=db2admin;
  PWD=~850926D39C5C57FA0AAEA66B472FB0757B;COGNOSSQL=FALSE'
<DEFAULT> 'Rational Team Concert' DB2 'DSN=RTCDB;UID=dbdwjadm;
  PWD=~BA3E90727065956DB28BB8B2C3E09690CB;TRUSTED=FALSE;COGNOSSQL=FALSE'  

<DEFAULT> 'Rational Data Warehouse' DB2 'DSN=RIDW;UID=db2admin;
  PWD=~850926D39C5C57FA0AAEA66B472FB0757B;COGNOSSQL=FALSE'
<DEFAULT> 'Rational Team Concert' DB2 'DSN=RTCDW;UID=dbdwadm;
  PWD=~BA3E90727065956DB28BB8B2C3E09690CB;TRUSTED=FALSE;COGNOSSQL=FALSE'  

The physical database connection information for the Rational Team Concert connection profile has been changed. You can now use these files to start the Rational Team Concert jobs.

Back to top

Automatically process Rational Team Concert resources

For each of the RTC*.txt alias files that were generated in the previous step, the Insight Rational Team Concert Data Manager job RTCE2E must be executed against them. To automate this processing, you must use the procedure node from the previous step and add to it the statements outlined below. The process will do the following.

1. Go through each of the RTC*.txt connection profile files, and call the RTCE2E job.
2. After that is complete, the files will be automatically deleted.

System(concat ('@echo OFF && for %c in (..\ricon\RTC*.txt) do (',char(13),
'for /F "skip=1 tokens=2 delims=^=.*;" %g in (',char(39),
'findstr /C:DSN %c',char(39),') do (',char(13),'rundsjob.exe  {$DMCATCONSTR} 
"{$JOB_NAME}" -A%c -VRESOURCEGROUP_ID=%g))'));
System(concat ('cmd /V:ON /C "@echo OFF && for %c in (..\ricon\RTC*.txt) 
do (',char(13),'for /F "skip=1 tokens=2 delims=@" %g in (',char(39),
'findstr /R .* %c',char(39),') do (',char(13),'set vg=%g && rundsjob.exe  
{$DMCATCONSTR} "{$JOB_NAME}" -A%c -VRESOURCEGROUP_ID=!vg:',char(39),'=!))"'));
System(concat ('cmd /V:ON /C "@echo OFF && for %c in (..\ricon\RTC*.txt) 
do (',char(13),'for /F "skip=1 tokens=4 delims=',char(39),'; eol=;" %g in 
(',char(39),'findstr .* %c',char(39),') do (',char(13),'for /F %a in (',char(39),
'echo %g^|FIND /C "Database"',char(39),') do (set ER=%a) && if "!ER!" 
EQU "1" (set vg=%g && rundsjob.exe  {$DMCATCONSTR} "{$JOB_NAME}" -A%c 
-VRESOURCEGROUP_ID=!vg:~9!)))"'));
System(concat ('cmd /V:ON /C "@echo OFF && for %c in (..\ricon\RTC*.txt) do
(',char(13),'for /F "skip=1 tokens=6 delims=',char(39),'; 
eol=;" %g in (',char(39),'findstr .* %c',char(39),') do (',char(13),'for /F %a 
in (',char(39),'echo %g^|FIND /C
"Database"',char(39),') do (set ER=%a) && if "!ER!" EQU "1" \
(set vg=%g && rundsjob.exe  {$DMCATCONSTR} 
"{$JOB_NAME}" -A%c -VRESOURCEGROUP_ID=!vg:~9!)))"'));
System('RMDIR /S /Q ..\ricon');

System('rm -f $COG_ROOT/ricon/rtc.sh');
System(concat('echo "for FILE in \`ls $COG_ROOT/ricon/RTC*.txt\`; do if [ \`sed 1d
\$FILE|grep -c DSN \` -gt 0 ]; then $COG_ROOT/bin/rundsjob  {$DMCATCONSTR} \"{$JOB_NAME}\"
-A\$FILE -VRESOURCEGROUP_ID=\`sed -n ',char(39),'/Rational Team \ Concert/p',
char(39),'  \$FILE |sed  ',char(39),'s/.*DSN=//g',char(39),' |sed \
',char(39),'s/;.*//g',char(39),'\`; fi; 
done">$COG_ROOT/ricon/ex_rtc.sh'));
System(concat('echo "for FILE in \`ls $COG_ROOT/ricon/RTC*.txt\`; do if [ \`sed 1d 
\$FILE|grep -c @ \` -gt 0 ]; then $COG_ROOT/bin/rundsjob  {$DMCATCONSTR} \"{$JOB_NAME}\" 
-A\$FILE -VRESOURCEGROUP_ID=\`sed -n ',char(39),'/Rational Team Concert/p', char(39),
' \$FILE |sed 
 ',char(39),'s/\(.*\)@//g',char(39),'|sed s/\',char(39),'//g\`; fi; 
done">>$COG_ROOT/ricon/ex_rtc.sh'));System('chmod +x $COG_ROOT/ricon/ex_rtc.sh');
System('$COG_ROOT/ricon/ex_rtc.sh');
System('rm -fR $COG_ROOT/ricon');

The job runs the executable file rundsjob.exe and runs the job RTCE2E by connecting to the databases that is specified in the RTC*.txt files. At the same time, you set the value of RESOURCEGROUP_ID.

Back to top

Download

Information about download methods

Note

1. Use the code samples provided in this txt file for code listings 1, 3, 4, 8, and 9.

Resources

Learn

• Find out about IBM Rational Team Concert features and benefits:
  
  • Rational Team Concert Information Center
  • IBM developerWorks page for Rational Team Concert, with links to many other resources
  • Jazz.net forums
  • Webcast: Using Rational Team Concert in a globally distributed team
  • Demo: Dashboards and reports
  • Podcast: IBM Rational Team Concert and Jazz
  • Trial downloads (free):
    • Enterprise Edition
    • Express Edition
    • Standard Edition
  
  
• Learn more about Rational Insight, a performance measurement and management tool that, helps you improve project and process performance:
  • Rational Insight Information Center, where you can also watch a demo
  • Rational Insight page on IBM develperWorks
  • Forum: Performance management with Rational Insight
  
  
• Learn about other applications in the IBM Rational Software Delivery Platform, including collaboration tools for parallel development and geographically dispersed teams, plus specialized software for architecture management, asset management, change and release management, integrated requirements management, process and portfolio management, and quality management. You can find product manuals, installation guides, and other documentation in the IBM Rational Online Documentation Center.
  
  
• Visit the Rational software area on developerWorks for technical resources and best practices for Rational Software Delivery Platform products.
  
  
• Explore Rational computer-based, Web-based, and instructor-led online courses. Hone your skills and learn more about Rational tools with these courses, which range from introductory to advanced. The courses on this catalog are available for purchase through computer-based training or Web-based training. Additionally, some "Getting Started" courses are available free of charge.
  
  
• Subscribe to the Rational Edge newsletter for articles on the concepts behind effective software development.
  
  
• Subscribe to the IBM developerWorks newsletter, a weekly update on the best of developerWorks tutorials, articles, downloads, community activities, webcasts and events.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Download trial versions of IBM Rational software.
  
  
• Download these IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Tivoli®, and WebSphere®.
  
  

Discuss

• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Register the Data Manager database
• Register the Rational Team Concert resources
• Remove the RESOURCEGROUP_ID variable definition
• Export the aliases from the catalog
• Automatically generate the connection files
• Automatically process Rational Team Concert resources
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Rational Team Concert Standard Edition
• Demo: Rational and Jazz
• Webcast: The Jazz revolution: Scaling agile practices across the enterprise
• Briefing: Jazz and Rational Team Concert
• Buy: Rational Team Concert Standard Edition

My developerWorks community

Dig deeper into Rational on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers