Need some help in Migration of Jobs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Need some help in Migration of Jobs
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
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
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...
Best take a backup first...
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
This is not a good idea. These 2 databases have different properties in their plug ins. Oracle to Oracle is a lot safer.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...
Mamu Kim
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 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
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
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..
Thanks
Sudharsanan A N
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Thanks Craig.. I got the ouput with the Query that you provided...
and here are the Results..
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
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.
Thanks
Sudharsanan A N
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:
I'm sure someone here knows the 'right' way to do this, including which OLETYPE is the DB2 stage.
Code: Select all
SELECT OLETYPE,JOBID,COUNT(*) from DS_JOBOBJECTS GROUP BY OLETYPE,JOBID
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
You will find this and other tools on the DataStage Tools page of www.anotheritco.com. These tools support DataStage versions 6 and above.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
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
;
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.