@RECORD Array to Column
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
@RECORD Array to Column
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
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
- Zulfi
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
I've left out repeating the job name (which is unchanging) on every line of the report.
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');
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
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
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.
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.
>
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.
- Zulfi
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What happens if you?
Code: Select all
SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
No records selected
Code: Select all
>SELECT COUNT(*) FROM DS_JOBOBJECTS WHERE OBJIDNO = '3664';
COUNT ( * )
0
1 records listed.
- Zulfi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
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.
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.
- Zulfi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: