Pages

Wednesday, March 13, 2024

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.

5 comments:

  1. It looks like the SQL built-in function. https://www.ibm.com/docs/en/i/7.5?topic=functions-trim

    ReplyDelete
  2. TRIM() will convert the integer return value into a varchar() for storage into the character data area.

    -Matt

    ReplyDelete
  3. I don't understand why you wrote it's limited to contain only one result. You can catenate multiple SELECT statements together. e.g.
    RUNSQL SQL('CALL QSYS2.QCMDEXC(''CRTDTAARA +
    DTAARA(QTEMP/TEMPDA) TYPE(*CHAR) +
    LEN(2000) VALUE('''''' || TRIM((SELECT +
    MAXIMUM_RECORD_LENGTH FROM QSYS2.SYSFILES +
    WHERE SYSTEM_TABLE_SCHEMA = ''SYSIBM'' +
    AND SYSTEM_TABLE_NAME = ''SYSDUMMY1'')) +
    concat +
    TRIM((SELECT STORAGE FROM +
    QSYS2.SYSCOLUMNS WHERE +
    SYSTEM_TABLE_SCHEMA = ''SYSIBM'' AND +
    SYSTEM_TABLE_NAME = ''SYSDUMMY1'' AND +
    SYSTEM_COLUMN_NAME = ''IBMREQD'' )) || +
    '''''')'')') COMMIT(*NONE)
    Although I much prefer to use separate commands with the first using CRTDTAARA and subsequent calls using CHGDTAARA with different start positions. You can build a better defined data structure.
    RUNSQL SQL('CALL QSYS2.QCMDEXC(''CHGDTAARA +
    DTAARA(QTEMP/TEMPDA (4 3)) VALUE('''''' || +
    TRIM((SELECT STORAGE FROM +
    QSYS2.SYSCOLUMNS WHERE +
    SYSTEM_TABLE_SCHEMA = ''SYSIBM'' AND +
    SYSTEM_TABLE_NAME = ''SYSDUMMY1'' AND +
    SYSTEM_COLUMN_NAME = ''IBMREQD'' )) || +
    '''''')'')') COMMIT(*NONE)

    ReplyDelete
    Replies
    1. That had not even occurred to me to do that.
      Thank you for sharing.

      Delete
  4. At this point it's probably better to go with an sql member and runsqlstm instead of fighting with apostrophes.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.