IBM Support

Formatting select command output to return in table format

Troubleshooting


Problem

A select command is run on a Tivoli Storage Manager V6 server and its output is returned in a list format whereas a table format is expected.

Cause

Working as designed

Diagnosing The Problem

An example of a select command that may return output in a list format on a TSM V6 server follows :

SELECT cast((STGPOOLS.STGPOOL_NAME) as char(20)) as "STGPOOL_NAME", STGPOOLS.MAXSCRATCH, Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH", STGPOOLS.MAXSCRATCH-count(STGPOOLS.MAXSCRATCH) as "Remaining_SCRATCH" FROM STGPOOLS,VOLUMES WHERE (VOLUMES.STGPOOL_NAME = STGPOOLS.STGPOOL_NAME) GROUP BY STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH

Example output :

STGPOOL_NAME: ARCHIVEPOOL
MAXSCRATCH:
Allocated_SCRATCH: 0
Remaining_SCRATCH:

STGPOOL_NAME: BACKUPPOOL
MAXSCRATCH:
Allocated_SCRATCH: 0
Remaining_SCRATCH:

STGPOOL_NAME: FILEPOOL
MAXSCRATCH: 100
Allocated_SCRATCH: 11
Remaining_SCRATCH: 89

The select output is displayed in a list format rather than table.

Resolving The Problem

For a V6 server, the column widths are based on the column width in the database and determine the length of the output line. If the output does not fit on a single line, a list format output is used.
To obtain a table format with the same command, the cast function can be used to restrict the column width such that the entire output fits on a single line. Long character columns are typically the columns causing a select output to not fit on a single line.

In the above example, the STGPOOL_NAME column is of type varchar(31) and therefore 31 characters long. The cast function may be used to reduce the column width. For example, to reduce the column width to 20, use :


SELECT cast((STGPOOLS.STGPOOL_NAME) as char(20)) as "STGPOOL_NAME", STGPOOLS.MAXSCRATCH, Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH", STGPOOLS.MAXSCRATCH-count(STGPOOLS.MAXSCRATCH) as "Remaining_SCRATCH" FROM STGPOOLS,VOLUMES WHERE (VOLUMES.STGPOOL_NAME = STGPOOLS.STGPOOL_NAME) GROUP BY STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH


Any storage pool name longer than 20 characters would however be truncated to 20 characters. To avoid possible truncation, use the max function to find out the maximum length of the stgpool_name column. For example :


tsm: TSM62>select max(length(stgpool_name)) from stgpools

Unnamed[1]
------------
11


In the above example, the maximum length of the stgpool_name value is 11. This maximum value may be used with the cast function to limit the column width. For example :


tsm: TSM62>SELECT cast((STGPOOLS.STGPOOL_NAME) as char(11)) as "STGPOOL_NAME", STGPOOLS.MAXSCRATCH, Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH", STGPOOLS. MAXSCRATCH-count(STGPOOLS.MAXSCRATCH) as "Remaining_SCRATCH" FROM STGPOOLS,VOLUMES WHERE (VOLUMES.STGPOOL_NAME = STGPOOLS.STGPOOL_NAME) GROUP BY STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH

STGPOOL_NAME MAXSCRATCH Allocated_SCRATCH Remaining_SCRATCH
------------- ------------ ------------------ ------------------
ARCHIVEPOOL 0
BACKUPPOOL 0
FILEPOOL 100 11 89


Note that when redirecting the output to a file using the -outfile option with the Tivoli Storage Manager administrator command line, the output will be in a table format, without having to limit the column width as with above.

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Server","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Supported Versions","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
17 June 2018

UID

swg21502715