Sorry Don, my fingers got tired before I got to finish all my thoughts on the idea. The META_REL and META_KYS are for storing the list of columns that make up the multiple keys or relationships that tables have. For example in the ky_exp column could be populated with “cust.cust_lastname+cust.cust_firstname“. When it came time to create the keys for the CUST table of customers, the Generator could read this row and write the proper CREATE statement to a file like createcust.sql.

You would also need to add the foreign key from META_MAST to each of these tables along with a column for the name of the database and table name.

My ideas and techniques do not come from one place or one project but from a myriad of projects that used a wide variety of Data Warehousing, CASE, and Data Dictionary tools. I would suggest looking in Google for the keywords metadata, SQL, and database.

Most of the commercial tools or homegrown tools that I have used, had a database backend. The project that I remember the best that this idea saved me on was a data conversion project, converting Mainframe (MF) data to SQL tables. The MF data to start with was “dirty” and had to be verified using various pieces of code snippets, looking for invalid data, corrupt data, orphan rows, and the like. The next problem was the MF data used data types that SQL did not have, like packed decimal. So I had to write another snippet to decode the packed decimal. Then the data had to be inserted into SQL tables. Sometimes this was a one for one basis, which was a piece of cake. But most of the MF tables had to be split into several tables and populated in a relational way into the SQL. If I chose to do this project by hand coding, I would still be working on it.

To make matters worse, the MF programmers were constantly changing the schema, and I was last to know. The icing on the cake was that we were dealing with 200 tables from the MF which created 350 SQL tables.

So I created a metadata Generator like the one I described before and stored all the attributes of the MF input data and the SQL output data in my metadata database. When the MF data came to me in large tapes with different schemas, no sweat, I merely remapped it in my schema description tables. Sometimes the SQL programmers came to me with ever changing SQL schemas. Again I just changed the SQL schema description tables. I extensively used TEMP tables and ad-hoc tables for key lookups and temporary data storage until the final COMMIT of each transaction.

With this system I could rapidly adapt to any curveballs thrown to me, and I could iteratively output SQL tables so that the SQL programmers could have some test data, and then feedback to me the schema changes on the SQL side. If any programmer added a table, it took me about five minutes to update the metadata and I was ready to crank out the data.

Some of the side benefits from using this metadata Generator were documentation, statistics, and accuracy. I wrote several SQL reports that queried the metadata, giving a complete map of the MF tables coming in and where they mapped to in the SQL. Sometimes the programmers used this documentation to find errors in their data structures. I wrote several SQL reports that generated statistics of rows inputted, outputted, errors found and the type of errors, corrections made by the program, etc. Finally, the overall accuracy of my data conversion project was better by far than a set of manually written scripts, and this improvement in productivity was provable to management.

Richard

There are three types of people in this world, those that can count and those that cannot. Anon