CA OPS/MVS

Expand all | Collapse all

OPSMVS RDF TABLE

Jump to Best Answer
  • 1.  OPSMVS RDF TABLE

    Posted 04-06-2016 03:48 PM

    I would like to see results from two RDF tables into one query.  Is this possible in OPSMVS?

     

    Example

    TABLE A

    NAME  

    TASK1

    TASK2

    TASK3

     

    TABLE B

    NAME

    TASK4

    TASK5

     

    RESULTS:

    TASK1

    TASK2

    TASK3

    TASK4

    TASK5

     

    Then do a SQL SELECT against the RESULTS.



  • 2.  Re: OPSMVS RDF TABLE

    Posted 04-06-2016 06:43 PM

    Randy,

     

    Not exactly what you are looking for but recently I was looking at the Knowledge Document TEC427260 to address a problem another client has when trying to join two RDF tables in CA OPS/MVS.

    Please visit this URL when time permits to look at the article:

    http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec427260.aspx

    Adapt the Select statement in the KD to your RDF table names and let us know if this helps.

     

    Regards, Cesar



  • 3.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 09:37 AM

    Hi Cesar,

     

    After read some of the OPSMVS doc, my understanding was OPSMVS did the JOIN process.  I thought I could get away with something a little more simple.

     

    From OPSMVS Doc:

     

    ADDRESS SQL "SELECT {columnlist|*} keywords"

     

    /* The following keyword must follow the optional INTO hostvarlist keywords, */

     

    /* if used. You may specify more than one tablename; if you do, be sure to */

     

    /* separate each one with a comma: */

     

     

    like

    "Select jobname from tablea,tableb"

     



  • 4.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 10:24 AM

    Yes. The join logic as you have coded..."Select jobname from tablea,tableb" will give you list of jobname.x variables for all the entries in both tables if that is what you are looking to do. If you want to do an additional query (SQL SELECT) against that returned list, this join data doesn't help you since you have no way of knowing what table to select from (output from join doesn't have table info). Provide some details of your exact subsequent queries that you want to do with the obtained 'joined' output and we'll  look at seeing what SQL statements will best fit the need. Perhaps adding some additional sub query WHERE logic on the join statement may help.



  • 5.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 10:39 AM

    What I what to do is pretty simple,  If a jobname exists in either table sqlcode = '0' then iterate (I don't care) but if it doesn't  do something else.   Right now I'm just trying to display the names in both tables,  I get a RC of 0 but names from only one table.

     

    Address SQL                                           

    "SELECT * FROM TABLEA,TABLEB"                         

    say rc                                                

    DO i = 1 to name.0                                    

    say name.i                                            

    end                                                  



  • 6.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 10:48 AM

    Are you perhaps on 12.3?...Quick test looks like we may have issue with SQL JOIN in 12.3 



  • 7.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 10:51 AM

    Ah yes we are on 12.3



  • 8.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 10:55 AM

    OK...let me poke around in a little bit....haven't really utilized join statements that much and I'll be the first to say that the doc doesnt really give 'useful' examples.  I am seeing some differences on 12.3, so I'll get that looked at too...I'll update with results later.... 



  • 9.  Re: OPSMVS RDF TABLE
    Best Answer

    Posted 04-07-2016 11:26 AM

    In the meantime, (if I understand what you are needing correctly) the below code that does two separate SQL selects  and compares SQLCODE = 100 (not found in either table) should do the trick if you dont have it already....

     

    jobname = 'TEST'                                     

    address SQL                                          

    "Select JOBNAME from TBL1 where JOBNAME = :jobname"  

    scode_tbl1 = sqlcode                                 

    "Select JOBNAME from TBL2 where JOBNAME = :jobname"  

    scode_tbl2 = sqlcode                                 

    If scode_tbl1 = '100' & scode_tbl2 = '100' then      

    do                                                  

      /* Do logic where jobname not in either table */   

      say 'Not in either table'                          

    end                                                 



  • 10.  Re: OPSMVS RDF TABLE

    Posted 04-07-2016 11:34 AM

    Thanks David
    That will work...