RE: SQL with variables

RUNSQLSTM command has a DFTRBDCOL() parameter.

Help for this parameter:
Default collection (DFTRDBCOL) - Help

Specifies the name of the schema identifier used for the
unqualified names of the tables, views, indexes, SQL
packages, aliases, constraints, external programs, node
groups, and triggers. This parameter applies only to
static SQL statements.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark S. Waterbury
Sent: Wednesday, September 09, 2009 2:33 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL with variables

Hi, Josh:

Have you tried this?

1. edit "scripts" (text source physical file members) to remove all
qualification (library or schema names) from SQL statements.
2. Issue CHGCURLIB to the library ("schema" or collection in SQL
terminology) where you want to create your tables and views.
3. issue the RUNSQLSTM with the appropriate "script" member.

You could even create a CL program to automate steps 2 and 3 above, if
needed.

This seems to work, regardless of whether *SQL or *SYS naming convention

is used (e.g. on the RUNSQLSTM command).

All the best,

Mark S. Waterbury

Josh Diggs 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?