Order of execution of DB2 query to append date in file name and generating FTP card

Question

Could anyone please explain the order of execution of below query?

Below query is used to append the date to file name and generate the corresponding ftp card.

SELECT CHAR('close',80) FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR('   /CSSC/Inbound/AP_MP_P2C_A47_Report/A47_"' ||
         SUBSTR(CHAR(CURRENT DATE,ISO),6,2) ||
         SUBSTR(CHAR(CURRENT DATE,ISO),9,2) ||
         SUBSTR(CHAR(CURRENT DATE,ISO),3,2) || '.TXT',80)
      FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR(
        'put ' ||
        X'27' ||
        'MCSP.WMCS11CD.OPENPOS.TABFILE.TEMP' ||
        X'27' ||
        ' +'
        ,80)
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR('ASCII',80) FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR('quit',80)  FROM SYSIBM.SYSDUMMY1;

The result is

ASCII                                                                           
put 'MCSP.WMCS11CD.OPENPOS.TABFILE.TEMP' +                                      
/CSSC/Inbound/AP_MP_P2C_A47_Report/A47_110914.TXT                                           
close                                                                           
quit

I understood the query but how it came in above order. if I change the line of query, it is giving another output (ie order matters)

Answer 1

As @mustaccio said, there is no order unless you provide an ORDER BY clause. Without that clause, the database engine will pick the "easiest" way to bring back the data. It's not even guaranteed to be the same order between two consecutive runs of the query.

If you need a specific order, then you should add a new field that provides the ordering, and use the ORDER BY clause:

SELECT text FROM (
    SELECT CHAR('ASCII',80), 1 FROM SYSIBM.SYSDUMMY1

        UNION ALL

    SELECT CHAR( 'put ' ||  X'27' ||
            'MCSP.WMCS11CD.OPENPOS.TABFILE.TEMP' ||
            X'27' || ' +' ,80), 2
    FROM SYSIBM.SYSDUMMY1

        UNION ALL

    SELECT CHAR('   /CSSC/Inbound/AP_MP_P2C_A47_Report/A47_' || 
                VARCHAR_FORMAT(CURRENT DATE, 'MMDDYY') || 
                '.TXT',80), 3
          FROM SYSIBM.SYSDUMMY1

    UNION ALL

    SELECT CHAR('close',80), 4 FROM SYSIBM.SYSDUMMY1

        UNION ALL

    SELECT CHAR('quit',80), 5  FROM SYSIBM.SYSDUMMY1
) AS A (text, num)
ORDER BY num

Not that I've changed your DATE substrings to a call to VARCHAR_FORMAT. This scalar function allows you to specify the format for how to convert a date type to a string type. I think it's a lot clearer than having to use substrings.