Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 4650
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: 4650
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us