Changes to the SELECT command

In previous versions of the server, the SELECT command allowed syntax that did not always conform to SQL syntax rules. With V7, the server conforms to SQL syntax rules in use by its database manager, the DB2® program. Some examples illustrate changes that you might need to make to SELECT statements that you use.

LIKE predicate for a nested SELECT statement

You cannot use the LIKE predicate for a nested SELECT statement. For example, you receive an error if you use the LIKE predicate as in this statement: 
select * from volumeusage where volume_name like (select distinct volume_name 
from volumeusage where node_name='node1')
Replace such usage with the in parameter, as in this statement:
select * from volumeusage where volume_name in (select distinct volume_name 
from volumeusage where node_name='node1')

SELECT statements for time calculation

Labeled duration cannot be compared in a SELECT statement. For example, the following statement results in an SQL error:
select * from actlog where (current_time-date_time) seconds <= 60 seconds
The following statements are examples of correct usage.
To list the activity log entries for the last 60 seconds, use a statement similar to this one:
select * from actlog where
TIMESTAMPDIFF(2,CHAR(current_timestamp-date_time)) <= 60
To list the activity log entries for the last 60 minutes, use a statement similar to this one:
select * from actlog where
TIMESTAMPDIFF(4,CHAR(current_timestamp-date_time)) <= 60

The index_keyseq and index_order columns

The system catalog tables SYSCAT.COLUMNS and SYSCAT.TABLES are now processed by the database manager, instead of directly by the Tivoli® Storage Manager server. The INDEX_KEYSEQ and INDEX_ORDER columns are not available. Use the KEYSEQ column instead. For instructions, go to the DB2 documentation and enter keyseq as the search term.

Access to database objects by using the SELECT command

The database objects that can be accessed by using the SELECT command are the same as for earlier versions of the server, with some additions for new functions.

However, the SYSCAT.COLUMNS and SYSCAT.TABLES catalog tables now include all database objects that are known to the server, including some objects that cannot be accessed through the SELECT command. You receive an error message if a SELECT command includes an attempt to access one of these objects.

Retrieval of information from more than one database table

To retrieve information from more than one table, use a join process. Many types of join processes can be used.

For example, the following command, which worked with earlier versions of the server, no longer works:
select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw)
from summary where entity in (select node_name from nodes) and
cast((current_timestamp-start_time)hours as decimal)<24 group by entity,activity
You can declare names for columns that are retrieved from multiple tables so that a conditional statement can be run with the results that you want from the SELECT command. For example: 
select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw)
from summary su, nodes nd where su.entity=nd.node_name and
cast((current_timestamp-start_time)hours as decimal)<24 group by entity,activity

Results of the SELECT command for the DISK device class

Results when you use the SELECT command to get information from the DEVCLASSES table have changed slightly for the DISK device class.

In previous releases, the SHARED field was blank (null) for the DISK device class. Now, the SHARED field contains the value NO. The SHARED field does not apply to the DISK device class, and the value NO can be ignored.

Extra spaces in output

If the output includes trailing spaces, such as in the following tabschema output example, you can use the RTRIM scalar function to remove them.
dsmadmc -errorlogn=errorlog -id=admin -pa=admin -comma -dataonly=y 
    'select tabschema,tabname from tables'

SYSCAT  ,ATTRIBUTES
SYSCAT  ,AUDITPOLICIES
SYSCAT  ,AUDITUSE

For example, if you are writing scripts for automation and must strip out the additional spaces, you can use the RTRIM scalar function:

select rtrim(tabschema) as tabschema, tabname from syscat.tables

Data types for arithmetic operations

Changes in how data types for arithmetic operations are handled might require changes to SELECT commands that worked in earlier versions of the server.

For example, the following command causes an arithmetic overflow error because of the SUM statement:
select node_name,sum(capacity) as capacity,
sum(capacity * (pct_util/100)) as used from filespaces group by node_name

To make the command compatible with V7.1.1, add the CAST function to convert the items in the SUM statement to decimal data types:

select node_name,sum(capacity) as capacity,
sum(cast(capacity as decimal) * cast((pct_util/100) as decimal)) as used from 
filespaces group by node_name