Assigning Job Serial Number

[SGT]

My requirement is this:
I have to assign some sort of user defined serial numbers to all the jobs in my project.
For instance
Job1 - SERIAL1001
Job2 - Serial1002
Job3 - Serial1003.

One way of doing this could be adding the serial number to the job name.
But if possible I want to avoid it.
Using this serial number I should be able to retrieve the job name also.
(Eg:- Given Serial1003 I should be able to retrieve the job name as Job3)

What mechanism can I use in Datastage to do this.
Can I add this serial number to the Job short description or elsewhere and then use it to query and
get the job.

Thanks,
SGT

[mhester]

SGT,

I now understand what you want to do and believe that outside of naming the job with a serial # or adding it to the short description "serializing" the job would be tough. The job already has a number in DS_JOBS that uniquely identifies it within that project. This number cannot be used since it may not be the same number from project to project.

If you chose to add it to the short description you could query the DS_JOBOBJECTS file to retrieve the name of the job. Try adding a serial number to your Job3 in the short description like -

SERIAL0003 (or whatever)

Now from the administrator enter the following command -

LIST DS_JOBOBJECTS LIKE ...ROOT AND F4 LIKE ...SERIAL0003... F3 F4

This should bring back the one job that has this serial number in the short description and display the job name and short description. Remember that since this is the short description there will be no "enforcement" of serial numbers across jobs or projects - this will fall on the development staff to maintain serial number correctness/uniqueness.

Maybe others will have a better solution, but I have never been faced with this type of requirement so this is the best that I have.

Also, the above command is one that will do no harm to any repository object.

Regards,

Michael Hester

[SGT]

Thanks for the solution. I understand that it will be the responsibility of the development team to maintain unique and proper serial numbers in the short description.

Can the query me modified just to return the jobname and nothing else. I do not want to see the root path and such things.

[mhester]

SGT,

Change the command to -

LIST DS_JOBOBJECTS LIKE ...ROOT AND F4 LIKE ...SERIAL0003... F3 F4 ID.SUP

Actually the command would be more proper like -

LIST DS_JOBOBJECTS LIKE ...ROOT AND DESC LIKE ...SERIAL0003... NAME DESC ID.SUP

[kduke]

Why would want to do this?

[SGT]

Thanks Michael.

Kim, This is to assign unique serial numbers to jobs and it is to comply
with some organizational standards.

Regards,
SGT

[ray.wurlod]

Lots of places do this by maintaining a separate table (so as not to muck about with the Repository).

Michael's approach, using the description, is about the only safe way to use the repository tables to achieve this end.

He forgot to tell you that, if you're more comfortable with SQL, this is also available as a query language. Note, however, that DESC is a reserved word in SQL, so has to be quoted when used as an identifier. Beware that identifiers (table and column names) are case-sensitive.

Code: Select all

SELECT "NAME", "DESC" 
FROM DS_JOBOBJECTS 
WHERE OBJNAME = 'ROOT'
AND "DESC" LIKE '%SERIAL0003%' ;

[ray.wurlod]

As you know from another thread, every DataStage job is assigned a unique number by DataStage itself, the mapping being recorded in the DS_JOBS table.

However, it's impractical to rely on this for your purposes, because you have no control over that number, and it's unlikely to be the same in your development, testing and production environments. (It will, however, be unique in each environment. If that's enough for your organizational standards, you don't actually have to do anything. Yay!)

[SGT]

No. I will need a unique preassigned value which will never change.
Thanks for the SQL format. That was better.