Re: SQL with variables

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

In my SQL create scripts (which are stored in the production library), I
actually have my test library ("JEFF") hardcoded, not the production
library.  If I somehow screw up and run the script, it won't affect
production.  Like this:

/*    *_> CNLLSTSPLF SRCFILE(@2/@1) +         */
/*    *_>                      SRCMBR(@3)                  */
/*    *_> RUNSQLSTM SRCFILE(@2/@1)  +        */
/*    *_>                      SRCMBR(@3) +              */
/*    *_>                      COMMIT(*NC)                 */

-- DROP TABLE JEFF/ACCSCNT ;

CREATE TABLE JEFF/ACCSCNT (
  IPNAME     CHAR(40) CCSID 37  NOT NULL DEFAULT '' ,

etc.  The commented DROP statement is a help also.

Alan, see anything familiar in there?  :)

        On Wed, Sep 9, 2009 at 3:01 PM, Josh Diggs <JDiggs@xxxxxxxxxxxx>wrote:

> I am looking for a way to simplify building logical files defined by sql
> create view statements.
>
> Currently we use runsqlstm and specify a source file and member.  This
> seems to work fine except when I do something like re-create a test
> environment.  In order to insure that all of the logicals point to the
> correct physical, I had to go through and edit the sql source for every file
> in order to change libraries that were called out in the sql statement.  I
> could let this fall through to the library list, but I prefer to call the
> libraries out explicitly.
>
> Two approaches have come to mind in order to achieve this goal.  One idea
> was to use query management queries.  This allows the use of variables, but
> it appears that a query management object has to be recompiled after each
> change before the actual query can be run.  I was hoping for something a
> little simpler.
>
> Second, I thought of using the db2 command from qshell and setting
> environment variables in order to substitute the appropriate libraries.  I
> favor this approach because it puts the source into text files in the IFS
> which are easier to integrate into other tools.  I got stuck on how best to
> perform the library substitutions into a predifined sql statement in a
> standard way that would allow the entire process to be scripted.
>
> How have others solved this problem?
>
>
>
> --
> 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.