Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Linking Oracle tables to each other

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Thu Mar 27, 2008 10:10 pm    Post subject: Linking Oracle tables to each other
Reply with quote

We have a software product that uses an Oracle database for its data storage. I need to produce some daily management reports from the content. There is one schema with about 250 tables. The vendor does not and will not publish the contents of the schema.

I'm using Oracle SQL*Plus on z/OS V1R7 for my task. I don't have access to any desktop-based tools for this.

I've been able to find out how to list the contents of the schema, how to find the contents of each table, and how to retrieve the data from the tables. One thing I'd like to do is to document the relationships of the indexes between all of the tables, so we know which tables to look at for JOIN operations.

Is there a SQL*Plus command that will show all of these relationships?
Back to top
View user's profile Send private message

shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Sat Mar 29, 2008 9:46 pm    Post subject: Reply to: Linking Oracle tables to each other
Reply with quote

Modify below query and let me know whether that is your requirement
Code:

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.name = N'DatabaseLog' OR o.name = N'Currency'
ORDER BY o.name, p.index_id;
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Mon Mar 31, 2008 12:05 pm    Post subject: Reply to: Linking Oracle tables to each other
Reply with quote

SuperK,

Do you need to compare Schemas...?
Back to top
View user's profile Send private message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Tue Apr 01, 2008 7:21 pm    Post subject: Reply to: Linking Oracle tables to each other
Reply with quote

I get an error:

Code:

SQL> SELECT O.NAME AS TABLE_NAME,P.INDEX_ID, I.NAME AS INDEX_NAME ,       
  2    AU.TYPE_DESC AS ALLOCATION_TYPE, AU.DATA_PAGES, PARTITION_NUMBER   
  3    FROM SYS.ALLOCATION_UNITS AS AU                                     
  4    JOIN SYS.PARTITIONS AS P ON AU.CONTAINER_ID = P.PARTITION_ID       
  5    JOIN SYS.OBJECTS AS O ON P.OBJECT_ID = O.OBJECT_ID                 
  6    JOIN SYS.INDEXES AS I ON P.INDEX_ID = I.INDEX_ID AND I.OBJECT_ID = 
  7      P.OBJECT_ID                                                       
  8    WHERE O.NAME = N'DATABASELOG' OR O.NAME = N'CURRENCY'               
  9    ORDER BY O.NAME, P.INDEX_ID;       
                               
  FROM SYS.ALLOCATION_UNITS AS AU                                         
                            *                                             
ERROR at line 3:                                                           
ORA-00933: SQL command not properly ended                                 
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts Need help with Oracle Dynamic SQL: Me... Anoop Chandran1984 COBOL Programming 1 Sat Feb 13, 2016 7:27 am
No new posts New DB2 tables creation kishpra DB2 3 Tue Feb 02, 2016 7:00 pm
No new posts TABLES/MS from Specialized Solutions ... Gary Jacek COBOL Programming 0 Thu Sep 03, 2015 12:08 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us