Capturing SQL results into a data area

The idea for this post came from a Facebook thread. The question was: "Does anyone know of a way to insert the result of an SQL SELECT into a CL Variable? I can make an SQL select into a table and read (RCVF) the file of course... But can it be done in a more "direct" way?" One of the replies to the question piqued my interest.

Before I start showing examples of code I needed a SQL statement that anyone with a recent release of IBM i could execute in their partitions. I came up with retrieving the record length for the SYSDUMMY1 table. I find the easiest way to get that information is to use the SYSFILES SQL View.

01 SELECT MAXIMUM_RECORD_LENGTH 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'SYSIBM' 04 AND SYSTEM_TABLE_NAME = 'SYSDUMMY1'

Line 1: I only want to retrieve the record length column.

Line 2: I use the SYSFILES View.

Lines 3 and 4: I want the data for the table SYSDUMMY1, which is in the library SYSIBM.

The result is:

MAXIMUM_RECORD_LENGTH --------------------- 3

If I wanted to get this information I would have created a table, I know that is not what was asked for, but at the time it was the only way I could think to do this in a CL program:

01 DCLF FILE(QTEMP/TEMPTABLE) 02 DLTF FILE(QTEMP/TEMPTABLE) 03 MONMSG MSGID(CPF2105) 04 RUNSQL COMMIT(*NONE) + 05 SQL('CREATE TABLE QTEMP.TEMPTABLE AS + 06 (SELECT MAXIMUM_RECORD_LENGTH + 07 FROM QSYS2.SYSFILES + 08 WHERE SYSTEM_TABLE_SCHEMA = ''SYSIBM'' + 09 AND SYSTEM_TABLE_NAME = ''SYSDUMMY1'') + 10 WITH DATA') 11 RCVF

Line 1: I will need to "read" the Table I am going to create, therefore, I need to declare it.

Line 2: Delete the file if it already exists in the library QTEMP.

Lines 4 – 10: I am creating a new SQL Table based on the results of a SQL statement.

Line 11: I would "read" the Table using the Receive File Command, RCVF.

To be able to compile this program I need a copy of the TEMPTABLE in QTEMP.

Richard Carrizo posted an answer on the thread explaining that there is a way to output from a SQL statement into a Data Area. I have to admit I have never heard of this, so I know I had to try it. This is my version of Richard's example statement in a CL program:

01 DCL VAR(&VARIABLE) TYPE(*CHAR) LEN(3) 02 DLTDTAARA DTAARA(QTEMP/TEMPDA) 03 MONMSG MSGID(CPF2105) 04 RUNSQL COMMIT(*NONE) + 05 SQL('CALL QSYS2.QCMDEXC(''+ 06 CRTDTAARA DTAARA(QTEMP/TEMPDA) TYPE(*CHAR) LEN(2000) + 07 VALUE('''''' || TRIM((+ 08 SELECT MAXIMUM_RECORD_LENGTH + 09 FROM QSYS2.SYSFILES + 10 WHERE SYSTEM_TABLE_SCHEMA = ''SYSIBM'' + 11 AND SYSTEM_TABLE_NAME = ''SYSDUMMY1'' + 12 )) || '''''')+ 13 '')') 14 RTVDTAARA DTAARA(QTEMP/TEMPDA (1 3)) RTNVAR(&VARIABLE)

I am going to retrieve the value from the data area at the end of this program, therefore, I need a variable to contain the result.

Line 2: Delete the data area TEMPDA if is already exists in the library QTEMP.

Lines 4 – 13: This is the Run SQL command, RUNSQL, that is going to create the data area.

Line 5: I am using the SQL QCMDEXC procedure to create the data area.

Line 6: This data area is called TEMPDA, it will be in QTEMP, it is a character type, and has a length of 2,000 characters which is the largest a data area can be.

Line 7: This is where it can get confusing, so bear with me. The value parameter of the command is where I give the data area its value. I am going to get that value from a SQL Select statement. The statement must return only one result. The Value parameter starts off with six apostrophes ( ' ). It is followed by the double pipe ( || ), which is the shortcut for concatenation. I have to admit I have no idea what the 'TRIM' is. I have looked in the IBM Documentation website and Googled without success. I have reached out to Richard, and at the time I am writing this he has not replied. The trim is followed by two opening parentheses ( '(' ).

Lines 8 – 11: This is the same SQL statement I have used in my previous two examples.

Line 12: Closing parentheses, double pipe for concatenation, and the six apostrophes that all close what was started on line seven.

Line 13: First close the QCMDEXC and then the RUNSQL SQL parameter.

Line 14: Retrieve the result into the variable &VARIABLE.

After compiling the above and I call it if I put a breakpoint after the RTVDTAARA command and check for the value in &VARIABLE I get:

&VARIABLE = '3 '

If I want to retrieve different information I can just change the command within the Value parameter to Change Data Area command, CHGDTAARA. In this case I am going to use the SYSCOLUMNS View to retrieve the size of the only column in SYSDUMMY1 Table:

15 RUNSQL COMMIT(*NONE) + 16 SQL('CALL QSYS2.QCMDEXC(''+ 17 CHGDTAARA DTAARA(QTEMP/TEMPDA) + 18 VALUE('''''' || TRIM((+ 19 SELECT STORAGE + 20 FROM QSYS2.SYSCOLUMNS + 21 WHERE SYSTEM_TABLE_SCHEMA = ''SYSIBM'' + 22 AND SYSTEM_TABLE_NAME = ''SYSDUMMY1'' + 23 AND SYSTEM_COLUMN_NAME = ''IBMREQD'' + 24 )) || '''''')+ 25 '')') 25 RTVDTAARA DTAARA(QTEMP/TEMPDA (1 3)) RTNVAR(&VARIABLE)

Line 17: I would use a Change Data Area command, CHGDTAARA, as the data area already exists.

Lines 19 – 23: The SQL statement to get the storage size of the IBMREQD column from the SYSCOLUMNS View.

What is the advantage of using a data area? The data area does not have to exist when I compile the CL program.

What is the disadvantage? It can only contain one result.

I can certainly think of places I could use the data area for a SQL result approach rather than a Table.

If you know what the "TRIM(( ... ))" means please let me know in the comments below.

Thank you Richard for your example.

 

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