How to run a SQL statement that is in a column of a table

This is a question that pops up every once in a while, therefore, I have decided to write this post to answer the question. I am sure what I am going to describe here is not the only way to execute a SQL statement that is in a column/field of a table/file. It is just the way I do it. If you know of another way please either comment, below, or use the Contact Form, in the right column, to send me the details.

In the example I am writing about I want to make SQL Aliases for each member in the multiple member file TESTFILE. I have created a CTE to do this, but what I am going to do is to show the individual SQL statements that make up the CTE before I show the CTE statement.

Firstly, I need to establish a list of members in TESTFILE. Here I can use the new SQL view SYSMEMBERSTAT. I am only interested in returning the following columns in the results:

1. SYSTEM_TABLE_SCHEMA:  Library the file is in
2. SYSTEM_TABLE_NAME:  File name
3. SYSTEM_TABLE_MEMBER:  Member name

This is the statement I used to show the members in this file:

01 SELECT SYSTEM_TABLE_SCHEMA, 02 SYSTEM_TABLE_NAME, 03 SYSTEM_TABLE_MEMBER 04 FROM QSYS2.SYSMEMBERSTAT 05 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 06 AND SYSTEM_TABLE_NAME = 'TESTFILE'

Which returns the following:

SYSTEM_TABLE_SCHEMA SYSTEM_TABLE_NAME SYSTEM_TABLE_MEMBER ------------------- ----------------- ------------------- MYLIB TESTFILE FIRST MYLIB TESTFILE SECOND MYLIB TESTFILE THIRD

The format that the CREATE ALIAS statement will be in is:

CREATE ALIAS QTEMP.<alias_name> FOR <file_library>.<file_name> (<member_name>)

I can only think of a couple of Aliases I have need to retain for longer than a particular job. Most of the Aliases I create I use in a job, and then do not need them until the job is run again. Therefore, I build these temporary Aliases in the library QTEMP.

I can build a statement for building Aliases for TESTFILES members with the following SQL statement:

01 SELECT 'CREATE OR REPLACE ALIAS QTEMP.' || 02 TRIM(SYSTEM_TABLE_NAME) || '_' || 03 TRIM(SYSTEM_TABLE_MEMBER) || ' FOR ' || 04 TRIM(SYSTEM_TABLE_SCHEMA) || '.' || 05 TRIM(SYSTEM_TABLE_NAME) || '(' || 06 TRIM(SYSTEM_TABLE_MEMBER) || ')' 07 FROM QSYS2.SYSMEMBERSTAT 08 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 09 AND SYSTEM_TABLE_NAME = 'TESTFILE'

Lines 1 – 6: I am concatenating columns I showed in my previous results from SYSMEMBERSTAT to make the CREATE ALIAS statement. Rather than have the CREATE I prefer to use the CREATE OR REPLACE for many SQL objects.

I always use the double pipe symbols ( || ) instead of the CONCATENATE or CONCAT as it, IMHO, makes the statement easier to understand. Be warned that if you move this statement from one IBM i partition to another that is using a different CCSID the || could be translated to some other characters. All of my partitions are CCSID 37, so this is not a problem.

I prefer my Alias name to be a combination of the File and Member names, separated by an underscore character ( _ ).

The results of this statement are:

ALIAS_STATEMENT ------------------------------------------------------------------------ CREATE OR REPLACE ALIAS QTEMP.TESTFILE_FIRST FOR MYLIB.TESTFILE(FIRST) CREATE OR REPLACE ALIAS QTEMP.TESTFILE_SECOND FOR MYLIB.TESTFILE(SECOND) CREATE OR REPLACE ALIAS QTEMP.TESTFILE_THIRD FOR MYLIB.TESTFILE(THIRD)

I need to put the results into a column in a SQL table, otherwise, I cannot show what I want in this example. This SQL statement does that:

01 CREATE TABLE QTEMP.OUTFILE 02 (ALIAS_STATEMENT) 03 AS 04 (SELECT 'CREATE OR REPLACE ALIAS QTEMP.' || 05 TRIM(SYSTEM_TABLE_NAME) || '_' || 06 TRIM(SYSTEM_TABLE_MEMBER) || ' FOR ' || 07 TRIM(SYSTEM_TABLE_SCHEMA) || '.' || 08 TRIM(SYSTEM_TABLE_NAME) || '(' || 09 TRIM(SYSTEM_TABLE_MEMBER) || ')' 10 FROM QSYS2.SYSMEMBERSTAT 11 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 12 AND SYSTEM_TABLE_NAME = 'TESTFILE') 13 WITH DATA

Line 1: My SQL Table will be called OUTFILE and will be created in the library QTEMP.

Line 2: This is the column list for the Table. It will only contain one column.

Line 3: I need the AS as that "tells" SQL that the statement that follows is what builds the Table.

Lines 4 - 12: This is identical to the statement I used before to create the Alias names.

Line 13: I want the results of the SQL statement to be the results in this file, therefore, WITH DATA is needed.

I can use the following SQL statement to view the contents of the generated Table:

SELECT * FROM QTEMP.OUTFILE

Which shows:

ALIAS_STATEMENT ------------------------------------------------------------------------ CREATE OR REPLACE ALIAS QTEMP.TESTFILE_FIRST FOR MYLIB.TESTFILE(FIRST) CREATE OR REPLACE ALIAS QTEMP.TESTFILE_SECOND FOR MYLIB.TESTFILE(SECOND) CREATE OR REPLACE ALIAS QTEMP.TESTFILE_THIRD FOR MYLIB.TESTFILE(THIRD)

I am going to use the RUNSQL command within the QCMDEXC scalar function.

The RUNSQL command does not run every type of SQL statement, just enough to be useful. I am putting a link to the RUNSQL page in IBM's documentation at the bottom of this post so you can see which statements it can execute.

My statement to build the Aliases is:

01 SELECT ALIAS_STATEMENT, 02 QSYS2.QCMDEXC('RUNSQL SQL(''' || ALIAS_STATEMENT || ''')') 03 AS "Return code" 04 FROM QTEMP.OUTFILE

Lines 2 and 3: This is where I am using the RUNSQL command within the QCMDEXC scalar function. If need to execute the SQL statement within ALIAS_STATEMENET, therefore, I need to concatenate the ALIAS_STATEMENT within the RUNSQL. What this does is makes the statement be:

01 SELECT ALIAS_STATEMENT, 02 QSYS2.QCMDEXC('RUNSQL SQL(''CREATE OR REPLACE ALIAS QTEMP.TESTFILE_FIRST + FOR MYLIB.TESTFILE(FIRST)'' 03 AS "Return code" 04 FROM QTEMP.OUTFILE

The results of the SQL statement are:

Return ALIAS_STATEMENT code ------------------------------------------------------------------------ ------- CREATE OR REPLACE ALIAS QTEMP.TESTFILE_FIRST FOR MYLIB.TESTFILE(FIRST) 1 CREATE OR REPLACE ALIAS QTEMP.TESTFILE_SECOND FOR MYLIB.TESTFILE(SECOND) 1 CREATE OR REPLACE ALIAS QTEMP.TESTFILE_THIRD FOR MYLIB.TESTFILE(THIRD) 1

The Return code of "1" show that the statement within the QCMDEXC scalar function completed successfully.

I want to combine all of the above into a RPG program so I can call it whenever I need to. To do that I need to be able to pass the library and file name to the CTE. I cannot use a RPG variable in the CTE's statement, but I can use value from SQL Global Variables. I happen to have a couple of Global Variables already created that will prove useful:

1. MYLIB.MY_LIBRARY:  Library name
2. MYLIB.MY_FILE:  File name

I used the following SQL statements to create them:

01 CREATE OR REPLACE VARIABLE MYLIB.MY_LIBRARY VARCHAR(10) ; 02 CREATE OR REPLACE VARIABLE MYLIB.MY_FILE VARCHAR(10) ;

The advantage of using Global Variables is that any value I move into them can only be "seen" by the current job, all other jobs either "see" the default value or whatever values they moved into them.

My RPG program looks like:

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-s ThisLibrary char(10) inz('MYLIB') ; 04 dcl-s ThisFile char(10) inz('TESTFILE') ; 05 exec sql DROP TABLE IF EXISTS QTEMP.CREATE_ALIASES ; 06 exec sql SET MYLIB.MY_LIBRARY = :ThisLibrary ; 07 exec sql SET MYLIB.MY_FILE = :ThisFile ; 08 exec sql CREATE TABLE QTEMP.CREATE_ALIASES 09 FOR SYSTEM NAME "CRTALIASES" 10 (ALIAS_STATEMENT,RETURN_CODE) AS 11 (WITH T0(ALIAS_STATEMENT) AS 12 (SELECT 'CREATE OR REPLACE ALIAS QTEMP.' || 13 TRIM(SYSTEM_TABLE_NAME) || '_' || 14 TRIM(SYSTEM_TABLE_MEMBER) || ' FOR ' || 15 TRIM(SYSTEM_TABLE_SCHEMA) || '.' || 16 TRIM(SYSTEM_TABLE_NAME) || '(' || 17 TRIM(SYSTEM_TABLE_MEMBER) || ')' 18 FROM QSYS2.SYSMEMBERSTAT 19 WHERE SYSTEM_TABLE_SCHEMA = MYLIB.MY_LIBRARY 20 AND SYSTEM_TABLE_NAME = MYLIB.MY_FILE) 21 SELECT ALIAS_STATEMENT, 22 QSYS2.QCMDEXC('RUNSQL SQL(''' || 23 T0.ALIAS_STATEMENT || 24 ''')') 25 FROM T0) WITH DATA ; 26 *inlr = *on ;

Lines 3 and 4: To keep this example simple these two variables contain the library and files names. If this was for production these two would be passed to the program.

Line 5: If the output Table exists, delete it.

Lines 6 and 7: I am moving the values for the Library and File names from the RPG variables into the SQL Global variables.

Lines 8 – 25: As the QCMDEXC scalar function returns a value I need somewhere to place those returned codes. I am going to create a Table so that the Create Alias statement and the Return code are stored.

Lines 8 and 9: My Table has a long name, CREATE_ALIASES, and a short system name, CRTALIASES.

Line 10: The Table will only have two columns. I think the column names accurately describe what they will contain.

Lines 11 - 20: Definition of the temporary table, T0, that the first SQL "statement" in the CTE generates. This "statement" is the same as the one I showed to generate the Create Alias statements.

Lines 21 – 25: The second "statement" is identical to the one I showed before to create the Aliases.

Line 25: I need WITH DATA so that CREATE_ALIASES contains the output from the CTE. Which is:

Return ALIAS_STATEMENT code ------------------------------------------------------------------------ ------- CREATE OR REPLACE ALIAS QTEMP.TESTFILE_FIRST FOR MYLIB.TESTFILE(FIRST) 1 CREATE OR REPLACE ALIAS QTEMP.TESTFILE_SECOND FOR MYLIB.TESTFILE(SECOND) 1 CREATE OR REPLACE ALIAS QTEMP.TESTFILE_THIRD FOR MYLIB.TESTFILE(THIRD) 1

I am sure you can think of simpler examples of using the same approach to executing SQL statements contained in columns/fields in Tables/Files.

 

You can learn more about the RUNSQL command from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.