Recognize an OCI stage in DS_JOBOBJECTS

Umbix62 · Post by **Umbix62** » Wed Aug 23, 2006 10:17 am

How can I recognize an OCI stage in the DS_JOBOBJECTS table (or other one) of DataStage Repository?

Can I know the columns name and type used in referenced stages from the previous query?

Thank you in advance

Umberto

kduke · Post by **kduke** » Wed Aug 23, 2006 6:37 pm

All records in DS_JOBOBJECTS are controller or identified by the field OLETYPE. This field really identifies the Windows program which deals with that plugin. You do not really care about how it is implemented normally all you want to do is report on it. The Oracle plugins almost all have OCI in the name. I could look them up if you want. The NAME field in DS_JOBOBJECTS is either the link name or the stage name in most cases. To get the job name you need to join JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO. There is lots of SQL posted to show how to get some of these fields.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 24, 2006 12:54 am

Most of the columns in the Repository tables are (deliberately) un-named. This allows the vendor to maintain the mystique and to make hacking the repository more difficult. It is also the case that many of the repository tables contain more than one record type (and therefore record format), which makes a mockery of formal SQL nomenclature - but is perfectly acceptable in a non-first-normal-form database such as the DataStage Engine.

Umbix62 · Post by **Umbix62** » Thu Aug 24, 2006 1:20 am

kduke wrote:All records in DS_JOBOBJECTS are controller or identified by the field OLETYPE. This field really identifies the Windows program which deals with that plugin. You do not really care about how it is implemented normally all you want to do is report on it. The Oracle plugins almost all have OCI in the name. I could look them up if you want. The NAME field in DS_JOBOBJECTS is either the link name or the stage name in most cases. To get the job name you need to join JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO. There is lots of SQL posted to show how to get some of these fields.

Thank you for the answer. Another question. Once it recognize the rows related to an OCI stage in the DS_JOBOBJECTS, can I extract the informations about the layer of tha table referenced by the stage? For example.

Using the designer I create a Job Server reading from an Oracle Table and writing data into a delimited file. The Oracle Table has only two columns. Column "A", varchar(255) not null, and column "B", decimal(10,2) not null. How can I referenced the information about columns of Oracle Table reading the DataStage Repository (in my example extracting information about columns "A" and "B"?

Thank you in advance

Umberto

kduke · Post by **kduke** » Thu Aug 24, 2006 6:34 am

Post your OLETYPE for the link in question then we will see if we can extract the column names. You need to specify the exact columns you want. Most plugins use a Custom stage and the table name is buried in a property field. There is a property name field and an associated property value field. If I remember correctly the property name is TABLE. These are multivalued fields. There are many property names and values in one record. Once the dictionary fields are created then you can report on them.

Column names and types are also multivalued.

kduke · Post by **kduke** » Thu Aug 24, 2006 6:37 am

You can extract this information in a DS job and create your own metadata tables. The latest EtlStats has several jobs which do this now. The dictionary items are also included. One of the jobs pulls the table names from custom links. It also pulls the property for truncate so I could create a report of all jobs which truncate or clear the table. We do not want to back these tables up any more so we put them in a separate schema.