Most tools that executes dynamic SQL (SPUFI, QMF, DSNTIAUL and others) displays a row as one long record. The consequence is that as soon as a returned row is more than 80 characters wide you have to scroll to the right in order to see the rest of the contents. You may change your emulator to work with a larger screen width, but the screen width is not unlimited.

Some columns has a width that is larger than the screen width. For such columns it can be very annoying to scroll left and right several times in order to get the correct impression of what the column contains. QMF has formatting rules that solves this problem, but in SPUFI and other tools that returns the SQL result in a dataset you have to use other methods to cope with the challenge. A solution is the following SQL statement:

SELECT key, substr(longcolumn, colno * 64 - 63, 64)
FROM mytable
   , (SELECT colno FROM SYSIBM.SYSCOLUMNS
      WHERE tbcreator = 'SYSIBM' and tbname = 'SYSTABLES'
        AND colno in (1,2,3,4)
     ) no
WHERE col1 = 'SOMEVALUE'
ORDER BY key, colno

In the above example LONGCOLUMN may be a VARCHAR(254) or a CHAR(254) Therefore I have chosen to split the column into four rows specified by IN (1,2,3,4) with 64 characters in each row. I know that 4 * 64 is 256, but this will not cause any errors. You will only receive errors if colno * number of characters (in the example colno * 64) is greater than the maximum length of the column. The contents of the WHERE clause does not matter, but the ORDER BY must contain colno as the last sort column.

I have chosen SYSIBM.SYSCOLUMNS as the second table in the FROM clause, because it contains column COLNO which always has the values 1 to N for all tables. I just need to select a table from SYSIBM.SYSCOLUMNS that I know exists. You can choose any table you like containing a numeric column having values from 1 to N. You can even create a special table for the above purpose.