Page 1 of 1
@RECORD Array to Column
Posted: Thu Mar 12, 2015 5:13 am
by zulfi123786
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
Posted: Thu Mar 12, 2015 7:35 am
by chulett
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.
Posted: Thu Mar 12, 2015 4:16 pm
by ray.wurlod
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.
Posted: Thu Mar 12, 2015 4:27 pm
by chulett
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.
Posted: Fri Mar 13, 2015 5:10 am
by zulfi123786
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.
Posted: Fri Mar 13, 2015 10:51 am
by kduke
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.
Posted: Sat Mar 14, 2015 3:25 am
by zulfi123786
Hi Kim,
The job does have design information as I can see it in the designer.
Posted: Sun Mar 15, 2015 3:14 pm
by ray.wurlod
What happens if you
Code: Select all
SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';
?
Posted: Mon Mar 16, 2015 1:44 am
by zulfi123786
No records selected
Code: Select all
>SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';
COUNT ( * )
0
1 records listed.
Posted: Mon Mar 16, 2015 3:16 pm
by ray.wurlod
Then it appears that you will need to extract your design information from the common metadata repository.
Posted: Tue Mar 17, 2015 1:19 am
by zulfi123786
Are you taking about XMETA Ray ?
Also, isnt this an issue ?
Posted: Tue Mar 17, 2015 3:29 pm
by ray.wurlod
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.
Posted: Wed Mar 18, 2015 1:26 am
by zulfi123786
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.
Posted: Wed Mar 18, 2015 7:50 am
by chulett
It's not documented though Ray does note your options there.
Posted: Wed Mar 18, 2015 3:07 pm
by ray.wurlod
Oh, the data MODEL is available. Just go to Model View in Metadata Workbench.