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
date: Mon, 13 Mar 2017 15:58:57 -0400
from: Buck Calabro <kc2hiz@xxxxxxxxx>
subject: Re: Where do you store the SQL source for your tables, views,
etc?
On 3/13/2017 3:49 PM, Mike Jones wrote:
I store SQL source in a custom database table inside DB2. Sounds weird
but
it lays the foundation for lots of really good stuff. I would never
willingly go back to using source files, and storing source in the IFS
would be a large step backward as well.
I'd be interested in hearing some of the benefits, but I'd also be
interested in hearing how you edit and execute the contents of the
custom 'SQL source' table.
--buck
date: Mon, 13 Mar 2017 20:02:20 +0000
from: Mike Cunningham <mike.cunningham@xxxxxxx>
subject: RE: Where do you store the SQL source for your tables, views,
etc?
Isn't QSQLSRC already a multi-member custom DB2 table?
Mike Cunningham
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Mike Jones
Sent: Monday, March 13, 2017 3:50 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Where do you store the SQL source for your tables, views, etc?
I store SQL source in a custom database table inside DB2. Sounds weird
but it lays the foundation for lots of really good stuff. I would never
willingly go back to using source files, and storing source in the IFS
would be a large step backward as well.
Mike
date: Mon, 13 Mar 2017 09:08:33 -0500
from: David Gibbs <david@xxxxxxxxxxxx>
subject: Where do you store the SQL source for your tables, views,
etc?
Folks:
I'm just curious ... where do you store the SQL source for your
tables, views, etc?
Do you put it in a source PF? If so, what name do you use?
Here, we put SQL source in QSQLSRC.
Thanks!
david
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
________________________________
This email may contain confidential information about a Pennsylvania
College of Technology student. It is intended solely for the use of the
recipient. This email may contain information that is considered an
?educational record? subject to the protections of the Family Educational
Rights and Privacy Act Regulations. The regulations may be found at 34
C.F.R. Part 99 for your reference. The recipient may only use or disclose
the information in accordance with the requirements of the Federal
Educational Rights and Privacy Act Regulations. If you have received this
transmission in error, please notify the sender immediately and permanently
delete the email.
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
------------------------------
End of MIDRANGE-L Digest, Vol 16, Issue 511
*******************************************
midrange.com
