@RECORD Array to Column

zulfi123786 · Post by **zulfi123786** » Thu Mar 12, 2015 5:13 am

Hi,

Spent quite a time seraching the forum, I want to read entire content of @RECORD from DS_JOBOBJECTS for specific OBJIDNO as one column.

Tried the below in Universe stage

SELECT DS_JOBS.NAME, CONVERT(@FM,"",@RECORD) AS RECORD FMT '5000L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.JOBNO='7978';

it looks like Convert is not being recognised

[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected symbol. Token was "CONVERT". Scanned command was

any help is appreciated

chulett · Post by **chulett** » Thu Mar 12, 2015 7:35 am

Why would you think CONVERT would work here? You're mixing... what's it called... UniVerse RetrieVe / SQL syntax with a DataStage function. Sorry, not sure what the answer is off the top of my head but that's not it.

I think Rocket Software has all of the old UniVerse manuals available for download, all of that crazy stuff will be in there somewhere.

Aha! Found them:

http://www.rocketsoftware.com/brand/roc ... se-v11.2.4

Note that it's not ALL applicable since (from what I recall) DS is built on an earlier code branch with changes not listed here but I believe the majority of it should be relevant.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 12, 2015 4:16 pm

CONVERT is not available as an SQL function, but you can include it in an EVAL expression. RECORD is a reserved word in DataStage/SQL, so I've replaced it with ENTIRE_RECORD. I'd also process the other potential dynamic array delimiter characters.

Try

Code: Select all

SELECT EVAL "CONVERT(@FM:@VM:@SM, '~}|', @RECORD)" AS ENTIRE_RECORD FMT '500L' FROM DS_JOBOBJECTS WHERE OBJIDNO = (SELECT JOBNO FROM DS_JOBS WHERE JOBNO ='7978');

I've left out repeating the job name (which is unchanging) on every line of the report.

chulett · Post by **chulett** » Thu Mar 12, 2015 4:27 pm

ray.wurlod wrote:CONVERT is not available as an SQL function, but you can include it in an EVAL expression.

Interesting. Thanks for the clarification.

zulfi123786 · Post by **zulfi123786** » Fri Mar 13, 2015 5:10 am

As usual can't thank enough Ray

I came across something unusual (atleast to me)

Non existent entries in DS_JOBOBJECTS for a job that runs every day in production

Code: Select all

>SELECT JOBNO FROM DS_JOBS WHERE  DS_JOBS.NAME='RDM006dDimDiagnosisCompareTL_td'
SQL+;
No...

3664

1 records listed.
>SELECT EVAL "CONVERT(@FM:@VM:@SM, '~}|', @RECORD)" AS ENTIRE_RECORD FMT '5000L' FROM DS_JOBOBJECTS  WHERE DS_JOBOBJECTS.OBJIDNO=(SELECT JOBNO FROM DS_JOBS WHERE  DS_JOBS.NAME='RDM006dDimDiagnosisCompareTL_td');
CONVERT ( ( char ( 254 ) ) : ( char ( 253 ) ) : ( char ( 252 ) ) , ~}| , @RECORD )

0 records listed.
>

Its a healthy job that runs with no issues, for a moment wondered if its due to importing only executables but I can see the job design via designer.

The issue here is our script that fetches job details which use/create a file (passed as parameter) is unable to pick this job up and there are over a thousand jobs in the project hence cant perform a manual search.

kduke · Post by **kduke** » Fri Mar 13, 2015 10:51 am

You are correct. If you only import executables then this will not work because DS_JOBOBJECTS is not populated. You need to do these searches in DEV. This is one of the flaws of not importing job designs.

zulfi123786 · Post by **zulfi123786** » Sat Mar 14, 2015 3:25 am

Hi Kim,

The job does have design information as I can see it in the designer.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 15, 2015 3:14 pm

What happens if you

Code: Select all

 SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';

?

zulfi123786 · Post by **zulfi123786** » Mon Mar 16, 2015 1:44 am

No records selected

Code: Select all

>SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';
COUNT ( * )

          0

1 records listed.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 16, 2015 3:16 pm

Then it appears that you will need to extract your design information from the common metadata repository.

zulfi123786 · Post by **zulfi123786** » Tue Mar 17, 2015 1:19 am

Are you taking about XMETA Ray ?

Also, isnt this an issue ?

ray.wurlod · Post by **ray.wurlod** » Tue Mar 17, 2015 3:29 pm

XMETA is only the conventional name - it can be called whatever you like. My current site, for example, uses schema name suffixes to discriminate DEV, TEST and PROD. That's why I prefer to call it the metadata repository.

As for your "isn't this an issue?" question, I can't see why, apart from the fact that the metadata repository database structure is not documented; it's still open (you can DESCRIBE table contents). In newer versions there are also some meaningfully-named views that help you to extract information from the metadata repository, as well as commands such as IAAdmin and RepositoryAdmin.

zulfi123786 · Post by **zulfi123786** » Wed Mar 18, 2015 1:26 am

Guess its time to start using metadata repository instead of Universe for more accurate information.

Not sure if anybody has figured out a way to get similar report (one fetched by scanning entire DS_OBJECTS record for searching something) with metadata repository.

you know what I am looking for, could you please share what manuals to look into (if this is documented) to get started.

I remember there were around 800 tables/view in metadata repository (v8.0), things would have been easy if the data model was available.

chulett · Post by **chulett** » Wed Mar 18, 2015 7:50 am

It's not documented though Ray does note your options there.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 18, 2015 3:07 pm

Oh, the data MODEL is available. Just go to Model View in Metadata Workbench.