DataStage Engine commands?
Moderators: chulett, rschirm, roy
DataStage Engine commands?
Hi all,
Do you have more information about DataStage Engine commands?
i need to fetch the job name, categroy,update action, and the array size and Rows per trasaction information for a list of jobs.
what could be the command that i need to issue in command interface of Ds administrator?
Thanks in advance
dsedi
Do you have more information about DataStage Engine commands?
i need to fetch the job name, categroy,update action, and the array size and Rows per trasaction information for a list of jobs.
what could be the command that i need to issue in command interface of Ds administrator?
Thanks in advance
dsedi
Good luck. I doubt if Ray could get all that easily. Job and category are easy. They are already been posted. Do a search. I am sure some of the rest may be in Reporting Assistant or MetaStage. JobReport may pull that out as well. I would almost think an XML export or using Perl against the DSX file would work. Array size and transaction size should be easy to find if you know how to hack into DS_JOBOBJECTS records. Update action is a lot harder to hack. It is a probably a number like 1 to 7. You can put an odd value in array size in then go search for it and see where DataStage stores the value. It will all depend on the stage type on what field they use. How many stage types do you use?
Mamu Kim
If you are referring to a stage other than ODBC, UV or UniData the metadata you require is stored in both the input and output stage. All of the records in DS_JOBOBJECTS that contain either CCustomInput or CCustomOutput in field 2 contain the information you seek.
This information is stored as multi valued associated data in both records. CCustomInput stores this information in fields 13 - -15 with 13 being the parent property and identifying how many values exist. The CCustomOutput stage stores this data in fields 12-14 and would have the same layout with 12 being the parent property.
In the input record field 14 gives you the property name and field 15 gives you the value. In the output record field 13 gives you the property name and field 14 gives you the value. Both are multi valued associated fields.
As far as getting at these values you have a couple of options. You can use SELECT (both SQL and Retrieve) syntax, although it will be a bit tricky if you do not understand multi valued fields.
I do not recommend writing custom routines to access these records since the chance exists that something could be corrupted, therefore your repository would be corrupted.
Regards,
This information is stored as multi valued associated data in both records. CCustomInput stores this information in fields 13 - -15 with 13 being the parent property and identifying how many values exist. The CCustomOutput stage stores this data in fields 12-14 and would have the same layout with 12 being the parent property.
In the input record field 14 gives you the property name and field 15 gives you the value. In the output record field 13 gives you the property name and field 14 gives you the value. Both are multi valued associated fields.
As far as getting at these values you have a couple of options. You can use SELECT (both SQL and Retrieve) syntax, although it will be a bit tricky if you do not understand multi valued fields.
I do not recommend writing custom routines to access these records since the chance exists that something could be corrupted, therefore your repository would be corrupted.
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
Take a look at my tool, List all files and tables used by jobs in a dsx file. This may give you a starting point, or a short list of jobs and tables of interest.
You will find it on the DataStage Tools page at www.anotheritco.com.
There is also a newly posted tool that Lists routines called by jobs and routines in a dsx file.
You will find it on the DataStage Tools page at www.anotheritco.com.
There is also a newly posted tool that Lists routines called by jobs and routines in a dsx file.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
Michael
Where are array size and transaction size stored in ODBC?
For custom stages:
Where are array size and transaction size stored in ODBC?
For custom stages:
Code: Select all
select
DS_JOBS.NAME AS JOB_NAME FMT '35L',
DS_JOBS.CATEGORY,
DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L',
EVAL DS_JOBOBJECTS."IF @RECORD<13,7> = 'ARRAYSIZE' THEN @RECORD<14,7> ELSE IF @RECORD<14,6> = 'ARRAYSIZE' THEN @RECORD<15,6> ELSE ''" AS ARRAYSIZE FMT '8R',
EVAL DS_JOBOBJECTS."IF @RECORD<14,7> = 'TRANSSIZE' THEN @RECORD<15,7> ELSE ''" AS TRANSSIZE FMT '8R'
from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OLETYPE LIKE 'CCustom%'
and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
;
Mamu Kim
I knew it was a simple matter of placing this bit of information in front of Kim and there would be a nicely written SQL statement within hours :D
Thanks Kim!
Thanks Kim!
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Kim,
To answer your question regarding ODBC input and output stages, the following applies -
CODBCInput
F21 - Trans Isolation Level
F22 - Trans Size
F23 - Array Size
CODBCOutput
F15 - Trans Isolation Level
Trans Isolation can be -
Regards,
To answer your question regarding ODBC input and output stages, the following applies -
CODBCInput
F21 - Trans Isolation Level
F22 - Trans Size
F23 - Array Size
CODBCOutput
F15 - Trans Isolation Level
Trans Isolation can be -
- Value Meaning
-1 All SQL statements are committed individually.
0 No transaction isolation.
1 Another transaction can access the data before it is committed.
2 Acquire read or write lock so that other transactions cannot access the data.
4 Acquire read locks on all data returned, and write locks on all modified data. Ensures that data does not change whilst transaction is in progress.
8 Lock entire range of data affected by transaction.
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
I would just like to reiterate that while some of this information can be easily gathered and used, other pieces of information are very difficult to retrieve without a very intimate knowledge of the repository metadata.
Others have pointed out numerous methods that are supported by the toolset like usage analysis, "right" clicking on a stage type to find out all the jobs where it is used etc.... I would like to empahsize that these should be used first since much of the information is contained within already established tools within DataStage.
It's certainly fun to hack away at the foundation of your project to gain a better understanding of how DS works, but this hacking can and does sometimes lead to corrupt projects that Ray and the others end up helping to recover!
Regards,
Others have pointed out numerous methods that are supported by the toolset like usage analysis, "right" clicking on a stage type to find out all the jobs where it is used etc.... I would like to empahsize that these should be used first since much of the information is contained within already established tools within DataStage.
It's certainly fun to hack away at the foundation of your project to gain a better understanding of how DS works, but this hacking can and does sometimes lead to corrupt projects that Ray and the others end up helping to recover!
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ray,
Thanks for pointing that out and I meant to state that very fact. Every version does in fact add additional fields or will overload current fields depending on the version and if it is PX or server.
Good catch
Regards,
Thanks for pointing that out and I meant to state that very fact. Every version does in fact add additional fields or will overload current fields depending on the version and if it is PX or server.
Good catch
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Expectation Management
I should point out that this is a service we sell; it's not done for free through this forum. It's a highly skilled and time consuming process, and we do this (among other things) for a living.It's certainly fun to hack away at the foundation of your project to gain a better understanding of how DS works, but this hacking can and does sometimes lead to corrupt projects that Ray and the others end up helping to recover!
Only last week I had to recover a project; not a hacked one, but one that got damaged (in the DS_JOBOBJECTS table) when a switch between the server and the SAN failed. It took six hours of intense work, and it wasn't that large a project. A couple of other files were also corrupted.
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.
Michael
Here it is:
Here it is:
Code: Select all
select
DS_JOBS.NAME AS JOB_NAME FMT '35L',
DS_JOBS.CATEGORY,
DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L',
EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<23> ELSE ''" AS ARRAYSIZE FMT '8R',
EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<22> ELSE ''" AS TRANSSIZE FMT '8R',
EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<21> ELSE ''" AS TRANS_LEVEL FMT '8R'
from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OLETYPE LIKE 'CODBCInput'
and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
;
Mamu Kim