9/29/13 DSSharing: Datastage Admin Commands

dsshar.blogspot.com/2013/01/datastage-admin-commands_28.html 1/3

DSSharing

Monday, 28 January 2013

Datastage Admin Commands

Using DataStage Administrator commands to find:

Command Description Command

DS Project Object counts SELECT COUNT(*) FROM DS_JOBS;

SELECT COUNT(*) FROM DS_JOBOBJECTS;

List all jobs in a DS project SELECT JOBNO, @ID, CATEGORY, NAME FROM DS_JOBS

Clear Log From Administrator select JOBNO from DS_JOBS where NAME='Name of the job'

CLEAR.FILE RT_Logxx where xx is JOBNO.

CLEAR HASH FILE. CLEAR.FILE <HASHED FILE NAME>

Find how many Jobs are using giv en Hashed file as INPUT

select DS_JOBS.NAME AS JOB_NAME FMT '35L' from DS_JOBOBJECTS,DS_JOBS where

DS_JOBOBJECTS.OLETYPE = 'CHashedInput' and DS_JOBOBJECTS.OBJIDNO =

DS_JOBS.JOBNO and EVAL DS_JOBOBJECTS."@RECORD<6>" = 'hashed file name' group

by JOB_NAME;

Find how many Jobs are using giv en Hashed file as OUTPU

select DS_JOBS.NAME AS JOB_NAME FMT '35L' from DS_JOBOBJECTS,DS_JOBS where

DS_JOBOBJECTS.OLETYPE = 'CHashedOutput' and DS_JOBOBJECTS.OBJIDNO =

DS_JOBS.JOBNO and EVAL DS_JOBOBJECTS."@RECORD<6>" = 'hashed file name' group

by JOB_NAME;

Find how many Jobs are using giv en Hashed file as INPUT and

OUTPUT

select DS_JOBS.NAME AS JOB_NAME FMT '35L' from DS_JOBOBJECTS, DS_JOBS where

DS_JOBOBJECTS.OLETYPE in ('CHashedOutput','CHashedInput') and

DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO and EVAL

DS_JOBOBJECTS."@RECORD<6>" = 'Hashed file name' group by JOB_NAME;

View column data in a hashed file SELECT COLUMN_NAME FROM HASHED_FILE_NAME

List metadata of hashfileSELECT "FIELD", "FIELD.TYPE", "FIELD.DEF", "FIELD.CONV", "FIELD.HEAD", "FIELD.FMT",

"FIELD.ASSOC" FROM DICT hashedfile ORDER BY "FIELD.NO", "TYPE";

Insert data into the hashfile INSERT INTO HASHED_FILE_NAME (COLUMN_NAME) VALUES ('VALUE');

Find which user modified the job lastSELECT INSTANCE, DTM, MODIFIER FROM DS_AUDIT WHERE CLASS = '2' AND INSTANCE

LIKE 'JOB NAME';

List all the projects in DataStage.. With Owner SELECT @ID, NAME FMT '40L' FROM UV.ACCOUNT;

Reset surrogate key generated using Keymgmt function in

DatastageUPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'Your_Sequence_Name';

List all the surrogate key v ariables used in datastage proj SELECT * FROM SDKSequences;

List Datastage error message decsription, giv en error me SELECT * FROM SYS.MESSAGE WHERE @ID = '081002';

SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY, DS_JOBOBJECTS.NAME AS

Total Pageviews

3 6 7 6

prasoon

Hi Guys, Welcome to DSShare blog.My nameis Prasoon and i have created this blog to sharedatastage concepts.I will appriciated yourcomments on posts.

View my complete profile

About Me

Free Backlinks

CNET NewsHow the'carstache'turned intomarketing magicfor LyftAuthor pleads:Please don't buymy new novel onAmazon (Q&A)Bad Beats: Whywas the original

CNET News.comFeed TechnologyNews

Apple Google Microsoft

Is Cupertino sacrificing its core to

Los Angeles TimesBy the time Apple plops a campus the size ofthe Pentagon into this city of 60,000 people,Cupertino, Calif., will be more economicallydependent on one company than any other

0Share More Next Blog» Create Blog

9/29/13 DSSharing: Datastage Admin Commands

dsshar.blogspot.com/2013/01/datastage-admin-commands_28.html 2/3

Posted by prasoon at 06:30

Location: Cape Town, South Africa

List all jobs using a TABLE

OBJECT_NAME, DS_JOBOBJECTS.OLETYPE, EVAL DS_JOBOBJECTS."if

index(upcase(@RECORD),'TABLE_NAME',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'

FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO

and FOUND = 'FOUND' GROUP BY JOB_NAME, DS_JOBS.CATEGORY, OBJECT_NAME,

DS_JOBOBJECTS.OLETYPE, FOUND;

List Sequencer jobs calling a specified job in the project @DsServ e >> /etl/Ascential/Datastage/DSEngine/bin/dssearch -ljobs -usedby -r -oc -

oj <projectname> <jobname>

List all jobs called in specified Sequencer job @DsServ e >> /etl/Ascential/Datastage/DSEngine/bin/dssearch -ljobs -uses -r -oc -oj

<projectname> <SeqJobname>

Recommend this on Google

Enter your comment...

Comment as: Google Account

Publish

Preview

1 comment:

Vamsi Krishna 12 February 2013 08:32

Your post is very nice.DO u have any link or material to know complete information about DS_JOBS,DS_JOBOBJECTS,DS_AUDIT

etc.As of now i know basic knowledge with the help of dsexchange

Reply

Silicon Valley town. Two years after the project

was announced, as the ...

Two Things Apple Got Absolutely Right In The

iPhone 5s and iOS 7ForbesWe've certainly had our fun around here withApple's Apple's recent product announcements,noting, for example that iOS7 seems to triggermotion sickness in that small portion of thepopulation susceptible to it. Others havenoticed that the iPhone 5c ...Related Articles »

Apple isn't dead yet – iPhone 5S and iOS7

have a secret weaponThe GuardianWhen Steve Jobs was still with us, manycommentators – yours truly included – used tocomplain about the "reality distortion field" thatsurrounded Apple's charismatic leader. Thosein attendance when Jobs launched the devicesand services (iPod ...Related Articles »

Apple hires top designer from Nike

tuaw.comAt Nike, Shaffer was the Studio Director of theInnovation Kitchen. This is Nike's research anddevelopment lab where new product designs arecreated. Under Shaffer's lead, Nike was namedthe most innovative company in 2013 by FastCompany.Related Articles »

pow ered by

9/29/13 DSSharing: Datastage Admin Commands

dsshar.blogspot.com/2013/01/datastage-admin-commands_28.html 3/3

Newer Post Older PostHome

Subscribe to: Post Comments (Atom)

Powered by Blogger.