Using something better than DSPDBR

I am sure we have all used the Display Database Relations command, DSPDBR, to establish any file's or table's dependent objects. We can also use a SQL Table Function to give us more (better) columns of data than the DSPSBR command does.

But before I start giving examples of using this Table function I need something to work with. Let me start with a very simple DDL table:

01 CREATE TABLE MYLIB.TABLE1 02 (FIRST VARCHAR(20))

I do not need to insert data into the table.

Next I will create an Index over my Table:

01 CREATE INDEX MYLIB.TABLE1INDEX 02 FOR SYSTEM NAME "TABLE1I" 03 ON TABLE1 (FIRST) ; 04 LABEL ON INDEX TABLE1INDEX IS 'Index built over TABLE1' ;

Line 2: I have given the Index a system name as "TABLE1INDEX" is more than ten characters.

Line 4: I have labeled the index, this is the same as using the TEXT parameter in Create Physical file, CRTPF, or Create Logical file, CRTLF.

After the Index I will create a View over the Table:

01 CREATE VIEW MYLIB.TABLE1VIEW 02 AS (SELECT * FROM TABLE1) ; 03 LABEL ON TABLE TABLE1VIEW IS 'View built over TABLE1' ;

And finally I will create an Alias.

01 CREATE ALIAS MYLIB.TABLE1ALIAS 02 FOR MYLIB.TABLE1 ;

I can use the DSPDBR command to see the relationships:

DSPDBR TABLE1

Which shows me:

MM/DD/YY Display Data Base Relations DSPDBR Command Input File . . . . . . . . . . . . . . . . . . . : FILE TABLE1 Library . . . . . . . . . . . . . . . . . : *LIBL Member . . . . . . . . . . . . . . . . . . : MBR *NONE Record format . . . . . . . . . . . . . . . : RCDFMT *NONE Output . . . . . . . . . . . . . . . . . . : OUTPUT * Specifications Type of file . . . . . . . . . . . . . . . : Physical File . . . . . . . . . . . . . . . . . . . : TABLE1 Library . . . . . . . . . . . . . . . . . : MYLIB Member . . . . . . . . . . . . . . . . . : *NONE Record format . . . . . . . . . . . . . . : *NONE Number of dependent files . . . . . . . . : 2 Files Dependent On Specified File Dependent File Library Dependency JREF Constraint TABLE1I MYLIB Data TABLE1VIEW MYLIB Data

All it returns is the system name library of the dependent objects. Notice that the Alias does not appear in the results.

The Table function RELATED_OBJECTS, resides in the SYSTOOLS library, and requires just two parameters:

1. LIBRARY_NAME:  Name of the library that contains the Table or file
2. FILE_NAME:  Name of the Table or file

If I wanted to list all the information for the objects related to TABLE1 I would use the following Select statement:

SELECT * FROM TABLE(SYSTOOLS.RELATED_OBJECTS(LIBRARY_NAME=>'MYLIB', FILE_NAME =>'TABLE1'));

I recommend you run the above statement for one of your Tables or files just to see what is contained in all of the columns. As I am limited by the width of this page the statement I am going to use is:

01 SELECT SQL_OBJECT_TYPE,SCHEMA_NAME,SQL_NAME,LIBRARY_NAME,SYSTEM_NAME, 02 OBJECT_OWNER,LAST_ALTERED 03 FROM TABLE(SYSTOOLS.RELATED_OBJECTS('MYLIB','TABLE1'));

I am not going to explain what each column contains as in my opinion the columns' names do just that.

Line 3: I do not have to use the parameters' names, I can just give the library and Table names.

My results for TABLE1 look like:

SQL_OBJECT SCHEMA SQL_ LIBRARY SYSTEM OBJECT TYPE _NAME NAME _NAME _NAME _OWNER ---------- ------ ----------- -------- ---------- ------ VIEW MYLIB TABLE1VIEW MYLIB TABLE1VIEW SIMON INDEX MYLIB TABLE1INDEX MYLIB TABLE1I SIMON ALIAS MYLIB TABLE1ALIAS MYLIB TABLE00001 SIMON LAST_ALTERED -------------------------- YYYY-MM-DD 08:44:21.452000 YYYY-MM-DD 08:44:21.420000 YYYY-MM-DD 09:26:40.493000

I have the following which is not shown on the DSPDBR display:

• The dependent object's type
• Dependent object's long, SQL, name
• Dependent object's owner user profile
• The last time the dependent object was altered

And the Alias is present! I normally build my Aliases in the library QTEMP, if I do that then RELATED_OBJECTS cannot retrieve that object's data.

The results are good information and give me more than I get from the DSPDBR command.

The next question I know we all have is: Does this work for DDS physical files too?

I need a physical file, whose definition is pretty much the same as TABLE1:

A R PFILE1R A FIRST 20 VARLEN

VARLEN in the field's definition will make this field the equivalent of SQL DDL's VARCHAR, variable length character column.

And then a logical file:

A R PFILE1R PFILE(PFILE1) A K FIRST

When I use the DSPDBR for PFILE1 I can see that I have a dependent object, but I do not know which object types it is:

MM/DD/YY Display Data Base Relations DSPDBR Command Input File . . . . . . . . . . . . . . . . . . . : FILE PFILE1 Library . . . . . . . . . . . . . . . . . : *LIBL Member . . . . . . . . . . . . . . . . . . : MBR *NONE Record format . . . . . . . . . . . . . . . : RCDFMT *NONE Output . . . . . . . . . . . . . . . . . . : OUTPUT * Specifications Type of file . . . . . . . . . . . . . . . : Physical File . . . . . . . . . . . . . . . . . . . : PFILE1 Library . . . . . . . . . . . . . . . . . : MYLIB Member . . . . . . . . . . . . . . . . . : *NONE Record format . . . . . . . . . . . . . . : *NONE Number of dependent files . . . . . . . . : 1 Files Dependent On Specified File Dependent File Library Dependency JREF Constraint PFILE1LF MYLIB Data

When I use the RELATED_OBJECTS Table function I can see what kind of object type that dependent object is:

SQL_OBJECT SCHEMA SQL_ LIBRARY SYSTEM OBJECT TYPE _NAME NAME _NAME _NAME _OWNER ------------------ ------ -------- -------- ---------- ------ KEYED LOGICAL FILE MYLIB PFILE1LF MYLIB PFILE1LF SIMON

This is another good improvement offered, via a Table function, by IBM.

 

You can learn more about the RELATED_OBJECTS SQL Table function from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.