Generating a Data Dictionary

[JoeOlliff400]

Is there a way using RPGLE to create a list of all of the field names and their descriptions in a selection of physical files? We have lots and lots of various physical files that we use and many of them have field names that make no sense on the surface so I was going to see about creating a searchable master list that would provide a description for a particular field name without having to figure out which file in given program it is from and then look up the field name and description in that file.

Thanks in advance

[EmmanuelW1]

Re: Generating a Data Dictionary

Query SYSCOLUMNS?

Cheers,

Emmanuel

[JoeOlliff400]

Re: Generating a Data Dictionary

That's only 9 million results, wow. Thanks though this should work nicely

[Michael Jones]

Re: Generating a Data Dictionary

IBM maintains a data dictionary automatically for you in the form of the System Catalog. Don't reinvent an existing wheel.

I second the recommendation to query QSYS2.SYSCOLUMNS.

Run this query to get a good high level look at the wealth of information available in the System Catalog QSYS2.SYS* objects:

--List of System Catalog tables and views
select * from QSYS2.SYSTABLES where TABLE_SCHEMA = 'QSYS2' and TABLE_NAME like 'SYS%' order by TABLE_NAME

Each item returned by that query can, in turn, be queried to yield more detail about objects in QSYS2, or any other SCHEMA / LIBRARY on the system.

Consider building a clear, VIEW abstraction layer around unclear database tables:
When you have unclear column and tables names, the code built against them is also unclear and more difficult to work with. If you have a table with poor field/column names, consider creating SQL view(s) over said table to map those unclear table and column names to clear column and table names. Then, build new programs that only read data against the SQL views with clear column and view names, instead of the referencing the legacy, hard to decipher column and table names. When you perform substantial changes to legacy read only programs, consider refactoring them to reference the SQL views instead. If/when new tables get built to replace the legacy tables, you can refactor the SQL views to point to the new tables, assuming the degree of change between the old and new tables lends itself well to continuing the use of the SQL views after they've been refactored.

Technically you can use the SQL views to update data as well, but that involves using INSTEAD OF triggers, which take significant effort to learn and get used to. With INSTEAD OF triggers in place for a VIEW, an attempt to update VIEW data fires the INSTEAD OF trigger. In the INSTEAD OF trigger, you place your code to update the table(s). A downside to this approach, other than adding some complexity, is the added overhead of triggers and their performance impact.

[JoeOlliff400]

Re: Generating a Data Dictionary

Thank you for the information. Your solution to the mess of table and column names would be ideal, but as I am low man on totem pole (and this is a very fast paced environment, I work in Customs Brokerage, I am not offered the luxury of time to set that up and go through the literally hundreds of thousands of files and programs to make that change. I will have the time to build a view that lists the specific files I often am trying to look things up in. Thanks again for the info

[Liam Allan]

Re: Generating a Data Dictionary

Thanks in advance

I am in no way advertising my blog here, but I had written up something about getting information like field names from a file there.

http://worksofbarry.tumblr.com/post/130402574086/rpgle-pf-info-into-your-program-using-embedded

Hopefully this can help.. with a bit of tinkering I'm sure you can get the field descriptions too.

[Michael Jones]

Re: Generating a Data Dictionary

Hi Joe,

Glad to be of help. I totally understand the low man on totem pole syndrome.

Personally, I'd never try to tackle building an abstraction layer like that as a project unto itself. There is too much effort with too little ROI as perceived by those outside IS/IT. As a result, I wouldn't even ask for permission to do that since the answer is virtually a guaranteed no.

The only way I'd build an abstraction layer like that is one program at a time, and the timing of that effort would be when I had to work on said program for a separate business purpose. The refactored program would end up considerably more understandable. If the business reason for modifying the program required a rather large effort, starting out by doing a search and replace of unclear column names to clear ones might actually speed up the business change request effort by making the program more understandable. Permission to tackle that one program at a time is something to consider sending up the totem pole. Testing should always be the majority of the effort required to change any production program, and it is a very good practice to seize opportunities to refactor programs for the better every single time you have to change, test, and re-install them into production.

Mike

[Rocky]

Re: Generating a Data Dictionary

Consider building a clear, VIEW abstraction layer around unclear database tables:
When you have unclear column and tables names, the code built against them is also unclear and more difficult to work with. If you have a table with poor field/column names, consider creating SQL view(s) over said table to map those unclear table and column names to clear column and table names. Then, build new programs that only read data against the SQL views with clear column and view names, instead of the referencing the legacy, hard to decipher column and table names. When you perform substantial changes to legacy read only programs, consider refactoring them to reference the SQL views instead. If/when new tables get built to replace the legacy tables, you can refactor the SQL views to point to the new tables, assuming the degree of change between the old and new tables lends itself well to continuing the use of the SQL views after they've been refactored.

Technically you can use the SQL views to update data as well, but that involves using INSTEAD OF triggers, which take significant effort to learn and get used to. With INSTEAD OF triggers in place for a VIEW, an attempt to update VIEW data fires the INSTEAD OF trigger. In the INSTEAD OF trigger, you place your code to update the table(s). A downside to this approach, other than adding some complexity, is the added overhead of triggers and their performance impact.

Actually - the INSTEAD OF trigger is only necessary if the view contains a JOIN.

The rub with the suggestion is that views aren't indexed - virtually forcing any programming to use embedded SQL (not necessarily a negative or a bad thing - one just has to be aware of it).

It's a great idea for those who want to query the data - create views with field names and text that makes more sense to the users that need to perform the queries.

[Michael Jones]

Re: Generating a Data Dictionary

Actually - the INSTEAD OF trigger is only necessary if the view contains a JOIN.

The rub with the suggestion is that views aren't indexed - virtually forcing any programming to use embedded SQL (not necessarily a negative or a bad thing - one just has to be aware of it).

It's a great idea for those who want to query the data - create views with field names and text that makes more sense to the users that need to perform the queries.

The fact that the views aren't indexed doesn't really matter. The code that gets invoked when the INSTEAD OF trigger fires should be coded to reference the physical tables, in which case the SQL engine will utilize indexes as appropriate in the case of a DELETE or UPDATE. That said, all triggers have a negative performance impact. From the viewpoint of reading data using views, the SQL engine will utilize indexes to satisfy data requests through a view. Again though, if you form complex queries that reference many views, performance tuning said queries becomes more difficult and the risk increases substantially for significant negative performance impact.

Simple views will likely be update-able directly. IBM has been reducing the restrictions on updating views. If you use them, make sure you read the CREATE VIEW limitations on using them for update, delete, insert purposes, for your OS level. There are other reasons to use INSTEAD OF triggers other than a JOIN being involved. If the view performs transformation of data, upon an update of the view, an INSTEAD OF trigger provides the place to reverse the transformation. Granted, applications like that are very uncommon, but I have one application where that design came in very handy.

[Rocky]

Re: Generating a Data Dictionary

I think we didn't quite communicate. Views can be directly "update-able" if they are not joins. In the case here you can use views to "rename" fields in a table to make them more meaningful - and you can give them different labels (COLHDG) to be more descriptive.

The "rub" in regards to keys has to do with traditional RPG/COBOL programming techniques - CHAIN, etc.. essentially any coding will need to be done using SQL to use the view as you will no longer be able to CHAIN to a key.

As soon as one creates a view with a JOIN it can't be "update-able" outside of the INSTEAD OF trigger.