DSXchange

Posted: **Mon Feb 23, 2009 1:25 pm**

we have around 1400 jobs in one of our project and trying to get the list of jobs and their modification times, we are querying the DS_AUDIT table for each project.

Code: Select all

LIST.DICT DS_AUDIT

The command ONLY returns

Code: Select all

1 records listed.

Also ran this command

Code: Select all

SELECT * FROM DS_AUDIT  WHERE CLASS = '2'

Result -

Code: Select all


DataStage/SQL: syntax error. Unexpected symbol. Token was "CLASS". Scanned command was FROM DS_AUDIT SELECT * WHERE CLASS

this command works fine in other project but not this one - do we need to reindex this project? or something else needs to be done.

Please advise.
Thanks

Posted: **Mon Feb 23, 2009 2:48 pm**

Can you try LIST DICT DS_AUDIT (without the dot)?

If only one record is listed then someone at your site has stripped the metadata from DS_AUDIT. So there is no definition for CLASS or, indeed, any of the columns in DS_AUDIT.

Find that person and castigate severely.

If the DS_AUDIT hashed file in a different project has its metadata (that is, you get more than one record) then you will be able to restore the stripped one.

Let us know and we'll show you how. Otherwise we can show you how to re-build these metadata manually.

Posted: **Mon Feb 23, 2009 11:03 pm**

Tried

Code: Select all

LIST DICT DS_AUDIT

same result. 1 record

How can we find the person who did this?

Please show us how to re-build these metadata manually.

Thanks alot

Posted: **Tue Feb 24, 2009 6:37 am**

It may appear long, but it's simply a list of INSERT statements then a COMPILE.DICT command. Beware that the column names list varies.

Code: Select all

INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('KEY','D','0','Class\ID','40L','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('DTC','D','1','Created on','20L','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('CREATOR','D','2','Created by','20T','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('PREVDTD','D','3','Last deleted on','20L','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('PREVDELETOR','D','4','Last deleted by','20T','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM,ASSOC) VALUES ('DTM','D','5','Modified on','20L','M','MODS');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM,ASSOC) VALUES ('MODIFIER','D','6','Modified by','20T','M','MODS');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM,ASSOC) VALUES ('REASON','D','7','Reason for modification','38T','M','MODS');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('CLASS','I','FIELD(KEY,"\",1,1)','Class','10L','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('INSTANCE','I','FIELD(KEY,"\",2,99)','Instance ID','40T','S');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM,ASSOC) VALUES ('DATE_MOD','I','SUBSTRINGS(DTM,1,10)','Date modified','10R','M','MODS');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC) VALUES ('MODS','PH','DTM MODIFIER REASON');
INSERT INTO DICT DS_AUDIT(ID,CODE,LOC) VALUES ('@','PH','ID.SUP KEY DTC CREATOR PREVDTD PREVDELETOR MODS BY CLASS BY INSTANCE');
COMPILE.DICT DS_AUDIT

Posted: **Tue Feb 24, 2009 1:03 pm**

Thanks for the commands Ray.

So we need to execute all the above insert statements, right?

What do you mean by

Code: Select all

Beware that the column names list varies.

Thanks

Posted: **Tue Feb 24, 2009 5:37 pm**

Different INSERT statements have different columns into which they insert.

And you do have to execute the COMPILE.DICT statement as well as the INSERT statements. Note that this is not SQL, so does not take a terminating semi-colon. And all the commands are case-sensitive.

You can copy/paste them into the Administrator client command window.

Posted: **Tue Feb 24, 2009 5:45 pm**

First Insert statement entered

Code: Select all

INSERT INTO DICT DS_AUDIT(ID,CODE,LOC,NAME,FORMAT,SM) VALUES ('KEY','D','0','Class\ID','40L','S');

Command Output:

Code: Select all

DataStage/SQL: Field "LOC" data type does not match insert value.

Please advise.

Posted: **Tue Feb 24, 2009 9:49 pm**

Please execute this command on your system, because the data type should be OK.

Code: Select all

LIST DICT.DICT 'LOC'

If the format ends in 'L' or 'T' then the quoted value is satisfactory. If you can't get it to work, use FUNC rather than LOC everywhere.

Posted: **Wed Feb 25, 2009 4:07 pm**

Code: Select all

LIST DICT.DICT 'LOC'

1 records listed.

Output Format
----------------

15T

Posted: **Wed Feb 25, 2009 5:52 pm**

I don't know. It works perfectly fine for me (8.0.1 on Windows). That the format of LOC is "15T" means that its implied data type is VarChar, and therefore that '0' is a valid value.

Try this.
There is a directory called Template, which is a sibling of the DataStage Engine directories. In it is the prototype for DS_AUDIT. Create a pointer to that, and try listing its file dictionary.

Code: Select all

SETFILE pathname_of_Template_directory/DS_AUDIT DS_AUDIT_PROTOTYPE

LIST DICT DS_AUDIT_PROTOTYPE

If that reports more than one record, then you can copy its records into the file dictionary of DS_AUDIT.

Code: Select all

COPYI FROM DICT DS_AUDIT_PROTOTYPE TO DICT DS_AUDIT ALL OVERWRITING

After that you no longer need the VOC pointer, so delete it.

Code: Select all

DELETE VOC 'DS_AUDIT_PROTOTYPE'

Posted: **Thu Feb 26, 2009 12:08 pm**

13 records listed
13 record copied
1 records DELETEd.

Thanks Ray. You are great.