Re: Where do you store the SQL source for your tables, views, etc?

Some of the benefits:

1. DB2 can easily reach out and run SQL that is inside a normal DB2
table. For me this is huge, as I have thousands of custom SQL scripts
that
DB2 can reach out an run without involving programs external to DB2.
This
makes it easy to build and run huge host side batch jobs that run inside
DB2 with no external dependencies. Performance also improves when you
reduce references to objects external to DB2.
2. I can run SQL queries over my code to find things. Technically, I use
a custom Microsoft Access application that has Excel grid like features
to
sort and filter my code in almost any way imaginable. Access generates
the
SQL code for me to sort and filter source code based on Excel grid-style
sorting and filtering selections.
3. With SQL code inside a real DB2 table, it was rather easy to build a
custom source code management / change control system.
4. I can easily replicate source code across systems using 3 part name
SQL. In my case, I typically use a replicate button for this in the
Access
app.
5. I have constraints defined over the source code table to enforce
coding rules, standards, and developer check outs.
6. I have triggers defined over the source code table to automatically
capture ALL history of code changes. To restore old source, I simply
need
to execute an UPDATE of the source row pulling the old source version
from
the history table (or perform an INSERT if it was deleted).
7. When our systems get upgraded next, I'll be adding code to call
function QSYS2.PARSE_STATEMENT to capture all objects referenced, using
the
triggers that fire when source code is changed. I'll be storing those
objects referenced inside new DB2 table(s), so that I will have enhanced
abilities to find every place an object is referenced, and to more
quickly
work with related source objects.
8. I use a mirror source code database inside the local PC Access
database, so I can work offline, if needed or desired. Typically, this
is
how I work. I first make sure the local database has the latest up to
date
source (I have a compare and replicate buttons for that). I make the
changes locally (each time I hit the Save button it captures a snapshot
of
that version of the source). When the local code passes all unit
testing,
I replicate the source up to the DB2 host and optionally check the
source
in if I have no immediate plans for more changes.

For light duty code editing, I use a TextBox control inside the custom
Access application which directly accesses the host DB2 table containing
the code. When I need to do a heavy edit session, I have a button which
places the source on the clipboard, and I paste it into Notepad++ to do the
editing. When it is time to save the source on the host, I have another
button which replaces the content of that TextBox with the clipboard
contents. Someday I might try to make Notepad++, or another editor, a
slave editor control inside the Access app.

To compile SQL source, I click on a button for that in the Access app. It
sends the source up to the host for execution, senses the SQL results, and
reports those results on the screen.

For execution, I have a myriad of options, but I typically do this:

1. For quick running unit tests, I typically use a RUN button in the
Access app. I can run all or a selected part of a script.
2. For small batch job testing, I can select a series of scripts and use
that same RUN button in Access, but I'll typically run small batch type
testing jobs in IBM ACS Run SQL Script. To quickly get a series of
scripts
into ACS, I can select a list of them, and click on a button that will
place all of them, in their selected order, onto the clipboard. Then I
simply paste them into ACS.
3. For larger batch jobs, I load up a host based job table with SQL
scripts, submit a host side job that calls an SQL stored procedure to
run
all those scripts, which also logs detailed results for every SQL
statement
executed. So, if I have a job that runs 1500 scripts, with an average
of 2
SQL statements per script, I'll end up with 3000 SQL statements logged
all
with individual run times.

To answer Mike's question - I agree that most would consider a multi-member
source file as being technically part of DB2, but since there are many
things you can't do with them, or they make things much more difficult to
do, that I don't consider them a standard part of DB2. For example, it
would have been vastly more difficult for me to have built source code
searching, filtering, and sorting over a multi-member source file
(performance against multi-member files is vastly slower as well). As I
work now, a separate source item is another row in the same table, not a
separate member. A standard SQL query accomplishes that task, and in my
case, Access generates that SQL for me based on the filtering and sorting
selections I make from a grid control. Using a real table for source makes
tool development vastly easier, and their features run dramatically faster.


So, with my code in a real DB2 table, it is much easier and faster to work
with. I like to think of my code as now being "my data", because in many
ways I'm now handling it like business data. Developers can gain just as
much as the business can by putting ALL of DB2's features to work for them.

Mike