DSXchange

Posted: **Wed Jun 08, 2005 1:49 am**

HI Gurus,

We are in the process of converting a warehouse database from db2 to oracle..while doing the conversion we will change all the db2 stages into oracle stages.. we want to know what are the precautions that are to be taken while doing the conversion.. can anybody share there experiences on conversions of databases.. I would like to know is there any query/routine to find out how many jobs are using that a particular stage (here in my case probably db2/odbc stage).. as we have more no. of jobs..we would like to know the impact of this change... any help on this would be really helpful....

Thanks
Sudharsanan A N

Posted: **Wed Jun 08, 2005 6:48 am**

You could do a SELECT against DS_JOBOBJECTS and count(*). You need to GROUP BY OLETYPE. This should give you what you want. To run SQL against the repository use DataStage Administrator and select your project then press the Command button.

Posted: **Wed Jun 08, 2005 9:31 am**

If you're brave enough, you can write a basic routine to automatically update your DSX export file, so every DB2 plugin is changed to an Oracle one. I did this recently upgrading an oracle 8 to 9 system.

Best take a backup first...

Posted: **Wed Jun 08, 2005 9:49 am**

You can do an usage analysis to obtain the jobs where the stage is used.

Posted: **Wed Jun 08, 2005 10:23 am**

If you're brave enough, you can write a basic routine to automatically update your DSX export file, so every DB2 plugin is changed to an Oracle one. I did this recently upgrading an oracle 8 to 9 system.

Best take a backup first...

This is not a good idea. These 2 databases have different properties in their plug ins. Oracle to Oracle is a lot safer.

Posted: **Wed Jun 08, 2005 12:54 pm**

You may also need to alter any user-defined SQL to adhere to the syntax of new RDBMS.

Posted: **Thu Jun 09, 2005 2:12 am**

Hi Gurus,

Thanks for your inputs.. I tried the Duke method to find out how many jobs are using DB2/ODBC stage by using the Administrator command line..I gave the following query

Code: Select all

select count(*) from DS_JOBOBJECTS GROUP BY OLETYPE
The result was 82 record selected..

I think i am missing the where condition in the query to select only the DB2/ODBC Stages. I don't know which column and the value for those stages... can someone help me in solving this issue...

Thanks
Sudharsanan A N

Posted: **Thu Jun 09, 2005 6:44 am**

Select the OLETYPE as well as the count, so you can see what objects the counts are associated with. After that, if should be fairly obvious.

Posted: **Thu Jun 09, 2005 11:01 pm**

Thanks Craig.. I got the ouput with the Query that you provided...
and here are the Results..

Code: Select all


SELECT OLETYPE,COUNT(*) from  DS_JOBOBJECTS GROUP BY OLETYPE 

OLETYPE........... count(*)
CContainerView  ---8
CCustomInput    ---5
CCustomOutput  ---7
CCustomStage   --- 10
CHashedFileStage---2
CHashedInput     ---1
CHashedOutput   ---1
CJSActivityInput  ---3
CJSActivityOutput---3
CJSJobActivity     ---2
CJSRoutineActivity---2
CJobDefn            ---8
CSeqFileStage     ---7
CSeqInput          ---4
CSeqOutput        ---3
CTransformerStage---5
CTrkInput           ---6
CTrxOutput         ---5

18 Records listed.

I need some more input.. from the query i cannot get the count of jobs where the DB2/ODBC Stage is used.. i want to count of jobs that uses these stages...Please help me in finding the count...

Thanks
Sudharsanan A N

Posted: **Fri Jun 10, 2005 6:35 am**

I don't have this information off the top of my head, but there must be a field that related DS_JOBOBJECTS back to DS_JOBS. Let's make one up and call it JOBID. Include that in the query:

Code: Select all

SELECT OLETYPE,JOBID,COUNT(*) from  DS_JOBOBJECTS GROUP BY OLETYPE,JOBID

I'm sure someone here knows the 'right' way to do this, including which OLETYPE is the DB2 stage.

Posted: **Fri Jun 10, 2005 8:25 am**

My List all files and tables used by jobs in a dsx file tool will give you the information necessary to identify all jobs with DB2 stages, and let you know if they are used for input or output. Additionally, if the stages use user defined SQL, it is include in the output.

You will find this and other tools on the DataStage Tools page of www.anotheritco.com. These tools support DataStage versions 6 and above.

Posted: **Fri Jun 10, 2005 6:45 pm**

CCustomStage, CCustomInput or CCustomOutput is probably what you want.

Code: Select all

select 
   DS_JOBS.NAME AS JOB_NAME FMT '35L', 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L'
from 
   DS_JOBOBJECTS, 
   DS_JOBS
where 
   DS_JOBOBJECTS.OLETYPE = 'CCustomInput' 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
group by 
   JOB_NAME,
   LINK_NAME
;

Code: Select all

select 
   COUNT(*) 
from  
   DS_JOBOBJECTS,
   DS_JOBS
where
   DS_JOBOBJECTS.OLETYPE = 'CCustomInput'
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
;

Posted: **Sat Jun 11, 2005 4:51 pm**

All this is likely to change substantially in the next release ("Hawk"), which is only a few months away. Try to wean yourselves off hacking the Repository where possible.

Posted: **Sat Jun 11, 2005 9:32 pm**

We will hack hawk too. Say it with me. Hacking is fun.

DSXchange

Need some help in Migration of Jobs

Need some help in Migration of Jobs