Using the same AS400 QRY in Dev, Test and Prod
I had a good question from a subscriber today:
We have a production environment and a test environment on our IBM i. We have processes in place to switch library lists depending on which environment we are in. We have issues where our test side processes and reports are inaccurate due to the queries that run behind the scenes. These queries are using the production environment libraries because there is no way to use *libl in the query. What options do we have to eliminate this issue?
This is a common situation that I’ve seen in many sites that use the good old AS400 and iSERIES Query functions.
The problem with old QUERY functions is that the library (Schema) for each file (table) is found at time of query creation and this library is attached to the query when it is created.
For example: if I create a query called QUERYNAME in my development library over a file called “CUSTOMERS“, it will look for a file called customers in the jobs *LIBL and set that file as the one that it is querying. If I move the query to another library it will still be scoped over the original version of “customers” even if there is a new version of “customers” in the new library.
We don’t want to recreate a different version of each query for each environment.
We dont have time to rewrite all these processes into SQL or RPG either.
Is there a way to force the test queries to use the test files?
Is an override in the CL a safe step or is there a better way?
The answer is YES
You could change the CL that use the RUNQRY statement.
RUNQRY QRY(QUERYNAME)to
RUNQRY QRY(QUERYNAME) QRYFILE(*LIBL/CUSTOMERS) This forces the query to use the file from the *LIBL.
Now it will work in each environment, using the copy of CUSTOMERS that it finds in each environment
