IDMS: Minimizing SQL column gaps in IDMSBCF output

Products

• IDMS

Issue/Introduction

With SQL issued within IDMSBCF, the gaps between column output can appear large especially if the data is character variables where the actual visible data is shorter than the definition.

Example:

SELECT EMP_ID AS ID, EMP_FNAME AS FN,
 EMP_LNAME AS LN, STREET AS ST
FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID=1003;
*+
*+     ID  FN                    LN
*+     --  --                    --
*+   1003  James                 Baldwin
*+
*+ ST
*+ --
*+ 21 South St
*+
*+ 1 row processed

How can these gaps be minimized?

Environment

Release : All supported releases.
Component : CA IDMS/DB

Cause

IDMSBCF uses the column definition to decide how to space the columns, and where character data is concerned, that can be large.

Resolution

The first temptation is to use the TRIM() built-in function. However, this only alters the length of the actual data in each column, not the definition. So it has no effect.

SELECT EMP_ID AS ID, TRIM(EMP_FNAME) AS FN,
 TRIM(EMP_LNAME) AS LN, TRIM(STREET) AS ST
FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID=1003;
*+
*+     ID  FN                    LN
*+     --  --                    --
*+   1003  James                 Baldwin
*+
*+ ST
*+ --
*+ 21 South St
*+
*+ 1 row processed

However, the CAST() function alters the implicit definition of the output column data, and so IDMSBCF displays them as shorter columns.

SELECT EMP_ID AS ID,
 CAST(TRIM(EMP_FNAME) AS CHAR(5)) AS FN,
 CAST(TRIM(EMP_LNAME) AS CHAR(8)) AS LN,
 CAST(TRIM(STREET) AS CHAR(12)) AS ST
FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID=1003;
*+
*+     ID  FN     LN        ST
*+     --  --     --        --
*+   1003  James  Baldwin   21 South St
*+
*+ 1 row processed